系统数据文件、用户数据文件全部丢失恢复简单步骤和测试

最近看了海明老师的视频 学习了下RMAN。下面是模拟全部数据文件丢失做的一个实验。

步骤：
startup nomount
restore controlfile from autobackup;
alter database mount;
restore database;

sqlplus：
recover database;
alter database open resetlogs;

测试：

删除所有数据
[oracle@web1 testdb2]$ ls
control01.ctl  control02.ctl  control03.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  test.dbf  undotbs01.dbf  users01.dbf
[oracle@web1 testdb2]$ mv * ../
[oracle@web1 testdb2]$ ls
[oracle@web1 testdb2]$ cd ..
[oracle@web1 oradata]$ ls
control01.ctl  control03.ctl  lost+found   redo01.log  redo03.log    system01.dbf  testdb2      test.dbf       users01.dbf
control02.ctl  dbs            oraRecovery  redo02.log  sysaux01.dbf  temp01.dbf    testdb2.bak  undotbs01.dbf

 

以为数据丢失，rman已经连接不上

[oracle@web1 ~]$ rman nocatalog target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 13 08:18:06 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/testdb2/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

sqlplus下操作
[oracle@web1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 13 08:18:12 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

将DBSHUTDOWN
SQL> conn /as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.

恢复文件：

1、rman连接上，恢复controlfile

[oracle@web1 ~]$ rman nocatalog target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 13 08:21:56 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: testdb2 (not mounted)
using target database control file instead of recovery catalog

RMAN> restore controlfile from autobackup;

Starting restore at 13-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

recovery area destination: /oradata/oraRecovery
database name (or database unique name) used for search: TESTDB2
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692087091_55nron4v_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oradata/testdb2/control01.ctl
output filename=/oradata/testdb2/control02.ctl
output filename=/oradata/testdb2/control03.ctl
Finished restore at 13-JUL-09

[oracle@web1 testdb2]$ ll
total 21012
-rw-r-----  1 oracle oinstall 7159808 Jul 13 08:22 control01.ctl
-rw-r-----  1 oracle oinstall 7159808 Jul 13 08:22 control02.ctl
-rw-r-----  1 oracle oinstall 7159808 Jul 13 08:22 control03.ctl

恢复完成。

2、恢复其他系统数据文件和用户数据文件
将database 加载到mount状态。进行restore database;
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
RMAN> restore database;

3、 恢复REDOLOG

没有ONLINE状态下， REDOLOG   SPFILE需要加上下面参数

/opt/oracle/product/10.2.0/db_1/dbs/initoradb.ora 文件加上_allow_resetlogs_corruption=TRUE，可以做没有ONLINE REDOLOG的恢复。

[oracle@web1 dbs]$ cat initoradb.ora
*.audit_file_dest='/opt/oracle/admin/testdb2/adump'
*.background_dump_dest='/opt/oracle/admin/testdb2/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oradata/testdb2/control01.ctl','/oradata/testdb2/control02.ctl','/oradata/testdb2/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/testdb2/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb2'
*.db_recovery_file_dest='/oradata/oraRecovery'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdb2XDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/testdb2/udump'
_allow_resetlogs_corruption=TRUE
[oracle@web1 dbs]$ pwd
/opt/oracle/product/10.2.0/db_1/dbs

因为我的数据库里面的SPFILE PFILE配置路径丢失，不能用SPFILE建立PFILE，
所以直接使用startup mount SPFILE文件来启动数据库。

正常的恢复步骤应该由PFILE启动数据库到MOUNT状态。
startup pfile=/opt/oracle/admin/testdb2/pfile/pfile.ora mount

[oracle@web1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 13 08:32:21 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL>alter database open resetlogs;
Database mounted.

数据库恢复正常

 

生产环境不能在线复制数据文件，最好在RMAN里面把文件都复制出来做备份，一旦RMAN文件集恢复失败，可以使用文件景象的备份进行物理恢复。