The management of temporary tablespaces and tempfiles in an environment where you want to maintain a consistent configuration across the Primary and Standby sites.
The addition of temporary files to TEMP tablespaces in the primary site is not handled automatically through the normal redo apply mechanisms in the same way as regular datafiles if the parameter standby_file_management is set to AUTO. The DBA must manually synchronised the primary and standby tempfile configuration if they require both the sites to be the same.
Note: tempfiles are established during an RMAN duplicate operation, this document details the process for managing tempfiles that are added to environments only after the standby has been built and is running.
Administering the addition of a tempfile to an existing temporary tablespace in the Primary and Standby sites
NOTE : The BEST Practice/ Rerequisites of Switchover is to Check for TEMP and and create one if it is not present before SWITCHOVER/FAILOVER.
Refer,
1. Add the temp file in the primary
Primary Site
SQL> alter tablespace temp add tempfile '+DATA' size 50M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
+DATA/rs1/tempfile/temp.264.800115667
+DATA/rs1/tempfile/temp.273.802356687
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 221
Next log sequence to archive 223
Current log sequence 223
SQL> alter system switch logfile;
System altered.
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
+DATA/rs1/tempfile/temp.264.800115667
+DATA/rs1/tempfile/temp.273.802356687
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 221
Next log sequence to archive 223
Current log sequence 223
SQL> alter system switch logfile;
System altered.
2. The tempfile will NOT automatically be created in the physical standby.
Standby Site
The tempfile thats has been added to the Primary site file is not automatically replicated to the Standby site like regular datafiles.
There is still only one tempfile in the Standby database even though there are now 2 tempfiles in the Primary.
The parameter standby_file_management=AUTO has no impact on tempfile management in the environment.
The tempfile thats has been added to the Primary site file is not automatically replicated to the Standby site like regular datafiles.
There is still only one tempfile in the Standby database even though there are now 2 tempfiles in the Primary.
The parameter standby_file_management=AUTO has no impact on tempfile management in the environment.
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1_stb/tempfile/temp.264.800115667
Media recovery complete.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1_stb/tempfile/temp.264.800115667
Even after log switches occur in the primary the file is not replicated (for those environments relying on archivelogs and ARCH as the shipping mechanism)
Primary Site
Primary Site
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 221
Next log sequence to archive 223
Current log sequence 223
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 221
Next log sequence to archive 223
Current log sequence 223
Standby Alert Log
Tue Dec 18 12:51:51 2012
Archived Log entry 215 added for thread 1 sequence 222 ID 0x6cb514f6 dest 1:
Tue Dec 18 12:51:54 2012
Media Recovery Log +FRA/rs1_stb/archivelog/2012_12_18/thread_1_seq_222.485.802356711
Media Recovery Waiting for thread 1 sequence 223 (in transit)
Tue Dec 18 12:55:15 2012
Standby controlfile consistent with primary
Archived Log entry 215 added for thread 1 sequence 222 ID 0x6cb514f6 dest 1:
Tue Dec 18 12:51:54 2012
Media Recovery Log +FRA/rs1_stb/archivelog/2012_12_18/thread_1_seq_222.485.802356711
Media Recovery Waiting for thread 1 sequence 223 (in transit)
Tue Dec 18 12:55:15 2012
Standby controlfile consistent with primary
Standby Site
There is still no tempfile appearing at the standby site even though recovery is running, standby_file_management=AUTO and log switching has occurred
There is still no tempfile appearing at the standby site even though recovery is running, standby_file_management=AUTO and log switching has occurred
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 221
Next log sequence to archive 0
Current log sequence 223
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1_stb/tempfile/temp.264.800115667
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 221
Next log sequence to archive 0
Current log sequence 223
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1_stb/tempfile/temp.264.800115667
3. Tempfiles will not be created as a part of the switchover or failover process
During a switchover operation the file is not created or replicated to the new primary site. It must be added manually. In this case it will be performed using the Data Guard broker command line utility.
Primary Site
Primary Site
DGMGRL> connect sys@rs1_prm_dgmgrl
Password:
Connected.
DGMGRL> show configuration;
Configuration - rs1_dg
Protection Mode: MaxAvailability
Databases:
rs1 - Primary database
rs1_stb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - rs1_dg
Protection Mode: MaxAvailability
Databases:
rs1 - Primary database
rs1_stb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to rs1_stb
Performing switchover NOW, please wait...
New primary database "rs1_stb" is opening...
Operation requires shutdown of instance "rs1" on database "rs1"
Shutting down instance "rs1"...
ORACLE instance shut down.
Operation requires startup of instance "rs1" on database "rs1"
Starting instance "rs1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rs1_stb"
Password:
Connected.
DGMGRL> show configuration;
Configuration - rs1_dg
Protection Mode: MaxAvailability
Databases:
rs1 - Primary database
rs1_stb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - rs1_dg
Protection Mode: MaxAvailability
Databases:
rs1 - Primary database
rs1_stb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to rs1_stb
Performing switchover NOW, please wait...
New primary database "rs1_stb" is opening...
Operation requires shutdown of instance "rs1" on database "rs1"
Shutting down instance "rs1"...
ORACLE instance shut down.
Operation requires startup of instance "rs1" on database "rs1"
Starting instance "rs1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rs1_stb"
The alert log in the standby site shows nothing about the addition of the tempfile that has been built into the Primary sites configuration.
The alert log extract below shows the switchover operations from the perspective of the standby site.
Standby Alert Log
Tue Dec 18 12:58:57 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Tue Dec 18 12:58:58 2012
Standby controlfile consistent with primary
RFS[2]: Selected log 5 for thread 1 sequence 225 dbid 1823795963 branch 800115647
Media Recovery Waiting for thread 1 sequence 224 (in transit)
Tue Dec 18 12:58:58 2012
Archived Log entry 217 added for thread 1 sequence 224 ID 0x6cb514f6 dest 1:
Media Recovery Log +FRA/rs1_stb/archivelog/2012_12_18/thread_1_seq_224.487.802357139
Media Recovery Waiting for thread 1 sequence 225 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 225 Reading mem 0
Mem# 0: +DATA/rs1_stb/onlinelog/group_5.275.801239591
Mem# 1: +FRA/rs1_stb/onlinelog/group_5.459.801239593
Tue Dec 18 12:59:36 2012
RFS[2]: Selected log 4 for thread 1 sequence 226 dbid 1823795963 branch 800115647
Tue Dec 18 12:59:36 2012
Archived Log entry 218 added for thread 1 sequence 225 ID 0x6cb514f6 dest 1:
Tue Dec 18 12:59:37 2012
Media Recovery Waiting for thread 1 sequence 226 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 226 Reading mem 0
Mem# 0: +DATA/rs1_stb/onlinelog/group_4.274.801239589
Mem# 1: +FRA/rs1_stb/onlinelog/group_4.458.801239591
RFS[2]: Possible network disconnect with primary database
Tue Dec 18 12:59:40 2012
RFS[4]: Assigned to RFS process 3788
RFS[4]: Selected log 4 for thread 1 sequence 226 dbid 1823795963 branch 800115647
Resetting standby activation ID 1823806710 (0x6cb514f6)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Tue Dec 18 12:59:40 2012
Archived Log entry 219 added for thread 1 sequence 226 ID 0x6cb514f6 dest 1:
Media Recovery Waiting for thread 1 sequence 227
Tue Dec 18 12:59:40 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_pr00_3763.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Tue Dec 18 12:59:42 2012
MRP0: Background Media Recovery process shutdown (rs1)
Managed Standby Recovery Canceled (rs1)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (rs1)
Maximum wait for role transition is 15 minutes.
krsv_proc_kill: Killing 3 processes (all RFS)
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_rsm0_3759.trc
SwitchOver after complete recovery through change 2653587
Online log +DATA/rs1_stb/onlinelog/group_1.276.801239713: Thread 1 Group 1 was previously cleared
Online log +FRA/rs1_stb/onlinelog/group_1.327.801239715: Thread 1 Group 1 was previously cleared
Online log +DATA/rs1_stb/onlinelog/group_2.277.801239715: Thread 1 Group 2 was previously cleared
Online log +FRA/rs1_stb/onlinelog/group_2.316.801239715: Thread 1 Group 2 was previously cleared
Online log +DATA/rs1_stb/onlinelog/group_3.278.801239719: Thread 1 Group 3 was previously cleared
Online log +FRA/rs1_stb/onlinelog/group_3.315.801239719: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2653585
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ARC3: Becoming the 'no SRL' ARCH
ALTER SYSTEM SET log_archive_dest_2='service="rs1_prm"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="rs1" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
ALTER DATABASE OPEN
Data Guard Broker initializing...
Tue Dec 18 12:59:48 2012
Assigning activation ID 1826002576 (0x6cd69690)
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Thread 1 advanced to log sequence 228 (thread open)
Tue Dec 18 12:59:48 2012
ARC0: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 228
Current log# 2 seq# 228 mem# 0: +DATA/rs1_stb/onlinelog/group_2.277.801239715
Current log# 2 seq# 228 mem# 1: +FRA/rs1_stb/onlinelog/group_2.316.801239715
Successful open of redo thread 1
ARC3: Becoming the 'no SRL' ARCH
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Dec 18 12:59:49 2012
SMON: enabling cache recovery
Tue Dec 18 12:59:49 2012
..
.
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12514 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'rs1_prm'. Error is 12514.
[3759] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:329674 end:330254 diff:580 (5 seconds)
Dictionary check beginning
Tue Dec 18 12:59:50 2012
Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_dbw0_3585.trc: <<<<<<<<<<<<<<<<ERRORS ON TEMP
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA/rs1_stb/tempfile/temp.264.800115667'
ORA-17503: ksfdopn:2 Failed to open file +DATA/rs1_stb/tempfile/temp.264.800115667
ORA-15012: ASM file '+DATA/rs1_stb/tempfile/temp.264.800115667' does not exist
Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_dbw0_3585.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA/rs1_stb/tempfile/temp.264.800115667'
File 201 not verified due to error ORA-01157
Tue Dec 18 12:59:50 2012
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile +DATA/rs1_stb/tempfile/temp.264.800115667 as +DATA/rs1_stb/tempfile/temp.280.802357191
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Dec 18 12:59:53 2012
QMNC started with pid=24, OS id=3790
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rs1';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rs1';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='rs1','rs1_stb' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='rs1','rs1_stb' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
ARC2: STARTING ARCH PROCESSES
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Tue Dec 18 12:59:57 2012
ARC4 started with pid=30, OS id=3792
Tue Dec 18 12:59:57 2012
.
CJQ0 started with pid=45, OS id=3818
Tue Dec 18 13:00:14 2012
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
Tue Dec 18 13:00:14 2012
db_recovery_file_dest_size of 4122 MB is 55.46% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Dec 18 13:00:35 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Tue Dec 18 13:00:37 2012
LGWR: Standby redo logfile selected to archive thread 1 sequence 230
LGWR: Standby redo logfile selected for thread 1 sequence 230 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 230 (LGWR switch)
Current log# 1 seq# 230 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713
Current log# 1 seq# 230 mem# 1: +FRA/rs1_stb/onlinelog/group_1.327.801239715
Tue Dec 18 13:00:37 2012
ARC3: Becoming the 'no SRL' ARCH
Tue Dec 18 13:00:37 2012
ARC0: Becoming the 'no SRL' ARCH
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 222 added for thread 1 sequence 229 ID 0x6cd69690 dest 1:
ARC3: Becoming the 'no SRL' ARCH
ARC3: Standby redo logfile selected for thread 1 sequence 229 for destination LOG_ARCHIVE_DEST_2
Thread 1 cannot allocate new log, sequence 231
Checkpoint not complete
Current log# 1 seq# 230 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713
Current log# 1 seq# 230 mem# 1: +FRA/rs1_stb/onlinelog/group_1.327.801239715
Tue Dec 18 13:00:49 2012
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 231
LGWR: Standby redo logfile selected for thread 1 sequence 231 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 231 (LGWR switch)
Current log# 2 seq# 231 mem# 0: +DATA/rs1_stb/onlinelog/group_2.277.801239715
Current log# 2 seq# 231 mem# 1: +FRA/rs1_stb/onlinelog/group_2.316.801239715
Tue Dec 18 13:00:51 2012
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 227 added for thread 1 sequence 230 ID 0x6cd69690 dest 1:
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Tue Dec 18 12:58:58 2012
Standby controlfile consistent with primary
RFS[2]: Selected log 5 for thread 1 sequence 225 dbid 1823795963 branch 800115647
Media Recovery Waiting for thread 1 sequence 224 (in transit)
Tue Dec 18 12:58:58 2012
Archived Log entry 217 added for thread 1 sequence 224 ID 0x6cb514f6 dest 1:
Media Recovery Log +FRA/rs1_stb/archivelog/2012_12_18/thread_1_seq_224.487.802357139
Media Recovery Waiting for thread 1 sequence 225 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 225 Reading mem 0
Mem# 0: +DATA/rs1_stb/onlinelog/group_5.275.801239591
Mem# 1: +FRA/rs1_stb/onlinelog/group_5.459.801239593
Tue Dec 18 12:59:36 2012
RFS[2]: Selected log 4 for thread 1 sequence 226 dbid 1823795963 branch 800115647
Tue Dec 18 12:59:36 2012
Archived Log entry 218 added for thread 1 sequence 225 ID 0x6cb514f6 dest 1:
Tue Dec 18 12:59:37 2012
Media Recovery Waiting for thread 1 sequence 226 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 226 Reading mem 0
Mem# 0: +DATA/rs1_stb/onlinelog/group_4.274.801239589
Mem# 1: +FRA/rs1_stb/onlinelog/group_4.458.801239591
RFS[2]: Possible network disconnect with primary database
Tue Dec 18 12:59:40 2012
RFS[4]: Assigned to RFS process 3788
RFS[4]: Selected log 4 for thread 1 sequence 226 dbid 1823795963 branch 800115647
Resetting standby activation ID 1823806710 (0x6cb514f6)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Tue Dec 18 12:59:40 2012
Archived Log entry 219 added for thread 1 sequence 226 ID 0x6cb514f6 dest 1:
Media Recovery Waiting for thread 1 sequence 227
Tue Dec 18 12:59:40 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_pr00_3763.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Tue Dec 18 12:59:42 2012
MRP0: Background Media Recovery process shutdown (rs1)
Managed Standby Recovery Canceled (rs1)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (rs1)
Maximum wait for role transition is 15 minutes.
krsv_proc_kill: Killing 3 processes (all RFS)
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_rsm0_3759.trc
SwitchOver after complete recovery through change 2653587
Online log +DATA/rs1_stb/onlinelog/group_1.276.801239713: Thread 1 Group 1 was previously cleared
Online log +FRA/rs1_stb/onlinelog/group_1.327.801239715: Thread 1 Group 1 was previously cleared
Online log +DATA/rs1_stb/onlinelog/group_2.277.801239715: Thread 1 Group 2 was previously cleared
Online log +FRA/rs1_stb/onlinelog/group_2.316.801239715: Thread 1 Group 2 was previously cleared
Online log +DATA/rs1_stb/onlinelog/group_3.278.801239719: Thread 1 Group 3 was previously cleared
Online log +FRA/rs1_stb/onlinelog/group_3.315.801239719: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2653585
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ARC3: Becoming the 'no SRL' ARCH
ALTER SYSTEM SET log_archive_dest_2='service="rs1_prm"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="rs1" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
ALTER DATABASE OPEN
Data Guard Broker initializing...
Tue Dec 18 12:59:48 2012
Assigning activation ID 1826002576 (0x6cd69690)
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Thread 1 advanced to log sequence 228 (thread open)
Tue Dec 18 12:59:48 2012
ARC0: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 228
Current log# 2 seq# 228 mem# 0: +DATA/rs1_stb/onlinelog/group_2.277.801239715
Current log# 2 seq# 228 mem# 1: +FRA/rs1_stb/onlinelog/group_2.316.801239715
Successful open of redo thread 1
ARC3: Becoming the 'no SRL' ARCH
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Dec 18 12:59:49 2012
SMON: enabling cache recovery
Tue Dec 18 12:59:49 2012
..
.
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12514 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'rs1_prm'. Error is 12514.
[3759] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:329674 end:330254 diff:580 (5 seconds)
Dictionary check beginning
Tue Dec 18 12:59:50 2012
Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_dbw0_3585.trc: <<<<<<<<<<<<<<<<ERRORS ON TEMP
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA/rs1_stb/tempfile/temp.264.800115667'
ORA-17503: ksfdopn:2 Failed to open file +DATA/rs1_stb/tempfile/temp.264.800115667
ORA-15012: ASM file '+DATA/rs1_stb/tempfile/temp.264.800115667' does not exist
Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_dbw0_3585.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA/rs1_stb/tempfile/temp.264.800115667'
File 201 not verified due to error ORA-01157
Tue Dec 18 12:59:50 2012
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile +DATA/rs1_stb/tempfile/temp.264.800115667 as +DATA/rs1_stb/tempfile/temp.280.802357191
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Dec 18 12:59:53 2012
QMNC started with pid=24, OS id=3790
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rs1';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rs1';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='rs1','rs1_stb' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='rs1','rs1_stb' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
ARC2: STARTING ARCH PROCESSES
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Tue Dec 18 12:59:57 2012
ARC4 started with pid=30, OS id=3792
Tue Dec 18 12:59:57 2012
.
CJQ0 started with pid=45, OS id=3818
Tue Dec 18 13:00:14 2012
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
Tue Dec 18 13:00:14 2012
db_recovery_file_dest_size of 4122 MB is 55.46% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Dec 18 13:00:35 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Tue Dec 18 13:00:37 2012
LGWR: Standby redo logfile selected to archive thread 1 sequence 230
LGWR: Standby redo logfile selected for thread 1 sequence 230 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 230 (LGWR switch)
Current log# 1 seq# 230 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713
Current log# 1 seq# 230 mem# 1: +FRA/rs1_stb/onlinelog/group_1.327.801239715
Tue Dec 18 13:00:37 2012
ARC3: Becoming the 'no SRL' ARCH
Tue Dec 18 13:00:37 2012
ARC0: Becoming the 'no SRL' ARCH
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 222 added for thread 1 sequence 229 ID 0x6cd69690 dest 1:
ARC3: Becoming the 'no SRL' ARCH
ARC3: Standby redo logfile selected for thread 1 sequence 229 for destination LOG_ARCHIVE_DEST_2
Thread 1 cannot allocate new log, sequence 231
Checkpoint not complete
Current log# 1 seq# 230 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713
Current log# 1 seq# 230 mem# 1: +FRA/rs1_stb/onlinelog/group_1.327.801239715
Tue Dec 18 13:00:49 2012
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 231
LGWR: Standby redo logfile selected for thread 1 sequence 231 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 231 (LGWR switch)
Current log# 2 seq# 231 mem# 0: +DATA/rs1_stb/onlinelog/group_2.277.801239715
Current log# 2 seq# 231 mem# 1: +FRA/rs1_stb/onlinelog/group_2.316.801239715
Tue Dec 18 13:00:51 2012
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 227 added for thread 1 sequence 230 ID 0x6cd69690 dest 1:
4. You must add the new tempfile to the new primary site manually after the switchover is complete. This will ensure you maintain the same tempfile structures across the Primary and Standby sites.
New Primary Site
SQL> alter tablespace temp add tempfile '+DATA' size 50M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1_stb/tempfile/temp.280.802357191
+DATA/rs1_stb/tempfile/temp.281.802357385
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1_stb/tempfile/temp.280.802357191
+DATA/rs1_stb/tempfile/temp.281.802357385
5. If a tempfile is added to the Primary and the standby site is OPEN READ ONLY you can add the new temp file manually to the Standby even if no switchover has been performed.
New Primary Site
A new file is added to the new Primary in this case the Old Standby site that is currently running in the Primary role.
SQL> alter tablespace temp add tempfile '+DATA' size 20M;
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 230
Next log sequence to archive 232
Current log sequence 232
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 230
Next log sequence to archive 232
Current log sequence 232
New Standby Site
To demonstrate the Standby will be opened read only and the tempfile added manually to the sites configuration.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
OPEN_MODE
--------------------
MOUNTED
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
As this an 11.2 environment Active Data Guard can be running while this file is built into the standby sites structure. Managed recovery is started in this demonstration.
SQL> recover managed standby database disconnect using current logfile;
Media recovery complete.
Media recovery complete.
The new 20M tempfile that was added to the Primary has not been replicated, is not a part of the standby sites structure and must be added manually if we want both sites tempfile configuration to be the same.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1/tempfile/temp.264.800115667
+DATA/rs1/tempfile/temp.273.802356687
SQL> alter tablespace temp add tempfile '+DATA' size 20M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1/tempfile/temp.264.800115667
+DATA/rs1/tempfile/temp.273.802356687
+DATA/rs1/tempfile/temp.274.802357821
NAME
--------------------------------------------------------------------------------
+DATA/rs1/tempfile/temp.264.800115667
+DATA/rs1/tempfile/temp.273.802356687
SQL> alter tablespace temp add tempfile '+DATA' size 20M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/rs1/tempfile/temp.264.800115667
+DATA/rs1/tempfile/temp.273.802356687
+DATA/rs1/tempfile/temp.274.802357821
Reference - Oracle (Doc ID 1514588.1) [Release 10.2 to 12.1]
