====== [DOC] Summary of Dataguard creation ======
===== creation =====
* create the db (i mean, the master one)
* modify tnsnames.ora
CIBERDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CIBERDB)
)
)
CIBERSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CIBERSTB)
)
)
* replace the orapw (as ORACLE)
/u01/app/11.2.0/grid/bin/orapwd file=/home/oracle/orapwCIBERDB password="mongui01" entries=1
mv -fv /u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB /u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB.old
cp -pfv /home/oracle/orapwCIBERDB /u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB
* broadcast the orapw file (as root):
rsync -av /u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB node2:/u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB
* enable archiving
shutdown immediate ;
startup mount;
alter database archivelog;
ALTER DATABASE FORCE LOGGING;
alter database open ;
* create the stby redos:
ALTER DATABASE ADD STANDBY LOGFILE ('+DG_NODE1_FRA/CIBERDB/stby01.rdo') SIZE 52428800 ;
ALTER DATABASE ADD STANDBY LOGFILE ('+DG_NODE1_FRA/CIBERDB/stby02.rdo') SIZE 52428800 ;
ALTER DATABASE ADD STANDBY LOGFILE ('+DG_NODE1_FRA/CIBERDB/stby03.rdo') SIZE 52428800 ;
ALTER DATABASE ADD STANDBY LOGFILE ('+DG_NODE1_FRA/CIBERDB/stby04.rdo') SIZE 52428800 ;
* change multiple params for data guard:
ALTER SYSTEM SET remote_login_passwordfile = SHARED scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CIBERDB,CIBERSTB)' scope=spfile sid='*';
ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIBERDB' scope=spfile sid='*';
ALTER SYSTEM SET log_archive_dest_2='SERVICE=CIBERSTB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CIBERSTB REOPEN=30' scope=spfile sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile sid='*';
alter system set FAL_SERVER=CIBERSTB Scope=BOTH;
alter system set FAL_CLIENT=CIBERDB SCOPE=BOTH;
ALTER SYSTEM set db_file_name_convert='+DG_NODE2_DATA/ciberstb/','+DG_NODE1_DATA/CIBERDB/','ciberstb','ciberdb','CIBERSTB','CIBERDB' scope=spfile ;
ALTER SYSTEM set log_file_name_convert='+DG_NODE2_DATA/ciberstb/','+DG_NODE1_DATA/CIBERDB/','+DG_NODE2_FRA/ciberstb/','+DG_NODE1_FRA/CIBERDB/','ciberstb','ciberdb','CIBERSTB','CIBERDB' scope=spfile;
* rman it
rman target /
run {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK MAXPIECESIZE = 16G;
crosscheck archivelog all;
delete force noprompt expired archivelog all;
backup full as backupset filesperset 64
database format '/u02/backup/DIVAPRO/CIBERDB/Dades_%d_%T_%U'
plus archivelog format '/u02/backup/DIVAPRO/CIBERDB/Arch_%d_%T_%U';
sql "create pfile=''/u02/backup/DIVAPRO/CIBERDB/initCIBERDB.ora'' from spfile";
sql "alter database backup controlfile to trace as ''/u02/backup/DIVAPRO/CIBERDB/Ctl_CIBERDB_001.trc''";
}
* restart in mount the DB
shutdown immediate ;
startup mount ;
* add the stby to oratab in node2:
CIBERSTB:/u01/app/oracle/product/11.2.0/db:N
* Transfer the backup to node2
* modify the init.ora (take care with that):
cd /u02/backup/DIVAPRO/CIBERDB
cat initCIBERDB.ora | egrep -v "^CIBERDB\.|db_file_name_convert|log_archive_dest_2|log_file_name_convert|fal_(client|server)" | sed 's,DG_NODE1,DG_NODE2,g' | sed 's,jhdg,ciberstb,g' > initCIBERSTB.ora
cat >> initCIBERSTB.ora <
* Startup de stby:
startup nomount pfile=/u02/backup/DIVASTB/CIBERDB/initCIBERSTB.ora
* Copy the spfile to the correct location
cp /u02/backup/DIVASTB/CIBERDB/initCIBERSTB.ora /u01/app/oracle/product/11.2.0/db/dbs/initCIBERSTB.ora
* Create spfile
Create spfile from pfile;
* Rman in the STBY:
rman auxiliary / target sys/mongui01@CIBERDB
DUPLICATE TARGET DATABASE FOR STANDBY;
* Query de stby status:
Select DATABASE_ROLE FROM V$DATABASE ;
* Start the redo receiver:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
* Open the primary:
alter database open;
Alter system switch logfile;
* Query the primary status (must be valid):
Select Status, Error from v$Archive_dest where dest_id=2;
* Query the apply status on the stby:
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
===== switchover to standby =====
* On the Primary:
Alter Database Commit to Switchover to Physical Standby with session Shutdown;
shutdown immediate ;
startup mount ;
* Check the status of the db (the old primary):
Select Database_role from v$database;
* And start the MRP process:
Alter Database Recover Managed Standby Database Disconnect;
* On the STBY HOST (the new primary) stop the MRP:
Alter database Recover Managed Standby Database Cancel;
* AND change the role:
Alter Database Commit to Switchover to PRIMARY with session Shutdown;
* check it:
Select Database_role from v$Database;
QUERY FOR CONTROL_FILES TO ASSURE THE CORRECT ONE'S ARE WORKING!!!
* Restart the new primary:
shutdown immediate ;
startup;
* CHECK status
Select Database_role from v$Database;
Select Status, Error from v$Archive_dest where dest_id=2;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
* It's possible that you need to alter the control files, in that case, you must query asmcmd for the actual ones.
ALTER SYSTEM SET CONTROL_FILES='+DG_NODE2_DATA/ciberstb/controlfile/current.290.806237525' scope=spfile sid='*';
* Make the standby create by their own the datafiles:
ALTER SYSTEM SET standby_file_management=AUTO scope=spfile sid='*' ;
===== Setting up DataGuard Broker =====
* Query the status:
show parameter broker ;
* Start it
alter system set dg_broker_start=true scope=both;
* watch alert to see the startup process
* start the CLI
dgmgrl 'sys/mongui01@CIBERDB'
* create the configuration:
create configuration 'jhtesting' as primary database is 'CIBERDB' connect identifier is 'CIBERDB';
* Add the standby:
add database 'CIBERSTB' as connect identifier is 'CIBERSTB' maintained as physical;
* Review the the config:
show configuration;
* Enable the config:
enable configuration;
* Perform a swithover:
switchover to 'CIBERSTB';
show database verbose 'CIBERSTB' ;
===== From Physical Standby to Snapshot =====
In the Stby, of course:
Alter database Recover Managed Standby Database Cancel;
Ensure that the database is mounted, but not open.
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
===== From Snapshot to Physical Standby =====
====== Status Querys ======
===== ROLE of the connected DB =====
Select Database_role from v$database;
===== Status of the REDO shipping to STBY =====
Maybe there are more than 1 stby database, so ''dest_id=X'' can be from 2 to "n":
Select Status, Error from v$Archive_dest where dest_id=2;
===== Determine the most recently archived redo log file =====
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
===== List of archived_log ordered =====
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
===== Determine the most recently archived redo log file at each redo transport destination =====
COL DESTINATION FORMAT A30 ;
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# -
FROM V$ARCHIVE_DEST_STATUS -
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
===== DataGuard STATS =====
on the slave
col SOURCE_DB_UNIQUE_NAME format a15
col unit format a30
col TIME_COMPUTED format a30
col DATUM_TIME format a30
col VALUE FORMAT A18;
SELECT * FROM V$DATAGUARD_STATS ;
===== DataGuard LOG =====
on any node
COL FACILITY FORMAT A40;
COL SEVERITY FORMAT A30;
COL MESSAGE FORMAT A120;
SELECT * FROM V$DATAGUARD_STATUS ;
===== Standby Apply process status =====
COL PID format A10;
COL CLIENT_PID FORMAT A10 ;
COL CLIENT_DBID FORMAT A12 ;
COL GROUP# FORMAT A12 ;
select * from V$MANAGED_STANDBY ;
===== Log History =====
SELECT THREAD#, SEQUENCE#, first_time, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;