OCM实验-备份恢复-控制文件

linuxx

--查看环境信息
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=&quot;color:#ff0000;&quot;>/u01/app/oracle/oradata/vict/control01.ctl</span>
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> <span style=&quot;color:#ff0000;&quot;>/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=&quot;color:#ff0000;&quot;> /u01/app/oracle/oradata/vict/control01.ctl (deleted)</span>
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> <span style=&quot;color:#ff0000;&quot;>/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 &quot;VICT&quot; 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



版权声明：本文为博主原创文章，未经博主允许不得转载。