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
Last revisionBoth sides next revision
dba:oracle:dataguard_mini-howto [2022/02/11 11:12] – ↷ Page moved from oracle:dataguard_mini-howto to dba:oracle:dataguard_mini-howto dodgerdba:oracle:docs:dataguard_mini-howto [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
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> 
dba/oracle/docs/dataguard_mini-howto.txt · Last modified: 2023/01/31 08:22 by dodger