RMAN DUPLICATE FROM Active Standby Database

1 Vote

Recently I have published post about issue with RMAN duplication of Oracle database when standby database was used as a target. Later in updates of mentioned post I have confirmed that with fix for BUG:11715084 everything works as expected.

Today I have decided to make post with step-by-step procedure with which I have tested mentioned feature – making standby database from standby database.
I will Simplify setup As Much As Possible(SAMAP):

single machine will be used as a host for all 3 databases
OMF(Oracle Managed Files) will be used for managing files location and file names
FRA(Fast/Flash Recovery Area) will be used for archive logs

1) Network Setup

Because I use only single server – all modifications will be done locally.

I will configure two more databases: orcl2 and orcl3

1.1) Static instance registration:

we have to configure static instance registration information to listener because at the duplicate stage RMAN have to be able to connect to idle instance:

modify $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=orcl)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC=
      (SID_NAME=orcl1)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC=
      (SID_NAME=orcl2)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )

We have to reload new configuration data
lsnrctl reload
lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-JUL-2012 14:09:24
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl1" has 1 instance(s).
  Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl2" has 1 instance(s).
  Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

1.2) Lets add database names resolution with SID specified

modify $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID=orcl)
    )
  )
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID=orcl1)
    )
  )
ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID=orcl2)
    )
  )

2) Setup original database:

I have fresh database orcl created using DBCA

Archive log mode is enables and FRA is used as a target for archived logs.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     97
Next log sequence to archive   99
Current log sequence           99

I will make several modifications to support Data Guard in this environment – no extensive comments because it’s not an introduction to Oracle Data Guard:

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET log_archive_config=’DG_CONFIG=(orcl,orcl1,orcl2)’;
ALTER SYSTEM SET log_archive_dest_1=’SERVICE=orcl1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1‘;
ALTER SYSTEM SET standby_file_management=auto;

3) Password files in DataGuard

because we must have the same SYS password in all DataGuard environment(actually, You may use REDO_TRANSPORT_USER to specify another user that will be used for Redo Transport),

I’ll just copy original password file to all my databases in environment:

cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl1
cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl2

4) Creating directories for Control Files

because of control file that will be copied from primary may not be placed in FRA(because of FRA at that time will not exist yet) I will create directories for control files for both standby databases(I use capital letters because of OMF will later use the same directory for placing database files)

mkdir -p /u01/app/oracle/oradata/ORCL1

mkdir -p /u01/app/oracle/oradata/ORCL2

5) Lets create first standby

5.1) using RMAN for starting first standby instance WITHOUT any PARAMETER FILE

rman target sys/oracle@ORCL1

startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

5.2) Duplicating ACTIVE primary

rman TARGET sys/oracle@ORCL AUXILIARY sys/oracle@ORCL1
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET “db_unique_name”=”orcl1”
SET “DB_CREATE_FILE_DEST”=”/u01/app/oracle/oradata”
SET “control_files”=”/u01/app/oracle/oradata/ORCL1/control01.ctl”
SET FAL_SERVER=”orcl”
;
At the end of this step You will have new standby database created from Active primary – without the need of any backups.

5.3) starting managed recovery

before starting managed recovery I’ll just add some standby log files to support LGWR(LNS) as a transport service – so we will be able use real-time apply feature:

ORCL1 SQL>

ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

6) Lets create second standby using first standby as a source

6.1) using RMAN for starting first standby WITHOUT any PARAMETER FILE

rman target sys/oracle@ORCL2

startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

6.2) Duplicating from ACTIVE standby – I use ORCL1 as a target

rman TARGET sys/oracle@ORCL1 AUXILIARY sys/oracle@ORCL2
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET “db_unique_name”=”orcl2”
SET “DB_CREATE_FILE_DEST”=”/u01/app/oracle/oradata”
SET “control_files”=”/u01/app/oracle/oradata/ORCL2/control01.ctl”
SET FAL_SERVER=”orcl1”
;
At the end of this step You will have new second standby database created from ACTIVE STANDBY DATABASE – without the need of any backups and without ANY LOAD ON PRIMARY DATABASE!

6.3) starting redo shipping from ORCL1 to ORCL2 – Cascading Standby

ORCL1 SQL>

ALTER SYSTEM SET set log_archive_dest_2=’SERVICE=orcl2VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2‘;

6.4) starting managed recovery

before starting managed recovery I’ll just add some standby log files to support LGWR(LNS) as a transport service – so we will be able use real-time apply feature:

ORCL2 SQL>

Conclusion:

really-really interesting and easy to implement feature, especially when used with OMF and FRA
keep in mind that there is a bug mentioned in RMAN DUPLICATE FROM Active Database From Standby ends with ORA-01671 so I have fix for it installed in my environment
RMAN ARCHIVELOG DELETION POLICY may be used for simplifying archive log maintenance in DataGuard environment, making it really self-managed – will make short post about it later.

References:

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

Source Knowledge : https://odenysenko.wordpress.com/2012/07/31/rman-duplicate-from-from-active-standby/

Kumpulan Tutorial Kito

Jumat, 16 April 2021

ORACLE-rman