Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:docs:dataguard_mini-howto

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:docs:dataguard_mini-howto [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:docs:dataguard_mini-howto [2023/01/31 08:22] (current) – ↷ Page moved from dba:oracle:dataguard_mini-howto to dba:oracle:docs:dataguard_mini-howto dodger
Line 1: Line 1:
 +====== [DOC] Summary of Dataguard creation ======
 +===== creation =====
  
 +  * create the db (i mean, the master one)
 +  * modify tnsnames.ora
 +<code sql>
 +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)
 + )
 + )
 +</code>
 +  * replace the orapw (as ORACLE)
 +<code sql>
 +/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
 +</code>
 +  * broadcast the orapw file (as root):
 +<code sql>
 +rsync -av /u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB node2:/u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB
 +</code>
 +  * enable archiving
 +<code sql>
 +shutdown immediate ;
 +startup mount;
 +alter database archivelog;
 +ALTER DATABASE FORCE LOGGING;
 +alter database open ;
 +</code>
 +  * create the stby redos:
 +<code SQL>
 +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 ;
 +</code>
 +  * change multiple params for data guard:
 +<code SQL>
 +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;
 +</code>
 +  * rman it
 +<code SQL>
 +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''";
 +}
 +</code>
 +  * restart in mount the DB
 +<code SQL>
 +shutdown immediate ;
 +startup mount ;
 +</code>
 +  * add the stby to oratab in node2:
 +<code SQL>
 +CIBERSTB:/u01/app/oracle/product/11.2.0/db:N
 +</code>
 +  * Transfer the backup to node2
 +  * modify the init.ora (take care with that):
 +<code SQL>
 +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
 +</code>
 +  * Startup de stby:
 +<code sql>
 +startup nomount pfile=/u02/backup/DIVASTB/CIBERDB/initCIBERSTB.ora
 +</code>
 +  * Copy the spfile to the correct location
 +<code sql>
 +cp /u02/backup/DIVASTB/CIBERDB/initCIBERSTB.ora /u01/app/oracle/product/11.2.0/db/dbs/initCIBERSTB.ora
 +</code>
 +  * Create spfile
 +<code sql>
 +Create spfile from pfile;
 +</code>
 +  * Rman in the STBY:
 +<code sql>
 +rman auxiliary / target sys/mongui01@CIBERDB
 +DUPLICATE TARGET DATABASE FOR STANDBY;
 +</code>
 +  * Query de stby status:
 +<code sql>
 +Select DATABASE_ROLE FROM V$DATABASE ;
 +</code>
 +  * Start the redo receiver:
 +<code sql>
 +ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
 +</code>
 +  * Open the primary:
 +<code sql>
 +alter database open;
 +Alter system switch logfile;
 +</code>
 +  * Query the primary status (must be valid):
 +<code sql>
 +Select Status, Error from v$Archive_dest where dest_id=2;
 +</code>
 +  * Query the apply status on the stby:
 +<code sql>
 +SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
 +SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
 +</code>
 +
 +
 +===== switchover to standby =====
 +  * On the Primary:
 +<code sql>
 +Alter Database Commit to Switchover to Physical Standby with session Shutdown;
 +shutdown immediate ;
 +startup mount ;
 +</code>
 +  * Check the status of the db (the old primary):
 +<code sql>
 +Select Database_role from v$database;
 +</code>
 +  * And start the MRP process:
 +<code sql>
 +Alter Database Recover Managed Standby Database Disconnect;
 +</code>
 +  * On the STBY HOST (the new primary) stop the MRP:
 +<code sql>
 +Alter database Recover Managed Standby Database Cancel;
 +</code>
 +  * AND change the role:
 +<code sql>
 +Alter Database Commit to Switchover to PRIMARY with session Shutdown;
 +</code>
 +  * check it:
 +<code sql>
 +Select Database_role from v$Database;
 +</code>
 + 
 + 
 +QUERY FOR CONTROL_FILES TO ASSURE THE CORRECT ONE'S ARE WORKING!!!
 + 
 +  * Restart the new primary:
 +<code sql>
 +shutdown immediate ;
 +startup;
 +</code>
 +  * CHECK status
 +<code sql>
 +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# ;
 +</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 CONTROL_FILES='+DG_NODE2_DATA/ciberstb/controlfile/current.290.806237525' scope=spfile sid='*';
 +</code>
 +
 +  * Make the standby create by their own the datafiles:
 +<code sql>
 +ALTER SYSTEM SET standby_file_management=AUTO  scope=spfile sid='*' ;
 +</code>
 +
 +===== Setting up DataGuard Broker =====
 +  * Query the status:
 +<code sql>
 +show parameter broker ;
 +</code>
 +  * Start it
 +<code sql>
 +alter system set dg_broker_start=true scope=both;
 +</code>
 +  * watch alert to see the startup process
 +  * start the CLI
 +<code sql>
 +dgmgrl 'sys/mongui01@CIBERDB'
 +</code>
 +  * create the configuration:
 +<code sql>
 +create configuration 'jhtesting' as primary database is 'CIBERDB' connect identifier is 'CIBERDB';
 +</code>
 +  * Add the standby:
 +<code sql>
 +add database 'CIBERSTB' as connect identifier is 'CIBERSTB' maintained as physical;
 +</code>
 +  * Review the the config:
 +<code sql>
 +show configuration;
 +</code>
 +  * Enable the config:
 +<code sql>
 +enable configuration;
 +</code>
 +  * Perform a swithover:
 +<code sql>
 +switchover to 'CIBERSTB';
 +show database verbose 'CIBERSTB' ;
 +</code>
 +
 +===== From Physical Standby to Snapshot =====
 +In the Stby, of course:
 +<code sql>
 +Alter database Recover Managed Standby Database Cancel;
 +</code>
 +Ensure that the database is mounted, but not open.
 +<code sql>
 +ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
 +</code>
 +
 +===== From Snapshot to Physical Standby =====
 +
 +
 +
 +
 +====== Status Querys ======
 +
 +===== ROLE of the connected DB =====
 +<code sql>Select Database_role from v$database;</code>
 +===== 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":
 +<code sql>Select Status, Error from v$Archive_dest 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 archived_log ordered =====
 +<code sql>
 +SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG 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, ARCHIVED_THREAD#, ARCHIVED_SEQ# -
 +FROM V$ARCHIVE_DEST_STATUS -
 +WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
 +</code>
 +===== DataGuard STATS =====
 +on the slave
 +<code sql>
 +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 ;
 +</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 CLIENT_PID FORMAT A10 ;
 +COL CLIENT_DBID  FORMAT A12 ;
 +COL GROUP# FORMAT A12 ;
 +select * from V$MANAGED_STANDBY ;
 +</code>
 +
 +===== Log History =====
 +<code sql>
 +SELECT THREAD#, SEQUENCE#, first_time, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;
 +</code>