_____________________________________________________________________________________________________________________
Assumptions
Primary
Scan : CLUSCANPROD01
DB Name : ADMPROD
DB Unique Name : ADMPROD
Instances : ADMPROD1
& ADMPROD2
PORT : 1521
Version & Storage :
Oracle 12c with ASM
Standby
Scan : CLUSCANSTY01
DB Name : ADMPROD
DB Unique Name : ADMPRODSTBY
Instances : ADMPROD1
& ADMPROD2
PORT : 1521
Version & Storage :
Oracle 12c with ASM
Setup Primary Database
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
2. Setup
Primary init Parameters
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.
5. Backup the
primary database for standby
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;
File created.
7. Copy Backup
and pfile (step 5 & 6) to standby database server.
$ 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'
10. Setting up
ASM & Directory
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'
12. Restore primary
database backup in standby node
rman target sys/paygate1@admprod auxiliary /
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
13. Add
tnsnames.ora entries in Standby Server
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<dbname>
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
Primary
orapwd file='+DATA/ADMPROD/PASSWORD/orapwadmprod'
password=paygate1 force=y ignorecase=y entries=5 dbuniquename=ADMPROD
Standby
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
15. Start the
recovery in Standby database.
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';
16. Create
SPFILE in Standby database.
create spfile='+DATA' from pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt';
Validate the file in the Standby ASM
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
19. Create the
adump directory in Standby Node 2
mkdir /u01/app/oracle/admin/ADMPRODSTBY/adump
20. Move the
password file to the ASM in standby database
orapwd file='+DATA/ADMPRODSTBY/PASSWORD/orapwadmprodstby'
password=paygate1 force=y ignorecase=y entries=5 dbuniquename=ADMPRODSTBY
21. Bounce the database
using srvctl
$ srvctl stop database -d admprodstby
$ srvctl start database -d admprodstby
22. Following queries
can be used to validate the database.
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
23. To change
the standby protection mode to maximum availability
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