Oracle Database and Linux Support Articles

Monday, June 17, 2019

RMAN-20005: target database name is ambiguous

RMAN Operation Fails With RMAN-20005: target data base name is ambiguous (Doc ID 224352.1)

RMAN-03002: failure of resync from db_unique_name command

RMAN catalog. In case you are not connected to the RMAN catalog you end up with the following error:

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of show command at 02/21/2017 13:58:53

RMAN-05037: FOR DB_UNIQUE_NAME option cannot be used in nocatalog mode

After connecting to the catalog, you can use this feature, e.g. to show the archive deletion policy.

$ rman target sys/welcome1 catalog /@rcat

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 21 14:25:10 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=644484523)

connected to recovery catalog database

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE1';

RMAN configuration parameters for database with db_unique_name DBIT121_SITE1 are:

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK;

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

RMAN configuration parameters for database with db_unique_name DBIT121_SITE2 are:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

There are quite a lot options which can be combined with the DB_UNIQUE_NAME feature like the following.

LIST ARCHIVELOG ALL FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

REPORT SCHEMA FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

SHOW ALL FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

But getting back to my issue. I was running a resync catalog from my Standby database and ended up with the following error:

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME 'DBIT121_SITE1';

resyncing from database with DB_UNIQUE_NAME DBIT121_SITE1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of resync from db_unique_name command on default channel at 02/21/2017 13:08:42

RMAN-20005: target database name is ambiguous

RMAN says that the target database name is ambiguous. But what does this mean. Let’s take a look a the RMAN error with the oerr utility. The oerr utility can not only be used with “ORA” error codes like “oerr ora 01555”, but also with “RMAN” error codes.

$ oerr rman 20005

20005, 1, "target database name is ambiguous"

// *Cause: two or more databases in the recovery catalog match this name

// *Action:

//

Ok. This error is much more precise. Looks like that RMAN found more the one database called DBIT121 in the catalog, and so RMAN does not know, on which DBID to perform the requested command. Ok. So let’s connect to the RMAN catalog and check if this is really the case.

SQL> SELECT DB.DB_KEY,DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME

        FROM DB, DBINC

       WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY

         AND DBINC.DB_NAME   = 'DBIT121' ;  2    3    4

    DB_KEY      DB_ID CURR_DBINC_KEY DB_NAME

---------- ---------- -------------- --------

         1  642589239              2 DBIT121

    546780  644484523         546781 DBIT121

Indeed. I do have two different DBID’s pointing to the same DB_NAME. Kinda confusing for RMAN. But which one is the one that have been backed up. We could query the RC_BACKUP_SET and RC_BACKUP_PIECE views to find that out.

SQL> SELECT RBS.DB_KEY

         , RD.NAME

         , RBS.DB_ID

  2    3    4           , RBS.BS_KEY

         , RBS.RECID

         , RBS.STAMP

         , RBS.BACKUP_TYPE

         , RBS.START_TIME, STATUS

  5    6    7    8    9        FROM RC_BACKUP_SET RBS, RC_DATABASE RD

     WHERE RBS.DB_KEY=RD.DB_KEY

       AND RBS.DB_ID=RD.DBID

       AND RD.NAME='DBIT121' ;  10   11   12

...

...

    DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIM S

---------- -------- ---------- ---------- ---------- ---------- - --------- -

    546780 DBIT121   644484523     555608       3070  936496831 I 21-FEB-17 A

    546780 DBIT121   644484523     555609       3071  936496832 I 21-FEB-17 A

    546780 DBIT121   644484523     555610       3072  936496836 D 21-FEB-17 A

    546780 DBIT121   644484523     555611       3073  936496860 D 21-FEB-17 A

    546780 DBIT121   644484523     555612       3074  936496875 D 21-FEB-17 A

    546780 DBIT121   644484523     555613       3075  936496884 D 21-FEB-17 A

    546780 DBIT121   644484523     555614       3076  936496890 D 21-FEB-17 A

    546780 DBIT121   644484523     555615       3077  936496895 L 21-FEB-17 A

    546780 DBIT121   644484523     555616       3078  936496897 L 21-FEB-17 A

    546780 DBIT121   644484523     555617       3079  936496897 L 21-FEB-17 A

    546780 DBIT121   644484523     555618       3080  936496898 D 21-FEB-17 A

    DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIM S

---------- -------- ---------- ---------- ---------- ---------- - --------- -

    546780 DBIT121   644484523     555619       3081  936496900 D 21-FEB-17 A

    546780 DBIT121   644484523     555620       3082  936498788 D 21-FEB-17 A

    546780 DBIT121   644484523     555621       3083  936502389 D 21-FEB-17 A

    546780 DBIT121   644484523     555622       3084  936505991 D 21-FEB-17 A

    546780 DBIT121   644484523     555623       3085  936509589 D 21-FEB-17 A

    546780 DBIT121   644484523     555624       3086  936513189 D 21-FEB-17 A

    546780 DBIT121   644484523     555625       3087  936516788 D 21-FEB-17 A

    546780 DBIT121   644484523     555626       3088  936520387 D 21-FEB-17 A

    546780 DBIT121   644484523     555627       3089  936523988 D 21-FEB-17 A

    546780 DBIT121   644484523     555628       3090  936527608 D 21-FEB-17 A

    546780 DBIT121   644484523     555629       3091  936531188 D 21-FEB-17 A

...

...

After checking the output, I see that DBID 644484523 is the correct one, and DBID 642589239 is the one I want to get rid of.

To do so, we can shutdown the Standby database and start it up with nomount. The reason for that, is that you can’t issue the SET DBID command against a database which is mounted or open.

RMAN> SET DBID=642589239;

executing command: SET DBID

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of set command at 02/21/2017 13:15:26

RMAN-06188: cannot use command when connected to a mounted target database

Ok. Let’s go the nomount and execute the “unregister database;” command after the correct DBID is set.

$ rman target sys/welcome1 catalog /@rcat

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 21 14:25:10 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (not mounted)

connected to recovery catalog database

RMAN> SET DBID=642589239;

executing command: SET DBID

database name is "DBIT121" and DBID is 642589239

RMAN> unregister database;

database name is "DBIT121" and DBID is 642589239

Do you really want to unregister the database (enter YES or NO)? YES

database unregistered from the recovery catalog

RMAN>

Let’s check the RMAN catalog again.

SQL> SELECT DB.DB_KEY, DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME

        FROM DB, DBINC

       WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY

         AND DBINC.DB_NAME   = 'DBIT121' ;  2    3    4

    DB_KEY      DB_ID CURR_DBINC_KEY DB_NAME

---------- ---------- -------------- --------

    556718  644484523         556719 DBIT121

Cool. Looks much better. Now my resync catalog from SITE1 issued from SITE2 works again.

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name

------- ------- ----------------- ---------------  ------------------

556718  DBIT121  644484523        PRIMARY          DBIT121_SITE1

556718  DBIT121  644484523        STANDBY          DBIT121_SITE2

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME 'DBIT121_SITE1';

resyncing from database with DB_UNIQUE_NAME DBIT121_SITE1

starting full resync of recovery catalog

full resync complete

Monday, May 13, 2019

Oracle auditing setup & configuration

The Oracle audit command is used to to create specific audit trails for Oracle tables. to enable Oracle auditing you must set the initialization parameter "audit_trail = true" and run the cataudit.sql scripts (as SYS).

Also see ORA-01400: cannot insert NULL into ("SYS"."AUDIT_DDL"."DICT_OBJ_TYPE") tips

Auditing is a method of recording database activity as part of database security. It allows the DBA to track user activity within the database. The audit records will provide information on who performed what database operation and when it was performed. Records are written to a SYS-owned table named AUD$. The SYS.AUD$ (dba_audit_trail) and dba_fga_audit_trail) are commonly referred to as the audit trail.

It is advisable to copy the AUD$ table into a separate tablespace from other SYS-owned objects. In some cases, the AUD$ table should be owned by a user other than SYS. There are three reasons for these two statements:

* The AUD$ table, if auditing is used, may grow to a very large size, depending on the audit options selected.

* In some situations, you may want to add a trigger to the AUD$ table to count logins, monitor for specific actions (prior to 8i), or perform other security-related functions (such as implement password-checking functionality prior to version 8). Remember, you can't add triggers to catalog objects owned by SYS.

* Since the table will grow and shrink and be high activity, it is advisable to move it from the SYSTEM tablespace to avoid fragmentation.

Auditing information is not collected without some impact on performance and database resources. How much of an impact auditing will have on your system depends largely on the type of auditing you enable. For example, setting high-level auditing such as connection activity will not have as much of a performance impact as tracking all SQL statements issued by all users. It is best to start out with high-level auditing and then refine additional auditing as needed.

You can audit all users with the exception of SYS and CONNECT INTERNAL. Auditing can only be performed for users connected directly to the database, not for actions on a remote database.

Auditing should be enabled if the following types of questionable activities are noted:

* Unexplained changes in passwords, tablespace settings, or quotas appear.

* Excessive deadlocks are encountered.

* Records are being read, deleted, or changed without authorization.

There are three types of auditing:

* Statement auditing

* Privilege auditing

* Object auditing

Enabling and Disabling Auditing

The database initialization parameter AUDIT_TRAIL controls the enabling and disabling of auditing. The default setting for this parameter is NONE, which means that no auditing will be performed, regardless of whether or not AUDIT commands are issued. It is important to remember that any auditing statements issued will not be performed if AUDIT_TRAIL=NONE. Unless auditing is enabled in the database parameter initialization file, any auditing options that have been turned on will not create records in the audit trail. Auditing is not completely disabled unless it is set to NONE in the database parameter initialization file.

You must set the database initialization parameter AUDIT_TRAIL to DB or OS in order to enable auditing. The DB setting means the audit trail records are stored in the database in the SYS.AUD$ table. OS will send the audit trail records to an operating system file. The OS setting is operating system-dependent and is not supported on all operating systems.

Managing Oracle audit trails

The Oracle audit command write the audit information to specific data dictionary views.

BEWARE: These audit trail rows can quickly fill-up your SYSTEM tablespace, and special care needs to be taken to ensure that you do not "lock-up" your database, by filling the SYSTEM tablespace. Check these tables:

dba_audit_exists
dba_audit_object
dba_audit_session
dba_audit_statement
dba_audit_trail

We also have these metadata views for Oracle auditing options:

dba_obj_audit_opts
dba_priv_audit_opts
dba_stmt_audit_opts

Auditing the audit trail

Today, we need to audit the audit trail itself to prevent "inside jobs" and this Oracle command will record all changes to the audit trail. See my notes on the types of "inside jobs" and Oracle hackers horror stories:

   audit
      delete table,
      insert table,
      update table
   on
      mytable
   by access;

Auditing user activity with the Oracle audit command

Oracle has syntax for auditing specific user activity. To audit the activity of user FRED we could issue these audit commands:

Audit all Oracle user activity.

This audits everything including DDL (create table), DML (inserts, updates, deletes) and login/logoff events:

audit all by FRED by access;

Audit all Oracle user viewing activity:

audit select table by FRED by access;

Audit all Oracle user data change activity:

audit update table, delete table,
insert table by FRED by access;

Audit all Oracle user viewing activity:

audit execute procedure by FRED by access;

We can also query the dba_audit_trail view. Here are the column descriptions from the Oracle documentation:

Column	Datatype	NULL	Description
`OS_USERNAME`	`VARCHAR2(255)`		Operating system login username of the user whose actions were audited
`USERNAME`	`VARCHAR2(30)`		Name (not ID number) of the user whose actions were audited
`USERHOST`	`VARCHAR2(128)`		Client host machine name
`TERMINAL`	`VARCHAR2(255)`		Identifier of the user's terminal
`TIMESTAMP`	`DATE`		Date and time of the creation of the audit trail entry (date and time of user login for entries created by `AUDIT SESSION`) in the local database session time zone
`OWNER`	`VARCHAR2(30)`		Creator of the object affected by the action
`OBJ_NAME`	`VARCHAR2(128)`		Name of the object affected by the action
`ACTION`	`NUMBER`	`NOT NULL`	Numeric action type code. The corresponding name of the action type is in the `ACTION_NAME` column.
`ACTION_NAME`	`VARCHAR2(28)`		Name of the action type corresponding to the numeric code in the `ACTION`column
`NEW_OWNER`	`VARCHAR2(30)`		Owner of the object named in the `NEW_NAME` column
`NEW_NAME`	`VARCHAR2(128)`		New name of the object after a `RENAME` or the name of the underlying object
`OBJ_PRIVILEGE`	`VARCHAR2(16)`		Object privileges granted or revoked by a `GRANT` or `REVOKE` statement
`SYS_PRIVILEGE`	`VARCHAR2(40)`		System privileges granted or revoked by a `GRANT` or `REVOKE` statement
`ADMIN_OPTION`	`VARCHAR2(1)`		Indicates whether the role or system privilege was granted with the `ADMIN`option
`GRANTEE`	`VARCHAR2(30)`		Name of the grantee specified in a `GRANT` or `REVOKE` statement
`AUDIT_OPTION`	`VARCHAR2(40)`		Auditing option set with the `AUDIT` statement
`SES_ACTIONS`	`VARCHAR2(19)`		Session summary (a string of 16 characters, one for each action type in the order `ALTER`, `AUDIT`, `COMMENT`, `DELETE`, `GRANT`, `INDEX`, `INSERT`, `LOCK`, `RENAME`,`SELECT`, `UPDATE`, `REFERENCES`, and `EXECUTE`. Positions 14, 15, and 16 are reserved for future use. The characters are: `-` - None `S` - Success `F` - Failure `B` - Both
`LOGOFF_TIME`	`DATE`		Date and time of user log off
`LOGOFF_LREAD`	`NUMBER`		Logical reads for the session
`LOGOFF_PREAD`	`NUMBER`		Physical reads for the session
`LOGOFF_LWRITE`	`NUMBER`		Logical writes for the session
`LOGOFF_DLOCK`	`VARCHAR2(40)`		Deadlocks detected during the session
`COMMENT_TEXT`	`VARCHAR2(4000)`		Text comment on the audit trail entry, providing more information about the statement auditedAlso indicates how the user was authenticated. The method can be one of the following: `DATABASE` - Authentication was done by password `NETWORK` - Authentication was done by Oracle Net Services or the Advanced Security option `PROXY` - Client was authenticated by another user; the name of the proxy user follows the method type
`SESSIONID`	`NUMBER`	`NOT NULL`	Numeric ID for each Oracle session
`ENTRYID`	`NUMBER`	`NOT NULL`	Numeric ID for each audit trail entry in the session
`STATEMENTID`	`NUMBER`	`NOT NULL`	Numeric ID for each statement run
`RETURNCODE`	`NUMBER`	`NOT NULL`	Oracle error code generated by the action. Some useful values: `0` - Action succeeded `2004` - Security violation
`PRIV_USED`	`VARCHAR2(40)`		System privilege used to execute the action
`CLIENT_ID`	`VARCHAR2(64)`		Client identifier in each Oracle session
`SESSION_CPU`	`NUMBER`		Amount of CPU time used by each Oracle session
`EXTENDED_TIMESTAMP`	`TIMESTAMP(6) WITH TIME ZONE`		Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by `AUDIT SESSION`) in UTC (Coordinated Universal Time) time zone
`PROXY_SESSIONID`	`NUMBER`		Proxy session serial number, if an enterprise user has logged in through the proxy mechanism
`GLOBAL_UID`	`VARCHAR2(32)`		Global user identifier for the user, if the user has logged in as an enterprise user
`INSTANCE_NUMBER`	`NUMBER`		Instance number as specified by the `INSTANCE_NUMBER` initialization parameter
`OS_PROCESS`	`VARCHAR2(16)`		Operating System process identifier of the Oracle process
`TRANSACTIONID`	`RAW(8)`		Transaction identifier of the transaction in which the object is accessed or modified
`SCN`	`NUMBER`		System change number (SCN) of the query
`SQL_BIND`	`NVARCHAR2(2000)`		Bind variable data of the query
`SQL_TEXT`	`NVARCHAR2(2000)`		SQL text of the query

This query by Arup Nanda, co-author of "Oracle Privacy Security Auditing", shows a sample query against dba_audit_trail for standard Oracle auditing. You can download his complete set of auditing scripts when you buy his book.

select 'standard audit', sessionid,

    proxy_sessionid, statementid, entryid, extended_timestamp, global_uid,

    username, client_id, null, os_username, userhost, os_process, terminal,

    instance_number, owner, obj_name, null, new_owner,

    new_name, action, action_name, audit_option, transactionid, returncode,

    scn, comment_text, sql_bind, sql_text,

    obj_privilege, sys_privilege, admin_option, grantee, priv_used,

    ses_actions, logoff_time, logoff_lread, logoff_pread, logoff_lwrite,

    logoff_dlock, session_cpu

  from 
  dba_audit_trail;

This is just a tiny sample of the Oracle audit functionality and see the book "Oracle Privacy Security Auditing", for details and working scripts for Oracle auditing.