将10g RAC库用rman 的方式备份并恢复到异地单机

最新推荐文章于 2022-11-07 10:28:02 发布

cka6619

最新推荐文章于 2022-11-07 10:28:02 发布

阅读量142

点赞数

文章标签：数据库

将10g RAC库用rman 的方式备份并恢复到异地单机

由于太长了,完整的在:http://www.itpub.net/thread-1080350-1-11.html

主RAC库：2个节点 rac1(192.168.218.168),rac2(192.168.218.168) 采用ASM+RAW
备机 standby(192.168.218.188) 文件系统

一。在rac库备份：
rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2
ora.racdb.db   application    ONLINE    ONLINE    rac1
ora....b1.inst application    ONLINE    ONLINE    rac1
ora....b2.inst application    ONLINE    ONLINE    rac2
rac1->
rac1-> export ORACLE_SID=racdb1
rac1-> rman target / nocatalog

RMAN> run{
2> allocate CHANNEL ch00 type DISK CONNECT 'sys/oracle@racdb1';
3> allocate CHANNEL ch01 type DISK CONNECT 'sys/oracle@racdb2';
4> backup database    format '/rmanset/racdb_full_%d%t%s%p'         tag 'fullbackup';
5> BACKUP FORMAT '/rmanset/%d_arch_%s_%p_%h' ARCHIVELOG ALL;
6> backup format '/rmanset/cf_%d_%s_%p'
7>          (current controlfile);
8>     RELEASE CHANNEL ch00;
9>     RELEASE CHANNEL ch01;
10>   }

allocated channel: ch00
channel ch00: sid=148 instance=racdb1 devtype=DISK

allocated channel: ch01
channel ch01: sid=148 instance=racdb2 devtype=DISK

Starting backup at 02-NOV-08
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
input datafile fno=00001 name=+RACDATA/racdb/datafile/system.259.669487425
input datafile fno=00005 name=+RACDATA/racdb/datafile/users.264.669487561
input datafile fno=00004 name=+RACDATA/racdb/datafile/undotbs2.263.669487539
channel ch00: starting piece 1 at 02-NOV-08
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
input datafile fno=00003 name=+RACDATA/racdb/datafile/sysaux.261.669487497
input datafile fno=00002 name=+RACDATA/racdb/datafile/undotbs1.260.669487479
channel ch01: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769770931 tag=FULLBACKUP comment=NONE
channel ch00: backup set complete, elapsed time: 00:02:19
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
channel ch00: starting piece 1 at 02-NOV-08
channel ch01: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769784941 tag=FULLBACKUP comment=NONE
channel ch01: backup set complete, elapsed time: 00:02:28
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ch01: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769914951 tag=FULLBACKUP comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:11
channel ch01: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769933961 tag=FULLBACKUP comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:05
Finished backup at 02-NOV-08

Starting backup at 02-NOV-08
current log archived
channel ch00: starting archive log backupset
channel ch00: specifying archive log(s) in backup set
input archive log thread=1 sequence=42 recid=141 stamp=669603048
input archive log thread=1 sequence=44 recid=149 stamp=669640424
input archive log thread=1 sequence=45 recid=151 stamp=669675778
input archive log thread=1 sequence=46 recid=152 stamp=669679426
input archive log thread=1 sequence=47 recid=156 stamp=669690456
channel ch00: starting piece 1 at 02-NOV-08
channel ch01: starting archive log backupset
channel ch01: specifying archive log(s) in backup set
input archive log thread=1 sequence=53 recid=170 stamp=669767346
input archive log thread=1 sequence=54 recid=172 stamp=669769357
input archive log thread=2 sequence=44 recid=165 stamp=669764276
input archive log thread=2 sequence=45 recid=167 stamp=669765472
input archive log thread=2 sequence=46 recid=168 stamp=669765474
input archive log thread=2 sequence=47 recid=169 stamp=669767345
input archive log thread=2 sequence=48 recid=171 stamp=669769352
input archive log thread=2 sequence=49 recid=175 stamp=669769944
channel ch01: starting piece 1 at 02-NOV-08
channel ch01: finished piece 1 at 02-NOV-08
piece handle=/rmanset/RACDB_arch_98_1_2 tag=TAG20081102T231211 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:04
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/RACDB_arch_97_1_1 tag=TAG20081102T231211 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:11
channel ch00: starting archive log backupset
channel ch00: specifying archive log(s) in backup set
input archive log thread=1 sequence=48 recid=158 stamp=669690550
input archive log thread=1 sequence=49 recid=160 stamp=669691146
input archive log thread=1 sequence=50 recid=162 stamp=669755973
input archive log thread=1 sequence=51 recid=163 stamp=669757116
input archive log thread=1 sequence=52 recid=166 stamp=669764278
input archive log thread=1 sequence=55 recid=173 stamp=669769375
input archive log thread=1 sequence=56 recid=174 stamp=669769928
channel ch00: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/RACDB_arch_99_1_1 tag=TAG20081102T231211 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:08
Finished backup at 02-NOV-08

Starting backup at 02-NOV-08
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
channel ch00: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/cf_RACDB_100_1 tag=TAG20081102T231235 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:07
Finished backup at 02-NOV-08

released channel: ch00

released channel: ch01

2. 在rac库上创建pfile

SQL> create pfile='/rmanset/initracdb.ora' from spfile;

将rac1,rac2上的备份出来的rman文件和pfile ftp 至 standby 主机

二： standby 主机做恢复的过程：
0.修改ftp过来的pfile，主要是rac中一些参数去掉：

#*.cluster_database_instances=2
*.cluster_database=false
#racdb2.instance_number=2
#racdb1.instance_number=1
#racdb2.thread=2
#racdb1.thread=1
#racdb2.undo_tablespace='UNDOTBS2'

#*.db_file_name_convert='+RACDATA/racdb/datafile/','/oradata/racdb/'
#*.fal_client='RACDB'
#*.fal_server='STANDBY'
#*.log_archive_config='DG_CONFIG=(racdb,standby)'
#racdb2.log_archive_dest_1='LOCATION=/racdb2_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'
#*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
#*.log_archive_dest_state_2='ENABLE'
#*.log_file_name_convert='+RACDATA/racdb/onlinelog/','/oradata/racdb/'
#*.remote_listener='LISTENERS_RACDB'
#*.standby_file_management='AUTO'

1.数据库启动到nomount

2.restore controlfile

3.restore archivelog

4.recover database

5.改变logfile 的位置，重新建redo

6.drop 一个undo tablespace.

7.add a temp tablespace

8. 配tnsnames.ora 和listener.ora

下为操作的过程：

standby-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Nov 2 14:22:39 2008

connected to target database: racdb (not mounted)
--恢复controlfile
RMAN> restore controlfile from '/rmanset/cf_RACDB_100_1';

Starting restore at 02-NOV-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=/oradata/racdb/control01.ctl
Finished restore at 02-NOV-08

---restore datafile and archive log

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> run{
2> set until sequence 57 thread 1;
3> set newname for datafile 1 to '/oradata/racdb/system01.dbf';
4> set newname for datafile 2 to '/oradata/racdb/undotbs01.dbf';
5> set newname for datafile 3 to '/oradata/racdb/sysaux01.dbf';
6> set newname for datafile 4 to '/oradata/racdb/users01.dbf';
7> set newname for datafile 5 to '/oradata/racdb/undotbs02.dbf';
8> restore database;
9> switch datafile all;
10> }

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 02-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/racdb/system01.dbf
restoring datafile 00004 to /oradata/racdb/users01.dbf
restoring datafile 00005 to /oradata/racdb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /rmanset/racdb_full_RACDB669769770931
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/racdb_full_RACDB669769770931 tag=FULLBACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oradata/racdb/undotbs01.dbf
restoring datafile 00003 to /oradata/racdb/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /rmanset/racdb_full_RACDB669769784941
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/racdb_full_RACDB669769784941 tag=FULLBACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 02-NOV-08

datafile 1 switched to datafile copy
input datafile copy recid=10 stamp=669738296 filename=/oradata/racdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=11 stamp=669738296 filename=/oradata/racdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=669738296 filename=/oradata/racdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=669738297 filename=/oradata/racdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=14 stamp=669738297 filename=/oradata/racdb/undotbs02.dbf

RMAN>

--列出备份的archivelog:
RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
89      2.44M      DISK        00:00:03     02-NOV-08
        BP Key: 89   Status: AVAILABLE Compressed: NO Tag: TAG20081102T231211
        Piece Name: /rmanset/RACDB_arch_98_1_2

List of Archived Logs in backup set 89
Thrd Seq     Low SCN    Low Time Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    53      356353     02-NOV-08 396535     02-NOV-08
1    54      396535     02-NOV-08 398232     02-NOV-08
2    44      350717     02-NOV-08 356350     02-NOV-08
2    45      356350     02-NOV-08 376487     02-NOV-08
2    46      376487     02-NOV-08 396536     02-NOV-08
2    47      396536     02-NOV-08 397820     02-NOV-08
2    48      397820     02-NOV-08 398228     02-NOV-08
2    49      398228     02-NOV-08 398963     02-NOV-08

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
90      17.42M     DISK        00:00:07     02-NOV-08
        BP Key: 90   Status: AVAILABLE Compressed: NO Tag: TAG20081102T231211
        Piece Name: /rmanset/RACDB_arch_97_1_1

List of Archived Logs in backup set 90
Thrd Seq     Low SCN    Low Time Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    42      285767     01-NOV-08 285925     01-NOV-08
1    44      286441     01-NOV-08 286516     01-NOV-08
1    45      286516     01-NOV-08 308174     01-NOV-08
1    46      308174     01-NOV-08 312441     01-NOV-08
1    47      312441     01-NOV-08 324657     02-NOV-08

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
91      9.69M      DISK        00:00:04     02-NOV-08
        BP Key: 91   Status: AVAILABLE Compressed: NO Tag: TAG20081102T231211
        Piece Name: /rmanset/RACDB_arch_99_1_1

List of Archived Logs in backup set 91
Thrd Seq     Low SCN    Low Time Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    48      324657     02-NOV-08 324727     02-NOV-08
1    49      324727     02-NOV-08 325613     02-NOV-08
1    50      325613     02-NOV-08 349040     02-NOV-08
1    51      349040     02-NOV-08 350714     02-NOV-08
1    52      350714     02-NOV-08 356353     02-NOV-08
1    55      398232     02-NOV-08 398433     02-NOV-08
1    56      398433     02-NOV-08 398960     02-NOV-08

--restore archivelog and recover database.

RMAN>

RMAN> run{
2> set archivelog destination to '/racdb_arch';
3> restore archivelog from sequence 49 thread 2;
}4>

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 02-NOV-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/racdb_arch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=49
channel ORA_DISK_1: reading from backup piece /rmanset/RACDB_arch_98_1_2
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/RACDB_arch_98_1_2 tag=TAG20081102T231211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 02-NOV-08

RMAN>

RMAN> run{
2> set archivelog destination to '/racdb_arch';
3> restore archivelog from sequence 44 thread 2;
4> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 02-NOV-08
using channel ORA_DISK_1

archive log thread 2 sequence 49 is already on disk as file /racdb_arch/2_49_669487401.dbf
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/racdb_arch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=44
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=45
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=47
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=48
channel ORA_DISK_1: reading from backup piece /rmanset/RACDB_arch_98_1_2
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/RACDB_arch_98_1_2 tag=TAG20081102T231211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 02-NOV-08

RMAN> run{
2> set until sequence 57 thread 1;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 02-NOV-08
using channel ORA_DISK_1

starting media recovery

archive log thread 2 sequence 49 is already on disk as file /racdb_arch/2_49_669487401.dbf
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=56
channel ORA_DISK_1: reading from backup piece /rmanset/RACDB_arch_99_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/RACDB_arch_99_1_1 tag=TAG20081102T231211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/racdb_arch/1_56_669487401.dbf thread=1 sequence=56
archive log filename=/racdb_arch/2_49_669487401.dbf thread=2 sequence=49
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-NOV-08

--对redo 作修改：

alter database rename file '+RACDATA/racdb/onlinelog/group_1.257.669487411' to '/oradata/racdb/redo1';
alter database rename file '+RACDATA/racdb/onlinelog/group_2.258.669487419' to '/oradata/racdb/redo2';
alter database rename file '+RACDATA/racdb/onlinelog/group_3.265.669489319' to '/oradata/racdb/redo3';
alter database rename file '+RACDATA/racdb/onlinelog/group_4.266.669489327' to '/oradata/racdb/redo4';

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+RACDATA/racdb/onlinelog/group_1.257.669487411
+RACDATA/racdb/onlinelog/group_2.258.669487419
+RACDATA/racdb/onlinelog/group_3.265.669489319
+RACDATA/racdb/onlinelog/group_4.266.669489327

SQL> alter database rename file '+RACDATA/racdb/onlinelog/group_1.257.669487411' to '/oradata/racdb/redo1';

Database altered.

SQL> alter database rename file '+RACDATA/racdb/onlinelog/group_2.258.669487419' to '/oradata/racdb/redo2';
alter database rename file '+RACDATA/racdb/onlinelog/group_3.265.669489319' to '/oradata/racdb/redo3';
alter database rename file '+RACDATA/racdb/onlinelog/group_4.266.669489327' to '/oradata/racdb/redo4';

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL>
SQL>
SQL>
SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC


SQL>
SQL> select group# from v$log where THREAD#=2;

    GROUP#
----------
         3
         4

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2: '/oradata/racdb/redo4'


SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2: '/oradata/racdb/redo4'


SQL> alter database clear unarchived logfile group 4;