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

linuxx 发表于 2015-11-9 13:11:10

--查看环境信息
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 10 23:00 ? 00:00:00 ora_ckpt_vict
--查看文件句柄信息
# cd /proc/2725/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 -> /u01/app/oracle/oradata/vict/control01.ctl
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> /home/oracle/flash_recovery_area/vict/control02.ctl
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.
--在未关闭数据库的情况下，手工将控制文件拷贝回来
$ cd /proc/2725/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 -> /u01/app/oracle/oradata/vict/control01.ctl (deleted)
lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> /home/oracle/flash_recovery_area/vict/control02.ctl (deleted)
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
$ cp 17 /u01/app/oracle/oradata/vict/control01.ctl
$ 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
$ 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 293426960 23:17 pts/2 00:00:00 /bin/bash -c ps -ef|grep smon
oracle 293629340 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
$ 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 Area313860096 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 Area313860096 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: []
--查看告警信息数据库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 Area313860096 bytes
Fixed Size 1336232 bytes
Variable Size 104860760 bytes
Database Buffers 201326592 bytes
Redo Buffers 6336512 bytes
--采用noresetlogs版本 --重建控制文件关键要设置好每个数据文件的位置
SQL> CREATE CONTROLFILE REUSE DATABASE "VICT" NORESETLOGSNOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7LOGFILE
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
12DATAFILE
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'
17CHARACTER 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# BYTESBLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIE
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------
1 1 7 52428800 512 1 NOCURRENT 817590 25-JUL-14 2.8147E+14
2 1 5 52428800 512 1 NOINACTIVE 793737 25-JUL-14 817000 25-JUL-4
3 1 6 52428800 512 1 NOINACTIVE 817000 25-JUL-14 817590 25-JUL-4
SQL> select * from v$logfile;
GROUP# STATUSTYPE 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 KeyType LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1007.97M DISK 00:00:59 25-JUL-14
BP Key: 1 Status: AVAILABLECompressed: NOTag: 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 TimeName
---- -- ---- ---------- --------- ----
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 Area313860096 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: []
--尝试恢复，此时推荐重建控制文件，本次测试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# BYTESBLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIE
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------
1 1 1 52428800 512 1 NOCURRENT 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

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

页: [1]

运维网's Archiver

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