Create physical standby from backup


Before building out your standby, ensure that the database has force logging enabled in your primary database.

     ALTER DATABASE FORCE LOGGING;

1. Ensure that your TNS_ADMIN is pointing to the proper location (typically this will be the Oracle Home where your database will utilize).

      echo $TNS_ADMIN
      export TNS_ADMIN=$ORACLE_HOME/network/admin

 2. Modify tnsnames.ora in $TNS_ADMIN on both primary and standby locations to include both your standby database TNS entry and the primary database TNS entry. 

 (example)

    stbydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stbydb)
    )
  )
prmrydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan_prd.server2.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prmrydb)
    )
  )

3.  Pre-create your directory structure for the standby database under the $ORACLE_BASE/admin.
(example)
    mkdir -p $ORACLE_BASE/admin/stbydb/adump
    mkdir -p $ORACLE_BASE/admin/stbydb/pfile
    mkdir -p $ORACLE_BASE/admin/stbydb/create

3. Copy over backup pieces from latest backup taken via RMAN.  The backup location you are copying them from has to be the exact same backup location that you copy them to on the standby db server.

 4.  Copy oracle password file from production to your standby server under $ORACLE_HOME/dbs, also create init parameter file for auxiliary instance.  You can create a PFILE from your SPFILE on your primary database and modify the necessary parameters accordingly.

 DB_NAME=prmrydb
DB_UNIQUE_NAME=stbydb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prmrydb,stbydb)'
CONTROL_FILES='+DATA', '+FRA'
DB_CREATE_ONLINE_LOG_DEST_1='+DATA'
DB_CREATE_ONLINE_LOG_DEST_2='+FRA'
LOG_ARCHIVE_FORMAT=stbydb_%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/stbydb/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=prmrydb
FAL_CLIENT=stbydb
5.  Create stby.cmd file for RMAN to build out the standby from backup pieces.
(example)


connect target sys/oracle@prmrydb;

connect auxiliary / ;
run {
allocate channel prm1 type disk;
allocate channel prm2 type disk;
allocate channel prm3 type disk;
allocate auxiliary channel stb1 type disk;
allocate auxiliary channel stb2 type disk;
allocate auxiliary channel stb3 type disk;
duplicate target database for standby dorecover nofilenamecheck;
}

6.  Start the RMAN duplicate job.  The above example assumes that you are executing the RMAN duplicate job from standby db server.
(example)

     nohup rman cmdfile stby.cmd log stby.log &

7.  After standby creation has finished, modify primary database parameters for standby database to sync up.

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(prmrydb,stbydb)';
ALTER SYSTEM SET log_archive_dest_2='SERVICE=stbydb valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=stbydb';
ALTER SYSTEM SET fal_client='prmrydb';
ALTER SYSTEM SET fal_server='stbydb';
ALTER SYSTEM SET standby_file_management='AUTO';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='enable';
8.  Bounce the standby database and enable active recovery on standby.

SQL> shutdown immediate;
SQL> startup mount;     
SQL> alter database recover managed standby database using current logfile disconnect;

 If you would like oracle to automatically delete your archivelogs after being applied to standby, do the following on both primary and standby:

 SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
RMAN> connect target /
   CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
When the db_recovery_file_dest_size hits ~99%, oracle will begin automatically deleting applied archivelogs.  This saves you have to run a job to delete them. 

 Oracle will precreate your standby redologs under the ORACLE_HOME/dbs directory if none exist currently in the primary database.
See highlighted below:
    GROUP# MEMBER
---------- --------------------------------------------------
         1 +DG01/stbydb/onlinelog/group_1.577.748263071
         2 +DG01/stbydb/onlinelog/group_2.578.748263073
         3 +DG01/stbydb/onlinelog/group_3.579.748263073
         4 +DG01/stbydb/onlinelog/group_4.580.748263073
         5 +DG01/stbydb/onlinelog/group_5.581.748263075
         6 +DG01/stbydb/onlinelog/group_6.582.748263075
         7 +DG01/stbydb/onlinelog/group_7.583.748263077
         8 +DG01/stbydb/onlinelog/group_8.584.748263077
         9 /orabase/product/11.2.0/db_1/dbs/srl1.f
        10 /orabase/product/11.2.0/db_1/dbs/srl2.f
        11 /orabase/product/11.2.0/db_1/dbs/srl3.f

     GROUP# MEMBER
---------- --------------------------------------------------
        12 /orabase/product/11.2.0/db_1/dbs/srl4.f
        13 /orabase/product/11.2.0/db_1/dbs/srl5.f
        14 /orabase/product/11.2.0/db_1/dbs/srl6.f
        15 /orabase/product/11.2.0/db_1/dbs/srl7.f
        16 /orabase/product/11.2.0/db_1/dbs/srl8.f
        17 /orabase/product/11.2.0/db_1/dbs/srl9.f
        18 /orabase/product/11.2.0/db_1/dbs/srl10.f



 9. You will need to drop/recreate or create all the standby redo logs.  
Ex.
 alter database drop logfile group 9;
 alter database add standby logfile thread 1 group 11 '+DG01' SIZE 200M;
If you want to use dataguard broker for primary database and it's running on RAC, by default the dg_broker_config_file1 and dg_broker_config_file2 are set to point to the Oracle Home.  You want to change that to point to a shared location, either NFS mount or ASM.
ex. 

SQL> alter system set dg_broker_config_file1='+DATA/prmrydb/dr1_prmrydb
.dat';
SQL> alter system set dg_broker_config_file2='+DATA/prmrydb/dr2_prmrydb
.dat';

To configure dg broker so that it can seamlessly startup/shutdown, failover/switchover your databases without manual intervention, configure the listener to include entry for DGMGRL.
ex.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
    (SDU=32767)
      (GLOBAL_DBNAME = stbydb)
      (ORACLE_HOME = /orabase/product/11.1.0.7.0/db_1)
      (SID_NAME = stbydb)
    )
    (SID_DESC =
    (SDU=32767)
      (GLOBAL_DBNAME = stbydb_DGMGRL)
      (ORACLE_HOME = /orabase/product/11.1.0.7.0/db_1)
      (SID_NAME = stbydb)
    )
  )

Comments

Post a Comment

Popular posts from this blog

RMAN-10038: database session for channel prm3 terminated unexpectedly

Problem: I had an issue where I was trying to build a 1.7 TB Data Guard environment and kept receiving the highlighted error below.  I thought it was some type of fluke so I restarted the job 2 more times only to receive the same error. Fix: After researching the issue, I found it was because the sqlnet.ora needed to be configured correctly.  I added the entry " SQLNET.EXPIRE_TIME = 10 " to the sqlnet.ora on both the source and the target database servers under the RDBMS $ORACLE_HOME/network/admin.  Upon restarting the RMAN active database standby build, it was able to move past this error and built successfully. Starting backup at 07-MAY-17 channel prm1: starting datafile copy input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.262.940412881 channel prm2: starting datafile copy input datafile file number=00129 name=+DATA/testdb/datafile/welldoc1.390.940509955 channel prm3: starting datafile copy input datafile file number=00130 name=+DATA/test
Read more

ORA-17630: Mismatch in the remote file protocol version client 2 server 3

I was trying to create an Oracle Data Guard environment (physical standby) from an Oracle database running version 12.1.0.2. The database was running an older PSU version ("Database Patch Set Update : 12.1.0.2.161018 (24006101)") to be specific. Upon trying to build the standby by using RMAN DUPLICATE with ACTIVE DATABASE, I ran into the below error: executing Memory Script Starting backup at 16-JUL-18 RMAN-03009: failure of backup command on prm1 channel at 07/16/2018 13:51:46 ORA-17629: Cannot connect to the remote database server ORA-17630: Mismatch in the remote file protocol version client 2 server 3 continuing other job steps, job failed will not be re-run released channel: prm1 released channel: prm2 released channel: prm3 released channel: prm4 released channel: prm5 released channel: stb1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ====================
Read more

ORA-00338: log {n} of thread {n} is more recent than control file

I received the following error in a primary database's alert log that I was supporting recently.   ORA-00312: online log 5 thread 1: '+DATA/db_name/onlinelog/group_5.297.846947859' ORA-00338: log 5 of thread 1 is more recent than control file The database has a DataGuard environment as well and below are the steps that I took to resolve the issue. 1.  First, I checked the status of the redo logs and their current sizes and status. SQL> select group#,bytes/1024/1024,status from v$log;     GROUP# BYTES/1024/1024 STATUS ---------- --------------- ----------------          1              50 INACTIVE          2              50 INACTIVE          3              50 INACTIVE          4             250 INACTIVE          5             250 INACTIVE          6             250 CURRENT 2.  As you can see, groups 1, 2, 3 have 50 MB redo log group sizes while group 4, 5, 6 are 250 MB redo log group sizes.  This is something that I highly suspect was/is the issue.  So I
Read more