Dbs Neeraj Oracle manual dataguard standby creation

 




 1. RAC to RAC Physical Dataguard:

Prepare Primary Site
Prepare Standby Site
Create RAC Physical Standby Database



1.1 Prepare Primary Site:
==>Enable Force Logging.
==>Create Spfile from the existing Pfile. Modify Dataguard related init Parameters:
==>Enable Archivelog Mode.
==>Create the SLRs (Standby Redo Logs).
==>Backup the Database for Standby
==>Create pfile for standby database.
==>Update the tnsnames.ora.




1.1.1. Enable Force Logging. --Completed (02Mar)
SYS@ntulearn> select NAME, FORCE_LOGGING from v$database;
 
If Force Logging is not enabled then use the following command to enable the force logging. 
alter database force logging;







1.1.2. Modify DataGuard related init Parameters: (Downtime needed)

Login to node N4LDB5003A
[ntulearn oracle@N4LDB5003A ~]$ cd $ORACLE_HOME/dbs
[ntulearn oracle@N4LDB5003A ~]$ cp -pr initntulearn1.ora initntulearn1.ora_<date>
Login to node N4LDB5003B
[ntulearn2 oracle@N4LDB5003B ~]$ cd $ORACLE_HOME/dbs
[ntulearn2 oracle@N4LDB5003B ~]$ cp -pr initntulearn2.ora initntulearn2.ora_<date>

Create below file in SQL plus

SYS@ntulearn2> create spfile='+DATA_N4LDB/NTULEARN/PARAMETERFILE/spfilentulearn.ora' from pfile='/tmp/initntulearn.ora';
Edit Below files:


Login to node N4LDB5003A
[ntulearn oracle@N4LDB5003A ~]$ cd $ORACLE_HOME/dbs
[ntulearn oracle@N4LDB5003A ~]$ vi initntulearn1.ora
SPFILE='+DATA_N4LDB/NTULEARN/PARAMETERFILE/spfilentulearn.ora'    ==> Only line needed in the parameter file

Login to node N4LDB5003B
[ntulearn2 oracle@N4LDB5003B ~]$ cd $ORACLE_HOME/dbs
[ntulearn2 oracle@N4LDB5003B ~]$ vi initntulearn2.ora
SPFILE='+DATA_N4LDB/NTULEARN/PARAMETERFILE/spfilentulearn.ora'    ==> Only line needed in the parameter file


Bounce database (Can be done on either N4LDB5003A/N4LDB5003B):

[ntulearn2 oracle@N4LDB5003B ~]$ srvctl stop database -d ntulearn

[ntulearn2 oracle@N4LDB5003B ~]$ srvctl start database -d ntulearn 



INFO: Parameter entries for configuration in DR setup
DB_NAME=ntulearn
DB_UNIQUE_NAME=ntulearn
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ntulearn,ntulearndr)'
LOG_ARCHIVE_DEST_1='LOCATION=+RECO_N4LDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ntulearn'
LOG_ARCHIVE_DEST_2='SERVICE=ntulearndr SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ntulearndr'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER=ntulearndr
DB_FILE_NAME_CONVERT='+DATA_S3LDB','+DATA_N4LDB'
LOG_FILE_NAME_CONVERT= '+RECO_S3LDB','+RECO_N4LDB','+DATA_S3LDB','+DATA_N4LDB'
STANDBY_FILE_MANAGEMENT=AUTO
The db_unique_name parameter has already been set to the appropriate value during the initial creation of the RAC database. 
The log_archive_dest_state_n and remote_login_passwordfile have default values set to ENABLE and EXCLUSIVE respectively. So, only above mentioned parameters needed to be changed here.



INFO: The below entries can be executed from N4LDB5003A/N4LDB5003B. -- Completed (02Mar)

SYS@ntulearn2> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ntulearn,ntulearndr)' scope=both sid='*';
SYS@ntulearn2> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO_N4LDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ntulearn' scope=both sid='*';
SYS@ntulearn2> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ntulearndr SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ntulearndr' scope=both sid='*';
SYS@ntulearn2> alter system set log_archive_max_processes=8 scope=both sid='*';
SYS@ntulearn2> alter system set fal_server=ntulearndr scope=both sid='*';
SYS@ntulearn2> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=both sid='*' ==> Not changed. 
SYS@ntulearn2> alter system set db_file_name_convert='+DATA_S3LDB','+DATA_N4LDB' scope=spfile sid='*';  ==> Changes will reflect after bouncing database
SYS@ntulearn2> alter system set log_file_name_convert='+RECO_S3LDB','+RECO_N4LDB','+DATA_S3LDB','+DATA_N4LDB' scope=spfile sid='*'; ==> Changes will reflect after bouncing database
SYS@ntulearn2> alter system set standby_file_management=AUTO scope=both sid='*';
SYS@ntulearn2> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=both sid='*' ==> Already done previously 



Verify that the values are set correctly for these parameters after bouncing the database.
SYS@ntulearn2> set linesize 500 pages 0
SYS@ntulearn2> col value for a90
SYS@ntulearn2> col name for a50
SYS@ntulearn2> select name, value from v$parameter  where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert','log_file_name_convert', 'standby_file_management');







1.1.3. Enable Archivelog Mode.
SYS@ntulearn2> archive log list;

If not in archive log mode convert to archive log mode.

[ntulearn2 oracle@N4LDB5003B ~]$ srvctl stop database -d ntulearn -> downtime is required to bounce database.

SYS@ntulearn2> startup mount (only one instance)
SYS@ntulearn2> alter database archivelog;
SYS@ntulearn2> alter database open;
[ntulearn2 oracle@N4LDB5003B ~]$ srvctl stop database -d ntulearn
[ntulearn2 oracle@N4LDB5003B ~]$ srvctl start database -d ntulearn <- This will start the remaining Instances on the cluster.






1.1.4. Create the SLRs (Standby Redo Logs).  --Completed (02Mar)

There should be minimum of (threads)*(groups Per Threads + 1) SLRs created on the standby database.  There are 2 threads with 8 groups per thread in this configuration on the primary side so there should be total of 18 SLRs at 
minimum needs to be created. Let’s get the total Groups and Max size of the logfile from v$log.

SYS@ntulearn2> select max (bytes), count (1) from v$log;
MAX(BYTES)   COUNT(1)
---------- ----------
4294967296          8
Standby Log Location: (Can be done on either N4LDB5003A/B) -- 
SYS@ntulearn2> alter system set standby_file_management=manual scope=both sid='*';  --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 17 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 18 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 19 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 20 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 21 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 22 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 23 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 24 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 1 group 25 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 26 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 27 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 28 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 29 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 30 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 31 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 32 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 33 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)
SYS@ntulearn2> alter database add standby logfile thread 2 group 34 ('+DATA_N4LDB','+RECO_N4LDB') size 4096M; --Completed (02Mar)

SYS@ntulearn2> alter system set standby_file_management=auto scope=both sid='*'; ==> Error from node N4LDB5003B(ORA-32001: write to SPFILE requested but no SPFILE is in use) . This error will show when using wrong spfile.

INFO: To Check Standby logfiles: 
SYS@ntulearn2> select group#,thread#,bytes,status from v$standby_log;







1.1.5. Backup the Database for Standby (Not mandatory as there is a copy in NFS) -- Completed(02 Mar)
Take the backup of the primary database and the standby controlfile. 
Login to RMAN on N4LDB5003A/B.
NOTE: Required when backup is not on shared directory.

[ntulearn2 oracle@N4LDB5003B ~] rman target /
run
{
sql "alter system switch logfile";
backup database;
backup current controlfile for standby; -> Needed for standby.
backup current controlfile for standby format '/backup/ntulearn/02-03-2018/ntulearn_N4LDB5003_Standby_cntrl_%Y%M%D_%s_%p_%t.bk'; ==>Used one
sql "alter system archive log current";
}





1.1.6. Create pfile for standby database. -- Completed( 02 Mar)

Our database not running on spfile.  Login to to either N4LDB5003A/B.
SYS@ntulearn2> create pfile='/tmp/pfile_for_standby.ora' from spfile='+DATA_N4LDB/ntulearn/spfilentulearn.ora’;




1.1.7. Update TNSNAMES.ora (On BOTH N4LDB5003A & N4LDB5003B) -- Completed (02 Mar)

[ntulearn oracle@N4LDB5003A ~]$ vi /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora
[ntulearn2 oracle@N4LDB5003B ~]$ vi /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora
ntulearn =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = N4LDB5003-SCAN)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ntulearn)
    )
  )
ntulearn1=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=N4LDB5003-SCAN)(PORT=1522))
            (CONNECT_DATA=
                (SERVICE_NAME=ntulearn)
                (INSTANCE_NAME=ntulearn1)
            )
        )
ntulearn2=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=N4LDB5003-SCAN)(PORT=1522))
            (CONNECT_DATA=
                (SERVICE_NAME=ntulearn)
                (INSTANCE_NAME=ntulearn2)
            )
        )
Note: Copy the tnsnames.ora on all the instances under $ORACLE_HOME/network/admin to keep the same tnsnames.ora on all the instances.





2.1 Prepare Standby Site for DataGuard Configuration:

Copy the RMAN backup and pfile_for_standby.ora.
Copy the password file.
Create required directories for Standby Instance.
Modify pfile_for_standby.ora file.
Copy the  tnsnames.ora  file from primary.
Create the ASM directories under the Diskgroups.


2.1.1 Copy the RMAN backup and pfile_for_standby.ora: -- Completed (02 Mar)
Copy the backups from primary node1 ( NDC2 ) to Standby node (NDC3) under the same location(NFS). 
Also copy the pfile that was created for standby on primary node1 to the dr-node1.
[ntulearn2 oracle@N4LDB5003B ~] scp /tmp/pfile_for_standby.ora S3LDB5003A:/backup/ntulearn/pfile_for_standby.ora



2.1.2 Copy the password file: --Completed (02 Mar)
Copy the file $ORACLE_HOME/dbs/orapwntulearn1 from Primary node 1 (N4LDB5003A) to both the standby nodes under the directory $ORACLE_HOME/dbs with the name of orapwntulearndr1 and orapwntulearn2 for N4LDB5003A and S3LDB5003A 
respectively.
 
 Password was missing on Node N4LDB5003B. Copied from Node N4LDB5003A.(File Location :/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwntulearn1)
 
[ntulearn oracle@N4LDB5003A ~] scp $ORACLE_HOME/dbs/orapwntulearn1 S3LDB5003A:$ORACLE_HOME/dbs/orapwntulearndr1
[ntulearn2 oracle@N4LDB5003B ~] scp $ORACLE_HOME/dbs/orapwntulearn2 S3LDB5003B:$ORACLE_HOME/dbs/orapwntulearndr2
orapwd file=orapw${ORACLE_SID} password=oradba123# entries=10 force=y



2.1.2 Create required directories for Standby Instance: -- Complete (02 Mar )
On S3LDB5003A: 
[s3bblrn1 oracle@S3LDB5003A ~] mkdir -p /u01/app/oracle/admin/ntulearndr/adump
[s3bblrn1 oracle@S3LDB5003A ~] mkdir -p /u01/app/oracle/diag/rdbms/ntulearndr/ntulearndr1
[s3bblrn1 oracle@S3LDB5003A ~] cd /u01/app/oracle/diag/rdbms/ntulearndr/ntulearndr1
[s3bblrn1 oracle@S3LDB5003A ~] mkdir trace cdump -> Create 2 folders named trace & cdump
 
On S3LDB5003B: 
[s3bblrn2 oracle@S3LDB5003B ~] mkdir -p /u01/app/oracle/admin/ntulearndr/adump
[s3bblrn2 oracle@S3LDB5003B ~] mkdir -p /u01/app/oracle/diag/rdbms/ntulearndr/ntulearndr2
[s3bblrn2 oracle@S3LDB5003B ~] cd /u01/app/oracle/diag/rdbms/ntulearndr/ntulearndr2
[s3bblrn2 oracle@S3LDB5003B ~] mkdir trace cdump -> Create 2 folders named trace & cdump
These directories location could be different according to the database parameters changes. 




2.1.3  Modify pfile_for_standby.ora file (under $ORACLE_HOME/dbs)  -- Completed (02 Mar 2018)
Below mentioned parameter needed to modifiy . The rest of the parameters remain same on both the primary and standby.
control_files
db_file_name_convert
db_unique_name
fal_server
log_archive_dest_1
log_archive_dest_2
log_file_name_convert
remote_listener
local_listener
Comment out below parameters in parameter file:
cluster_database
dg_broker_config_file1
dg_broker_config_file2
dg_broker_start




2.1.4. Copy the tnsnames.ora file from primary: -- Completed (02 Mar 2018)
[ntulearn oracle@N4LDB5003A ~] scp /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora S3LDB5003A:/u01/app/12.1.0.2/grid/network/admin/
[ntulearn2 oracle@N4LDB5003B ~] scp /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora S3LDB5003B:/u01/app/12.1.0.2/grid/network/admin/




2.1.5 Create the ASM directories under the DiskGroups (S3LDB5003A or S3LDB5003B).  -- Completed (02 Mar 2018)
[+ASM2 grid@S3LDB5003B ~] asmcmd
ASMCMD> cd +DATA_S3LDB/
ASMCMD> mkdir NTULEARNDR
ASMCMD> cd +DATA_S3LDB/NTULEARNDR
ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG STANDBYLOG
 
ASMCMD> cd +RECO_S3LDB
ASMCMD> mkdir NTULEARNDR
ASMCMD> cd +RECO_S3LDB/NTULEARNDR
ASMCMD> mkdir ONLINELOG STANDBYLOG CONTROLFILE




2.1.6 Create the Physical Standby Database:
Start the Instance in NOMOUNT using the pfile_for_standby.ora.
Restore the Standby Database using RMAN DUPLICATE command.
Create the Online Redo logs and Standby redo logs.
Modify the TNSNAMES.ORA of Standby to add the Standby Database’s TNS alias.
Modify the TNSNAMES.ORA of Primary to add the Standby Database’s TNS alias.
Start the Managed Recovery Process
Create the spfile and start the database on both the nodes using spfile.
Register the New Standby database to the OCR.




2.1.7 Start the Instance in NOMOUNT using the pfile_for_standby.ora: -- Completed (02 mar 2018)
Add the below entry in /etc/oratab files and start the ntulearndr1 instance using pfile_for_standby.ora file. 
[oracle@S3LDB5003A ~] vi /etc/oratab
ntulearndr:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
ntulearndr1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
[oracle@S3LDB5003B ~] vi /etc/oratab
ntulearndr:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
ntulearndr2:/u01/app/oracle/product/12.1.0.2/dbhome_1:N
[oracle@S3LDB5003A ~]$ . oraenv
ORACLE_SID = [-] ? ntulearndr

[ntulearndr1 oracle@S3LDB5003A ~]$ sqlplus / as sysdba

SYS@ntulearndr1> startup nomount pfile=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/pfile_for_standby.ora
Restore the RMAN backup using DUPLICATE DATABASE option (on either S3LDB5003A or S3LDB5003B):
Mandatory: Add Static Listener Enrty for DR Database in node where you will initaite restore.In Our case we did from node S3LDB5003A.

vi /u01/app/12.1.0.2/grid/network/admin/listener.ora
==> Add Below entry to listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ntulearndr)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
      (SID_NAME = ntulearndr)
    )
  )
  
Bounce the listener:
lsnrstl stop LISTENER
lsnrstl start LISTENER
lsnrstl status LISTENER ==> Note Service status for the DR database should not be BLOCKED.

Connect to the Target database (ntulearn) and auxiliary instance (ntulearndr) from dr-node1(S3LDB5003A) host to start the DUPLICATE.

[ntulearndr1 oracle@S3LDB5003A ~]$ rman target sys/*******@ntulearn auxiliary / 
run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}




From Active database :
rman target sys/oradba123#@ut auxiliary sys/oradba123#@utdr

duplicate target database for standby from active database nofilenamecheck;





2.1.8 Modify the TNSNAMES.ORA of Standby to add the Standby Database’s TNS alias.  --Completed (02Mar)
Existing tnsnames.ora does not contain the TNS alias of the standby database. 
It only contains TNS aliases for the Primary database because this tnsnames.ora was copied over from primary. 
Add the below TNS alias for standby database/instances into the tnsnames.ora file on both the standby hosts.
[ntulearndr1 oracle@S3LDB5003A ~]$ vi /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora
[ntulearndr2 oracle@S3LDB5003B ~]$ vi /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora
ntulearndr =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = S3LDB5003-SCAN)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ntulearndr)
    )
  )

ntulearndr1=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=S3LDB5003-SCAN)(PORT=1522))
            (CONNECT_DATA=
                (SERVICE_NAME=ntulearndr)
                (INSTANCE_NAME=ntulearndr1)
            )
        )
ntulearndr2=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=S3LDB5003-SCAN)(PORT=1522))
            (CONNECT_DATA=
                (SERVICE_NAME=ntulearndr)
                (INSTANCE_NAME=ntulearndr2)
            )
        )




2.1.9. Modify the TNSNAMES.ORA of Primary to add the Standby Database’s TNS alias.--Completed (02 mar)
Add the below TNS alias for standby database/instances into the tnsnames.ora file on all primary hosts (N4LDB5003A & N4LDB5003B).
ntulearndr =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = S3LDB5003-SCAN)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ntulearndr)
    )
  )
ntulearndr1=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=S3LDB5003-SCAN)(PORT=1522))
            (CONNECT_DATA=
                (SERVICE_NAME=ntulearndr)
                (INSTANCE_NAME=ntulearndr1)
            )
        )
ntulearndr2=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=S3LDB5003-SCAN)(PORT=1522))
            (CONNECT_DATA=
                (SERVICE_NAME=ntulearndr)
                (INSTANCE_NAME=ntulearndr2)
            )
        )




2.1.10. Start the Managed Recovery Process:
Start the Managed recovery process on dr-node1 (S3LDB5003A) and verify that the log transport and log application is happening. Alert log is a quick and easy way to see if things log transport/Gap resolution and log application 
is working as expected. Start the tail –f on alert logs on both the standby nodes before starting the MRP.
SYS@ntulearndr1> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 
Perform the log switch on the PRIMARY database so initiate the log Transport.
 
SYS@ntulearn2> alter system switch logfile;
Similar information can be verified by running below SQLs on standby database.
SYS@ntulearndr1> select * from v$archive_gap;
SYS@ntulearndr1> select process, client_process, sequence#, status from v$managed_standby;
SYS@ntulearndr1> select sequence#, first_time, next_time, applied from v$archived_log;
SYS@ntulearndr1> select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
SYS@ntulearndr1> select thread#, max (sequence#) from v$log_history group by thread#;
SYS@ntulearndr1> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;





2.1.11. Create the spfile and start the database (S3LDB5003A) on both the nodes using spfile:
SYS@ntulearndr1> create spfile='+DATA_S3LDB/NTULEARNDR/spfilentulearndr.ora' from pfile='/backup/ntulearn/pfile_for_standby.ora';
File created.
After creating the spfile, create the below init.ora files under $ORACLE_HOME/dbs on both the dr nodes with the spfile entry so that the instance can start with the newly created spfile.
[ntulearndr1 oracle@S3LDB5003A ~]$ cd /u01/app/oracle/product/12.1.0/.2/dbhome_1/dbs/
[ntulearndr1 oracle@S3LDB5003A ~]$ vi initntulearndr1.ora
SPFILE='+DATA_S3LDB/NTULEARNDR/spfilentulearndr.ora' -> Add entry in initntulearndr1.ora file
[ntulearndr2 oracle@S3LDB5003B ~]$ cd /u01/app/oracle/product/12.1.0/.2/dbhome_1/dbs/
[ntulearndr2 oracle@S3LDB5003B ~]$ vi initntulearndr2.ora
SPFILE='+DATA_S3LDB/NTULEARNDR/spfilentulearndr.ora' 



2.1.12. Register the New Standby database (either S3LDB5003A or S3LDB5003B) to the OCR(Oracle Cluster Registry):
[ntulearndr1 oracle@S3LDB5003A ~]$ srvctl add database -d ntulearndr -n ntulearn -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA_S3LDB/NTULEARNDR/spfilentulearndr.ora -r physical_standby -a DATA_S3LDB,RECO_S3LDB
[ntulearndr1 oracle@S3LDB5003A ~]$ srvctl add instance -d ntulearndr -i ntulearndr1  -n S3LDB5003A
[ntulearndr1 oracle@S3LDB5003A ~]$ srvctl add instance -d ntulearndr -i ntulearndr2 -n S3LDB5003B
[ntulearndr1 oracle@S3LDB5003A ~]$ srvctl modify database -d ntulearndr -s mount
Stop the Managed Recovery Process:
SYS@ntulearndr1>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Shutdown DR Database :
SYS@ntulearndr1>Shut immediate
Start DR Database with Cluster command:
[ntulearndr1 oracle@S3LDB5003A ~]$ srvctl start database -d ntulearndr
 
Verify the configuration of standby database (either S3LDB5003A or S3LDB5003B) ntulearndr.
 
[ntulearndr1 oracle@S3LDB5003A ~]$ srvctl config database -d ntulearndr
Start the Managed Recovery Process:
SYS@ntulearndr1> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;





2.1.13. Create the Standby Redo Logs (SRLs) on Standby:
DUPLICATE DATABASE command has replicated the same no. of Online Redo Logs and Standby Redologs from primary database to the Standby database. 
So, they were not needed to create here.




2.1.14. Change the Protection Mode: (Default Configuration is maximum performance )
Node Protection Mode: Maximum Performance 
This is the lowest data protection mode. In this mode, service on the primary database is independent of the redo log transfer to standby databases. The performance of the primary database is not affected because the primary database does not wait for the log transport service to complete. MAXIMUM PERFORMANCE mode is the default mode. The DBA can choose between LGWR and ARCH for the transfers of redo logs. 
The network I/O attribute should be ASYNC when LGWR is writing redo logs on the standby database. Also, the disk I/O attribute should be NOAFFIRM to minimize any performance impact on the primary site. The following examples show the MAXIMUM PERFORMANCE mode settings.
On Primary (on either N4LDB5003A or N4LDB5003B):
SYS@ntulearn2> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ntulearndr ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ntulearn ' scope=both sid='*';
SYS@ntulearn2> alter database set standby database to maximize performance;
 
On Standby (on either S3LDB5003A or S3LDB5003B):
SYS@ntulearndr1> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ntulearn ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ntulearn' scope=both sid='*';

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more