_____________________________________________________________________________________________________________________
Primary
Scan : CLUSCANPROD01 DB Name : ADMPROD DB Unique Name : ADMPROD Instances : ADMPROD1 & ADMPROD2 PORT : 1521 Version & Storage : Oracle 12c with ASM
Scan : CLUSCANSTY01 DB Name : ADMPROD DB Unique Name : ADMPRODSTBY Instances : ADMPROD1 & ADMPROD2 PORT : 1521 Version & Storage : Oracle 12c with ASM
1. Enable force logging
SQL> select force_logging from V$DATABASE; FORCE_LOGGING ------------- NO SQL> alter database force logging; Database altered. SQL> select force_logging from V$DATABASE; FORCE_LOGGING --------------------------------------- YES
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ADMPROD,ADMPRODSTBY)' scope=both sid='*'; alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ADMPROD' scope=both sid='*'; alter system set LOG_ARCHIVE_DEST_2='SERVICE=ADMPRODSTBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPRODSTBY' scope=both sid='*'; alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*'; alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*'; alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile sid='*'; alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*'; alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*'; alter system set FAL_SERVER=ADMPRODSTBY scope=both sid='*'; alter system set DB_FILE_NAME_CONVERT='ADMPRODSTBY','ADMPROD' scope=spfile sid='*'; alter system set LOG_FILE_NAME_CONVERT='ADMPRODSTBY','ADMPROD' scope=spfile sid='*'; Note: Concept of convert parameter is as follows In primary site: Standby Name -> primary Name('ADMPRODSTBY','ADMPROD') In Standby Site: Primary Name -> Standby Name('ADMPROD','ADMPRODSTBY')
The above commands which specified as scope=spfile requires database bounce; but we have a database bounce planned in step 3.
3. Change the primary database to archive log mode.
This step requires database bounce which will make the above alter system commands to effect. In case your database is already in archive log mode you have to bounce explicitly to make above changes to effect.
srvctl stop database -d ADMPROD sqlplus / as sysdba startup mount; alter database archivelog; alter database open; archive log list; scripts]$ srvctl start database -d ADMPROD scripts]$ srvctl status database -d admprod
4. Create directory for standby arch log in Primary and Create standby archive logs
Concepts of standby redo log file groups
a. The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
b. Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads
But in my example case I have created same number standby redo log groups as redo log groups
ASMCMD> mkdir +DATA/ADMPROD/STANDBYLOG ASMCMD> mkdir +DATA/ADMPROD/PASSWORD ASMCMD> mkdir +DATA/ADMPROD/DATAGUARDCONFIG alter system set standby_file_management=manual scope=both sid='*'; alter database add standby logfile thread 1 group 7 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_07_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_07_b.log') size 500M; alter database add standby logfile thread 2 group 8 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_08_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_08_b.log') size 500M; alter database add standby logfile thread 1 group 9 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_09_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_09_b.log') size 500M; alter database add standby logfile thread 2 group 10 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_10_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_10_b.log') size 500M; alter system set standby_file_management=auto scope=both sid='*'; SQL> select member,type from v$logfile; MEMBER TYPE ------------------------------------------------------------ ------- +DATA/ADMPROD/ONLINELOG/group_1.615.923394927 ONLINE +DATA/ADMPROD/ONLINELOG/group_1.614.923394929 ONLINE +DATA/ADMPROD/ONLINELOG/group_2.607.923394929 ONLINE +DATA/ADMPROD/ONLINELOG/group_2.606.923394931 ONLINE +DATA/ADMPROD/ONLINELOG/group_3.626.923394933 ONLINE +DATA/ADMPROD/ONLINELOG/group_3.627.923394933 ONLINE +DATA/ADMPROD/ONLINELOG/group_4.628.923394935 ONLINE +DATA/ADMPROD/ONLINELOG/group_4.629.923394937 ONLINE +DATA/ADMPROD/STANDBYLOG/stbylog_group_07_a.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_07_b.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_08_a.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_08_b.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_09_a.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_09_b.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_10_a.log STANDBY +DATA/ADMPROD/STANDBYLOG/stbylog_group_10_b.log STANDBY 16 rows selected.
rman target / nocatalog run { sql "alter system switch logfile"; allocate channel ch1 type disk format '/u01/DBBKUP/ADMPROD/Primary_bkp24Sep_for_stndby_%U'; backup database; backup current controlfile for standby; sql "alter system archive log current"; }
6. Create parameter for standby database. This will be modified with standby database related parameters
SQL> create pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt' from spfile;
$ pwd /u01/DBBKUP/ADMPROD $ scp * oracle@node1:/u01/DBBKUP/ADMPROD
8. Set TNS entries in both primary nodes
Open $ORACLE_HOME/network/admin/tnsnames.ora file and add tns entries.
vi /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora ADMPROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANPROD01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ADMPROD) ) ) ADMPRODSTBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANSTY01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ADMPRODSTBY) ) )
Setup Standby Database
9. Modify below parameter in standby_pfile.txt file which we created and copied in step 6 & 7
*.audit_file_dest='/u01/app/oracle/admin/ADMPRODSTBY/adump'*.control_files='+DATA/ADMPRODSTBY/CONTROLFILE/control01.ctl','+DATA/ADMPRODSTBY/CONTROLFILE/control02.ctl' *.db_file_name_convert='ADMPROD','ADMPRODSTBY' *.db_unique_name='ADMPRODSTBY' *.fal_server='ADMPROD' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ADMPRODSTBY' *.log_archive_dest_2='SERVICE=ADMPROD ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPROD' *.log_file_name_convert='ADMPROD','ADMPRODSTBY' *.remote_listener='CLUSCANSTY01:1521'
Filesystem mkdir /u01/app/oracle/admin/ADMPRODSTBY/adump
ASM ASMCMD> mkdir ADMPRODSTBY ASMCMD> cd ADMPRODSTBY ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG STANDBYLOG PASSWORD DATAGUARDCONFIG
11. Startup (nomount mode) standby instance in RAC Node 1 by setting the environment variables and new parameter file.
ORACLE_SID=ADMPROD1 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 sqlplus / as sysdba startup nomount pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt'
rman target sys/paygate1@admprod auxiliary / DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
vi /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora ADMPRODSTBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANSTY01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ADMPRODSTBY) ) ) ADMPROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANPROD01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ADMPROD) ) )
14. Setting up the Password file. Name: orapwd
Check where is your database password file located. If it is 12c RAC database which is created by DBCA, the password file generally to ASM location. You can check the location by using following command.
$ srvctl config database -d ADMPROD|grep Password Password file: +DATA/ADMPROD/PASSWORD/orapwadmprod
orapwd file='+DATA/ADMPROD/PASSWORD/orapwadmprod' password=paygate1 force=y ignorecase=y entries=5 dbuniquename=ADMPROD
In Standby database initially you create the password file in the file system and further you can move to ASM when the ASM is ready with both instance and spfile.
orapwd file='$ORACLE_HOME/dbs/orapwADMPROD1' password=paygate1 force=y ignorecase=y entries=5
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. Switch the logfile in Primary SQL> alter system switch logfile; System altered. Check the error if any by using following query in primary set pages 1000 lines 120 select DEST_NAME,INST_ID,ERROR from gV$ARCHIVE_DEST_STATUS where DEST_NAME='LOG_ARCHIVE_DEST_2';
create spfile='+DATA' from pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt';
ASMCMD> pwd +DATA/ADMPRODSTBY/PARAMETERFILE ASMCMD> ls spfile.428.923407977 Get the SPFILE location from the ASM as below and update the content of the parameter (init) file /u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD1.ora with spfile='+DATA/ADMPRODSTBY/PARAMETERFILE/spfile.428.923407977' [ dbs]$ cat /u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD1.ora spfile='+DATA/ADMPRODSTBY/PARAMETERFILE/spfile.428.923407977'
17. Copy init and password file from standby node 1 to node 2. Note the init file name with instance numbers.
$ scp /u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD1.ora oracle@node2:/u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD2.ora $ scp /u01/app/oracle/product/12.1.0/db_1/dbs/orapwADMPROD1 oracle@node2:/u01/app/oracle/product/12.1.0/db_1/dbs/orapwADMPROD2
18. Add the database to cluster using srvctl command
Note add the DB Unique name to the cluster using srvctl command.
· srvctl add database -d ADMPRODSTBY -n ADMPROD -o /u01/app/oracle/product/12.1.0/db_1 -r physical_standby · srvctl add instance -d ADMPRODSTBY -i ADMPROD1 -n node1 · srvctl add instance -d ADMPRODSTBY -i ADMPROD2 -n node2
mkdir /u01/app/oracle/admin/ADMPRODSTBY/adump
orapwd file='+DATA/ADMPRODSTBY/PASSWORD/orapwadmprodstby' password=paygate1 force=y ignorecase=y entries=5 dbuniquename=ADMPRODSTBY
$ srvctl stop database -d admprodstby $ srvctl start database -d admprodstby
a. To check the database mode and status.
SQL> select name, open_mode, database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- ADMPROD READ ONLY PHYSICAL STANDBY ADMPROD READ ONLY PHYSICAL STANDBY b. To check the standby database gap
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 26 26 0 2 7 7 0 c. To check the Primary log_archive_dest status
select DEST_NAME,INST_ID,ERROR from gV$ARCHIVE_DEST_STATUS where DEST_NAME='LOG_ARCHIVE_DEST_2' DEST_NAME INST_ID ERROR ------------------------------ ---------- ----------- LOG_ARCHIVE_DEST_2 2 LOG_ARCHIVE_DEST_2 1
Primary:alter system set LOG_ARCHIVE_DEST_2='SERVICE=ONLPRODSTBY SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPRODSTBY' scope=both sid='*'; alter database set standby database to maximize availability; SQL> select protection_mode,name, open_mode, database_role from gv$database; PROTECTION_MODE NAME OPEN_MODE DATABASE_ROLE -------------------- --------- -------------------- ---------------- MAXIMUM AVAILABILITY ADMPROD READ WRITE PRIMARY MAXIMUM AVAILABILITY ADMPROD READ WRITE PRIMARY Standby:
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ADMPROD SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPRODSTBY' scope=both sid='*'; alter database set standby database to maximize availability; SQL> select protection_mode,name, open_mode, database_role from gv$database; PROTECTION_MODE NAME OPEN_MODE DATABASE_ROLE -------------------- --------- -------------------- ---------------- MAXIMUM AVAILABILITY ADMPROD READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY ADMPROD READ ONLY WITH APPLY PHYSICAL STANDBY
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment