使用NORESETLOGS重建控制文件恢复数据库

最新推荐文章于 2021-10-15 14:16:26 发布

cuiwu7783

最新推荐文章于 2021-10-15 14:16:26 发布

阅读量173

点赞数

文章标签：数据库

-- 备份控制文件
RMAN> backup datafile 1;

Starting backup at 29-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: starting piece 1 at 29-SEP-13
channel ORA_DISK_1: finished piece 1 at 29-SEP-13
piece handle=/u01/app/oracle/flash_recovery_area/WAILON/backupset/2013_09_29/o1_mf_nnndf_TAG20130929T174837_94hxvp14_.bkp tag=TAG20130929T174837 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-SEP-13
channel ORA_DISK_1: finished piece 1 at 29-SEP-13
piece handle=/u01/app/oracle/flash_recovery_area/WAILON/backupset/2013_09_29/o1_mf_ncsnf_TAG20130929T174837_94hxwjhq_.bkp tag=TAG20130929T174837 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-13

-- 模拟交易产生并切换日志
17:50:13 SYS@wailon> create table scott.a1 tablespace wailon as select * from scott.a;

Table created.

17:51:36 SYS@wailon> alter system switch logfile;

System altered.

17:51:45 SYS@wailon> create table scott.b1 tablespace wailon as select * from scott.b;

Table created.

17:51:53 SYS@wailon> alter system switch logfile;

System altered.

17:52:10 SYS@wailon> alter system checkpoint;

System altered.

17:52:15 SYS@wailon> select count(*) from scott.a1;

COUNT(*)
----------
23

17:52:20 SYS@wailon> select count(*) from scott.b1;

COUNT(*)
----------
23

17:52:25 SYS@wailon> select group#,sequence#,status from v$log;

    GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
         1          7 CURRENT
         3          6 INACTIVE
         2          5 INACTIVE

17:52:30 SYS@wailon> select * from v$controlfile;

STATUS NAME                                          IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- --------------------------------------------- --- ---------- --------------
        /u01/app/oracle/oradata/wailon/control01.ctl NO       16384            614
        /u01/app/oracle/oradata/wailon/control02.ctl NO       16384            614

-- 模拟意外断电，并且控制文件丢失
17:52:41 SYS@wailon> shutdown abort;
ORACLE instance shut down.
17:52:47 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

17:53:22 SYS@wailon> host ls /u01/app/oracle/oradata/wailon/control*
ls: cannot access /u01/app/oracle/oradata/wailon/control*: No such file or directory

-- 启动数据库报错，找不到控制文件
17:53:30 SYS@wailon> startup
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             352324912 bytes
Database Buffers           54525952 bytes
Redo Buffers                8466432 bytes
ORA-00205: error in identifying control file, check alert log for more info

-- 关闭数据库，启动到NOMOUNT状态
17:53:38 SYS@wailon> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
17:53:55 SYS@wailon> startup nomount;
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             352324912 bytes
Database Buffers           54525952 bytes
Redo Buffers                8466432 bytes

-- 由于所有联机日志文件可用，使用NORESETLOGS重建控制文件
17:54:01 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" NORESETLOGS FORCE LOGGING ARCHIVELOG
17:54:06   2    MAXLOGFILES 16
17:54:06   3    MAXLOGMEMBERS 3
17:54:06   4    MAXDATAFILES 100
17:54:06   5    MAXINSTANCES 8
17:54:06   6    MAXLOGHISTORY 292
17:54:06   7 LOGFILE
17:54:06   8    GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log' SIZE 50M BLOCKSIZE 512,
17:54:06   9    GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log' SIZE 50M BLOCKSIZE 512,
17:54:06 10    GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log' SIZE 50M BLOCKSIZE 512
17:54:06 11 -- STANDBY LOGFILE
17:54:06 12 DATAFILE
17:54:06 13    '/u01/app/oracle/oradata/system01.dbf',
17:54:06 14    '/u01/app/oracle/oradata/sysaux01.dbf',
17:54:06 15    '/u01/app/oracle/oradata/undotbs01.dbf',
17:54:06 16    '/u01/app/oracle/oradata/users01.dbf',
17:54:06 17    '/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
17:54:06 18 CHARACTER SET ZHS16GBK
17:54:06 19 ;

Control file created.

-- 由于数据文件不一致，需要进行介质恢复
17:54:08 SYS@wailon> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/system01.dbf'

17:54:37 SYS@wailon> recover database;
Media recovery complete.

17:54:48 SYS@wailon> alter database open;

Database altered.
-- 成功打开数据库

-- 验证数据
17:55:08 SYS@wailon> select count(*) from scott.a1;

COUNT(*)
----------
23

17:55:28 SYS@wailon> select count(*) from scott.b1;

COUNT(*)
----------
23

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/429786/viewspace-777154/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/429786/viewspace-777154/