Many Oracle environments run backups from their Dataguard physical standby database, if applicable. This is best practice in the field because it allows you to offload any overhead of a database backup to the standby database where no applications and/or users are connected. This configuration can also be utilized for reporting purposes.
In our test environment we use the enterprise backup and recovery product TSM – Tivoli Storage Manager, now IBM Spectrum Protect. Before we go further please note that it is IBM’s and Oracle’s recommendation that you use a full RMAN recovery catalog, especially when you run backups from your standby database. However, not using an RMAN recovery catalog is supported. We are not using a full RMAN recovery catalog in this test environment. By default the RMAN utility, which is Oracle’s backup and recovery utility, will use the Oracle database’s control file to store backup metadata. You can optionally create an RMAN recovery catalog, which is essentially another Oracle database, to store RMAN metadata as well. In any event, you may run into issues if you ever have to run the TDPOSYNC Utility which comes with Spectrum Protect TDPO, Tivoli Data Protection for Oracle, against a standby database, not using a full RMAN recovery catalog.
The TDPOSYNC Utility compares the database backups which the TSM server database knows about against what the target Oracle database’s control file and RMAN recovery catalog, if utilized, knows about. It will then show backups which the TSM server database knows about but RMAN does not know about. This utility essentially ensures that both the TSM server database and RMAN catalog are in sync. There is an issue with running this utility against a Dataguard standby database. The issue is due to the fact that the connect string which the TDPOSYNC Utility generates is expecting a data dictionary to be available.
With a Dataguard physical standby the database is not opened, but mounted so we cannot connect to it via the connect string that TDPO uses:” justinb/PWD@DBNAME”. We need to connect with the connect string: “justinb/PWD@DBNAME as sysdba”. The latter connect string allows you to authenticate using the Oracle password file, which you have to use with a standby because the database is mounted, not opened. Oracle uses a password file to address the concept of authenticating to a database when the data dictionary, which is only available when the database is opened, is not available. The Oracle data dictionary is typically where database user authentication information is stored. The password file is like the /etc/shadow file on some UNIX and all Linux systems. It will store a username and a password. The user stored in that file could have a few different privileges but the one relevant here is SYSDBA. When a user is granted the sysdba privilege that user is added to the password file. When a user is added to the password file the user can then authenticate to a database instance without it being opened, because that user is authenticating against the password file which is external to the data dictionary.
In this test environment db1 is the name of the standby database. It is also the name of the Dataguard primary database but we will not be working with the primary database here.
When you attempt to connect to a standby database, the way TDPOSYNC Utility does, you will get the following error because the standby database is not opened but mounted:
$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 31 07:37:05 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: justinb
Enter password:
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
If you attempt to connect to a standby database using “/ as sysdba” you connect just fine. You do so because we are using Oracle operating system authentication here.
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 31 07:37:25 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning option
SQL> select name from v$database;
NAME
———
DB1
SQL> select open_mode from v$database;
OPEN_MODE
——————–
MOUNTED
SQL> select host_name from v$instance;
HOST_NAME
—————————————————————-
aixdbhost1
SQL> select database_role from v$database;
DATABASE_ROLE
———————–
PHYSICAL STANDBY
The below query shows that my username, justinb, is in the password file. This means that I have been granted the sysdba privilege and will be able to connect via a password file, i.e. authenticate even when the database is mounted and not opened.
SQL> select username from v$pwfile_users where username = ‘BLEISJ’;
USERNAME
——————————
BLEISJ
The way tdposync needs to connect to a standby database is as follows. This will allow us to connect to a standy database, which is always mounted. Tdposync can then run the proper SQL select statement. On a side note it’s a good thing that tdpo is selecting from a fixed view, v$backup_piece, because a data dictionary view like DBA_*, USER_* or ALL_* would not be available until the database is opened anyway.
$ sqlplus justinb/PWD@db1 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 31 07:40:03 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning option
SQL> show user;
USER is “SYS”
SQL> select name from v$database;
NAME
———
DB1
SQL> select host_name from v$instance;
HOST_NAME
—————————————————————-
aixdbhost1
SQL> select database_role from v$database;
DATABASE_ROLE
———————–
PHYSICAL STANDBY
Below is some reverse engineering I have done with tdposync against a standby database:
If I enter “as sysdba” as part of the “Catalog 1 User Name:” tdposync prompt, the generated connect string is:
justinb as sysdba/PWD@db1
If I enter “as sysdba” as part of the “Catalog 1 Password: “ tdposync prompt, the generated connect string is:
justinb/PWD as sysdba@db1
If I enter the “as sysdba” as part of the “Catalog 1 Connect String:” tdposync prmopt, the generated connect string is:
justinb/PWD@db1 as sysdba
Note, when I do it the last way, the connect string syntax is now correct. I can now connect to an Oracle database which is mounted, not opened and the tdposync command can now run it’s SQL in an Sqlplus session and then compare properly. I still need to verify each piece from RMAN but I am now seeing the TSM backup metadata for the standby node.
