Data Guard Physical Standby - Managing temporary tablespace tempfiles (文档 ID 1514588.1)

一时失言乱红尘 2022-08-10 16:42 39阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td align="left"> Data Guard Physical Standby - Managing temporary tablespace tempfiles (文档 ID 1514588.1)</td> 
 <td align="right">&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

<table style="table-layout:fixed; overflow:hidden"> 
 <tbody> 
 <tr> 
 <td style="overflow:hidden"> In this Document 
 <table> 
 <tbody> 
 <tr> 
 <td></td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#GOAL" rel="nofollow">Goal</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#FIX" rel="nofollow">Solution</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#aref_section21" rel="nofollow">&nbsp;Administering the addition of a tempfile to an existing temporary tablespace in the Primary and Standby sites</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#aref_section22" rel="nofollow">1. Add the temp file in the primary</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#aref_section23" rel="nofollow">2. The tempfile will NOT automatically be created in the physical standby.</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#aref_section24" rel="nofollow">3. Tempfiles will not be created as a part of the switchover or failover process</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#aref_section25" rel="nofollow">4. You must add the new tempfile to the new primary site manually after the switchover is complete.&nbsp; This will ensure you maintain the same tempfile structures across the Primary and Standby sites.</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#aref_section26" rel="nofollow">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.</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bxa2ev12i_4&amp;_afrLoop=860281618711008#REF" rel="nofollow">References</a></td> 
 </tr> 
 </tbody> 
 </table> 
 <hr> <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> APPLIES TO:</h2> Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2] Information in this document applies to any platform. <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>GOAL</h2> 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.&nbsp; 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. <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SOLUTION</h2> <a rel="nofollow"></a> <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em">&nbsp;Administering the addition of a tempfile to an existing temporary tablespace in the Primary and Standby sites</h3> <a rel="nofollow"></a> <h4 style="font-size:13px; font-style:italic; color:rgb(0,0,136); padding-top:0.5em"> 1. Add the temp file in the primary</h4> Primary Site 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; alter tablespace temp add tempfile '+DATA' size 50M; Tablespace altered. SQL&gt; select name from v$tempfile; NAME -------------------------------------------------- +DATA/rs1/tempfile/temp.264.800115667 +DATA/rs1/tempfile/temp.273.802356687 SQL&gt; alter system switch logfile; System altered. SQL&gt; archive log list Database log mode&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Archive Mode Automatic archival&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Enabled Archive destination&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE_DB_RECOVERY_FILE_DEST Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 221 Next log sequence to archive&nbsp;&nbsp; 223 Current log sequence&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 223 SQL&gt; alter system switch logfile; System altered. 
 </div> <a rel="nofollow"></a> <h4 style="font-size:13px; font-style:italic; color:rgb(0,0,136); padding-top:0.5em"> 2. The tempfile will NOT automatically be created in the physical standby.</h4> Standby Site&nbsp; The tempfile thats has been added to the Primary site file is not automatically replicated to the Standby site like regular datafiles.&nbsp; There is still only one tempfile in the Standby database even though there are now 2 tempfiles in the Primary. The parameter&nbsp;standby_file_management=AUTO&nbsp;has no impact&nbsp;on tempfile management in the environment. 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; recover managed standby database disconnect; Media recovery complete. SQL&gt; select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/rs1_stb/tempfile/temp.264.800115667 
 &nbsp; 
 </div> 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 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; alter system switch logfile; System altered. SQL&gt; archive log list Database log mode&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Archive Mode Automatic archival&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Enabled Archive destination&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE_DB_RECOVERY_FILE_DEST Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 221 Next log sequence to archive&nbsp;&nbsp; 223 Current log sequence&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 223 
 </div> Standby Alert Log 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 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 
 &nbsp; 
 </div> 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 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; archive log list Database log mode&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Archive Mode Automatic archival&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Enabled Archive destination&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE_DB_RECOVERY_FILE_DEST Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 221 Next log sequence to archive&nbsp;&nbsp; 0 Current log sequence&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 223 SQL&gt; select name from v$tempfile;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; NAME -------------------------------------------------------------------------------- +DATA/rs1_stb/tempfile/temp.264.800115667 
 
 
 </div> <a rel="nofollow"></a> <h4 style="font-size:13px; font-style:italic; color:rgb(0,0,136); padding-top:0.5em"> 3. Tempfiles will not be created as a part of the switchover or failover process</h4> During a switchover operation the file is not created or replicated to the new primary site.&nbsp; It must be added manually.&nbsp; In this case it will be performed using the Data Guard broker command line utility. Primary Site 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 DGMGRL&gt; connect sys@rs1_prm_dgmgrl Password: Connected. DGMGRL&gt; show configuration; Configuration - rs1_dg &nbsp; Protection Mode: MaxAvailability &nbsp; Databases: &nbsp;&nbsp;&nbsp; rs1&nbsp;&nbsp;&nbsp;&nbsp; - Primary database &nbsp;&nbsp;&nbsp; rs1_stb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL&gt; enable configuration; Enabled. DGMGRL&gt; show configuration; Configuration - rs1_dg &nbsp; Protection Mode: MaxAvailability &nbsp; Databases: &nbsp;&nbsp;&nbsp; rs1&nbsp;&nbsp;&nbsp;&nbsp; - Primary database &nbsp;&nbsp;&nbsp; rs1_stb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL&gt; switchover to rs1_stb&nbsp; 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" 
 </div> 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 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 Tue Dec 18 12:58:40 2012 NSV0 started with pid=42, OS id=3755&nbsp; Tue Dec 18 12:58:45 2012 RSM0 started with pid=43, OS id=3759&nbsp; 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 fal_server='rs1_prm' SCOPE=BOTH; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Tue Dec 18 12:58:50 2012 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /u01/app/oracle/diag/rdbms/rs1_stb/rs1/trace/rs1_pr00_3663.trc: ORA-16037: user requested cancel of managed recovery operation Recovery interrupted! Tue Dec 18 12:58:50 2012 MRP0: Background Media Recovery process shutdown (rs1) Managed Standby Recovery Canceled (rs1) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE&nbsp; THROUGH ALL SWITCHOVER DISCONNECT&nbsp; USING CURRENT LOGFILE Attempt to start background Managed Standby Recovery process (rs1) Tue Dec 18 12:58:51 2012 MRP0 started with pid=37, OS id=3761&nbsp; MRP0: Background Managed Standby Recovery process started (rs1) &nbsp;started logmerger process Tue Dec 18 12:58:57 2012 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 2 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Tue Dec 18 12:58:57 2012 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE&nbsp; THROUGH ALL SWITCHOVER DISCONNECT&nbsp; 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 &nbsp; Mem# 0: +DATA/rs1_stb/onlinelog/group_5.275.801239591 &nbsp; 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 &nbsp; Mem# 0: +DATA/rs1_stb/onlinelog/group_4.274.801239589 &nbsp; 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 &nbsp; Current log# 2 seq# 228 mem# 0: +DATA/rs1_stb/onlinelog/group_2.277.801239715 &nbsp; 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 &nbsp;&nbsp;&nbsp; ns secondary err code: 0 &nbsp;&nbsp;&nbsp; nt main err code: 0 &nbsp;&nbsp;&nbsp; nt secondary err code: 0 &nbsp;&nbsp;&nbsp; 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: 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&nbsp; 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&nbsp; Tue Dec 18 12:59:57 2012 NSS2 started with pid=31, OS id=3794&nbsp; ARC4: Archival started ARC2: STARTING ARCH PROCESSES COMPLETE *********************************************************************** Fatal NI connect error 12528, connecting to: &nbsp;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=grid1vm1.au.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rs1.au.oracle.com)(CID=(PROGRAM=oracle)(HOST=grid1vm2.au.oracle.com)(USER=oracle)))) .. . TNS-12564: TNS:connection refused &nbsp;&nbsp;&nbsp; ns secondary err code: 0 &nbsp;&nbsp;&nbsp; nt main err code: 0 &nbsp;&nbsp;&nbsp; nt secondary err code: 0 &nbsp;&nbsp;&nbsp; nt OS err code: 0 Error 12528 received logging on to the standby Tue Dec 18 13:00:01 2012 Error 12528 for archive log file 3 to 'rs1_prm' LGWR: Failed to archive log 3 thread 1 sequence 229 (12528) Thread 1 advanced to log sequence 229 (LGWR switch) &nbsp; Current log# 3 seq# 229 mem# 0: +DATA/rs1_stb/onlinelog/group_3.278.801239719 &nbsp; Current log# 3 seq# 229 mem# 1: +FRA/rs1_stb/onlinelog/group_3.315.801239719 Tue Dec 18 13:00:02 2012 Archived Log entry 221 added for thread 1 sequence 228 ID 0x6cd69690 dest 1: Tue Dec 18 13:00:03 2012 *********************************************************************** Fatal NI connect error 12514, connecting to: &nbsp;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=grid1vm1.au.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rs1_DGB.au.oracle.com)(INSTANCE_NAME=rs1)(CID=(PROGRAM=oracle)(HOST=grid1vm2.au.oracle.com)(USER=oracle)))) .. . TNS-12564: TNS:connection refused &nbsp;&nbsp;&nbsp; ns secondary err code: 0 &nbsp;&nbsp;&nbsp; nt main err code: 0 &nbsp;&nbsp;&nbsp; nt secondary err code: 0 &nbsp;&nbsp;&nbsp; nt OS err code: 0 Tue Dec 18 13:00:05 2012 ARC3: Becoming the 'no SRL' ARCH Tue Dec 18 13:00:13 2012 Starting background process CJQ0 Tue Dec 18 13:00:14 2012 CJQ0 started with pid=45, OS id=3818&nbsp; 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) &nbsp; Current log# 1 seq# 230 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713 &nbsp; 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 &nbsp; Current log# 1 seq# 230 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713 &nbsp; 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) &nbsp; Current log# 2 seq# 231 mem# 0: +DATA/rs1_stb/onlinelog/group_2.277.801239715 &nbsp; 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: 
 </div> <a rel="nofollow"></a> <h4 style="font-size:13px; font-style:italic; color:rgb(0,0,136); padding-top:0.5em"> 4. You must add the new tempfile to the new primary site manually after the switchover is complete.&nbsp; This will ensure you maintain the same tempfile structures across the Primary and Standby sites.</h4> New Primary Site 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; alter tablespace temp add tempfile '+DATA' size 50M; Tablespace altered. SQL&gt;&nbsp; select name from v$tempfile;&nbsp;&nbsp; &nbsp; NAME -------------------------------------------------------------------------------- +DATA/rs1_stb/tempfile/temp.280.802357191 +DATA/rs1_stb/tempfile/temp.281.802357385 
 </div> <a rel="nofollow"></a> <h4 style="font-size:13px; font-style:italic; color:rgb(0,0,136); padding-top:0.5em"> 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.</h4> 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. 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; alter tablespace temp add tempfile '+DATA' size 20M; Tablespace altered. SQL&gt; alter system switch logfile; System altered. SQL&gt; archive log list Database log mode&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Archive Mode Automatic archival&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Enabled Archive destination&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE_DB_RECOVERY_FILE_DEST Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 230 Next log sequence to archive&nbsp;&nbsp; 232 Current log sequence&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 232 
 
 
 </div> &nbsp; New Standby Site To demonstrate the Standby will be opened read only and the tempfile added manually to the sites configuration. 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL&gt; recover managed standby database cancel; Media recovery complete. SQL&gt; alter database open; Database altered. 
 
 
 </div> As this an 11.2 environment Active Data Guard can be running while this file is built into the standby sites structure.&nbsp; Managed recovery is started in this demonstration. 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; recover managed standby database disconnect using current logfile; Media recovery complete. 
 </div> &nbsp;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. 
 <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
 SQL&gt; select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/rs1/tempfile/temp.264.800115667 +DATA/rs1/tempfile/temp.273.802356687 SQL&gt; alter tablespace temp add tempfile '+DATA' size 20M; Tablespace altered. SQL&gt; select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/rs1/tempfile/temp.264.800115667 +DATA/rs1/tempfile/temp.273.802356687 +DATA/rs1/tempfile/temp.274.802357821 
 </div> </td> 
 </tr> 
 </tbody> 
</table>