Oracle DataGuard

INIT POWER

POWER.__db_cache_size=1207959552
POWER.__java_pool_size=16777216
POWER.__large_pool_size=16777216
POWER.__shared_pool_size=352321536
POWER.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/POWER/adump'
*.background_dump_dest='/u01/app/oracle/admin/POWER/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/POWER/control01.ctl','/u01/app/oracle/oradata/POWER/control02.ctl','/u01/app/oracle/oradata/POWER/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/POWER/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='POWER'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u01/app/oracle/POWER/flash_recovery_area'
*.DB_UNIQUE_NAME='POWER'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=POWERXDB)'
*.FAL_CLIENT='POWER'
*.FAL_SERVER='AUXPOWER'
*.job_queue_processes=10
*.log_archive_config='dg_config=(POWER,AUXPOWER)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/POWER/flash_recovery_area'
*.log_archive_dest_2='service=AUXPOWER async valid_for=(online_logfile,primary_role) db_unique_name=AUXPOWER'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.nls_language='LATIN AMERICAN SPANISH'
*.nls_territory='COLOMBIA'
*.open_cursors=300
*.pga_aggregate_target=848297984
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=225
*.sga_target=1610612736
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/POWER/udump'


INIT AUXPOWER


*.audit_file_dest='/u01/app/oracle/admin/AUXPOWER/adump'
*.background_dump_dest='/u01/app/oracle/admin/AUXPOWER/bdump'
*.compatible='10.2.0.3.0'
*.core_dump_dest='/u01/app/oracle/admin/AUXPOWER/cdump'
*.control_files='/u01/app/oracle/oradata/AUXPOWER/control01.ctl','/u01/app/oracle/oradata/AUXPOWER/control02.ctl','/u01/app/oracle/oradata/AUXPOWER/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='POWER'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u01/app/oracle/AUXPOWER/flash_recovery_area'
*.DB_UNIQUE_NAME='AUXPOWER'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AUXPOWERXDB)'
*.job_queue_processes=10
*.log_archive_config='dg_config=(POWER,AUXPOWER)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/AUXPOWER/flash_recovery_area'
*.log_archive_dest_2='service=POWER async valid_for=(online_logfile,primary_role) db_unique_name=POWER'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.nls_language='LATIN AMERICAN SPANISH'
*.nls_territory='COLOMBIA'
*.open_cursors=300
*.pga_aggregate_target=848297984
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=225
*.sga_target=700612736
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/AUXPOWER/udump'
*.FAL_SERVER=POWER
*.FAL_CLIENT=AUXPOWER
*.db_file_name_convert=('/u01/app/oracle/oradata/POWER','/u01/app/oracle/oradata/AUXPOWER')
*.log_file_name_convert=('/u01/app/oracle/oradata/POWER','/u01/app/oracle/oradata/AUXPOWER')




SQL> alter system set log_archive_config='dg_config=(POWER,AUXPOWER)';

SQL> alter system set log_archive_dest_2='service=AUXPOWER async valid_for=(online_logfile,primary_role) db_unique_name=AUXPOWER';


-bash-3.00$ rman target sys/sys@power auxiliary /

INSTANCIA PRIMARIA


startup mount pfile=initPOWER.ora
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2139256 bytes
Variable Size 396188552 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes
Database mounted.


SQL> alter database add standby logfile
('/u01/app/oracle/oradata/POWER/srl01.log',
'/u01/app/oracle/oradata/POWER/srl02.log',
'/u01/app/oracle/oradata/POWER/srl03.log')
size 52428800
/


SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/AUXPOWER/auxpowerstby.ctl';


SELECT * FROM V$STANDBY_LOG;


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2139256 bytes
Variable Size 396188552 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.


NOMOUNT


$ export ORACLE_SID=AUXPOWER

SQL> startup nomount pfile=initAUXPOWER.ora
ORACLE instance started.

Total System Global Area 700448768 bytes
Fixed Size 2141504 bytes
Variable Size 186471104 bytes
Database Buffers 507510784 bytes
Redo Buffers 4325376 bytes
SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-3.00$ rman target sys/sys@power auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 30 18:02:57 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: POWER (DBID=1586828541)
connected to auxiliary database: POWER (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> create spfile from pfile;


EJEMPLO DE SALIDA DE STANDBY DATABASE

-bash-3.00$ rman target sys/sys@power auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 1 15:57:13 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: POWER (DBID=1586828541)
connected to auxiliary database: POWER (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 01-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=104 devtype=DISK

contents of Memory Script:
{
set until scn 3950745;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 01-OCT-08
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input filename=/u01/app/oracle/oradata/POWER/auxpower.ctl
output filename=/u01/app/oracle/oradata/AUXPOWER/control01.ctl
output filename=/u01/app/oracle/oradata/AUXPOWER/control02.ctl
output filename=/u01/app/oracle/oradata/AUXPOWER/control03.ctl
Finished restore at 01-OCT-08

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
set until scn 3950745;
set newname for tempfile 1 to
"/u01/app/oracle/oradata/AUXPOWER/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/AUXPOWER/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/AUXPOWER/undotbs01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/AUXPOWER/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/AUXPOWER/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/AUXPOWER/example01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/AUXPOWER/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-OCT-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=104 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/AUXPOWER/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/AUXPOWER/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/AUXPOWER/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/AUXPOWER/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/AUXPOWER/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2008_10_01/o1_mf_nnndf_TAG20081001T144816_4g7o00mh_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2008_10_01/o1_mf_nnndf_TAG20081001T144816_4g7o00mh_.bkp tag=TAG20081001T144816
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-OCT-08

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=10 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=11 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=12 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/example01.dbf

contents of Memory Script:
{
set until scn 3950745;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-OCT-08
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 24 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_24_666695680.arc
archive log thread 1 sequence 25 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_25_666695680.arc
archive log thread 1 sequence 26 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_26_666695680.arc
archive log thread 1 sequence 27 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_27_666695680.arc
archive log thread 1 sequence 28 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_28_666695680.arc
archive log thread 1 sequence 29 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_29_666695680.arc
archive log thread 1 sequence 30 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_30_666695680.arc
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_24_666695680.arc thread=1 sequence=24
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_25_666695680.arc thread=1 sequence=25
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_26_666695680.arc thread=1 sequence=26
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_27_666695680.arc thread=1 sequence=27
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_28_666695680.arc thread=1 sequence=28
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_29_666695680.arc thread=1 sequence=29
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_30_666695680.arc thread=1 sequence=30
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-OCT-08
Finished Duplicate Db at 01-OCT-08

RMAN> exit


Recovery Manager complete.
-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 1 15:59:23 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 AUXPOWER
afrodita
10.2.0.4.0 01/10/08 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/AUXPOWER/flash_recovery_area
Oldest online log sequence 23
Next log sequence to archive 0
Current log sequence 31
SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 AUXPOWER
afrodita
10.2.0.4.0 01/10/08 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> set line 300
SQL> /



SQL> SELECT * FROM V$DATAGUARD_STATS;


INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- -------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 AUXPOWER afrodita 10.2.0.4.0 01/10/08 MOUNTED NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/AUXPOWER/flash_recovery_area
Oldest online log sequence 31
Next log sequence to archive 0
Current log sequence 31
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

SQL> SELECT * FROM V$DATAGUARD_STATS;


SQL> ALTER DATABASE OPEN READ ONLY;


Verify the new redo data was archived on the standby database.

SQL > SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


SQL> SELECT NAME,OPEN_MODE,SWITCHOVER_STATUS,GUARD_STATUS,CURRENT_SCN,FLASHBACK_ON, DB_UNIQUE_NAME, STANDBY_BECAME_PRIMARY_SCN FROM V$DATABASE;

SQL > SELECT PROTECTION_MODE FROM V$DATABASE;


Verify that received redo has been applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#


SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;





SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;


SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;


SQL> SELECT NAME,GUARD_STATUS,CURRENT_SCN,LAST_OPEN_INCARNATION#,SWITCHOVER_STATUS FROM V$DATABASE


SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE

SQL> SELECT * FROM V$MANAGED_STANDBY