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
Next revision		Previous revision
Last revisionBoth sides next revision
oracle:dataguard_mini-howto [2018/07/11 15:13] – [Standby Apply process status] 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