|
--查看环境信息
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/vict/c
ontrol01.ctl, /home/oracle/fla
sh_recovery_area/vict/control0
2.ctl
--模拟场景一:非归档下,删除所有控制文件,数据库未关闭。
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
--手工方式备份控制文件 重建控制时文件关键在于各个数据文件的位置
SQL> alter database backup controlfile to trace;
Database altered.
SQL> col value for a100;
SQL> select value from v$diag_info where name ='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2745.trc
--查找当前控制文件文件描述信息
SQL> !ps -ef|grep ckpt|grep -v grep
oracle 2725 1 0 23:00 ? 00:00:00 ora_ckpt_vict
--查看文件句柄信息
[iyunv@vict selinux]# cd /proc/2725/fd
[iyunv@vict fd]# ls -l
total 0
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 0 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 1 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 10 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trc
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 11 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trm
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 12 -> /u01/app/oracle/product/11.2/dbs/hc_vict.dat
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 13 -> /dev/zero
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 14 -> /proc/2725/fd
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 15 -> /dev/zero
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 16 -> /u01/app/oracle/product/11.2/dbs/lkVICT
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 17 -> <span style="color:#ff0000;">/u01/app/oracle/oradata/vict/control01.ctl</span>
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> <span style="color:#ff0000;">/home/oracle/flash_recovery_area/vict/control02.ctl</span>
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 2 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 3 -> /u01/app/oracle/product/11.2/rdbms/log/vict_ora_2697.trc
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 4 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 5 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 6 -> /dev/null
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 7 -> /u01/app/oracle/product/11.2/dbs/hc_vict.dat
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 8 -> /u01/app/oracle/product/11.2/dbs/lkinstvict (deleted)
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 9 -> /proc/2725/fd
--数据库正常开启情况下,删除控制文件。
SQL> !rm -rf /u01/app/oracle/oradata/vict/control01.ctl
SQL> !rm -rf /home/oracle/flash_recovery_area/vict/control02.ctl
--数据库没有第一时间报错
SQL> select * from dual;
D
-
X
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
--手工做检查点 也没有报错
SQL> alter system checkpoint;
System altered.
--在未关闭数据库的情况下,手工将控制文件拷贝回来
[oracle@vict flash_recovery_area]$ cd /proc/2725/fd
[oracle@vict fd]$ ls -l
total 0
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 0 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 1 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 10 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trc
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 11 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trm
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 12 -> /u01/app/oracle/product/11.2/dbs/hc_vict.dat
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 13 -> /dev/zero
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 14 -> /proc/2725/fd
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 15 -> /dev/zero
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 16 -> /u01/app/oracle/product/11.2/dbs/lkVICT
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 17 -><span style="color:#ff0000;"> /u01/app/oracle/oradata/vict/control01.ctl (deleted)</span>
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> <span style="color:#ff0000;">/home/oracle/flash_recovery_area/vict/control02.ctl (deleted)</span>
lrwx------. 1 oracle oinstall 64 Jul 25 23:15 19 -> /u01/app/oracle/oradata/vict/system01.dbf
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 2 -> /dev/null
lrwx------. 1 oracle oinstall 64 Jul 25 23:15 20 -> /u01/app/oracle/oradata/vict/sysaux01.dbf
lrwx------. 1 oracle oinstall 64 Jul 25 23:15 21 -> /u01/app/oracle/oradata/vict/undotbs01.dbf
lrwx------. 1 oracle oinstall 64 Jul 25 23:15 22 -> /u01/app/oracle/oradata/vict/users01.dbf
l-wx------. 1 oracle oinstall 64 Jul 25 23:08 3 -> /u01/app/oracle/product/11.2/rdbms/log/vict_ora_2697.trc
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 4 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 5 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 6 -> /dev/null
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 7 -> /u01/app/oracle/product/11.2/dbs/hc_vict.dat
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 8 -> /u01/app/oracle/product/11.2/dbs/lkinstvict (deleted)
lr-x------. 1 oracle oinstall 64 Jul 25 23:08 9 -> /proc/2725/fd
[oracle@vict fd]$ cp 17 /u01/app/oracle/oradata/vict/control01.ctl
[oracle@vict fd]$ cp 18 /home/oracle/flash_recovery_area/vict/control02.ctl
-rw-r-----. 1 oracle oinstall 9748480 Jul 25 23:15 /u01/app/oracle/oradata/vict/control01.ctl
[oracle@vict fd]$ ls -l /home/oracle/flash_recovery_area/vict/control02.ctl
-rw-r-----. 1 oracle oinstall 9748480 Jul 25 23:16 /home/oracle/flash_recovery_area/vict/control02.ctl
-- 关闭数据库,重新打开,校验是否成功。
SQL> shutdown immediate;
Database closed.
Error while trying to retrieve text for error ORA-03113
separator not found in message(3144)separator not found in message(3142)
SQL> shutdown abort;
Error while trying to retrieve text for error ORA-24324
Error while trying to retrieve text for error ORA-01041
SQL> ho ps -ef|grep smon
oracle 2934 2696 0 23:17 pts/2 00:00:00 /bin/bash -c ps -ef|grep smon
oracle 2936 2934 0 23:17 pts/2 00:00:00 grep smon
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@vict ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 25 23:17:56 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 104860760 bytes
Database Buffers 201326592 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
--数据库正常打开
----------------
--模拟场景二:非归档下,删除所有控制文件,数据库已关闭。
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> show parameter control_f;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/vict/c
ontrol01.ctl, /home/oracle/fla
sh_recovery_area/vict/control0
2.ctl
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--删除所有控制文件,此时无任何备份。
SQL> !rm -rf /u01/app/oracle/oradata/vict/control01.ctl
SQL> !rm -rf /home/oracle/flash_recovery_area/vict/control02.ctl
SQL> ! ls /u01/app/oracle/oradata/vict/control01.ctl
ls: cannot access /u01/app/oracle/oradata/vict/control01.ctl: No such file or directory
SQL> ! ls /home/oracle/flash_recovery_area/vict/control02.ctl
ls: cannot access /home/oracle/flash_recovery_area/vict/control02.ctl: No such file or directory
--启动数据库报错。
SQL> startup;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 104860760 bytes
Database Buffers 201326592 bytes
Redo Buffers 6336512 bytes
ORA-00205: Message 205 not found; No message file for product=RDBMS,
facility=ORA; arguments: [0] [] [0]
--查看告警信息数据库mount阶段报错。
715 Fri Jul 25 23:25:25 2014
716 ALTER DATABASE MOUNT
717 ORA-00210: Message 210 not found; No message file for product=RDBMS, facility=ORA
718 ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/home/oracle/flash_recovery_area/
vict/control02.ctl]
719 ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
720 Linux Error: 2: No such file or directory
721 Additional information: 3
722 ORA-00210: Message 210 not found; No message file for product=RDBMS, facility=ORA
723 ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/app/oracle/oradata/vict/cont
rol01.ctl]
724 ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
725 Linux Error: 2: No such file or directory
726 Additional information: 3
727 ORA-205 signalled during: ALTER DATABASE MOUNT...
728 Fri Jul 25 23:25:26 2014
729 Checker run found 2 new persistent data failures
--根据 control_files 参数发现,控制文件不存在,重建控制文件。
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 104860760 bytes
Database Buffers 201326592 bytes
Redo Buffers 6336512 bytes
--采用noresetlogs版本 --重建控制文件关键要设置好每个数据文件的位置
SQL> CREATE CONTROLFILE REUSE DATABASE "VICT" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/vict/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/vict/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/vict/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/vict/system01.dbf',
14 '/u01/app/oracle/oradata/vict/sysaux01.dbf',
15 '/u01/app/oracle/oradata/vict/undotbs01.dbf',
16 '/u01/app/oracle/oradata/vict/users01.dbf'
17 CHARACTER SET AL32UTF8
18 ;
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> ! ls /u01/app/oracle/oradata/vict/control01.ctl
/u01/app/oracle/oradata/vict/control01.ctl
SQL> ! ls /home/oracle/flash_recovery_area/vict/control02.ctl
/home/oracle/flash_recovery_area/vict/control02.ctl
--数据库正常启动
--检查文件信息,临时文件是需要手工添加的
SQL> set linesize 1000;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIE
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------
1 1 7 52428800 512 1 NO CURRENT 817590 25-JUL-14 2.8147E+14
2 1 5 52428800 512 1 NO INACTIVE 793737 25-JUL-14 817000 25-JUL-4
3 1 6 52428800 512 1 NO INACTIVE 817000 25-JUL-14 817590 25-JUL-4
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER _
---------- ------- ------- ---------------------------------------------------------------------------------------------------------
3 STALE ONLINE /u01/app/oracle/oradata/vict/redo03.log O
2 STALE ONLINE /u01/app/oracle/oradata/vict/redo02.log O
1 ONLINE /u01/app/oracle/oradata/vict/redo01.log O
SQL> select * from v$dbfile;
FILE# NAME
---------- -------------------------------------------------------------------------------------------------------------------------
4 /u01/app/oracle/oradata/vict/users01.dbf
3 /u01/app/oracle/oradata/vict/undotbs01.dbf
2 /u01/app/oracle/oradata/vict/sysaux01.dbf
1 /u01/app/oracle/oradata/vict/system01.dbf
SQL> select * from dba_temp_files;
no rows selected
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/vict/temp01.dbf';
Tablespace altered.
-------------------------------------------
--模拟场景三:归档模式下有备份,删除所有控制文件。
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
--做好备份
RMAN> backup database include current controlfile;
Starting backup at 25-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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/vict/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/vict/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/vict/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/vict/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-JUL-14
channel ORA_DISK_1: finished piece 1 at 25-JUL-14
piece handle=/home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_nnndf_TAG20140725T234120_9x6mdjwl_.bkp tag=TAG2014072E
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:07
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 25-JUL-14
channel ORA_DISK_1: finished piece 1 at 25-JUL-14
piece handle=/home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_ncsnf_TAG20140725T234120_9x6mgnvx_.bkp tag=TAG2014072E
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUL-14
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1007.97M DISK 00:00:59 25-JUL-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140725T234120
Piece Name: /home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_nnndf_TAG20140725T234120_9x6mdjwl_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/system01.dbf
2 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/sysaux01.dbf
3 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/undotbs01.dbf
4 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/users01.dbf
--开始删除文件
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/vict/c
ontrol01.ctl, /home/oracle/fla
sh_recovery_area/vict/control0
2.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> shutdown immediate;
ORA-01109: Message 1109 not found; No message file for product=RDBMS, facility=ORA
Database dismounted.
ORACLE instance shut down.
SQL> !rm -rf /u01/app/oracle/oradata/vict/control01.ctl
SQL> !rm -rf /home/oracle/flash_recovery_area/vict/control02.ctl
--启动报错
SQL> startup;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 104860760 bytes
Database Buffers 201326592 bytes
Redo Buffers 6336512 bytes
ORA-00205: Message 205 not found; No message file for product=RDBMS, facility=ORA; arguments: [0] [] [0]
--尝试恢复,此时推荐重建控制文件,本次测试restore recover
RMAN> restore controlfile from
2> '/home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_ncsnf_TAG20140725T234120_9x6mgnvx_.bkp';
Starting restore at 25-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/vict/control01.ctl
output file name=/home/oracle/flash_recovery_area/vict/control02.ctl
Finished restore at 25-JUL-14
RMAN> mount database;
using target database control file instead of recovery catalog
database mounted
RMAN> recover database;
Starting recover at 26-JUL-14
Starting implicit crosscheck backup at 26-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 26-JUL-14
Starting implicit crosscheck copy at 26-JUL-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-JUL-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_ncsnf_TAG20140725T234120_9x6mgnvx_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/oradata/vict/redo01.log
archived log file name=/u01/app/oracle/oradata/vict/redo01.log thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-JUL-14
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
--以resetlogs打开 重置了日志sequence,之后做好备份一次。也可以以noresetlogs重建控制文件,就无需重置日志。
SQL> alter database open resetlogs;
Database altered.
SQL> set linesize 1000;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIE
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------
1 1 1 52428800 512 1 NO CURRENT 818055 26-JUL-14 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
版权声明:本文为博主原创文章,未经博主允许不得转载。 |
|