Configuring Data Guard in 11g using Duplicate

Oracle 11.2.0.4.8
Solaris SPARC 10u11

Primary: 200.31.108.103 ORACLE_SID: LCM2DBS1 RAC, ASM
Standby: 200.31.108.21 ORACLE_SID: LCM2DBS single instance, non-ASM

Logging

Check that the primary database is in archivelog mode.

SELECT force_logging, log_mode FROM v$database;

FOR LOG_MODE
--- ------------
YES ARCHIVELOG

If it is noarchivelog mode, switch to archivelog mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “LCM2DBS” on the primary database.

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string LCM2DBS

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string LCM2DBS
SQL>

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value “LCM2DBSTD”.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(LCM2DBS,LCM2DBSTD)';

Set suitable remote archive log destinations. In this case I’m using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FLASHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=LCM2DBS';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=LCM2DBSTD NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LCM2DBSTD';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive. In our case, all these parameters have already been set (LOG_ARCHIVE_MAX_PROCESSES=4).

--ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
--ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.

ALTER SYSTEM SET FAL_SERVER=LCM2DBSTD;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/lcm2dbstd','+DATADG/lcm2dbs' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/lcm2dbstd','+DATADG/lcm2dbs','/oraarch/lcm2dbstd','+FLASHDG/lcm2dbs' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

Service Setup

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.

LCM2DBS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 200.31.108.110)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LCM2DBS)
    )
  )

LCM2DBS1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 200.31.108.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LCM2DBS)
    )
  )

LCM2DBSTD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 200.31.108.21)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LCM2DBS)
    )
  )

Standby Server Setup (DUPLICATE)

Copy Files

Create the necessary directories on the standby server.

mkdir -p /oraapp/oracle/admin/lcm2dbstd/adump
mkdir -p /oradata/lcm2dbstd
mkdir -p /oradata/lcm2dbstd/datafile
mkdir -p /oradata/lcm2dbstd/onlinelog
mkdir -p /oraarch/lcm2dbstd
mkdir -p /oraarch/lcm2dbstd/onlinelog
mkdir -p /oraarch/lcm2dbstd/flash_recovery_area

Copy the files from the primary to the standby server.

$ # Remote login password file.
$ scp oracle@200.31.108.103:/oraapp/oracle/product/11.2.0/dbhome_1/dbs/orapwLCM2DBS1 /oraapp/oracle/product/11.2.0/dbhome_1/dbs/orapwLCM2DBSTD

# Since the primary database is RAC, we also need to copy pwd file to the second instance
$ scp oracle@200.31.108.103:/oraapp/oracle/product/11.2.0/dbhome_1/dbs/orapwLCM2DBS1 /oraapp/oracle/product/11.2.0/dbhome_1/dbs/orapwLCM2DBS2
$ # Parameter file.
$ cd $ORACLE_HOME/dbs
$ vi initLCM2DBSTD.ora
*.db_name='LCM2DBS'

Start Listener

When using active duplicate, the standby server requires static listener configuration in a “listener.ora” file. In this case I used the following configuration.

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = LCM2DBS)
      (ORACLE_HOME = /oraapp/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = LCM2DBSTD)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 200.31.108.21)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Make sure the listener is started on the standby server.

$ lsnrctl start

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

select group#, thread#, sequence#, bytes/1024/1024, status from v$log;
select group#, thread#, sequence#, bytes/1024/1024, status from v$standby_log;
col member for a60
set lines 200 pages 200
select group#, type, member from v$logfile;

We need to add standby logfile groups (one more group for each thread) containing the same logfile size with online logfiles:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 19 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('+DATADG','+FLASHDG') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('+DATADG','+FLASHDG') SIZE 200M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary “init.ora” file.

$ export ORACLE_SID=LCM2DBSTD
$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT;

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication. Note that we are connecting to the 1st instance. Otherwise ORA-19505 : No such file or directory may occur.

$ rman TARGET sys/Pass@LCM2DBS1 AUXILIARY sys/Pass@LCM2DBSTD

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name 'LCM2DBSTD'
SET cluster_database 'FALSE'
SET local_listener ''
SET remote_listener ''
SET audit_file_dest '/oraapp/oracle/admin/lcm2dbstd/adump'
SET control_files '/oradata/lcm2dbstd/crontrol01.ctl'
SET db_block_size '8192'
SET db_create_file_dest '/oradata/lcm2dbstd'
SET db_file_name_convert '+DATADG/lcm2dbs','/oradata/lcm2dbstd'
SET db_name 'LCM2DBS'
SET diagnostic_dest='/oraapp/oracle'
SET fal_server='LCM2DBS'
SET log_archive_dest_1 'LOCATION=/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=LCM2DBSTD'
SET log_file_name_convert '+DATADG/lcm2dbs','/oradata/lcm2dbstd','+FLASHDG/lcm2dbs','/oraarch/lcm2dbstd'
SET pga_aggregate_target '5368709120'
SET sga_max_size '16106127360'
SET sga_target '16106127360'
SET standby_file_management 'AUTO'
SET undo_tablespace 'UNDOTBS1'
SET log_archive_dest_2 'SERVICE=LCM2DBS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LCM2DBS'
SET db_recovery_file_dest_size '500G'
SET db_recovery_file_dest '/oraarch/lcm2dbstd/flash_recovery_area'
NOFILENAMECHECK;

In case you want to run duplicate command again (to reinstate database or recover from duplicate error):

# on standby database
rm -rf /oraapp/oracle/product/11.2.0/dbhome_1/dbs/spfileLCM2DBSTD.ora
rm -rf /oraapp/oracle/product/11.2.0/dbhome_1/dbs/lkLCM2DBSTD
rm -rf /oraapp/oracle/product/11.2.0/dbhome_1/dbs/hc_LCM2DBSTD.dat
rm -rf /oraapp/oracle/admin/lcm2dbstd/adump
rm -rf /oradata/lcm2dbstd
rm -rf /oraarch/lcm2dbstd

mkdir -p /oraapp/oracle/admin/lcm2dbstd/adump
mkdir -p /oradata/lcm2dbstd
mkdir -p /oradata/lcm2dbstd/datafile
mkdir -p /oradata/lcm2dbstd/onlinelog
mkdir -p /oraarch/lcm2dbstd
mkdir -p /oraarch/lcm2dbstd/onlinelog
mkdir -p /oraarch/lcm2dbstd/flash_recovery_area

export ORACLE_SID=LCM2DBSTD
SQL> startup nomount;

and then run DUPLICATE command in primary database again.

A brief explanation of the individual clauses is shown below.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Start Apply Process

# enable flashback database
ALTER DATABASE FLASHBACK ON;

Start the apply process on standby server.

# Background redo apply with real-time apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

set lines 200 pages 0
SELECT sequence#, thread#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;

select thread#, max(sequence#)
from gv$archived_log
where applied='YES'
group by thread#;

select dest_id, status, type, recovery_mode, protection_mode, ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_THREAD#, APPLIED_SEQ#
from gv$archive_dest_status
where status<>'INACTIVE';

ALTER SYSTEM ARCHIVE LOG CURRENT;

Check the new archived redo log has arrived at the standby server and been applied.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

set lines 200 pages 0
SELECT sequence#, thread#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;

select thread#, max(sequence#)
from gv$archived_log
where applied='YES'
group by thread#;

set lines 200 pages 200
select * from v$dataguard_stats; -- apply lag should be 0 seconds
select process, status from v$managed_stanadby; -- MRP0 is APPLYING_LOG

select * from v$archive_gap; -- should be no rows

Reference:

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE [ID 1075908.1]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s