Step by Step approach to
create an Oracle 12c Data Guard Active Standby Database (Multitenant Database –
4 Node RAC)
Step by Step
approach to create an Oracle 12c Data Guard Active Standby Database.
Environment Setup details
- Primary database up and running
on 4 node RAC
- Data files, OCR and voting Disks (in
separate DG) are in ASM storage.
- Binaries are in files system.
- DB Password files and
init<sid>.ora files are in $ORACLE_HOME/dbs
- Installed and Configured Grid
Infra on Standby machines, 4 Node RAC.
- OCR and voting Disks (in separate
DG) are in ASM storage.
- Created required Disk group on
Standby site for data files.
- RDBMS software installed in
standby machines.
- Ensured Network connectivity
between PRIMARY and STANDBY server (TCP/IP – Port 1521 & 1525).
Environment details – Primary Databases
OS Version:
Linux 6.2
|
Oracle Version: 12.1.0.2
|
Host Server:
LABPRD1, LABPRD2, LABPRD3, LABPRD4
|
Primary DB :
PRASPRD Instances :
PRASPRD1,PRASPRD2,PRASPRD3,PRASPRD4
|
Storage System:
ASM for Data Storage and Filesystem for Binaries
|
Grid Home(Binaries):
/prasprd/gridinfra/grid1
|
RDBMS
Home(Binaries) :/prasprd/db/db1
|
Disk Groups:
CRS1(High Redundancy, For OCR and Voting Disk), LABDATA2(Data files, Red logs
and Control files)
|
Environment details – Standby Databases
OS Version:
Linux 6.2
|
Oracle Version: 12.1.0.2
|
Host Server :
LABSBY1, LABSBY2, LABSBY3, LABSBY4
|
Primary DB :
PRASSBY Instances :
PRASPRD1,PRASPRD2,PRASPRD3,PRASPRD4
|
Storage System:
ASM for Data Storage and Filesystem for Binaries
|
Grid Home(Binaries):
/prasprd/gridinfra/grid1
|
RDBMS
Home(Binaries) :/prasprd/db/db1
|
Disk Groups:
CRS1(High Redundancy, For OCR and Voting Disk), LABDATA3(Data files, Red logs
and Control files)
|
Procedure
- Enable force logging in PRIMARY.
- Create SRL (standby redo logs) in
PRIMARY.
- Copy init and password file from
primary to STANDBY.
- Create directory structure in
STANDBY.
- Make Standby parameter changes in
the parameter file of PRIMARY.
- Make Standby parameter changes in
the parameter file of STANDBY.
- Add an entry in the oratab file
- Establish the connectivity between
PRIMARY and STANDBY.
- Start the STANDBY instance
- Use RMAN duplicate to create
standby database from PRIMARY database.
- Start the MRP process in STANDBY.
- Verify whether the log are shipped
and applied properly @the STANDBY.
- Start other standby RAC nodes.
- Verify Pluggable database’s
status.
Step by Step Active Standby Build
1. Enable force logging on PRIMARY
SQL> alter database force logging;
2. Configure Standby Redo Log on PRIMARY
a. Check the log
files and sizes.
SQL>SELECT GROUP#, BYTES FROM V$LOG;
b. Create Standby Redo
Log
SQL> ALTER DATABASE ADD STANDBY
LOGFILE THREAD <TN> GROUP <GN> (‘/path/<File
Name>’, ‘/path/<File
Name>’ ) SIZE <S> ;
NOTE: SIZE OF STANDBY LOG FILE SHOULD BE SAME AS
ONLINE LOG FILE
c. Verify the
standby redo log file groups were created .Verify this in standby after standby
build.
SQL> SELECT GROUP#, THREAD#, SEQUENCE#,
ARCHIVED, STATUS FROM V$STANDBY_LOG;
(Primary)
GROUP#
|
THREAD#
|
SEQUENCE#
|
ARC
|
STATUS
|
9
|
1
|
0
|
YES
|
UNASSIGNED
|
10
|
1
|
0
|
YES
|
UNASSIGNED
|
11
|
2
|
0
|
YES
|
UNASSIGNED
|
12
|
2
|
0
|
YES
|
UNASSIGNED
|
13
|
3
|
0
|
YES
|
UNASSIGNED
|
14
|
3
|
0
|
YES
|
UNASSIGNED
|
15
|
4
|
0
|
YES
|
UNASSIGNED
|
16
|
4
|
0
|
YES
|
UNASSIGNED
|
(Standby – Below table output after the
standby is created)
GROUP#
|
THREAD#
|
SEQUENCE#
|
ARC
|
STATUS
|
9
|
1
|
0
|
YES
|
UNASSIGNED
|
10
|
1
|
509
|
YES
|
ACTIVE
|
11
|
2
|
0
|
YES
|
UNASSIGNED
|
12
|
2
|
298
|
YES
|
ACTIVE
|
13
|
3
|
0
|
YES
|
UNASSIGNED
|
14
|
3
|
500
|
YES
|
ACTIVE
|
15
|
4
|
0
|
YES
|
UNASSIGNED
|
16
|
4
|
287
|
YES
|
ACTIVE
|
**Here I have 4 nodes having 2 redo groups each
node, total 8 groups; created 8 more standby log groups 2 per node.
3. Copy
init and password file from primary to standby
Copy
initPRASPRD1.ora to standby database (all 4 Nodes), name of init<sid>.ora
needs to be changed depends on instance names
Copy the password file orapwPRASPRD1
from primary to standby database (all 4 nodes), name of init needs to be
changed depends on instance names
4. Create
directory structure in standby
$ mkdir -p
/u01/app/oracle/admin/prasprd/adump (on all 4 node)
5. Make
Standby parameter changes in the parameter file of PRIMARY.
Add below entries in the
init<sid>.ora.(Make the entry in one Node pfile and copy to other nodes)
$ cat
initprasprd1.ora
##########DG
Enrty ################
*.log_archive_max_processes='8'
*.db_unique_name=PRASPRD'
*.log_archive_config='dg_config=(PRASSBY,PRASPRD)'
*.log_archive_dest_1='location=+LABDATA2
valid_for=(all_logfiles,all_roles)
*.log_Archive_dest_2='service=PRASSBY_DR
async noaffirm reopen=15 valid_for=(all_logfiles,primary_role)
db_unique_name=PRASSBY'
*.log_file_name_convert='+LABDATA3','+LABDATA2'
*.db_file_name_convert='+LABDATA3','+LABDATA2'
*.fal_server=PRASSBY_DR;
*.fal_client=PRASPRD;
#############DG Entry ############################
6. Make
Standby parameter changes in the parameter file of Standby
Add below entries in the
init<sid>.ora.(Make the entry in one Node pfile and copy to other nodes)
$ cat initprasprd1.ora
##########DG Enrty ################
*.log_archive_max_processes='8'
*.db_unique_name='PRASSBY'
*.standby_file_management='AUTO'
*.log_archive_config='dg_config=(PRASPRD,PRASSBY)'
*.log_archive_dest_1='location=+LABDATA3 valid_for=(all_logfiles,all_roles) db_unique_name=PRASSBY'
*.log_Archive_dest_2='service=PRASPRD async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRASPRD'
*.log_file_name_convert='+LABDATA2','+LABDATA3'
*.db_file_name_convert='+LABDATA2','+LABDATA3'
*.fal_server=PRASPRD
*.fal_client=PRASSBY_DR
#############DG Entry ############################
7. Add an entry
in the oratab file
PRASPRD1:
/prasprd/db/db1: N (Node1)
PRASPRD2:
/prasprd/db/db1: N (Node2)
PRASPRD3:
/prasprd/db/db1: N (Node3)
PRASPRD4:
/prasprd/db/db1: N (Node4)
8. Establish
the connectivity between PRIMARY and STANDBY.
On Standby (Add a static entry in
the listener.ora for PRASSBY)
LISTENER12C =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =LABSBY1.me.domain)(PORT = 1525))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
)
)
SID_LIST_LISTENER12C
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRASSBY)
(ORACLE_HOME = /prasprd/db/db1)
(SID_NAME = PRASPRD1)
)
)
Reload the listener
$ lsnrctl reload
listener12c
LSNRCTL for
Linux: Version 12.1.0.2.0 - Production on Tue Jan 6 12:11:31 2015
Copyright (c)
1991, 2014, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= LABPRD1.me.domain) (PORT=1525)))
The command
completed successfully
On Primary (Add a below entry
on tnsnames.ora)
PRASSBY_DR =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = LABSBY1.me.domain)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PRASSBY)
)
)
PRASPRD =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST =LABPRD1.corporate.domain)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRASPRD)
)
)
On Standby(Add a below entry on tnsnames.ora)
PRASSBY_DR =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = LABSBY1.corporate.domain)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PRASSBY)
)
)
PRASPRD =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST =LABPRD1.corporate.domain)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRASPRD)
)
)
9. Start
the STANDBY database instance.
$ . oraenv
ORACLE_SID =
[prasprd1] ? prasprd1
The Oracle base has
been set to /u01/app/orabase
oradb@PRASSBY1.ME.DOMAIN:/home/oradb
~]sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Jan 6 12:11:31 2015
Copyright (c) 1982, 2014, Oracle. All
rights reserved.
Connected to
an idle instance.
SQL>
startup nomount;
ORACLE instance started.
Total System Global
Area XXXXXXXXX bytes
Fixed
Size
XXXXXXX bytes
Variable
Size
XXXXXXXXX bytes
Database
Buffers XXXXXXXX
bytes
Redo
Buffers XXXXXXX
bytes
10. Use
RMAN duplicate to create standby database on Primary database.
Recovery
Manager: Release 12.1.0.2.0 – Production on Tue Jan 6 14:40:12 2015 Copyright
(c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN>
connect target sys/*****@PRASPRD connected to target database: PRASPRD
(DBID=2845711114)
RMAN>
connect auxiliary sys/*****@PRASSBY_DR
connected
to auxiliary database: PRASPRD (not mounted)
run
{
allocate
channel c1 type disk;
allocate
channel c2 type disk;
allocate
auxiliary channel aux type disk;
duplicate
target database for standby from active database;
alter
system set log_archive_max_processes=’8′;
alter
system set db_unique_name=’PRASSBY’;
alter
system standby_file_management=’AUTO’;
alter
system set log_archive_config=’dg_config=(PRASPRD,PRASSBY)’;
alter
system set log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST
valid_for=(all_logfiles,all_roles) db_unique_name=PRASSBY’;
alter
system set log_Archive_dest_2=’service=prasprd async noaffirm reopen=15
valid_for=(all_logfiles,primary_role) db_unique_name=PRASPRD’;
log_file_name_convert=’+LABDATA3′,’+LABDATA2′
db_file_name_convert=’+LABDATA3′,’+LABDATA2′
}
11. Start the MRP process on
standby database
SQL> startup ;
ORACLE instance
started.
Total System Global
Area XXXXXXXXX bytes
Fixed
Size
XXXXXXX bytes
Variable
Size
XXXXXXXXX bytes
Database
Buffers
XXXXXXXXX bytes
Redo
Buffers XXXXXXX
bytes
Database mounted.
Database opened.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Media recovery complete……..
12. Verify
MRP process on standby database
oradb@LABPRD.CORPORATE.DOMAIN:/home/oradb
~]ps -ef | grep mrp
oradb
23678 1 0 Jan05
? 00:00:05 ora_mrp0_prasprd1
SQL>
select process,status,thread#,sequence#,blocks from v$managed_standby where
process like ‘%MRP%’;
PROCESS
|
STATUS
|
THREAD#
|
SEQUENCE#
|
BLOCKS
|
MRP0
|
APPLYING_LOG
|
1
|
498
|
102400
|
13. Verify whether the log
are shipped and applied properly @the standby.
On Primary
SQL> ALTER
SYSTEM SWITCH LOGFILE;
SQL > select
thread#, max(sequence#) “Last Primary Seq Generated”
From v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
THREAD#
|
LAST
PRIMARY SEQ GENERATED
|
1
|
497
|
2
|
283
|
3
|
484
|
4
|
277
|
On Standby
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
|
497
|
497
|
0
|
2
|
283
|
283
|
0
|
3
|
484
|
484
|
0
|
4
|
277
|
277
|
0
|
14. Startup other
RAC standby node.
SQL> startup
open;
15. Check pluggable database
and status in PRIMARY and STANDBY.
On Primary
SQL> select name, open_mode from v$pdbs; or show pdbs
NAME
|
OPEN_MODE
|
PDB$SEED
|
READ ONLY
|
PDB1
|
MOUNTED
|
PDB2
|
MOUNTED
|
On Standby
SQL>
select name, open_mode from v$pdbs; or show pdbs
NAME
|
OPEN_MODE
|
PDB$SEED
|
READ ONLY
|
PDB1
|
MOUNTED
|
PDB2
|
MOUNTED
|
16. Check
database ROLE
On Primary
SQL> select
NAME, DATABASE_ROLE, SWITCHOVER_STATUS,DB_UNIQUE_NAME,CDB,CON_ID from
v$database;
NAME
|
DATABASE_ROLE
|
SWITCHOVER_STATUS
|
DB_UNIQUE_NAME
|
CDB
|
CON_ID
|
CDBORCL
|
PRIMARY
|
TO STANDBY
|
cdborcl
|
YES
|
0
|
On Standby
SQL>
select NAME, DATABASE_ROLE, SWITCHOVER_STATUS,DB_UNIQUE_NAME,CDB,CON_ID from
v$database;
NAME
|
DATABASE_ROLE
|
SWITCHOVER_STATUS
|
DB_UNIQUE_NAME
|
CDB
|
CON_ID
|
CDBORCL
|
PHYSICAL STANDBY
|
NOT ALLOWED
|
cdborcdr
|
YES
|
0
|
17. Confirm
that the primary database protection mode
SQL> SELECT
PROTECTION_MODE FROM V$DATABASE;
PROTECTION_MODE
——————————-
MAXIMUM PERFORMANCE
——————>
At this point Physical standby creation completed.
Test
SCENARIOS
=============
You
can do several test scenarios to make sure changes are getting shipped to
standby location.
- · Test
1 – Create Table on any PDB schema can verify the changes take effect on
Standby site.
Primary
Create
a table, insert some values
Standby
1.
alter pluggable database all open read only;
2. Connect to PDB .
3. Select count (*) from schema.tablename
- · Test
2 – Clone a PDB and verify the changes take effect on Standby site.
Primary
CREATE PLUGGABLE DATABASE pdb2 ADMIN
USER pdb2adm IDENTIFIED BY meadmin;
SQL> select name, open_mode from
v$pdbs; or show pdbs;
Standby
1.
Verify Alert log
2. SQL> select name, open_mode
from v$pdbs; or show pdbs;
