373 lines
12 KiB
Plaintext
Executable File
373 lines
12 KiB
Plaintext
Executable File
== CONTEXT
|
|
==========
|
|
|
|
~~ PRIMARY cluster: vortex-db01,vortex-db02
|
|
~~ PRIMARY database: DB_NAME=GOTAL, DB_UNIQUE_NAME=GOTALPRD
|
|
|
|
~~ STANDBY cluster: kessel-db01,kessel-db02
|
|
~~ STANDBY database: DB_NAME=GOTAL, DB_UNIQUE_NAME=GOTALDRP
|
|
|
|
|
|
== PRIMARY database creation
|
|
============================
|
|
|
|
-- in 11gR2 version, if we want different DB_NAME <> DB_UNIQUE_NAME, for exammple: DB_NAME=GOTAL and DB_UNIQUE_NAME=GOTALPRD
|
|
-- we should manualy create DB_NAME directory under data diskgroup before starting dbca
|
|
|
|
asmcmd mkdir +DATA/GOTAL
|
|
|
|
$ORACLE_HOME/bin/dbca \
|
|
-silent \
|
|
-createDatabase \
|
|
-templateName General_Purpose.dbc \
|
|
-gdbName GOTAL \
|
|
-sid GOTALPRD \
|
|
-initParams db_unique_name=GOTALPRD \
|
|
-characterSet AL32UTF8 \
|
|
-sysPassword secret \
|
|
-systemPassword secret \
|
|
-emConfiguration NONE \
|
|
-storageType ASM \
|
|
-diskGroupName DATA \
|
|
-redoLogFileSize 100 \
|
|
-sampleSchema FALSE \
|
|
-totalMemory 1000 \
|
|
-nodelist vortex-db01,vortex-db02
|
|
|
|
-- dbca will create 2 directory under data diskgroup: DB_NAME and DB_UNIQUE_NAME
|
|
-- DB_NAME directory contains only a link to physical spfile in DB_UNIQUE_NAME directory
|
|
-- DB_NAME can pe supressed if we crete the spfile directly link under DB_UNIQUE_NAME directory and we modify the database spfile parameter value in CRS
|
|
|
|
SQL> create pfile='/tmp/pfile.txt' from spfile='+DATA/gotal/spfilegotalprd.ora';
|
|
ASMCMD > rm -rf +DATA/GOTAL
|
|
SQL> create spfile='+DATA/GOTALPRD/spfilegotalprd.ora' from pfile='/tmp/pfile.txt';
|
|
|
|
srvctl modify database -d GOTALPRD -p +DATA/GOTALPRD/spfilegotalprd.ora
|
|
srvctl stop database -d GOTALPRD
|
|
srvctl start database -d GOTALPRD
|
|
srvctl status database -d GOTALPRD -v
|
|
|
|
|
|
~~ enable ARCHIVELG mode on the PRIMARY database
|
|
|
|
alter system set db_recovery_file_dest_size = 4G scope=both sid='*';
|
|
alter system set db_recovery_file_dest = '+RECO' scope=both sid='*';
|
|
alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';
|
|
|
|
srvctl stop database -d GOTALPRD
|
|
|
|
startup mount exclusive
|
|
alter database archivelog;
|
|
alter database open;
|
|
|
|
srvctl stop database -d GOTALPRD
|
|
srvctl start database -d GOTALPRD
|
|
|
|
alter system archive log current;
|
|
|
|
|
|
== STANDBY database creation
|
|
============================
|
|
|
|
- create pfile from PRIMARY spfile
|
|
- modify pfile by replacing required values like DB_UNIQUE_NAME, INSTANCE_NAME, remote_listener etc.
|
|
- copy pfile on a STANDBY host and test a startup nomount
|
|
- copy the passwordfile from PRIMARY to STANDBY hosts
|
|
|
|
|
|
== NETWORK configuration
|
|
========================
|
|
|
|
-- listener.ora enteries on vortex-db01
|
|
# For DATAGUARD...
|
|
SID_LIST_LISTENER_DG =
|
|
(SID_LIST =
|
|
(SID_DESC =
|
|
(GLOBAL_DBNAME = GOTALPRD_DGMGRL)
|
|
(SID_NAME = GOTALPRD1)
|
|
(ORACLE_HOME = /app/oracle/product/11.2/db_1)
|
|
)
|
|
)
|
|
|
|
# ...For DATAGUARD
|
|
|
|
-- listener.ora enteries on vortex-db02
|
|
# For DATAGUARD...
|
|
SID_LIST_LISTENER_DG =
|
|
(SID_LIST =
|
|
(SID_DESC =
|
|
(GLOBAL_DBNAME = GOTALPRD_DGMGRL)
|
|
(SID_NAME = GOTALPRD2)
|
|
(ORACLE_HOME = /app/oracle/product/11.2/db_1)
|
|
)
|
|
)
|
|
# ...For DATAGUARD
|
|
|
|
|
|
-- listener.ora enteries on kessel-db01
|
|
# For DATAGUARD...
|
|
SID_LIST_LISTENER_DG =
|
|
(SID_LIST =
|
|
(SID_DESC =
|
|
(GLOBAL_DBNAME = GOTALDRP_DGMGRL)
|
|
(SID_NAME = GOTALDRP1)
|
|
(ORACLE_HOME = /app/oracle/product/11.2/db_1)
|
|
)
|
|
)
|
|
|
|
# ...For DATAGUARD
|
|
|
|
-- listener.ora enteries on kessel-db02
|
|
# For DATAGUARD...
|
|
SID_LIST_LISTENER_DG =
|
|
(SID_LIST =
|
|
(SID_DESC =
|
|
(GLOBAL_DBNAME = GOTALDRP_DGMGRL)
|
|
(SID_NAME = GOTALDRP2)
|
|
(ORACLE_HOME = /app/oracle/product/11.2/db_1)
|
|
)
|
|
)
|
|
# ...For DATAGUARD
|
|
|
|
|
|
-- GLOBAL_DBNAME value is the name of the service visible with:
|
|
lsnrctl services LISTENER_DG
|
|
|
|
-- cross connection tests; we should be able to connect to iddle instances too
|
|
sqlplus /nolog
|
|
connect sys/secret@vortex-db01-dba-vip:1541/GOTALPRD_DGMGRL as sysdba
|
|
connect sys/secret@vortex-db02-dba-vip:1541/GOTALPRD_DGMGRL as sysdba
|
|
connect sys/secret@kessel-db01-dba-vip:1541/GOTALDRP_DGMGRL as sysdba
|
|
(for the moment the standby pfile/passwordfile are not deployed on second node of the standby cluster)
|
|
|
|
-- aliases to add on tnsnames.ora on all database nodes
|
|
# For DATAGUARD...
|
|
GOTALPRD_DG =
|
|
(DESCRIPTION =
|
|
(FAILOVER = YES)
|
|
(ADDRESS_LIST =
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = vortex-db01-dba-vip)(PORT = 1541))
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = vortex-db02-dba-vip)(PORT = 1541))
|
|
)
|
|
(CONNECT_DATA =
|
|
(SERVER = DEDICATED)
|
|
(SERVICE_NAME = GOTALPRD_DGMGRL)
|
|
)
|
|
)
|
|
|
|
|
|
GOTALDRP_DG =
|
|
(DESCRIPTION =
|
|
(FAILOVER = YES)
|
|
(ADDRESS_LIST =
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = kessel-db01-dba-vip)(PORT = 1541))
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = kessel-db02-dba-vip)(PORT = 1541))
|
|
)
|
|
(CONNECT_DATA =
|
|
(SERVER = DEDICATED)
|
|
(SERVICE_NAME = GOTALDRP_DGMGRL)
|
|
)
|
|
)
|
|
# ...For DATAGUARD
|
|
|
|
|
|
-- connexion test using TNS aliases
|
|
-- we should be able to connect to iddle instances
|
|
|
|
sqlplus /nolog
|
|
connect sys/secret@GOTALPRD_DG as sysdba
|
|
connect sys/secret@GOTALDRP_DG as sysdba
|
|
|
|
-- put the primary database in FORCE LOGGING mode
|
|
SQL> alter database force logging;
|
|
SQL> select force_logging from gv$database;
|
|
|
|
-- from the spfile of primary DB we create an spfile for the secondary DB and we start thesecondary DB in nomount
|
|
rman target sys/secret@GOTALPRD_DG auxiliary sys/secret@GOTALDRP_DG
|
|
run {
|
|
allocate channel pri1 device type DISK;
|
|
allocate channel pri2 device type DISK;
|
|
allocate auxiliary channel aux1 device type DISK;
|
|
allocate auxiliary channel aux2 device type DISK;
|
|
duplicate target database
|
|
for standby
|
|
from active database
|
|
nofilenamecheck;
|
|
|
|
}
|
|
|
|
~~ Dataguard Broker configuration
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
-- on primary database
|
|
alter system set dg_broker_start=FALSE scope=both sid='*';
|
|
alter system set dg_broker_config_file1='+DATA/GOTALPRD/dr1GOTALPRD.dat' scope=both sid='*';
|
|
alter system set dg_broker_config_file2='+DATA/GOTALPRD/dr2GOTALPRD.dat' scope=both sid='*';
|
|
alter system set dg_broker_start=TRUE scope=both sid='*';
|
|
|
|
-- on secondary database
|
|
alter system set dg_broker_start=FALSE scope=both sid='*';
|
|
alter system set dg_broker_config_file1='+DATA/GOTALDRP/dr1GOTALDRP.dat' scope=both sid='*';
|
|
alter system set dg_broker_config_file2='+DATA/GOTALDRP/dr2GOTALFRP.dat' scope=both sid='*';
|
|
alter system set dg_broker_start=TRUE scope=both sid='*';
|
|
|
|
|
|
-- creation of STANDBY REDELOG on both databases
|
|
|
|
ALTER DATABASE ADD STANDBY LOGFILE thread 1 size 100M;
|
|
ALTER DATABASE ADD STANDBY LOGFILE thread 1 size 100M;
|
|
ALTER DATABASE ADD STANDBY LOGFILE thread 1 size 100M;
|
|
|
|
ALTER DATABASE ADD STANDBY LOGFILE thread 2 size 100M;
|
|
ALTER DATABASE ADD STANDBY LOGFILE thread 2 size 100M;
|
|
ALTER DATABASE ADD STANDBY LOGFILE thread 2 size 100M;
|
|
|
|
|
|
select GROUP#,THREAD#,STATUS, BYTES from v$standby_log;
|
|
|
|
col MEMBER for a60
|
|
select * from v$logfile;
|
|
|
|
|
|
-- create DGMGRL configuration
|
|
dgmgrl
|
|
DGMGRL> connect sys/secret@GOTALPRD_DG
|
|
DGMGRL> create configuration GOTAL as
|
|
primary database is GOTALPRD
|
|
connect identifier is GOTALPRD_DG;
|
|
DGMGRL> add database GOTALDRP
|
|
as connect identifier is GOTALDRP_DG
|
|
maintained as physical;
|
|
|
|
DGMGRL> edit database 'gotaldrp' set property ArchiveLagTarget=0;
|
|
DGMGRL> edit database 'gotaldrp' set property LogArchiveMaxProcesses=2;
|
|
DGMGRL> edit database 'gotaldrp' set property LogArchiveMinSucceedDest=1;
|
|
DGMGRL> edit database 'gotaldrp' set property StandbyFileManagement='AUTO';
|
|
|
|
DGMGRL> edit database 'gotalprd' set property ArchiveLagTarget=0;
|
|
DGMGRL> edit database 'gotalprd' set property LogArchiveMaxProcesses=2;
|
|
DGMGRL> edit database 'gotalprd' set property LogArchiveMinSucceedDest=1;
|
|
DGMGRL> edit database 'gotalprd' set property StandbyFileManagement='AUTO';
|
|
|
|
DGMGRL> enable configuration;
|
|
DGMGRL> show configuration;
|
|
|
|
-- VERY IMPORANT
|
|
-- set StaticConnectIdentifier for all PRIMARY/DATAGUARD database instances
|
|
-- use complete DESCRIPTION syntax to uniquely identifiing the instances of each node
|
|
|
|
EDIT INSTANCE 'GOTALPRD1' SET PROPERTY 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vortex-db01-dba-vip)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=GOTALPRD_DGMGRL)(INSTANCE_NAME=GOTALPRD1)(SERVER=DEDICATED)))';
|
|
EDIT INSTANCE 'GOTALPRD2' SET PROPERTY 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vortex-db02-dba-vip)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=GOTALPRD_DGMGRL)(INSTANCE_NAME=GOTALPRD2)(SERVER=DEDICATED)))';
|
|
EDIT INSTANCE 'GOTALDRP1' SET PROPERTY 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kessel-db01-dba-vip)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=GOTALDRP_DGMGRL)(INSTANCE_NAME=GOTALDRP1)(SERVER=DEDICATED)))';
|
|
EDIT INSTANCE 'GOTALDRP2' SET PROPERTY 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kessel-db02-dba-vip)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=GOTALDRP_DGMGRL)(INSTANCE_NAME=GOTALDRP2)(SERVER=DEDICATED)))';
|
|
|
|
-- move on ASM the spfile of the secondary database
|
|
create pfile='/tmp/pfile.txt' from spfile='/app/oracle/product/11.2/db_1/dbs/spfileGOTALDRP1.ora';
|
|
create spfile ='+DATA/gotaldrp/spfileGOTALDRP.ora' from pfile='/tmp/pfile.txt';
|
|
|
|
-- on secodary servers (kessel-db01 and kessel-db02)
|
|
init.ora:
|
|
spfile ='+DATA/gotaldrp/spfileGOTALDRP.ora'
|
|
|
|
-- register standby database in the CRS
|
|
srvctl add database -d GOTALDRP -o /app/oracle/product/11.2/db_1 -c RAC -p '+DATA/gotaldrp/spfileGOTALDRP.ora' -r physical_standby -n GOTAL
|
|
|
|
-- pay attention to -s <start_options>; the default value is OPEN, that means that your DATAGUARD will be OPENED (active DATAGUARD)
|
|
|
|
srvctl add instance -d GOTALDRP -i GOTALDRP1 -n kessel-db01
|
|
srvctl add instance -d GOTALDRP -i GOTALDRP2 -n kessel-db02
|
|
|
|
srvctl start database -d GOTALDRP
|
|
srvctl status database -d GOTALDRP -v
|
|
|
|
|
|
|
|
== SWITCHOVER/SWITCHBACK
|
|
========================
|
|
|
|
~~ Switchover
|
|
~~~~~~~~~~~~~
|
|
DGMGRL> switchover to 'gotaldrp'
|
|
|
|
~~ Switchback
|
|
~~~~~~~~~~~~~
|
|
DGMGRL> switchover to 'gotaldrp'
|
|
|
|
|
|
== Other operations
|
|
===================
|
|
|
|
-- STOP/START Media Recovery Process (MRP) on the STANDBY
|
|
DGMGRL> edit database 'gotalprd' set STATE='LOG-APPLY-OFF';
|
|
DGMGRL> edit database 'gotalprd' set STATE='ONLINE';
|
|
|
|
== DATABASE SEVICES considerations
|
|
==================================
|
|
|
|
~~ keep in mind that in a RAC environement, database services are declared in the CRS and stored in the CRS and in the database also
|
|
~~ as CRS are differents on PRIMARY / SECONDARY clusters, we should declare every service twice: on PRIMARY CRS and on SECONDARY CRS
|
|
~~ to differentiate target status of a service along a database role
|
|
~~ the services should be created with -l <role> option
|
|
|
|
~~in the next exemple, we will create a GOTAL_WEB_APPLICATION service for primary database and a GOTAL_ADHOC_REPORTING on the read-only standby
|
|
|
|
~~ on vortex-db01 (part of primary cluster)
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
srvctl add service -d GOTALPRD -s GOTAL_WEB_APPLICATION -r "GOTALPRD1,GOTALPRD2" -P BASIC -l primary
|
|
srvctl start service -d GOTALPRD -s GOTAL_WEB_APPLICATION
|
|
|
|
srvctl add service -d GOTALPRD -s GOTAL_ADHOC_REPORTING -r "GOTALPRD1,GOTALPRD2" -P BASIC -l physical_standby
|
|
|
|
~~ the service will be created in the database when the service is starting
|
|
~~ for propagation on the standby, force archive of log current logfile
|
|
|
|
srvctl start service -d GOTALPRD -s GOTAL_ADHOC_REPORTING
|
|
srvctl stop service -d GOTALPRD -s GOTAL_ADHOC_REPORTING
|
|
|
|
SQL> alter system archive log current;
|
|
|
|
|
|
~~ on vkessel-db01 (part of secondary cluster)
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
srvctl add service -d GOTALDRP -s GOTAL_ADHOC_REPORTING -r "GOTALDRP1,GOTALDRP2" -P BASIC -l physical_standby
|
|
srvctl start service -d GOTALDRP -s GOTAL_ADHOC_REPORTING
|
|
|
|
srvctl add service -d GOTALDRP -s GOTAL_WEB_APPLICATION -r "GOTALDRP1,GOTALDRP2" -P BASIC -l primary
|
|
|
|
~~ on CLIENT side
|
|
~~~~~~~~~~~~~~~~~
|
|
~~ aliases in tnsnames.ora for transparent switchover/failover
|
|
|
|
GOTAL_WEB_APPLICATION =
|
|
(DESCRIPTION =
|
|
(FAILOVER = YES)
|
|
(ADDRESS_LIST =
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = vortex-scan)(PORT = 1521))
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = kessel-scan)(PORT = 1521))
|
|
)
|
|
(CONNECT_DATA =
|
|
(SERVICE_NAME = GOTAL_WEB_APPLICATION)
|
|
)
|
|
)
|
|
|
|
GOTAL_ADHOC_REPORTING =
|
|
(DESCRIPTION =
|
|
(FAILOVER = YES)
|
|
(ADDRESS_LIST =
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = kessel-scan)(PORT = 1521))
|
|
(ADDRESS = (PROTOCOL = TCP)(HOST = vortex-scan)(PORT = 1521))
|
|
)
|
|
(CONNECT_DATA =
|
|
(SERVICE_NAME = GOTAL_ADHOC_REPORTING)
|
|
)
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|