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) ) )
/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
rsync -av /u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB node2:/u01/app/oracle/product/11.2.0/db/dbs/orapwCIBERDB
shutdown IMMEDIATE ; startup mount; ALTER DATABASE archivelog; ALTER DATABASE FORCE LOGGING; ALTER DATABASE OPEN ;
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 ;
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 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''"; }
shutdown IMMEDIATE ;
startup mount ;
CIBERSTB:/u01/app/oracle/product/11.2.0/db:N
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 nomount pfile=/u02/backup/DIVASTB/CIBERDB/initCIBERSTB.ora
cp /u02/backup/DIVASTB/CIBERDB/initCIBERSTB.ora /u01/app/oracle/product/11.2.0/db/dbs/initCIBERSTB.ora
CREATE spfile FROM pfile;
rman auxiliary / target sys/mongui01@CIBERDB DUPLICATE TARGET DATABASE FOR STANDBY;
SELECT DATABASE_ROLE FROM V$DATABASE ;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect FROM SESSION;
ALTER DATABASE OPEN; ALTER system switch logfile;
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# ;
ALTER DATABASE Commit TO Switchover TO Physical Standby WITH SESSION Shutdown; shutdown IMMEDIATE ; startup mount ;
SELECT Database_role FROM v$database;
ALTER DATABASE Recover Managed Standby DATABASE Disconnect;
ALTER DATABASE Recover Managed Standby DATABASE Cancel;
ALTER DATABASE Commit TO Switchover TO PRIMARY WITH SESSION Shutdown;
SELECT Database_role FROM v$Database;
QUERY FOR CONTROL_FILES TO ASSURE THE CORRECT ONE'S ARE WORKING!!!
shutdown IMMEDIATE ;
startup;
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# ;
ALTER SYSTEM SET CONTROL_FILES='+DG_NODE2_DATA/ciberstb/controlfile/current.290.806237525' scope=spfile sid='*';
ALTER SYSTEM SET standby_file_management=AUTO scope=spfile sid='*' ;
SHOW parameter broker ;
ALTER system SET dg_broker_start=TRUE scope=BOTH;
dgmgrl 'sys/mongui01@CIBERDB'
CREATE configuration 'jhtesting' AS PRIMARY DATABASE IS 'CIBERDB' CONNECT identifier IS 'CIBERDB';
ADD DATABASE 'CIBERSTB' AS CONNECT identifier IS 'CIBERSTB' maintained AS physical;
SHOW configuration;
enable configuration;
switchover TO 'CIBERSTB'; SHOW DATABASE verbose 'CIBERSTB' ;
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;
SELECT Database_role FROM v$database;
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;
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
COL DESTINATION FORMAT A30 ; SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# - FROM V$ARCHIVE_DEST_STATUS - WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
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 ;
on any node
COL FACILITY FORMAT A40; COL SEVERITY FORMAT A30; COL MESSAGE FORMAT A120; SELECT * FROM V$DATAGUARD_STATUS ;
COL PID format A10; COL CLIENT_PID FORMAT A10 ; COL CLIENT_DBID FORMAT A12 ; COL GROUP# FORMAT A12 ; SELECT * FROM V$MANAGED_STANDBY ;
SELECT THREAD#, SEQUENCE#, first_time, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;