修復由於主庫NOLOGGING操作引起的備庫ORA-01578和ORA-26040錯誤
修復由於主庫NOLOGGING操作引起的備庫ORA-01578和ORA-26040錯誤
ORA-01578和ORA-26040--NOLOGGING操作引起的壞塊-錯誤解釋和解決方案 : http://blog.itpub.net/26736162/viewspace-2152783/
眾所周知 ， DG 資料同步是基於日誌流的 ， 這也是為什麼在配置 DG 階段需要 將主庫設定為 FORCE LOGGING 的原因 。但是 ， 這也會帶來很多問題 ， SQL 執行效率 慢 ，例如：當我們使用資料泵進行遷移時我們希望最少停機時間完成，這時候我們就可能會考慮到以最小日誌匯入的方式以加快匯入速度，然後重新同步備庫。
在一些場景中，我們會去使用 nologging 操作去節省大量資料插入的時間，而這種操作所帶來的問題就是，如果該庫在有備庫的情況下，因為主庫的 nologging 插入操作不會生成 redo ，所以不會在備庫上傳輸和應用，這會導致備庫的資料出現問題。
在一個具有主備關係的主庫上將 force_logging 設定為 nologging 模式，隨後建立一張表，設定為 nologging 模式 ：
SQL> alter database no force logging;
SQL> create table DEMO tablespace users pctfree 99 as select rownum n from xmltable('1 to 100');
SQL> alter table DEMO nologging;
之後使用 /* +append*/ 插入資料並提交
SQL> insert /*+ append */ into DEMO select rownum n from xmltable('1 to 1000');
SQL> commit
這時候在備庫對該表進行查詢會看到如下報錯資訊
SQL>select count(1) from demo;
select count(1) from demo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 819)
ORA-01110: data file 4: '/data/data1/ORCL2/datafile/o1_mf_users_3ft1e9qb_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
1.1 11g
在 Oracle 11g 中 ，如果遇到這樣的問題，可以通過在備庫恢復有問題的資料檔案來解決問題 。 而要修復這個問題，需要將包含缺少的資料的資料檔案從主庫複製到物理備庫。
1 、查詢主庫
SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE_CHANGE#
-------------------------------------------- ---------------------
+DATADG/orcl/datafile/system.270.972381717 0
+DATADG/orcl/datafile/sysaux.265.972381717 0
+DATADG/orcl/datafile/undotbs1.261.972381717 0
+DATADG/orcl/datafile/users.259.972381717 6252054
+DATADG/orcl/datafile/example.264.972381807 0
+DATADG/orcl/datafile/undotbs2.258.972381927 0
+DATADG/orcl/datafile/example.266.972400297 0
+DATADG/orcl/datafile/ax.268.973612569 0
2 、查詢備庫
sys@ORCL>SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE_CHANGE#
--------------------------------------------- ---------------------
/data/data1/ORCL2/datafile/o1_mf_system_3dt1e9op_.dbf 0
/data/data1/ORCL2/datafile/o1_mf_sysaux_3ct1e9nb_.dbf 0
/data/data1/ORCL2/datafile/o1_mf_undotbs1_3gt1e9qq_.dbf 0
/data/data1/ORCL2/datafile/o1_mf_users_3ft1e9qb_.dbf 5383754
/data/data1/ORCL2/datafile/o1_mf_example_3et1e9ps_.dbf 0
/data/data1/ORCL2/datafile/o1_mf_undotbs2_3ht1e9r1_.dbf 0
/data/data1/ORCL2/datafile/o1_mf_example_3at1e9nb_.dbf 0
/data/data1/ORCL2/datafile/o1_mf_ax_3bt1e9nb_.dbf 0
3 、比較主資料庫和備用資料庫的查詢結果
在兩個查詢結果中比較 UNRECOVERABLE_CHANGE# 列的值。如果主庫中 UNRECOVERABLE_CHANGE ＃列的值大於備庫中的同一列，則需要將這些資料檔案在備庫恢復。
將主庫對應的資料檔案拷貝至備庫 ：
SQL> alter tablespace users begin backup ;
SQL> exit
ASMCMD>cp +DATADG/orcl/datafile/users.259.972381717 /tmp
$ scp /tmp/users.259.972381717 10.10.60.123:/data/data1/ORCL2/datafile/
SQL> alter tablespace users end backup ;
備庫將舊的資料檔案 RENAME 至新的資料檔案 ：
SQL> startup mount force
SQL> alter database recover managed standby database cancel;
SQL> alter system set standby_file_management=manual; # 在備庫執行 rename 操作時，需要此引數為 manual
SQL> alter database rename file '/data/data1/ORCL2/datafile/o1_mf_users_3ft1e9qb_.dbf' to '/data/data1/ORCL2/datafile/users.259.972381717';
SQL> alter system set standby_file_management=auto;
SQL> alter database recover managed standby database using current logfile disconnect from session;
之後就可以在備庫查詢到例項表 DEMO
SQL> select count(1) from demo;
COUNT(1)
----------
1 1 00
1.2 12.1
對於這種情況，在 12.1 版本中， RMAN 提供了一種便捷的方式讓我們不需要在主庫上進行資料檔案的備份傳輸而可以在備庫使用 restore database (or datafile ) from service 去從主庫進行恢復。
當然， Oracle 的 RMAN 是足夠聰明的：如果資料檔案是正常的狀態， RMAN 可以根據它們的資料檔案頭進行跳躍恢復。如果，由於 nologging 操作導致某些塊被標記為損壞的，那麼這部分資料檔案就是需要恢復的，然後怎麼辦？在恢復命令中有 FORCE 選項。但我們可能並不需要它。因為有些時候資料檔案是同步的，實時日誌應用程式還是在執行的。這個時候，為了恢復，我們需要停止應用。
一旦我們停止了應用，那麼就不需要執行 RESOTORE DATABASE FORCE 操作，因為現在資料檔案的狀態是過舊的，就算你不加 FORCE 選項 RMAN 也是不會跳過這些資料檔案的。
備庫關掉實時日誌應用，並開啟至 mount 狀態。
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started
備庫登陸 RMAN, 使用 restore database (or datafile ) from service 進行恢復
RMAN> restore database from service 'primary_db'; # 這裡的 primary_db, 為備庫至主庫的 tns 連線串的別名
Starting restore at 2018-05-03 17:00:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service primary_db
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/data1/ORCL2/datafile/o1_mf_system_02t1t9ck_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service primary_db
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data/data1/ORCL2/datafile/o1_mf_sysaux_03t1t9d3_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service primary_db
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /data/data1/ORCL2/datafile/o1_mf_undotbs1_04t1t9di_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service primary_db
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /data/data1/ORCL2/datafile/o1_mf_users_05t1t9dm_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2018-05-03 17:01:34
當然要記得去起庫並開啟實時日誌應用程式！
1.3
12.2
在 12.2 中， Oracle 提供了一種更方便的方式去進行恢復主庫會將未記錄的塊的列表傳送至備庫，並記錄在備庫控制檔案中，我們可以從備庫的 v$nonlogged_block 這個檢視檢視到相關資訊。不需要傳送主庫的整個資料檔案，而是在 RMAN 執行一個簡單的命令來恢復它們：
RECOVER DATABASE NONLOGGED BLOCK
停止備庫實時日誌應用
SQL> alter database recover managed standby database cancel;
備庫登陸 RMAN 執行
RECOVER DATABASE NONLOGGED BLOCK
注意：執行此步驟前請確認主備庫的 log_archive_config 引數已經設定
RMAN> recover database nonlogged block;
Starting recover at 2018-05-03 14:54:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 device type=DISK
starting recovery of nonlogged blocks
List of Datafiles
=================
File Status Nonlogged Blocks Blocks Examined Blocks Skipped
---- ------ ---------------- --------------- --------------
1 OK 0 0 107519
3 OK 0 0 262399
4 OK 0 0 149759
5 OK 0 0 31999
6 OK 0 0 42239
7 OK 0 16707 21532
8 OK 0 0 12799
9 OK 0 0 76799
18 OK 0 0 33279
19 OK 0 0 57599
20 OK 0 0 24959
21 OK 0 0 33279
22 OK 0 0 51199
23 OK 0 0 12799
29 OK 0 0 1310719
30 OK 0 0 12799
31 OK 0 0 33279
32 OK 0 0 52479
33 OK 0 0 923519
34 OK 0 16822 8777
35 OK 0 0 12799
37 OK 0 0 24959
Details of nonlogged blocks can be queried from v$nonlogged_block view
recovery of nonlogged blocks complete, elapsed time: 00:00:08
Finished recover at 2018-05-03 14:54:32
最後別忘了開啟實時日誌應用程式。
綜上來看， 12.2 中這個特性在資料倉儲等一些場景是可以嘗試的。以往我們開啟 force logging 造成大量的 redo 日誌並且影響一部分 dml 語句的執行效率。在 12.2 我們可以嘗試使用 nonlogging 操作去節省大量資料插入的時間，然後在系統空閒時間進行備庫恢復操作。但是注意這種操作也存在弊端，這樣你的備庫的可用性就大大降低了。凡事總有取捨！
https://docs.oracle.com/cd/B28359_01/server.111/b28294/scenarios.htm#i1015738
13.4.2 Recovery Steps for Physical Standby Databases
When the archived redo log file is copied to the standby site and applied to the physical standby database, a portion of the datafile is unusable and is marked as being unrecoverable. When you either fail over to the physical standby database, or open the standby database for read-only access, and attempt to read the range of blocks that are marked as
UNRECOVERABLE
, you will see error messages similar to the following:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521) ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
To recover after the
NOLOGGING
clause is specified, you need to copy the datafile that contains the missing redo data from the primary site to the physical standby site. Perform the following steps:
Step 1 Determine which datafiles should be copied.
Follow these steps:
-
SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE; NAME UNRECOVERABLE ----------------------------------------------------- ------------- /oracle/dbs/tbs_1.dbf 5216 /oracle/dbs/tbs_2.dbf 0 /oracle/dbs/tbs_3.dbf 0 /oracle/dbs/tbs_4.dbf 0 4 rows selected.
-
Query the standby database:
SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE; NAME UNRECOVERABLE ----------------------------------------------------- ------------- /oracle/dbs/stdby/tbs_1.dbf 5186 /oracle/dbs/stdby/tbs_2.dbf 0 /oracle/dbs/stdby/tbs_3.dbf 0 /oracle/dbs/stdby/tbs_4.dbf 0 4 rows selected.
-
Compare the query results of the primary and standby databases.
Compare the value of the
UNRECOVERABLE_CHANGE#column in both query results. If the value of theUNRECOVERABLE_CHANGE#column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.In this example, the value of the
UNRECOVERABLE_CHANGE#in the primary database for thetbs_1.dbfdatafile is greater, so you need to copy thetbs_1.dbfdatafile to the standby site.
Step 2 On the primary site, back up the datafile you need to copy to the standby site.
Issue the following SQL statements:
SQL> ALTER TABLESPACE system BEGIN BACKUP; SQL> EXIT; % cp tbs_1.dbf /backup SQL> ALTER TABLESPACE system END BACKUP;
Step 3 Copy the datafile to the standby database.
Copy the datafile that contains the missing redo data from the primary site to location on the physical standby site where files related to recovery are stored.
Step 4 On the standby database, restart Redo Apply.
Issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
You might get the following error messages (possibly in the alert log) when you try to restart Redo Apply:
ORA-00308: cannot open archived log 'standby1' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
If you get the
ORA-00308
error and Redo Apply does not terminate automatically, you can cancel recovery by issuing the following statement from another terminal window:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
These error messages are returned when one or more log files in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps, and repeat Step 4 . See Section 6.3.3.1 for information about manually resolving an archive gap.
13.4.3 Determining If a Back up Is Required After Unrecoverable Operations
If you performed unrecoverable operations on your primary database, determine if a new backup operation is required by following these steps:
-
Query the
V$DATAFILEview on the primary database to determine the system change number (SCN) or the time at which the Oracle database generated the most recent invalidated redo data. -
Issue the following SQL statement on the primary database to determine if you need to perform another backup:
SELECT UNRECOVERABLE_CHANGE#, TO_CHAR(UNRECOVERABLE_TIME, 'mm-dd-yyyy hh:mi:ss') FROM V$DATAFILE;
-
If the query in the previous step reports an unrecoverable time for a datafile that is more recent than the time when the datafile was last backed up, then make another backup of the datafile in question.
See
Oracle Database Reference
for more information about the
V$DATAFILE
view.
https://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ls.htm#i1016645
10.5.5 Adding or Re-Creating Tables On a Logical Standby Database
Typically, you use the
DBMS_LOGSTDBY.INSTANTIATE_TABLE
procedure to
re-create a table after an unrecoverable operation. You can also use this procedure to enable SQL Apply on a table that was formerly skipped.
Before you can create a table, it must meet the requirements described in
Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified"
. Then, you can use the following steps to re-create a table named
HR.EMPLOYEES
and resume SQL Apply. The directions assume that there is already a database link
BOSTON
defined to access the primary database.
The following list shows how to re-create a table and restart SQL Apply on that table:
-
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
-
Ensure no operations are being skipped for the table in question by querying the
DBA_LOGSTDBY_SKIPview:SQL> SELECT * FROM DBA_LOGSTDBY_SKIP; ERROR STATEMENT_OPT OWNER NAME PROC ----- ------------------- ------------- ---------------- ----- N SCHEMA_DDL HR EMPLOYEES N DML HR EMPLOYEES N SCHEMA_DDL OE TEST_ORDER N DML OE TEST_ORDER
Because you already have skip rules associated with the table that you want to re-create on the logical standby database, you must first delete those rules. You can accomplish that by calling the
DBMS_LOGSTDBY.UNSKIPprocedure. For example:SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt => 'DML', - schema_name => 'HR', - object_name => 'EMPLOYEES');
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt => 'SCHEMA_DDL', - schema_name => 'HR', - object_name => 'EMPLOYEES');
-
Re-create the table
HR.EMPLOYEESwith all its data in the logical standby database by using theDBMS_LOGSTDBY.INSTANTIATE_TABLEprocedure. For example:SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name => 'HR', - object_name => 'EMPLOYEES', - dblink => 'BOSTON');
-
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about the
DBMS_LOGSTDBY.UNSKIPand theDBMS_LOGSTDBY.INSTANTIATE_TABLEprocedures
To ensure a consistent view across the newly instantiated table and the rest of the database, wait for SQL Apply to catch up with the primary database before querying this table. You can do this by performing the following steps:
-
On the primary database, determine the current SCN by querying the
V$DATABASEview:SQL> SELECT CURRENT_SCN FROM V$DATABASE@BOSTON; CURRENT_SCN --------------------- 345162788
-
Make sure SQL Apply has applied all transactions committed before the
CURRENT_SCNreturned in the previous query:SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS; APPLIED_SCN -------------------------- 345161345
When the
APPLIED_SCNreturned in this query is greater than theCURRENT_SCNreturned in the first query, it is safe to query the newly re-created table.
| Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (文件 ID 958181.1) |
In this Document
|
|
Purpose |
|
|
Scope |
|
|
Details |
|
|
STEPS |
|
|
1. Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been applied to a small subset of the database: |
|
|
|
|
|
|
|
|
2. Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been applied to a large portion of the database: |
|
|
References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 16-July-2015***
***Checked for relevance on 27-Oct-2016***
PURPOSE
This document describes a method of rolling forward a standby database using incremental backups to fix the ORA-1578 and the ORA-26040 errors that were cuased due to Nologging/Unrecoverable operation.
SCOPE
When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.
This kind of NOLOGGING/UNRECOVERABLE will mark the affected blocks as corrupt during the media recovery on the standby database.Now, when you either activate the standby database, or open the standby database with the read-only option, and attempt to read the range of blocks that are marked as "UNRECOVERABLE," you see error messages similar to the following:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/vobs/oracle/dbs/stdby/tbs_1.f'
ORA-26040: Data block was loaded using the NOLOGGING option
In this article we will be checking the steps to fix the nologging changes have been applied to a small subset of the database and the nologging changes have been applied to a large portion of the database:
A look-a-like procedure is documented in :
Oracle® Data Guard Concepts and Administration 11g Release 1 (11.1) Part Number B28294-03
Section 13.4 Recovering After the NOLOGGING Clause Is Specified
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/scenarios.htm#i1015738
DETAILS
STEPS
1. Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been applied to a small subset of the database:
1. List the files that have had nologging changes applied by querying the V$DATAFILE view on the standby database. For example:
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
FILE# FIRST_NONLOGGED_SCN
---------- -------------------
4 225979
5 230184
2. Stop Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. On the standby database, offline the datafiles (recorded in step 0) that have had nologging changes. Taking these datafiles offline ensures redo data is not skipped for the corrupt blocks while the incremental backups are performed.
SQL> ALTER DATABASE DATAFILE 4 OFFLINE FOR DROP;
SQL> ALTER DATABASE DATAFILE 5 OFFLINE FOR DROP;
4. Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
5. While connected to the primary database as the RMAN target, create an incremental backup for each datafile listed in the FIRST_NONLOGGED_SCN column (recorded in step 0). For example:
RMAN> BACKUP INCREMENTAL FROM SCN 225979 DATAFILE 4 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';
RMAN> BACKUP INCREMENTAL FROM SCN 230184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';
6. Transfer all backup sets created on the primary system to the standby system. (Note that there may be more than one backup file created.)
% scp /tmp/ForStandby_* standby:/tmp
7. While connected to the physical standby database as the RMAN target, catalog all incremental backup pieces. For example:
RMAN> CATALOG START WITH '/tmp/ForStandby_';
8. Stop Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
9. Online the datafiles on the standby database
SQL> ALTER DATABASE DATAFILE 4 ONLINE;
SQL> ALTER DATABASE DATAFILE 5 ONLINE;
10. While connected to the physical standby database as the RMAN target, apply the incremental backup sets:
RMAN> RECOVER DATAFILE 4, 5 NOREDO;
11. Query the V$DATAFILE view on the standby database to verify there are no datafiles with nologged changes. The following query should return zero rows
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
12. Recreate the Standby Controlfile following:
Note 459411.1 Steps to recreate a Physical Standby Controlfile
13. Remove the incremental backups from the standby system:
RMAN> DELETE BACKUP TAG 'FOR STANDBY';
14. Manually remove the incremental backups from the primary system. For example, the following example uses the Linux rm command:
% rm /tmp/ForStandby_*
15. Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Note: Starting from 12c we can use RECOVER DATABASE...FROM SERVICE clause in RMAN to generate, transfer and apply the incremental backup in a single step. Please refer below document for examples:
Note 1987763.1
ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C
2. Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been applied to a large portion of the database:
1. Query the V$DATAFILE view on the standby database to record the lowest FIRST_NONLOGGED_SCN:
SQL> SELECT MIN(FIRST_NONLOGGED_SCN) FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN>0;
MIN(FIRST_NONLOGGED_SCN)
------------------------
223948
2.Stop Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3.While connected to the primary database as the RMAN target, create an incremental backup from the lowest FIRST_NONLOGGED_SCN (recorded in step 0)
RMAN> BACKUP INCREMENTAL FROM SCN 223948 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FOR STANDBY';
4.Transfer all backup sets created on the primary system to the standby system. (Note that more than one backup file may have been created.) The following example uses the scp command to copy the files:
% scp /tmp/ForStandby_* standby:/tmp
5.While connected to the standby database as the RMAN target, catalog all incremental backup piece(s)
RMAN> CATALOG START WITH '/tmp/ForStandby_';
6.While connected to the standby database as the RMAN target, apply the incremental backups:
RMAN> RECOVER DATABASE NOREDO;
7.Query the V$DATAFILE view to verify there are no datafiles with nologged changes. The following query on the standby database should return zero rows:
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
8. Recreate the Standby Controlfile following:
Note 459411.1 Steps to recreate a Physical Standby Controlfile
9.Remove the incremental backups from the standby system:
RMAN> DELETE BACKUP TAG 'FOR STANDBY';
10.Manually remove the incremental backups from the primary system. For example, the following removes the backups using the Linux rm command:
% rm /tmp/ForStandby_*
11.Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Note:
If the affected files belong to a READ ONLY tablespace, those files will be ignored during backup. To bypass the issue, at Primary Database, switch the tablespace from read only to read write and back to read only again :
SQL> alter tablespace <tablespace_name> read write ;
SQL> alter tablespace <tablespace_name> read only ;
REFERENCES
NOTE:794505.1 - ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution
Rolling Forward a Physical Standby Using Recover From Service Command in 12c (文件 ID 1987763.1)
In this Document
|
|
Goal |
|
|
Solution |
|
|
References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
GOAL
Rolling Forward a Physical Standby Database Using the RECOVER FROM SERVICE Command
A standby database is a transactionally-consistent copy of the production database. It enables production Oracle database to survive disasters and data corruption. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch a standby database to the production role, minimizing the downtime associated with the outage. Moreover, performance of production database can be improved by offloading resource-intensive backup and reporting operations to standby systems. As you can see, it’s always desirable to have standby database synchronized with the primary database.
Prior to 12c, in order to roll forward the standby database using incremental backups you would need to:
-
Create a control file for the standby database on the primary database.
-
Take an incremental backup on the primary starting from the SCN# of the standby database.
-
Copy the incremental backup to the standby host and catalog it with RMAN.
-
Mount the standby database with newly created standby control file.
-
Cancel managed recovery of the standby database and apply incremental backup to the standby database.
-
Start managed recovery of standby database.
In 12c, this procedure has been dramatically simplified. Now you can use the RECOVER … FROM SERVICE command to synchronize the physical standby database with the primary database. This command does the following:
-
Creates an incremental backup containing the changes to the primary database. All changes to data files on the primary database, beginning with the SCN in the standby data file header, are included in the incremental backup.
-
Transfers the incremental backup over the network to the physical standby database.
-
Applies the incremental backup to the physical standby database.
This results in rolling forward the standby data files to the same point-in-time as the primary. However, the standby control file still contains old SCN values which are lower than the SCN values in the standby data files. Therefore, to complete the synchronization of the physical standby database, the standby control file needs to be refreshed to update the SCN#.
SOLUTION
Steps to Refresh a Physical Standby Database with Changes Made to the Primary Database
Environment:
Primary Database:
DB_UNIQUE_NAME: prim ( net service name 'PRIM')
Standby Database:
DB_UNIQUE_NAME:clone( net service name 'CLONE')
Use the following steps to refresh the physical standby database with changes made to the primary database:
Prerequisites
:
-
Oracle Net connectivity is established between the physical standby database and the primary database.
-
You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database.
-
The password files on the primary database and the physical standby database are the same.
-
The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0.
-
Start RMAN and connect as target to the physical standby database.
Check the existing size of the Primary database and compare with the standby existing size as we need at-least the difference in size (free space) since standby is behind ,if the datafile on primary has autoextended then standby file would be same in size compared to prod,so when you do the incremental rollforward it would apply the blocks and add any new one to match the size of standby file.
1. Place the physical standby database in MOUNT mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
2. Stop the managed recovery processes on the physical standby database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
NOTE: If using broker, you will need to stop MRP through DGMGRL. I.e.:
DGMGRL> edit database '<Standby db_unique_name>' set STATE='APPLY-OFF' ;
3. Let us identify the datafiles on standby database which are out of sync with respect to primary.
Primary:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /u01/app/oracle/oradata/prim/system01.db 1984501
3 /u01/app/oracle/oradata/prim/sysaux01.db 1984501
4 /u01/app/oracle/oradata/prim/undotbs01.d 1984501
5 /u01/app/oracle/oradata/prim/pdbseed/sys
1733076
6 /u01/app/oracle/oradata/prim/users01.dbf 1984501
7 /u01/app/oracle/oradata/prim/pdbseed/sys
1733076
8 /u01/app/oracle/oradata/prim/pdb1/system 1984501
9 /u01/app/oracle/oradata/prim/pdb1/sysaux 1984501
10 /u01/app/oracle/oradata/prim/pdb1/pdb1_u 1984501
16 /u01/app/oracle/oradata/prim/pdb3/system 1984501
17 /u01/app/oracle/oradata/prim/pdb3/sysaux 1984501
18 /u01/app/oracle/oradata/prim/pdb3/pdb1_u 1984501
19 /u01/app/oracle/oradata/prim/pdb3/test.d 1984501
13 rows selected.
STANDBy:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /u01/app/oracle/oradata/clone/system01.d 1980995
3 /u01/app/oracle/oradata/clone/sysaux01.d 1980998
4 /u01/app/oracle/oradata/clone/undotbs01. 1981008
5 /u01/app/oracle/oradata/clone/pdbseed/sy
1733076
6 /u01/app/oracle/oradata/clone/users01.db 1981012
7 /u01/app/oracle/oradata/clone/pdbseed/sy
1733076
8 /u01/app/oracle/oradata/clone/pdb1/syste 1981015
9 /u01/app/oracle/oradata/clone/pdb1/sysau 1981021
10 /u01/app/oracle/oradata/clone/pdb1/pdb1_ 1981028
16 /u01/app/oracle/oradata/clone/pdb3/syste 1981030
17 /u01/app/oracle/oradata/clone/pdb3/sysau 1981036
18 /u01/app/oracle/oradata/clone/pdb3/pdb1_ 1981043
19 /u01/app/oracle/oradata/clone/pdb3/test. 1981044
13 rows selected.
On checking SCN in datafile headers on primary (prim) and standby (clone), we note that whereas SCN
of datafiles 5,7 match on primary and standby, for rest of the datafiles (1,3,4,6,8,9,10,16,17) standby is lagging behind primary.
4. Note the current SCN of the physical standby database. This is required to determine, in a later step, if new data files were added to the primary database.
Query the V$DATABASE view to obtain the current SCN using the following command:
SELECT CURRENT_SCN FROM V$DATABASE;
5.
The RECOVER … FROM SERVICE command refreshes the standby data files and rolls them forward to the same point-in-time as the primary.
[oracle@localhost ~]$ rman target/
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Mar 9 18:22:52 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=4165840403, not open)
RMAN> recover database from service prim noredo using compressed backupset;
Log:
Starting recover at 09-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
skipping datafile 5; already restored to SCN 1733076
skipping datafile 7; already restored to SCN 1733076
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00001: /u01/app/oracle/oradata/clone/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00003: /u01/app/oracle/oradata/clone/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00004: /u01/app/oracle/oradata/clone/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00006: /u01/app/oracle/oradata/clone/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00008: /u01/app/oracle/oradata/clone/pdb1/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00009: /u01/app/oracle/oradata/clone/pdb1/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00010: /u01/app/oracle/oradata/clone/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00016: /u01/app/oracle/oradata/clone/pdb3/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00017: /u01/app/oracle/oradata/clone/pdb3/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00018: /u01/app/oracle/oradata/clone/pdb3/pdb1_users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service prim
destination for restore of datafile 00019: /u01/app/oracle/oradata/clone/pdb3/test.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 09-MAR-15
6. Lets check the SCNs of the datafiles at primary and standby now.
Primary
--------
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /u01/app/oracle/oradata/prim/system01.db 1985174
3 /u01/app/oracle/oradata/prim/sysaux01.db 1985183
4 /u01/app/oracle/oradata/prim/undotbs01.d 1985194
5 /u01/app/oracle/oradata/prim/pdbseed/sys 1733076
6 /u01/app/oracle/oradata/prim/users01.dbf 1985203
7 /u01/app/oracle/oradata/prim/pdbseed/sys 1733076
8 /u01/app/oracle/oradata/prim/pdb1/system 1985206
9 /u01/app/oracle/oradata/prim/pdb1/sysaux 1985212
10 /u01/app/oracle/oradata/prim/pdb1/pdb1_u 1985218
16 /u01/app/oracle/oradata/prim/pdb3/system 1985221
17 /u01/app/oracle/oradata/prim/pdb3/sysaux 1985343
18 /u01/app/oracle/oradata/prim/pdb3/pdb1_u 1985350
19 /u01/app/oracle/oradata/prim/pdb3/test.d 1985354
Standby
--------
RMAN> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /u01/app/oracle/oradata/clone/system01.d 1985174
3 /u01/app/oracle/oradata/clone/sysaux01.d 1985183
4 /u01/app/oracle/oradata/clone/undotbs01. 1985194
5 /u01/app/oracle/oradata/clone/pdbseed/sy 1733076
6 /u01/app/oracle/oradata/clone/users01.db 1985203
7 /u01/app/oracle/oradata/clone/pdbseed/sy 1733076
8 /u01/app/oracle/oradata/clone/pdb1/syste 1985206
9 /u01/app/oracle/oradata/clone/pdb1/sysau 1985212
10 /u01/app/oracle/oradata/clone/pdb1/pdb1_ 1985218
16 /u01/app/oracle/oradata/clone/pdb3/syste 1985221
17 /u01/app/oracle/oradata/clone/pdb3/sysau 1985343
18 /u01/app/oracle/oradata/clone/pdb3/pdb1_ 1985350
19 /u01/app/oracle/oradata/clone/pdb3/test. 1985354
13 rows selected
From above,we can see primary and standby SCNs matching now.
However, the standby control file still contains old SCN values which are lower than the SCN values in the standby data files.
Therefore, to complete the synchronization of the physical standby database, we must refresh the standby control file to update the SCN#.
7. Use the following commands to shut down the standby database and then start it in NOMOUNT mode.
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
8. Restore the standby control file by using the control file on the primary database using service prim.
The following command restores the control file on the physical standby database by using the primary database control file.
RESTORE STANDBY CONTROLFILE FROM SERVICE <primary_tns_service>;
RMAN> restore standby controlfile from service prim;
Starting restore at 09-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/clone/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/clone/control02.ctl
Finished restore at 09-MAR-15
After this step, the names of files in the standby control file are the names that were used in the primary database.
NOTE: Depending on the configuration, the path and/or names of the standby datafiles after the standby controlfile refresh may be correct and thus steps #9 and #10 can be skipped.
Mount the standby database using the following command:
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN> report schema;
Starting implicit crosscheck backup at 09-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 09-MAR-15
Starting implicit crosscheck copy at 09-MAR-15
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 09-MAR-15
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_17_11q13dm8_.arc
File Name: /u01/app/oracle/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_16_10q13dm8_.arc
File Name: /u01/app/oracle/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_2_bhk1ctcz_.arc
File Name: /u01/app/oracle/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_1_bhk17cw8_.arc
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CLONE
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM *** /u01/app/oracle/oradata/prim/system01.dbf
3 730 SYSAUX *** /u01/app/oracle/oradata/prim/sysaux01.dbf
4 90 UNDOTBS1 *** /u01/app/oracle/oradata/prim/undotbs01.dbf
5 250 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/prim/pdbseed/system01.dbf
6 5 USERS *** /u01/app/oracle/oradata/prim/users01.dbf
7 590 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/prim/pdbseed/sysaux01.dbf
8 260 PDB1:SYSTEM *** /u01/app/oracle/oradata/prim/pdb1/system01.dbf
9 620 PDB1:SYSAUX *** /u01/app/oracle/oradata/prim/pdb1/sysaux01.dbf
10 5 PDB1:USERS *** /u01/app/oracle/oradata/prim/pdb1/pdb1_users01.dbf
16 260 PDB3:SYSTEM *** /u01/app/oracle/oradata/prim/pdb3/system01.dbf
17 620 PDB3:SYSAUX *** /u01/app/oracle/oradata/prim/pdb3/sysaux01.dbf
18 5 PDB3:USERS *** /u01/app/oracle/oradata/prim/pdb3/pdb1_users01.dbf
19 50 PDB3:TEST *** /u01/app/oracle/oradata/prim/pdb3/test.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/prim/temp01.dbf
2 20 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/prim/pdbseed/pdbseed_temp01.dbf
3 373 PDB1:TEMP 32767 /u01/app/oracle/oradata/prim/pdb1/temp01.dbf
4 71 PDB3:TEMP 32767 /u01/app/oracle/oradata/prim/pdb3/temp01.dbf
9. Update the names of the data files and the temp files in the standby control file.
Use the CATALOG command and the SWITCH command to update all the data file names.
RMAN> catalog start with '<path where the actual standby datafile existed>';
In this case
RMAN> Catalog start with '/u01/app/oracle/oradata/clone/';
searching for all files that match the pattern /u01/app/oracle/oradata/clone
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/clone/pdb1/pdb1_users01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb1/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb1/system01.dbf
File Name: /u01/app/oracle/oradata/clone/pdbseed/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/pdbseed/system01.dbf
File Name: /u01/app/oracle/oradata/clone/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/system01.dbf
File Name: /u01/app/oracle/oradata/clone/undotbs01.dbf
File Name: /u01/app/oracle/oradata/clone/users01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/pdb1_users01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/system01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/test.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/clone/pdb1/pdb1_users01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb1/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb1/system01.dbf
File Name: /u01/app/oracle/oradata/clone/pdbseed/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/pdbseed/system01.dbf
File Name: /u01/app/oracle/oradata/clone/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/system01.dbf
File Name: /u01/app/oracle/oradata/clone/undotbs01.dbf
File Name: /u01/app/oracle/oradata/clone/users01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/pdb1_users01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/sysaux01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/system01.dbf
File Name: /u01/app/oracle/oradata/clone/pdb3/test.dbf
10. Switch to cataloged copy.
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "/u01/app/oracle/oradata/clone/system01.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/clone/sysaux01.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/clone/undotbs01.dbf"
datafile 5 switched to datafile copy "/u01/app/oracle/oradata/clone/pdbseed/system01.dbf"
datafile 6 switched to datafile copy "/u01/app/oracle/oradata/clone/users01.dbf"
datafile 7 switched to datafile copy "/u01/app/oracle/oradata/clone/pdbseed/sysaux01.dbf"
datafile 8 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb1/system01.dbf"
datafile 9 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb1/sysaux01.dbf"
datafile 10 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb1/pdb1_users01.dbf"
datafile 16 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb3/system01.dbf"
datafile 17 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb3/sysaux01.dbf"
datafile 18 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb3/pdb1_users01.dbf"
datafile 19 switched to datafile copy "/u01/app/oracle/oradata/clone/pdb3/test.dbf"
Here, /u01/app/oracle/oradata/clone is the location of the data files on the physical standby database.
All data files must be stored in this location.
11. Use the current SCN returned in Step 4 to determine if new data files were added to the primary database since the standby database was last refreshed. If yes, these data files need to be restored on the standby from the primary database.
The following example assumes that the CURRENT_SCN returned in Step 6 is 1984232 and lists the data files that were created on the primary after the timestamp represented by this SCN:
SELECT file# FROM V$DATAFILE WHERE creation_change# >= 1984232;
If no files are returned in Step 11, then go to Step 13. If one or more files are returned in Step 11, then restore these data files from the primary database as in step 12.
12. If you are not connected to a recovery catalog, then use the following commands to restore data files that were added to the primary after the standby was last refreshed ( assuming datafile 21 added to the primary):
RUN
{
SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/clone';
RESTORE DATAFILE 21 FROM SERVICE prim;
}
If you are connected to a recovery catalog, then use the following command to restore data files that were added to the primary after the standby was last refreshed (assuming data file 21 added to the primary):
RESTORE DATAFILE 21 FROM SERVICE prim;
13. Update the names of the online redo logs and standby redo logs in the standby control file using one of the following methods:
- Use the ALTER DATABASE CLEAR command to clear the log files in all redo log groups of the standby database. RMAN then recreates all the standby redo logs and the online redo log files.
Note:
Clearing log files is recommended only if the standby database does not have access to the online redo log files and standby redo log
files of the primary database( for ex: standby and primary at same server or using same ASM disk group). If the standby database has access to the redo log files of the primary database and the redo log file
names of the primary database are OMF names, then the ALTER DATABASE command will delete log files on the primary database.
- Use the ALTER DATABASE RENAME FILE command to rename the redo log files.
Use a separate command to rename each log file.
To rename log files, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.
Renaming log files is recommended when the number of online redo logs files and standby redo log files is the same
in the primary database and the physical standby database.
(Oracle Active Data Guard only) Perform the following steps to open the physical standby database:
On the primary database, switch the archived redo log files using the following command:
ALTER SYSTEM ARCHIVE LOG CURRENT;
On the physical standby database, run the following commands:
RECOVER DATABASE;
ALTER DATABASE OPEN READ ONLY;
Start the managed recovery processes on the physical standby database by using the following command:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
REFERENCES
NOTE:1646232.1 - ORA-19573 when trying to restore to standby with incremental backup From Primary or During any RMAN restore operation
About Me
|
........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人微信公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2018-07-01 06:00 ~ 2018-07-31 24:00 在魔都完成 ● 最新修改時間：2018-07-01 06:00 ~ 2018-07-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微信客戶端 掃描下面的二維碼來關注小麥苗的微信公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗微信， 學習最實用的資料庫技術。
........................................................................................................................ |
|
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2158170/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 修復由於主庫NOLOGGING引起的備庫ORA-01578和ORA-26040錯誤Oracle
- ORA-01578和ORA-26040--NOLOGGING操作引起的壞塊-錯誤解釋和解決方案
- Oracle standby的ORA-01578 ORA-01110 ORA-26040 坑爹的NOLOGGINGOracle
- 挽救DG中主庫的nologging操作的塊
- Oracle資料庫由dataguard備庫引起的log file sync等待Oracle資料庫
- innodb 庫的備份注意點（由phpmyadmin引起的解決方案）PHP
- Golang 中由零值和 gob 庫的特性引起的 BUGGolang
- 【DATAGUARD】Oracle Dataguard nologging 塊修復Oracle
- 一次rman恢復引起的nologging問題模擬
- 在DG備庫備份資料庫並恢復到一個主機上，報錯RMAN-06820資料庫
- 如何掃描和修復 Linux 磁碟錯誤Linux
- Polardb資料庫掛庫後，如何恢復主備關係資料庫
- dedecms資料庫檔案出錯的修復方法資料庫
- 如何修復 HTTP 505 錯誤？HTTP
- 如何修復HTTP 301錯誤？HTTP
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 登錄檔引起的 I/O 操作發生了不可恢復的錯誤處理辦法
- 記錄一個由於倉庫層錯誤導致軟刪除失效的問題
- PostgreSql資料庫的備份和恢復SQL資料庫
- win10系統lsp錯誤怎樣修復_win10修復lsp錯誤的步驟Win10
- Vue packages version conflicts 錯誤修復VuePackage
- 如何修復HTTP 302錯誤呢？HTTP
- Oracle資料庫冷備和恢復Oracle資料庫
- 資料庫資料恢復—附加資料庫錯誤823的SQL Server資料恢復案例資料庫資料恢復SQLServer
- Interbase資料庫修復資料庫
- Firebird資料庫修復資料庫
- MySQL 5.7 主庫崩潰切備庫MySql
- 一個由於侵入框架引起的故障框架
- Checkpoint log:invalid bitmap page錯誤修復
- MySQL GTID複製錯誤修復演示MySql
- 如何修復http代理406錯誤呢？HTTP
- 如何修復代理400錯誤請求？
- http代理401錯誤修復步驟HTTP
- eclipse中maven專案failonmissingwebxml錯誤的修復EclipseMavenAIWebXML
- 如何修復http代理出現的503錯誤？HTTP
- SVN倉庫備份和遷移基本操作
- Mongo 資料庫備份和恢復命令Go資料庫
- OracleDG備庫恢復–gapOracle