Oracle Database 11G RMAN 冷备异地恢复数据库

xiaoxiao2022-07-13 160

Oracle Database 11G RMAN 冷备异地恢复数据库

简述步骤1. 从源数据库获取 pfile2. 修正pfile3. 异地数据库-用 pfile 启动数据库到 nomount 状态4. 异地数据库-指定备份集恢复控制文件到指定路径5. 异地数据库-将备份集放入到新的快速恢复区6. 异地数据库-将数据文件恢复到指定路径7. 异地数据库-备份控制文件到 trace8. 异地数据库-创建新的控制文件9. 异地数据库-打开数据库详情1 从源数据库获取备份集1.1 查询数据库版本1.2 启动到 mount 状态后全库冷备 2 从源数据库创建 pfile 文件3 修改 pfile 文件3.1 修改 pfile 文件参数3.2 异地数据库-创建目标数据库的目录结构 4 异地数据库-使用 pfile 启动数据库到 nomout 状态5 异地数据库-使用 RMAN 恢复控制文件到 pfile 指定的路径6 异地数据库-启动数据库到 mount 状态7 异地数据库-使用 RMAN 恢复数据文件7.1 使用 RMAN 查看备份集信息7.2 异地数据库-将包含数据文件的备份集放到指定路径7.3 异地数据库-使用 RMAN 执行数据文件恢复7.4 异地数据库-检查数据文件信息7.5 异地数据库-检查联机重做日志文件与临时表空间文件信息 8 异地数据库-创建新数据库的控制文件8.1 创建控制文件的副本8.2 创建新的控制文件 9 异地数据库-启动数据库附录ERROR：源数据库11G异地恢复到12C 引用

简述

该实验在 11G 的版本进行，先通过 RMAN 进行全库冷备，然后在异地通过 RMAN 进行恢复。

SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

步骤

1. 从源数据库获取 pfile

SQL> create pfile='/orcl/app/oracle_rman/pfile.ora' from spfile;

2. 修正pfile

修改 pfile 文件的以下参数。

参数默认值含义orcl.__oracle_base$ORACLE_BASEoracle 软件的基目录*.audit_file_dest$ORACLE_BASE/admin/$SID/adump审计文件目录*.control_files'$ORACLE_BASE/oradata/$SID/control01.ctl','$DB_RECOVERY_FILE_DEST/$SID/control02.ctl'控制文件的存储位置*.db_name$SID数据库的标识ID*.db_recovery_file_dest$DB_RECOVERY_FILE_DEST快速恢复区的目录，默认为 $ORACLE_HOME/fast_recovery_area*.diagnostic_dest$ORACLE_BASE诊断日志的目录

3. 异地数据库-用 pfile 启动数据库到 nomount 状态

SQL> startup nomount pfile=/orcl/app/oracle_rman/pfile.ora

4. 异地数据库-指定备份集恢复控制文件到指定路径

控制文件的新路径需要与 pfile 文件指定的 *.control_files 相一致。

RMAN> restore controlfile to '/orcl/app/oracle_rman/oradata/orcl/control01.ctl' from '/orcl/app/oracle_rman/fast_recovery_area/ORCL/backupset/2019_05_23//o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp'; RMAN> restore controlfile to '/orcl/app/oracle_rman/fast_recovery_area/orcl/control02.ctl' from '/orcl/app/oracle_rman/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp';

5. 异地数据库-将备份集放入到新的快速恢复区

6. 异地数据库-将数据文件恢复到指定路径

7. 异地数据库-备份控制文件到 trace

SQL> alter database backup controlfile to trace as '/orcl/app/oracle_rman/control_new.trc';

根据异地数据库的配置修改新生成的控制文件脚本。

8. 异地数据库-创建新的控制文件

关闭数据库后，继续将数据库用 pfile 文件启动到 nomount 状态。

SQL> startup nomount pfile=/orcl/app/oracle_rman/pfile.ora

生成新的控制文件。

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/orcl/app/oracle_rman/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/orcl/app/oracle_rman/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/orcl/app/oracle_rman/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/orcl/app/oracle_rman/oradata/orcl/system01.dbf', '/orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf', '/orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf', '/orcl/app/oracle_rman/oradata/orcl/users01.dbf', '/orcl/app/oracle_rman/oradata/orcl/example01.dbf' CHARACTER SET WE8MSWIN1252 ;

9. 异地数据库-打开数据库

以 RESETLOGS 模式打开数据库。

SQL> ALTER DATABASE OPEN RESETLOGS;

为临时表空间添加数据文件。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

详情

1 从源数据库获取备份集

1.1 查询数据库版本

确保数据库版本为 11G。

1.2 启动到 mount 状态后全库冷备

确保数据库处于非归档状态；

SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Current log sequence 7

将数据库启动到 mount 状态后，使用 RMAN 指令创建源数据库的冷备。

RMAN> BACKUP DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;

查看备份集信息

RMAN> list backup;

数据库的控制文件与参数文件的备份集信息(示例).

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 8 Full 9.36M DISK 00:00:08 23-MAY-19 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20190523T220250 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_ncsnf_TAG20190523T220250_ggfb0lqb_.bkp SPFILE Included: Modification time: 23-MAY-19 SPFILE db_unique_name: ORCL Control File Included: Ckp SCN: 2777305 Ckp time: 23-MAY-19

数据库的数据文件备份集信息(示例).

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Full 1.39G DISK 00:07:33 23-MAY-19 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190523T220607 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp List of Datafiles in backup set 9 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/system01.dbf 2 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/sysaux01.dbf 3 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/undotbs01.dbf 4 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/users01.dbf 5 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/example01.dbf

并将备份集拷贝到目标机器。

2 从源数据库创建 pfile 文件

pfile/spfile 文件是数据库从 shutdown 启动到 nomount 状态时，依赖的初始化参数文件；该初始化参数文件指定了 oracle 数据库内存需求、控制文件路径信息、数据文件的块大小、快速恢复区路径等；其中，spfile 是存储在 oracle 数据库的二进制文件，pfile 是从 spfile 导出的可编辑文本文件。

创建 pfile 文件。

SQL> select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED SQL> create pfile='/orcl/app/oracle_rman/pfile.ora' from spfile; File created.

查看 pfile 文件

orcl.__db_cache_size=591396864 orcl.__java_pool_size=4194304 orcl.__large_pool_size=75497472 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=171966464 orcl.__sga_target=901775360 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=218103808 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=1073741824 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'

3 修改 pfile 文件

3.1 修改 pfile 文件参数

3.2 异地数据库-创建目标数据库的目录结构

将文件中的字符串 /u01/app/oracle替换成目标目录的路径，如/orcl/app/oracle_rman 进入 $ORACLE_BASE 目录后，创建子级目录文件

mkdir -p admin/orcl/adump mkdir -p oradata/orcl mkdir -p fast_recovery_area/orcl

4 异地数据库-使用 pfile 启动数据库到 nomout 状态

使用修改后的 pfile 参数文件，将数据库启动到 nomout 状态

注：此操作需要在空闲的数据库实例中执行。

5 异地数据库-使用 RMAN 恢复控制文件到 pfile 指定的路径

数据库打开到 nomount 状态后，通过 RMAN 从指定数据集中恢复控制文件到指定路径。注：恢复的控制文件路径需与 pfile 参数文件指定的 *.control_files 参数保持一致。

[oracle@oratest admin]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 24 16:19:12 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore controlfile to '/orcl/app/oracle_rman/oradata/orcl/control01.ctl' from '/orcl/app/oracle_rman/noarchivelog/o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp'; Starting restore at 24-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=191 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 24-MAY-19 RMAN> restore controlfile to '/orcl/app/oracle_rman/fast_recovery_area/orcl/control02.ctl' from '/orcl/app/oracle_rman/noarchivelog/o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp'; Starting restore at 24-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 24-MAY-19

6 异地数据库-启动数据库到 mount 状态

拥有控制文件后，即可将数据库启动到 mount 状态。

RMAN> alter database mount; database mounted released channel: ORA_DISK_1

7 异地数据库-使用 RMAN 恢复数据文件

7.1 使用 RMAN 查看备份集信息

因备份集是通过 NOCATALOG 方式进行备份，此时通过指令 list backup; 可查看控制文件的备份集信息；将已有的备份集放置到查询出的备份集目录下。

RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Full 1.39G DISK 00:07:33 23-MAY-19 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190523T220607 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp List of Datafiles in backup set 9 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/system01.dbf 2 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/sysaux01.dbf 3 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/undotbs01.dbf 4 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/users01.dbf 5 Full 2779793 23-MAY-19 /u01/app/oracle/oradata/orcl/example01.dbf

7.2 异地数据库-将包含数据文件的备份集放到指定路径

由备份集信息可知，需要将相应的备份集放入 $ORACLE_BASE 的相对路径目录 “fast_recovery_area/ORCL/backupset/2019_05_23/”.

[oracle@localhost oracle_rman]$ mkdir -p fast_recovery_area/ORCL/backupset/2019_05_23/

将备份集移动到新的目录下

[oracle@localhost oracle_rman]$ mv noarchivelog/o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp fast_recovery_area/ORCL/backupset/2019_05_23/ [oracle@localhost oracle_rman]$ mv noarchivelog/o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp fast_recovery_area/ORCL/backupset/2019_05_23/ [oracle@localhost oracle_rman]$ ls -l fast_recovery_area/ORCL/backupset/2019_05_23 total 1470212 -rw-r--r--. 1 oracle oinstall 9830400 May 23 10:13 o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp -rw-r--r--. 1 oracle oinstall 1495662592 May 23 10:13 o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp

下一步骤进行RMAN恢复数据文件时，RMAN将会自动检索快速恢复区下的备份集信息，即可识别该备份集。

7.3 异地数据库-使用 RMAN 执行数据文件恢复

将数据文件恢复到新的目录

RMAN> run { set newname for datafile 1 to '/orcl/app/oracle_rman/oradata/orcl/system01.dbf'; set newname for datafile 2 to '/orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf'; set newname for datafile 3 to '/orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf'; set newname for datafile 4 to '/orcl/app/oracle_rman/oradata/orcl/users01.dbf'; set newname for datafile 5 to '/orcl/app/oracle_rman/oradata/orcl/example01.dbf'; restore database; switch datafile all; recover database; } executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 24-MAY-19 Starting implicit crosscheck backup at 24-MAY-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=191 device type=DISK Crosschecked 8 objects Finished implicit crosscheck backup at 24-MAY-19 Starting implicit crosscheck copy at 24-MAY-19 using channel ORA_DISK_1 Finished implicit crosscheck copy at 24-MAY-19 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /orcl/app/oracle_rman/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp File Name: /orcl/app/oracle_rman/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_ncsnf_TAG20190523T220607_ggfbnr4m_.bkp using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /orcl/app/oracle_rman/oradata/orcl/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /orcl/app/oracle_rman/oradata/orcl/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /orcl/app/oracle_rman/oradata/orcl/example01.dbf channel ORA_DISK_1: reading from backup piece /orcl/app/oracle_rman/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp channel ORA_DISK_1: piece handle=/orcl/app/oracle_rman/fast_recovery_area/ORCL/backupset/2019_05_23/o1_mf_nnndf_TAG20190523T220607_ggfb6j45_.bkp tag=TAG20190523T220607 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 24-MAY-19 datafile 1 switched to datafile copy input datafile copy RECID=7 STAMP=1009083267 file name=/orcl/app/oracle_rman/oradata/orcl/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=8 STAMP=1009083267 file name=/orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=9 STAMP=1009083267 file name=/orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=10 STAMP=1009083267 file name=/orcl/app/oracle_rman/oradata/orcl/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=11 STAMP=1009083267 file name=/orcl/app/oracle_rman/oradata/orcl/example01.dbf Starting recover at 24-MAY-19 using channel ORA_DISK_1 starting media recovery RMAN-08187: warning: media recovery until SCN 2779793 complete Finished recover at 24-MAY-19

7.4 异地数据库-检查数据文件信息

此时，数据文件变更到新的目录。

SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> select file#,name from v$datafile; FILE# NAME ---------- --------------------------------------------- 1 /orcl/app/oracle_rman/oradata/orcl/system01.dbf 2 /orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf 3 /orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf 4 /orcl/app/oracle_rman/oradata/orcl/users01.dbf 5 /orcl/app/oracle_rman/oradata/orcl/example01.dbf

7.5 异地数据库-检查联机重做日志文件与临时表空间文件信息

但是，联机重做日志与临时表空间仍然采用源数据库的路径。

SQL> select MEMBER from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo01.log SQL> select name from v$tempfile; NAME ------------------------------------------------------------ /u01/app/oracle/oradata/orcl/temp01.dbf

查询数据库的相关信息

SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> select dbid from v$database; DBID ---------- 1533552104 SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> select name from v$database; NAME --------------------------- ORCL SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /orcl/app/oracle_rman/oradata/orcl/control01.ctl /orcl/app/oracle_rman/fast_recovery_area/orcl/control02.ctl

因 RMAN 恢复时重新指定了数据问价的路径，所以此时，数据文件采用目标数据库的路径。

SQL> col name for a45 SQL> select file#,name from v$datafile; FILE# NAME ---------- --------------------------------------------- 1 /orcl/app/oracle_rman/oradata/orcl/system01.dbf 2 /orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf 3 /orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf 4 /orcl/app/oracle_rman/oradata/orcl/users01.dbf 5 /orcl/app/oracle_rman/oradata/orcl/example01.dbf

此时，联机重做日志的数据文件仍采用源数据库的路径。

SQL> col status for a15 SQL> col type for a15 SQL> col member for a60 SQL> select group#,member from v$logfile; GROUP# MEMBER ---------- ------------------------------------------------------------ 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log SQL> select group#,bytes/1024/1024 as MB,blocksize,members from v$log; GROUP# MB BLOCKSIZE MEMBERS ---------- ---------- ---------- ---------- 1 50 512 1 3 50 512 1 2 50 512 1

此时，临时表空间的数据文件仍采用源数据库的路径。

SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/temp01.dbf

查询结果中NLS_CHARACTERSET表示字符集，在创建后原则上不能更改。

SQL> col parameter for a30 SQL> col value for a30 SQL> select * from v$nls_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET WE8MSWIN1252 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ------------------------------ NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 19 rows selected.

8 异地数据库-创建新数据库的控制文件

因数据库的文件组织结构发生变更，源数据库的控制文件已经无法管理新数据库的数据文件等，此时需要根据新数据库的文件组织结构，创建新数据库的控制文件。

8.1 创建控制文件的副本

当数据库处于 mount 状态时，可备份控制文件到 trace。

SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database backup controlfile to trace as '/orcl/app/oracle_rman/control_new.trc'; Database altered.

查看控制文件的脚本。

[oracle@oratest rman-restore]$ cat control_new.trc -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. -- -- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' -- -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf -- -- DB_UNIQUE_NAME="orcl" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=4 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY' -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_1=ENABLE -- -- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- need to re-create the control file. -- -- Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- WARNING! The current control file needs to be checked against -- the datafiles to insure it contains the correct files. The -- commands printed here may be missing log and/or data files. -- Another report should be made after the database has been -- successfully opened. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/orcl/app/oracle_rman/oradata/orcl/system01.dbf', '/orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf', '/orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf', '/orcl/app/oracle_rman/oradata/orcl/users01.dbf', '/orcl/app/oracle_rman/oradata/orcl/example01.dbf' CHARACTER SET WE8MSWIN1252 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/orcl/app/oracle_rman/fast_recovery_area/ORCL/archivelog/2019_05_24/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/orcl/app/oracle_rman/fast_recovery_area/ORCL/archivelog/2019_05_24/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' REUSE; -- End of tempfile additions. -- -- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- WARNING! The current control file needs to be checked against -- the datafiles to insure it contains the correct files. The -- commands printed here may be missing log and/or data files. -- Another report should be made after the database has been -- successfully opened. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/orcl/app/oracle_rman/oradata/orcl/system01.dbf', '/orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf', '/orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf', '/orcl/app/oracle_rman/oradata/orcl/users01.dbf', '/orcl/app/oracle_rman/oradata/orcl/example01.dbf' CHARACTER SET WE8MSWIN1252 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/orcl/app/oracle_rman/fast_recovery_area/ORCL/archivelog/2019_05_24/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/orcl/app/oracle_rman/fast_recovery_area/ORCL/archivelog/2019_05_24/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' REUSE; -- End of tempfile additions. --

8.2 创建新的控制文件

截取 trace 文件脚本如下

STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf', '/u01/app/oracle/oradata/orcl/example01.dbf' CHARACTER SET WE8MSWIN1252 ; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

根据目标机器的目录结构修改trace文件，创建新的控制文件。

SQL> startup nomount pfile=/orcl/app/oracle_rman/pfile.ora ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 469762952 bytes Database Buffers 591396864 bytes Redo Buffers 5517312 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/orcl/app/oracle_rman/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/orcl/app/oracle_rman/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/orcl/app/oracle_rman/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/orcl/app/oracle_rman/oradata/orcl/system01.dbf', '/orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf', '/orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf', '/orcl/app/oracle_rman/oradata/orcl/users01.dbf', '/orcl/app/oracle_rman/oradata/orcl/example01.dbf' CHARACTER SET WE8MSWIN1252 ; Control file created.

创建新的控制文件后，数据库将自动从 noumout 状态切换到 mount 状态；检查新控制文件管理的文件组织结构。

SQL> select name from v$controlfile; NAME ------------------------------------------------------------ /orcl/app/oracle_rman/oradata/orcl/control01.ctl /orcl/app/oracle_rman/fast_recovery_area/orcl/control02.ctl SQL> select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED SQL> alter database open resetlogs; Database altered. SQL> select MEMBER from v$logfile; MEMBER -------------------------------------------------------------------------------- /orcl/app/oracle_rman/oradata/orcl/redo03.log /orcl/app/oracle_rman/oradata/orcl/redo02.log /orcl/app/oracle_rman/oradata/orcl/redo01.log SQL> select file#,name from v$datafile; FILE# NAME ---------- ------------------------------------------------------------ 1 /orcl/app/oracle_rman/oradata/orcl/system01.dbf 2 /orcl/app/oracle_rman/oradata/orcl/sysaux01.dbf 3 /orcl/app/oracle_rman/oradata/orcl/undotbs01.dbf 4 /orcl/app/oracle_rman/oradata/orcl/users01.dbf 5 /orcl/app/oracle_rman/oradata/orcl/example01.dbf

9 异地数据库-启动数据库

使用 RESETLOGS 模式启动数据库

SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.

为临时表空间添加数据文件。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle_rman/oradata/orcl/temp01.dbf' size 1G reuse autoextend on next 10M maxsize 2g; Tablespace altered. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle_rman/oradata/orcl/temp01.dbf

附录

ERROR：源数据库11G异地恢复到12C

异地恢复的数据库版本如果不一致，将无法在异地以 open read only 或 open 模式打开数据库。

源数据库版本 11.2.0.4.0

异地数据库版本 12.2.0.1.0

SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production CORE 12.2.0.1.0 Production TNS for Linux: Version 12.2.0.1.0 - Production NLSRTL Version 12.2.0.1.0 - Production

打开数据库将报以下错误。

SQL> alter database open read only; alter database open read only * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-00904: "ACDRROWTSINTCOL#": invalid identifier Process ID: 10347 Session ID: 99 Serial number: 24043

引用

通过RMAN备份恢复数据库到其他服务器 rman异地不同路径下恢复简单手记

最新回复(0)