How to check the recovery catalog users configuration. (81574)

Date - APR 2009
Affected Product & Version - NVBU 8.2.1
Affected Module & Version - Oracle APM 6.0
OS Version - N/A
Application Information - Oracle 9i, 10g, 11g

When you configure the rman user the manual tells you to run the following commands.

CREATE USER rman IDENTIFIED BY <password>
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tools
QUOTA UNLIMITED ON tools;
GRANT RECOVERY_CATALOG_OWNER TO rman;
GRANT CONNECT TO rman;

To check that these settings have been made you can run these commands from sqlplus on the server where the recovery catalog is.

select USERNAME, PROFILE, DEFAULT_TABLESPACE, ACCOUNT_STATUS from DBA_USERS where USERNAME='RMAN';
select * from DBA_TS_QUOTAS where USERNAME='RMAN';
select * from DBA_ROLE_PRIVS where GRANTEE='RMAN';
select * from DBA_PROFILES where profile='DEFAULT';

SQL> select username, profile, default_tablespace, account_status from DBA_USERS where USERNAME='RMAN';

USERNAME PROFILE
------------------------------ ------------------------------
DEFAULT_TABLESPACE ACCOUNT_STATUS
------------------------------ --------------------------------
RMAN DEFAULT
EXAMPLE OPEN


This shows which profile the RMAN user has, its default tablespace and if the account is open. The profile name will be used later in one of the commands.


SQL> select * from DBA_TS_QUOTAS where USERNAME='RMAN';

TABLESPACE_NAME USERNAME BYTES
------------------------------ ------------------------------ ----------
MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------- ---------- ---------- ---
EXAMPLE RMAN 8716288
-1 1064 -1 NO

Here we should see the name of the tablespace where the recovery catalog resides, and the MAX_BYTES and MAX_BLOCKS should be -1. The TABLESPACE_NAME listed here should match the DEFAULT_TABLESPACE from the previous command.


SQL> select * from DBA_ROLE_PRIVS where GRANTEE='RMAN';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RMAN RECOVERY_CATALOG_OWNER NO YES
RMAN CONNECT NO YES

This shows that they granted the RECOVERY_CATALOG_OWNER and CONNECT roles to the user RMAN.


SQL> select * from DBA_PROFILES where profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL
UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL
UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL
UNLIMITED


PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT CPU_PER_CALL KERNEL
UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL
UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL
UNLIMITED


PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT IDLE_TIME KERNEL
UNLIMITED

DEFAULT CONNECT_TIME KERNEL
UNLIMITED

DEFAULT PRIVATE_SGA KERNEL
UNLIMITED


PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
10

DEFAULT PASSWORD_LIFE_TIME PASSWORD
UNLIMITED

DEFAULT PASSWORD_REUSE_TIME PASSWORD
UNLIMITED


PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_REUSE_MAX PASSWORD
UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD
UNLIMITED


PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_GRACE_TIME PASSWORD
UNLIMITED


16 rows selected.

SQL>

If the profile listed from the first command isn't DEFAULT, replace DEFAULT with the name of the profile RMAN has. There may be some limits set here which will cause problems.