User Tools

Site Tools


dba:oracle:docs:dataguard_mini-howto

[DOC] Summary of Dataguard creation

creation

  • create the db (i mean, the master one)
  • modify tnsnames.ora <code sql> CIBERDB = (DESCRIPTION = (ADDRESSLIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521)) ) (CONNECTDATA = (SERVICE_NAME = CIBERDB) ) )

CIBERSTB =

(DESCRIPTION =
	(ADDRESS_LIST =
	  (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
	)
	(CONNECT_DATA =
	  (SERVICE_NAME = CIBERSTB)
	)
)

</code>

  • 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 <<EOF
*.db_create_file_dest='+DG_NODE2_DATA'
*.db_file_name_convert='+DG_NODE1_DATA/jhdg/','+DG_NODE2_DATA/ciberstb/','jhdg','ciberstb','CIBERDB','CIBERSTB'
*.db_unique_name='CIBERSTB'
*.db_recovery_file_dest='+DG_NODE2_FRA'
*.fal_client='CIBERSTB'
*.fal_server='CIBERDB'
*.log_archive_config='DG_CONFIG=(CIBERDB,CIBERSTB)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIBERSTB'
*.log_file_name_convert='+DG_NODE1_DATA/CIBERDB/','+DG_NODE2_DATA/jhsbt/','+DG_NODE1_FRA/CIBERDB/','+DG_NODE2_FRA/ciberstb/','jhdg','ciberstb','CIBERDB','CIBERSTB'
*.log_archive_dest_2='service=CIBERDB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=CIBERDB'
EOF
  • 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 CONTROLFILES TO ASSURE THE CORRECT ONE'S ARE WORKING!!! * Restart the new primary: <code sql> shutdown immediate ; startup; </code> * CHECK status <code sql> Select Databaserole from v$Database; Select Status, Error from v$Archivedest where destid=2; SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGEDSTANDBY; SELECT SEQUENCE#, APPLIED FROM V$ARCHIVEDLOG ORDER BY SEQUENCE# ; </code>

  • It's possible that you need to alter the control files, in that case, you must query asmcmd for the actual ones. <code sql> ALTER SYSTEM SET CONTROLFILES='+DGNODE2_DATA/ciberstb/controlfile/current.290.806237525' scope=spfile sid='*'; </code>
  • Make the standby create by their own the datafiles: <code sql> ALTER SYSTEM SET standbyfilemanagement=AUTO scope=spfile sid='*' ; </code>

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 destid=X'' can be from 2 to “n”: <code sql>Select Status, Error from v$Archivedest where dest_id=2;</code> ===== Determine the most recently archived redo log file ===== <code sql> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; </code> ===== List of archivedlog ordered ===== <code sql> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVEDLOG ORDER BY SEQUENCE# ; </code> ===== Determine the most recently archived redo log file at each redo transport destination ===== <code sql> COL DESTINATION FORMAT A30 ; SELECT DESTINATION, STATUS, ARCHIVEDTHREAD#, ARCHIVEDSEQ# - FROM V$ARCHIVEDESTSTATUS - WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; </code> ===== DataGuard STATS ===== on the slave <code sql> col SOURCEDBUNIQUENAME format a15 col unit format a30 col TIMECOMPUTED format a30 col DATUMTIME format a30 col VALUE FORMAT A18; SELECT * FROM V$DATAGUARDSTATS ; </code> ===== DataGuard LOG ===== on any node <code sql> COL FACILITY FORMAT A40; COL SEVERITY FORMAT A30; COL MESSAGE FORMAT A120; SELECT * FROM V$DATAGUARD_STATUS ; </code> ===== Standby Apply process status ===== <code sql> COL PID format A10; COL CLIENTPID FORMAT A10 ; COL CLIENTDBID FORMAT A12 ; COL GROUP# FORMAT A12 ; select * from V$MANAGED_STANDBY ; </code> ===== Log History ===== <code sql> SELECT THREAD#, SEQUENCE#, firsttime, FIRSTCHANGE#, NEXTCHANGE# FROM V$LOGHISTORY; </code>

dba/oracle/docs/dataguard_mini-howto.txt · Last modified: 2023/01/31 08:22 by dodger