LESSON 26
Steps to create a
logical standby database on the Oracle 12c Multitenant Architecture (CDB)
Missions:
-
Creating
a physical database on Multitenant Container
-
Using
Maximum Performance Mode
-
Using
Asynchronous Redo Transport and Real-time apply (The default DG
Configuration)
Summary Steps:
-
Make the
Primary database ready for Standby
-
Create a
Physical Standby database of a CDB
-
Create a
pluggable in a Primary database
Make the Primary
database ready for standby
-
Enable Forced logging
o
SQL>
ALTER DATABASE FORCE LOGGING;
-
Configure Redo Transport Authentication
o
Can be
authenticated either by SSL or a remote login password file. By SSL,
the LOG_ARCHIVE_DEST_n, and
FAL_SERVER parameters using Oracle
Net String connection. By remote login, you need to copy the primary
password file to each standby.
-
Configure the Primary Database to Receive Redo Data
o
You do this
to make Primary ready for a quick transition to the standby.
o
SQL>
ALTER DATABASE ADD STANDBY LOGIFLE ("/oradata/mycdb/"")
SIZE 500M;
-
Set
Primary Database Initialization Parameters
o
The following
parameter will be used when it is on Primary role.
"
DB_NAME=mydb
"
DB_UNIQUE_NAME=a
"
LOG_ARCHIVE_CONFIG="DG_CONFIG=(a,b)"
"
CONTROL_FILES="",""
"
LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=a"
"
LOG_ARCHIVE_DEST_2="SERVICE=b ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=b"
"
REMOTE_LOGIN_PASSWORKFILE=EXCLUSIVE
"
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
o
The following
parameter will be use when it is on Standby role.
"
FAL_SERVER=b
"
DB_FILE_NAME_CONVERT=
"
LOG_FILE_NAME_CONVERT=
"
STANDBY_FILE_MANAGEMENT=AUTO
-
Enable Archiving
o
SQL> SHUTDOWN IMMEDIATE;
o
SQL> STARTUP MOUNT;
o
SQL> ALTER DATABASE ARCHIVELOG;
o
SQL> ALTER DATABASE OPEN;
Create a physical
standby database on a CDB
-
Need
a backup copy of your primary
o
# rman target
/
o
RMAN> BACKUP
DATABASE PLUS ARCHIVELOG;
-
Create Control file
o
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS "b.ctl";
-
Create Parameter file
o
SQL> CREATE PFILE="initb.ora" FROM SPFILE;
o
Change the
file.
o
The following
parameter will be used when it is on Primary role.
"
DB_NAME=mydb
"
DB_UNIQUE_NAME=b
"
LOG_ARCHIVE_CONFIG="DG_CONFIG=(a,b)"
"
CONTROL_FILES="",""
"
LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=b"
"
LOG_ARCHIVE_DEST_2="SERVICE=a
ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=a"
"
REMOTE_LOGIN_PASSWORKFILE=EXCLUSIVE
"
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
o
The following
parameter will be use when it is on Standby role.
"
FAL_SERVER=a
"
DB_FILE_NAME_CONVERT=
"
LOG_FILE_NAME_CONVERT=
"
STANDBY_FILE_MANAGEMENT=AUTO
-
Copy
data files from primary to standby
o
# scp /oradata/a/* oracle@b:/oradata/b/.
-
Start
and verify the standby database
o
# rman target /
o
RMAN> STARTUP MOUNT;
o
RMAN> RESTORE "
o
RMAN> exit
o
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM
SESSION;
When you create a
standby database on a multi-tenant container database, you will notice
the following differences.
-
The database role is at the CDB level not on each container level.
-
A switchover or failover will be on entire CDB no each container.
Multi-tenant data guard
To
create a standby database on the multitenant container database, you
will do the same steps as we did for a non-multitenant database.
Just to
make sure do not miss:
Assumption:
Primary: mycdb
Standby: mycdbx
Make
sure:
Add
to listener for mycdb.
==========================
(SID_DESC
=
(GLOBAL_DBNAME = mycdb)
(ORACLE_HOME = /app/oracle/product/12.1.0.1/Db_1)
(ENVS = "LD_LIBRARY_PATH=/app/oracle/product/12.1.0.1/Db_1/lib")
(SID_NAME = mycdb)
)
(SID_DESC =
(GLOBAL_DBNAME = mycdb_dgmgrl)
(ORACLE_HOME = /app/oracle/product/12.1.0.1/Db_1)
(ENVS = "LD_LIBRARY_PATH=/app/oracle/product/12.1.0.1/Db_1/lib")
(SID_NAME = mycdb)
)
)
Add
to listener for mycdbx.
==========================
(SID_DESC =
(GLOBAL_DBNAME = mycdbx)
(ORACLE_HOME = /app/oracle/product/12.1.0.1/Db_1)
(ENVS = "LD_LIBRARY_PATH=/app/oracle/product/12.1.0.1/Db_1/lib")
(SID_NAME = mycdb)
)
(SID_DESC =
(GLOBAL_DBNAME = mycdbx_dgmgrl)
(ORACLE_HOME = /app/oracle/product/12.1.0.1/Db_1)
(ENVS = "LD_LIBRARY_PATH=/app/oracle/product/12.1.0.1/Db_1/lib")
(SID_NAME = mycdbx)
)
)
tnsnames: (Priamry and standby)
==========================
mycdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = p-eds-edsdevl.iselfschooling.com)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mycdb_dgmgrl)
(UR=A)
)
)
mycdbx =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = p-eds-oidmdbd.iselfschooling.com)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mycdbx_dgmgrl)
(UR=A)
)
)
On
the primary add the standby redo log files:
==============================================
SQL> alter database add standby logfile '/oradata/MYCDB/standbylog/stndby04.log'
size 52428800;
SQL> alter database add standby logfile '/oradata/MYCDB/standbylog/stndby05.log'
size 52428800;
SQL> alter database add standby logfile '/oradata/MYCDB/standbylog/stndby06.log'
size 52428800;
Start
the broker on Priamry and standby databases:
SQL> alter system set dg_broker_start=true;
Once
you duplicated your standby, then create the dg configuration file:
========================================================================
# rman target sys/mypass@mycdb auxiliary sys/mypass@mycdbx
RMAN> duplicate target database for standby from active database
nofilenamecheck;
# dgmgrl sys/mypass@mycdb
Connected.
DGMGRL> CREATE CONFIGURATION dg_mycdb AS PRIMARY DATABASE is mycdb
CONNECT IDENTIFIER is mycdb;
If
you get the following error, make sure to clear your LOG_ARCHIVE_DEST_n:
ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
--clearing on both Primary and standby.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_n=" ";
-- add
standby
DGMGRL> ADD DATABASE mycdbx AS CONNECT IDENTIFIER is mycdbx;
--enable
the configuration:
DGMGRL> enable configuration;
DGMGRL> show configuration;
If
you get the following error:
Error:
ORA-16525: the Data Guard broker is not yet available
You did
not set the dg_broker_start to true on both primary and standby:
SQL> alter system set dg_broker_start=true;
Good Luck!
|