一.准备工作 因为要完全恢复，所以在拿到除数据库的备份片外，还要拿备份后时间跑出的归档文件，当然，还要拿到最后一个curren日志组的归档。
当然如果做不完全恢复的话，不需要current日志组的归档也是可以的。 注：因为我的本次实验是在rac下，已经有数据库后，删除后模拟做的迁移，所以有些小细节可能不同，不过原理都是一样的。 1.环境：数据库 11.2.0.4 Linux：centos 6.5 2.RAC配置 -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA.dg ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.FRA.dg ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.LISTENER.lsnr ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.asm ONLINE ONLINE hua1 Started ONLINE ONLINE hua2 Started ora.gsd OFFLINE OFFLINE hua1 OFFLINE OFFLINE hua2 ora.net1.network ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.ons ONLINE ONLINE hua1 ONLINE ONLINE hua2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE hua1 ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE hua2 ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE hua2 ora.cvu 1 ONLINE ONLINE hua2 ora.hua1.vip 1 ONLINE ONLINE hua1 ora.hua2.vip 1 ONLINE ONLINE hua2 ora.oc4j 1 ONLINE ONLINE hua2 ora.scan1.vip 1 ONLINE ONLINE hua1 ora.scan2.vip 1 ONLINE ONLINE hua2 ora.scan3.vip 1 ONLINE ONLINE hua2 二.目标端数据恢复 2.1 在节点1上创建所需目录 [oracle@hua1 ~]$ mkdir -p /u01/app/oracle/admin/PROD1/adump [oracle@hua1 ~]$ mkdir -p /u01/app/oracle/archivelog [oracle@hua1 ~]$ mkdir -p /u01/app/oracle/oradata/PROD1 [oracle@hua1 ~]$ mkdir -p /u01/app/oracle/backup 2.2 拷贝源数据库的备份片、归档文件和最后一个redo.log 2.3 新建参数文件 vim initPROD1.ora PROD2.__db_cache_size=272629760 PROD1.__db_cache_size=310378496 PROD2.__java_pool_size=4194304 PROD1.__java_pool_size=4194304 PROD2.__large_pool_size=8388608 PROD1.__large_pool_size=71303168 PROD1.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment PROD2.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment PROD2.__pga_aggregate_target=335544320 PROD1.__pga_aggregate_target=310378496 PROD2.__sga_target=503316480 PROD1.__sga_target=528482304 PROD2.__shared_io_pool_size=0 PROD1.__shared_io_pool_size=0 PROD2.__shared_pool_size=209715200 PROD1.__shared_pool_size=134217728 PROD2.__streams_pool_size=0 PROD1.__streams_pool_size=0 *.audit_file_dest=‘/u01/app/oracle/admin/PROD1/adump‘ *.audit_trail=‘db‘ *.cluster_database=false *.compatible=‘11.2.0.4.0‘ *.control_files=‘+DATA/prod1/controlfile/current.258.991842525‘#Restore Controlfile *.db_block_size=8192 *.db_create_file_dest=‘+DATA‘ *.db_domain=‘‘ *.db_name=‘PROD1‘ *.db_recovery_file_dest=‘+FRA‘ *.db_recovery_file_dest_size=4621074432 *.diagnostic_dest=‘/u01/app/oracle‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PRODXDB)‘ PROD2.instance_number=2 PROD1.instance_number=1 *.log_archive_format=‘%t_%s_%r.dbf‘ *.memory_target=838860800 *.open_cursors=300 注：等数据库恢复完毕后，再把luster_database设置为true。 2.4 用pfile文件创建spfile文件 2.4.1启动数据库 [oracle@hua1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 10 19:32:27 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile=‘/home/oracle/initPROD1.ora‘ ; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 520096848 bytes Database Buffers 310378496 bytes Redo Buffers 2371584 bytes SQL> create spfile=‘+DATA‘ from pfile=‘/home/oracle/initPROD1.ora‘ 2 ; File created. SQL> shutdown abort; ORACLE instance shut down. 2.5 将所有节点上的pfile中 写上路径指到spfile 2.5.1 查看spfile在ASM磁盘组中的位置 [root@hua1 ~]# su - grid [grid@hua1 ~]$ asmcmd ASMCMD> ls DATA/ FRA/ ASMCMD> cd data ASMCMD> ls PROD1/ hua-cluster/ ASMCMD> cd PROD1 ASMCMD> ls PARAMETERFILE/ ASMCMD> cd PAREMETERFILE ASMCMD-8002: entry ‘PAREMETERFILE‘ does not exist in directory ‘+data/PROD1/‘ ASMCMD> cd PARAMETERFILE ASMCMD> ls spfile.258.991856479 ASMCMD> pwd +data/PROD1/PARAMETERFILE 2.5.2 把pfile中路径指向spfile [oracle@hua1 dbs]$ vim initPROD1.ora [oracle@hua1 dbs]$ cat initPROD1.ora spfile=‘+data/PROD1/PARAMETERFILE/spfile.258.991856479‘ [oracle@hua2 dbs]$ vim initPROD2.ora [oracle@hua2 dbs]$ cat initPROD2.ora spfile=‘+data/PROD1/PARAMETERFILE/spfile.258.991856479‘ 2.6 创建口令文件 [oracle@hua1 dbs]$ orapwd file=orapwPROD1 password=oracle [oracle@hua1 dbs]$ ls hc_PROD1.dat init.ora initPROD1.ora orapwPROD1 [oracle@hua2 dbs]$ orapwd file=orapwPROD1 password=oracle [oracle@hua2 dbs]$ ls hc_PROD2.dat init.ora initPROD1.ora orapwPROD1 2.7 还原控制文件 2.7.1 修改控制文件位置 SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 520096848 bytes Database Buffers 310378496 bytes Redo Buffers 2371584 bytes SQL> show parameter control_file NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ control_file_record_keep_time integer 7 control_files string +DATA/prod1/controlfile/curren t.258.991842525 SQL> alter system set control_files=‘+DATA‘ scope=spfile sid=‘*‘; System altered. SQL> startup force nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 520096848 bytes Database Buffers 310378496 bytes Redo Buffers 2371584 bytes 2.7.2 恢复控制文件 [oracle@hua1 dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Nov 10 20:09:43 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD1 (not mounted) RMAN> restore controlfile from ‘/home/oracle/back/o1_mf_s_991757598_fybjdz2g_.bkp‘; Starting restore at 2018-11-10 20:11:56 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=31 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=+DATA/prod1/controlfile/current.267.991858317 Finished restore at 2018-11-10 20:11:58 2.8 恢复数据文件 2.8.1 反注册备份信息 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> list backup; specification does not match any backup in the repository RMAN> catalog start with ‘/home/oracle/back‘; searching for all files that match the pattern /home/oracle/back List of Files Unknown to the Database ===================================== File Name: /home/oracle/back/o1_mf_1_167_fybht5sz_.arc File Name: /home/oracle/back/o1_mf_1_164_fyb47n6c_.arc File Name: /home/oracle/back/o1_mf_1_160_fy9x5kcv_.arc File Name: /home/oracle/back/redo03.log File Name: /home/oracle/back/o1_mf_1_166_fybht3v4_.arc File Name: /home/oracle/back/o1_mf_s_991757598_fybjdz2g_.bkp File Name: /home/oracle/back/o1_mf_1_171_fybjnhdq_.arc File Name: /home/oracle/back/o1_mf_1_170_fybjnf1z_.arc File Name: /home/oracle/back/o1_mf_1_168_fybjn7gb_.arc File Name: /home/oracle/back/o1_mf_nnndf_TAG20181109T160911_fybj589n_.bkp File Name: /home/oracle/back/o1_mf_1_163_fyb46lho_.arc File Name: /home/oracle/back/o1_mf_1_165_fyb8ncdt_.arc File Name: /home/oracle/back/o1_mf_1_162_fy9xhp96_.arc File Name: /home/oracle/back/o1_mf_1_158_fy9x5ddn_.arc File Name: /home/oracle/back/o1_mf_1_161_fy9x5lrh_.arc File Name: /home/oracle/back/o1_mf_1_159_fy9x5got_.arc File Name: /home/oracle/back/o1_mf_1_169_fybjn8mr_.arc Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/back/o1_mf_1_167_fybht5sz_.arc File Name: /home/oracle/back/o1_mf_1_164_fyb47n6c_.arc File Name: /home/oracle/back/o1_mf_1_160_fy9x5kcv_.arc File Name: /home/oracle/back/o1_mf_1_166_fybht3v4_.arc File Name: /home/oracle/back/o1_mf_s_991757598_fybjdz2g_.bkp File Name: /home/oracle/back/o1_mf_1_171_fybjnhdq_.arc File Name: /home/oracle/back/o1_mf_1_170_fybjnf1z_.arc File Name: /home/oracle/back/o1_mf_1_168_fybjn7gb_.arc File Name: /home/oracle/back/o1_mf_nnndf_TAG20181109T160911_fybj589n_.bkp File Name: /home/oracle/back/o1_mf_1_163_fyb46lho_.arc File Name: /home/oracle/back/o1_mf_1_165_fyb8ncdt_.arc File Name: /home/oracle/back/o1_mf_1_162_fy9xhp96_.arc File Name: /home/oracle/back/o1_mf_1_158_fy9x5ddn_.arc File Name: /home/oracle/back/o1_mf_1_161_fy9x5lrh_.arc File Name: /home/oracle/back/o1_mf_1_159_fy9x5got_.arc File Name: /home/oracle/back/o1_mf_1_169_fybjn8mr_.arc List of Files Which Where Not Cataloged ======================================= File Name: /home/oracle/back/redo03.log RMAN-07529: Reason: catalog is not supported for this file type 2.8.1 修改日志组位置 SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD1/redo03.log /u01/app/oracle/oradata/PROD1/redo02.log /u01/app/oracle/oradata/PROD1/redo01.log SQL> alter database rename file ‘/u01/app/oracle/oradata/PROD1/redo03.log‘ to ‘+DATA‘; Database altered. SQL> alter database rename file ‘/u01/app/oracle/oradata/PROD1/redo02.log‘ to ‘+DATA‘; Database altered. SQL> alter database rename file ‘/u01/app/oracle/oradata/PROD1/redo01.log‘ to ‘+DATA‘; Database altered. 2.8.2 还原数据文件 RMAN> run{ 2> set newname for database to ‘+DATA‘; 3> restore database; 4> switch datafile all; 5> } executing command: SET NEWNAME Starting restore at 2018-11-10 20:36:39 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 +DATA channel ORA_DISK_1: restoring datafile 00002 to +DATA channel ORA_DISK_1: restoring datafile 00003 to +DATA channel ORA_DISK_1: restoring datafile 00004 to +DATA channel ORA_DISK_1: restoring datafile 00005 to +DATA channel ORA_DISK_1: restoring datafile 00006 to +DATA channel ORA_DISK_1: reading from backup piece /home/oracle/back/o1_mf_nnndf_TAG20181109T160911_fybj589n_.bkp channel ORA_DISK_1: piece handle=/home/oracle/back/o1_mf_nnndf_TAG20181109T160911_fybj589n_.bkp tag=TAG20181109T160911 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:02:55 Finished restore at 2018-11-10 20:39:37 datafile 1 switched to datafile copy input datafile copy RECID=11 STAMP=991859979 file name=+DATA/prod1/datafile/system.260.991859801 datafile 2 switched to datafile copy input datafile copy RECID=12 STAMP=991859979 file name=+DATA/prod1/datafile/sysaux.272.991859803 datafile 3 switched to datafile copy input datafile copy RECID=13 STAMP=991859980 file name=+DATA/prod1/datafile/undotbs1.271.991859803 datafile 4 switched to datafile copy input datafile copy RECID=14 STAMP=991859981 file name=+DATA/prod1/datafile/users.268.991859803 datafile 5 switched to datafile copy input datafile copy RECID=15 STAMP=991859982 file name=+DATA/prod1/datafile/example.270.991859803 datafile 6 switched to datafile copy input datafile copy RECID=16 STAMP=991859983 file name=+DATA/prod1/datafile/vastdata.269.991859803 三.开机 最激动人心的时刻到了，参数文件、控制文件、数据文件我们都已经恢复了，所以，我们要跑归档了 RMAN> recover database; Starting recover at 2018-11-10 20:43:45 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 168 is already on disk as file /home/oracle/back/o1_mf_1_168_fybjn7gb_.arc archived log for thread 1 with sequence 169 is already on disk as file /home/oracle/back/o1_mf_1_169_fybjn8mr_.arc archived log for thread 1 with sequence 170 is already on disk as file /home/oracle/back/o1_mf_1_170_fybjnf1z_.arc archived log for thread 1 with sequence 171 is already on disk as file /home/oracle/back/o1_mf_1_171_fybjnhdq_.arc archived log file name=/home/oracle/back/o1_mf_1_168_fybjn7gb_.arc thread=1 sequence=168 archived log file name=/home/oracle/back/o1_mf_1_169_fybjn8mr_.arc thread=1 sequence=169 archived log file name=/home/oracle/back/o1_mf_1_170_fybjnf1z_.arc thread=1 sequence=170 archived log file name=/home/oracle/back/o1_mf_1_171_fybjnhdq_.arc thread=1 sequence=171 unable to find archived log archived log thread=1 sequence=172 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/10/2018 20:43:54 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 172 and starting SCN of 2648979 这个时候呢，如果做不完全恢复，或者没有拿到current日志组的归档文件，用这个scn值，做不完全恢复 用命令 recover database using backup until scn 值; alter database open resetlogs; 是可以开启数据库的 不过，要做完全恢复 SQL> recover database using backup controlfile until cancel; ORA-00279: change 2648979 generated at 11/09/2018 16:17:19 needed for thread 1 ORA-00289: suggestion : +FRA ORA-15173: entry ‘ARCHIVELOG‘ does not exist in directory ‘PROD1‘ ORA-00280: change 2648979 for thread 1 is in sequence #172 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /home/oracle/back/redo03.log Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered. 四.配置数据库参数 4.1 查看数据情况 SQL> col value for a10 SQL> select * from v$option where parameter=‘Real Application Clusters‘; PARAMETER VALUE ----------------------------------------------- ---------- Real Application Clusters TRUE SQL> show parameter cluster NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 1 SQL> show parameter instance_number NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_number integer 1 SQL> alter system set cluster_database=true scope=spfile sid=‘*‘; System altered. SQL> alter system set cluster_database_instances=2 scope=spfile sid=‘*‘; System altered. SQL> alter system set instance_number=1 scope=spfile sid=‘hua1‘; System altered. SQL> alter system set instance_number=2 scope=spfile sid=‘hua2‘; System altered. SQL> alter system set thread=1 scope=spfile sid=‘hua1‘; System altered. SQL> alter system set thread=2 scope=spfile sid=‘hua2‘; System altered. SQL> show parameter undo_tablespace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 SQL> create undo tablespace UNDOTBS2 datafile ‘+DATA/wxqyh/datafile/undotbs02.dbf‘ size 100M; Tablespace created. SQL> alter system set undo_tablespace=‘UNDOTBS2‘ scope=spfile sid=‘hua2‘; System altered. SQL> alter database add logfile thread 2 group 4 (‘+DATA‘,‘+FRA‘) size 50M; Database altered. SQL> alter database add logfile thread 2 group 5 (‘+DATA‘,‘+FRA‘) size 50M; Database altered. SQL> alter database add logfile thread 2 group 6 (‘+DATA‘,‘+FRA‘) size 50M; Database altered. 注：根据实际情况分配日志文件大小，可以增加日志组文件 alter database add logfile member ‘+FRA‘ to group 4; SQL> alter database enable thread 2; Database altered SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 4.2 将信息注册到CRS里 [oracle@hua1 ~]$ srvctl add database -d PROD1 -o $ORACLE_HOME -p +data/PROD1/PARAMETERFILE/spfile.265.992007825
[oracle@hua1 ~]$ srvctl add instance -d PROD1 -i PROD1 -n hua1 [oracle@hua1 ~]$ srvctl add instance -d PROD1 -i PROD2 -n hua2 [oracle@hua1 ~]$ srvctl start database -d PROD1 -o open 把SCAN添加到GI资源中，数据库中添加REMOTE_LISTENER 4.3 确定实例情况 [oracle@hua1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 12 14:44:34 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select instance_number,instance_name,host_name from gv$instance; INSTANCE_NUMBER INSTANCE_NAME HOST_NAME --------------- -------------------- ---------- 1 PROD1 hua1.us.or acle.com 2 PROD2 hua2 4.4 执行catclust.sql脚本创建相关视图
If you did not create your Oracle RAC database with the Database Configuration Assistant (DBCA), then you must run the CATCLUST.SQL script to create views and tables related to Oracle RAC. You must have SYSDBA privileges to run this script.
SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql 4.5 重建temp表空间 SQL> alter tablespace temp add tempfile ‘+DATA‘ size 100M; Tablespace altered. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD1/temp01.dbf +DATA/prod1/tempfile/temp.263.992011789 重启数据库删除原表空间 [oracle@hua1 db_1]$ srvctl stop database -d PROD1 -o immediate [oracle@hua1 db_1]$ srvctl start database -d PROD1 -o open SQL> alter database tempfile ‘/u01/app/oracle/oradata/PROD1/temp01.dbf‘ drop including datafiles; Database altered. 五.验证数据库及集群情况 5.1查看数据库的状态 [oracle@hua1 dbs]$ srvctl status database -d PROD1 Instance PROD1 is running on node hua1 Instance PROD2 is running on node hua2 5.2查看数据的配置情况 [oracle@hua1 dbs]$ srvctl config database -d PROD1 Database unique name: PROD1 Database name: Oracle home: /u01/app/oracle/product/11.2.0/db_1 Oracle user: oracle Spfile: +data/PROD1/PARAMETERFILE/spfile.265.992007825 Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: PROD1 Database instances: PROD1,PROD2 Disk Groups: DATA,FRA Mount point paths: Services: Type: RAC Database is administrator managed 5.3查看集群配置 [root@hua1 ~]# crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA.dg ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.FRA.dg ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.LISTENER.lsnr ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.asm ONLINE ONLINE hua1 Started ONLINE ONLINE hua2 Started ora.gsd OFFLINE OFFLINE hua1 OFFLINE OFFLINE hua2 ora.net1.network ONLINE ONLINE hua1 ONLINE ONLINE hua2 ora.ons ONLINE ONLINE hua1 ONLINE ONLINE hua2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE hua1 ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE hua2 ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE hua2 ora.cvu 1 ONLINE ONLINE hua2 ora.hua1.vip 1 ONLINE ONLINE hua1 ora.hua2.vip 1 ONLINE ONLINE hua2 ora.oc4j 1 ONLINE ONLINE hua2 ora.prod1.db 1 ONLINE ONLINE hua1 Open 2 ONLINE ONLINE hua2 Open ora.scan1.vip 1 ONLINE ONLINE hua1 ora.scan2.vip 1 ONLINE ONLINE hua2 ora.scan3.vip 1 ONLINE ONLINE hua2 [root@hua1 ~]# olsnodes -s hua1 Active hua2 Active --------------------- 作者：程序员丶清欢渡 来源：CSDN 原文：https://blog.csdn.net/cxy_sakura/article/details/83930523 版权声明：本文为博主原创文章，转载请附上博文链接！
原文：https://www.cnblogs.com/chendian0/p/10961857.html