Oracle Database 10 Data Guard Logical Standby

From AMCPU Wiki

Contents 1 Introduction 2 Backup and restore primary to standby 2.1 Backup Primary using RMAN 2.2 Restore database to standby using RMAN 3 Oracle networking configuration 3.1 Configure listener.ora 3.2 Configure tnsnames.ora 4 Configure Physical Standby Database 4.1 Primary Server Configuration 4.2 Standby Server Configuration 5 Configure Logical Standby Database 5.1 Primary Server Configuration 5.2 Standby Server Configuration 6 Upgrade Protection Mode 6.1 Primary Database Configuration 6.2 Standby Database Configuration 6.3 Complete Configuration 6.4 Validate SQL Apply Services 7 Switchover Primary to Standby (transition roles) 8 Failover Operation (primary server fails)

Introduction

This is a walkthrough for configuring one primary Oracle 10g database and one logical standby Oracle 10g database in a fully replicated Data Guard scenario. These steps were taken directly from the Oracle Data Guard 10g Concepts and Administration documentation found at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/toc.htm.

For the purposes of this walkthrough, the SID of the database replicated is "amcpudb" and the unique database names of the primary and standby database servers will be "amcpudb_pri" and "amcpudb_sby" respectively. The server hostnames will be "prihost" and "sbyhost". Pretty original, eh?

This walkthrough assumes that you have already set up the primary database as a normal standalone installation with fairly vanilla configuration. As with all walkthroughs, some of your parameters will vary depending on the resources you are working with. Remember that this walkthrough is focused on a Data Guard configuration and does not touch on initial database configuration and tuning.

Some environmentals for this particular walkthrough:

$ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

Datafiles=/u01/oradata/amcpudb, /u02/oradata/amcpudb

Archive Logs=/u02/oradata/amcpudb/archive1/primary, /u02/oradata/amcpudb/archive1/standby, /u02/oradata/amcpudb/archive2/primary, /u02/oradata/amcpudb/archive2/standby

SID=amcpudb

Backup and restore primary to standby

Backup Primary using RMAN

Take cold backup of the primary server using RMAN:

# su - oracle
oracle@primary$ sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup mount;
SQL> exit

$ rman nocatalog
RMAN> connect target
RMAN> run {
   allocate channel d1 type disk;
   backup full tag full_backup format "/u02/orabackup/Sun/remedy_data_t%t_s%s_p%p" database;
   copy current controlfile to "$BACKUP_DIRECTORY/controlfile_$DATE.ctl";
   release channel d1;
}

RMAN> exit

Restore database to standby using RMAN

oracle@prihost$ scp /u02/orabackup/Sun/* secondary:/u02/orabackup/Sun

oracle@prihost$ scp $ORACLE_HOME/dbs/init<SID>.ora secondary:$ORACLE_HOME/dbs

Modify the pfile that is copied over as appropriate. Most important is the db_unique_name.

On the secondary server, copy the backed up control file to the locations defined in the pfile by the *.control_files parameter.

Start the standby database to the mount state:

oracle@sbyhost$ sqlplus '/as sysdba'

SQL> startup mount
SQL> exit

Now, restore the RMAN backup on the secondary server:

oracle@sbyhost$ rman nocatalog

RMAN> connect target
RMAN>run {
restore database;
}
RMAN> exit

Now recover the database and reset the redo logs:

SQL> recover database using backup controlfile until cancel;
cancel

SQL> shutdown immediate;

Now, create a standby control file on the primary server and copy it over to the standby server:

SQL> alter database create standby controlfile as '/var/tmp/amcpudb_sby.ctl';

Copy this standby control file over to the standby server:

oracle@prihost$ scp /var/tmp/amcpudb_sby.ctl sbyhost:/var/tmp

On the standby server copy this new control file over to correct locations:

oracle@sbyhost$ cp /var/tmp/amcpudb_sby.ctl /u01/oradata/amcpudb/control01.ctl
oracle@sbyhost$ cp /var/tmp/amcpudb_sby.ctl /u02/oradata/amcpudb/control02.ctl
...

Oracle networking configuration

Configure listener.ora

Now, edit the $ORACLE_HOME/network/admin/listener.ora file on the primary:

amcpudb_pri =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCamcpudb_pri))
     (ADDRESS = (PROTOCOL = TCP)(HOST = prihost)(PORT = 1521))
 )
SID_LIST_amcpudb_pri =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = amcpudb)
     (GLOBAL_DB_NAME = amcpudb_pri)
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
   )

   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
     (PROGRAM = extproc)
   )
 )

And then the standby:

amcpudb_sby =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCamcpudb_sby))
     (ADDRESS = (PROTOCOL = TCP)(HOST = sbyhost)(PORT = 1521))
 )
SID_LIST_amcpudb_sby =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = amcpudb)
     (GLOBAL_DB_NAME = amcpudb_sby)
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
   )

   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
     (PROGRAM = extproc)
   )
 )

Configure tnsnames.ora

Then edit $ORACLE_HOME/network/admin/tnsnames.ora to be the following:

amcpudb_PRI =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = prihost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = amcpudb_pri)
   )
 )

amcpudb_SBY =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = sbyhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = amcpudb_sby)
   )
 )

Start the listeners on both servers:

oracle@prihost$ lsnrctl start amcpudb_pri

oracle@sbyhost$ lsnrctl start amcpudb_sby

Configure Physical Standby Database

Primary Server Configuration

oracle@prihost$ sqlplus '/ as sysdba'

SQL> ALTER DATABASE FORCE LOGGING;
 
SQL> alter system set log_archive_config='DG_CONFIG=(amcpudb_pri,amcpudb_sby)' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/oradata/amcpudb/archive1/primary/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_pri' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=amcpudb_sby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=amcpudb_sby' scope=both;

System altered.

SQL> alter system set remote_login_passwordfile='EXCLUSIVE';

System altered.

SQL> alter system set FAL_SERVER='amcpudb_sby' scope=both;

System altered.

SQL> alter system set FAL_CLIENT='amcpudb_pri' scope=both;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='amcpudb_sby','amcpudb_pri' scope=spfile;

System altered.
 
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 7700742144 bytes
Fixed Size                  1990672 bytes
Variable Size            1342185456 bytes
Database Buffers         6341787648 bytes
Redo Buffers               14778368 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Standby Server Configuration

oracle@sbyhost$ sqlplus '/as sysdba'
SQL> startup mount;

SQL> alter system set log_archive_dest_1='LOCATION=/u02/oradata/amcpudb/archive1/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_pri' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=amcpudb_pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=both;

System altered.

SQL> alter system set remote_login_passwordfile='EXCLUSIVE';

System altered.

SQL> alter system set FAL_SERVER='amcpudb_sby' scope=both;

System altered.

SQL> alter system set FAL_CLIENT='amcpudb_pri' scope=both;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='amcpudb_pri','amcpudb_sby' scope=spfile;

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;

SQL> shutdown immediate;

SQL> !

Create password file

oracle@sbyhost$ cd $ORACLE_HOME/dbs
oracle@sbyhost$ orapwd file=orapwamcpudb password="syspass" entries=5 force=y
oracle@sbyhost$ exit

Startup the physical standby database to a mount state:

SQL> startup mount;

Start the redo log recovery from the primary (commands executed on standby server):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the primary server, switch the logfile to verify redo transfer to standby:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the sequence numbers are sync'd up by running this query on both servers:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Physical standby database is complete.

Configure Logical Standby Database

First, stop the redo log apply on the physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Primary Server Configuration

Modify the LOG_ARCHIVE_DEST_3 initialization parameters as follows:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u02/oradata/amcpudb/archive2/primary/   VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=both;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='ENABLE' scope=both;

Now, prepare the LogMiner dictionary that is required for converting redo logs to actual SQL statements for SQL Apply to work properly.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

Standby Server Configuration

Convert the physical standby database to a logical standby database:

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY;

Re-create the password file. This is a required step after converting from a physical to logical standby.

oracle@sbyhost$ cd $ORACLE_HOME/dbs
oracle@sbyhost$ orapwd file=orapwamcpudb password="syspass" entries=5 force=y

Now, modify the initialization parameters as follows:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/oradata/amcpudb/archive1/standby/    VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_sby' scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=amcpudb_pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=spfile;
SQL> LOG_ARCHIVE_DEST_3='LOCATION=/u02/oradata/amcpudb/archive2/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=amcpudb_sby' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='ENABLE' scope=both;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Now open the database for access and reset the log sequences:

SQL> ALTER DATABASE OPEN RESETLOGS;

Finally, start the SQL Apply processes to start logical standby log apply:

SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Upgrade Protection Mode

For this configuration I chose MAXIMUM AVAILABILITY mode so that the primary database does not shutdown if the redo is not validated to apply to the standby. MAXIMUM PROTECTION mode will do anything possible, including shutting down the primary database, to ensure that redo data is applied to the standby database. Since we want this system to focus on making sure the application is available as often as possible, we don't want to compromise that availability. We will configure MAXIMUM AVAILABILITY below. For other protection modes (PROTECTION, PERFORMANCE), please reference the Oracle Data Guard Concepts and Administration documentation.

Primary Database Configuration

Modify the protection mode on the primary database.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Start up the database:

SQL> ALTER DATABASE OPEN;

Alter the log_archive_dest_2 parameter and shutdown the database:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=amcpudb_sby OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB                                                 _UNIQUE_NAME=amcpudb_sby;
SQL> SHUTDOWN IMMEDIATE

Standby Database Configuration

Alter the log_archive_dest_2 parameter and shutdown the database:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=amcpudb_pri OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB                                                 _UNIQUE_NAME=amcpudb_pri;
SQL> SHUTDOWN IMMEDIATE

Complete Configuration

Start up the standby database first and then start up the primary.

SQL> startup;

And then the primary

SQL> startup;

Validate SQL Apply Services

On primary database, create a table and commit the changes (as a non-sysdba user):

SQL> create table tony (col1 varchar(1));

Table created.

SQL> insert into tony values (1);

1 row created.

SQL> commit;

Commit complete.

Then describe the table on the standby:

SQL> desc tony;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                               VARCHAR2(1)

Get rid of the table on the primary:

SQL> drop table tony;
SQL> commit;

Configuration COMPLETE!!!

Switchover Primary to Standby (transition roles)

This procedure will switchover the primary role over to the server originally performing the logical standby role and vice-versa. This is done as a planned event.

Make sure that the primary is in a state that is ready for a switchover. This value should read "TO STANDBY" or "ACTIVE SESSIONS" to be ready.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY

Prepare current primary to switchover to standby:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

Prepare standby to switchover to primary:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

Validate the original primary is ready for switchover to logical standby role:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO LOGICAL STANDBY

If you need to back out to the original configuration now is the time. Perform these steps in the order below to back out:

Cancel switchover on the primary database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;

Cancel the switchover on the logical standby database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;

Otherwise, continue on with switchover of the original primary to be the new logical standby:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Switch the original logical standby to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Finally, start the SQL Apply on the NEW logical standby (the original primary, prihost):

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Failover Operation (primary server fails)

This procedure will bring up the original standby server as the primary role in the event that the original primary server has an unplanned outage.

First, if necessary, register any archive log files that were not applied to the logical standby:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE  '/u02/oradata/amcpudb/archive2/standby/log-%r_%s_%t.arc';
Database altered.

Make sure that all redo logs have been applied:

SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN
----------- ----------
    190725     190725

On standby server, sbyhost, that we are failing over to, prepare it to take on the primary role:

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;

Defer the logs being sent to the standby server with:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;

Finally, restore the original server as if you were building a new logical standby and bring it into the Data Guard configuration. You will then enable the LOG_ARCHIVE_DEST_STATE_2 that was deferred above.