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

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

In this Document Goal   Solution    Administering the addition of a tempfile to an existing temporary tablespace in the Primary and Standby sites   1. Add the temp file in the primary   2. The tempfile will NOT automatically be created in the physical standby.   3. Tempfiles will not be created as a part of the switchover or failover process   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.   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.   References APPLIES TO: 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. GOAL 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. SOLUTION  Administering the addition of a tempfile to an existing temporary tablespace in the Primary and Standby sites 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. 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. SQL> recover managed standby database disconnect; 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 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 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   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 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 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 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" 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:40 2012 NSV0 started with pid=42, OS id=3755  Tue Dec 18 12:58:45 2012 RSM0 started with pid=43, OS id=3759  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  THROUGH ALL SWITCHOVER DISCONNECT  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  MRP0: Background Managed Standby Recovery process started (rs1)  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  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: 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 NSS2 started with pid=31, OS id=3794  ARC4: Archival started ARC2: STARTING ARCH PROCESSES COMPLETE *********************************************************************** Fatal NI connect error 12528, connecting to:  (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     ns secondary err code: 0     nt main err code: 0     nt secondary err code: 0     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)   Current log# 3 seq# 229 mem# 0: +DATA/rs1_stb/onlinelog/group_3.278.801239719   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:  (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     ns secondary err code: 0     nt main err code: 0     nt secondary err code: 0     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  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 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   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. 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.  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