数据库冷备恢复及各种Oracle相关文件丢失下的异常现象
一、我的数据库当前是“活”的,正常的,证明如下
[root@ora11 ~]# su -
oracle
[oracle@ora11 ~]$ sqlplus / as
sysdba
SQL*Plus: Release 11.2.0.3.0
Production on Sat Nov 16 16:55:36 2013
Copyright (c) 1982, 2011,
Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
SQL> set
pagesize 2000 linesize 800
SQL> select *
from scott.emp;
EMPNO ENAME JOB MGR
HIREDATE SAL COMM DEPTNO
---------- ---------- ---------
---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698
20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698
22-FEB-81 1250 500 30
7566 JONES MANAGER 7839
02-APR-81 2975 20
7654 MARTIN
SALESMAN 7698
28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-81 2850 30
7782 CLARK MANAGER 7839
09-JUN-81 2450 10
7788 SCOTT ANALYST 7566
19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER
SALESMAN 7698
08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566
03-DEC-81 3000 20
7934 MILLER
CLERK 7782 23-JAN-82 1300 10
14 rows selected.
二、核心的文件在哪里?
SQL> select name
from v$datafile union
2
select member from v$logfile union
3
select name from v$controlfile order by
1;
NAME
------------------------------------------------------
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
SQL> show
parameter spfile;
NAME TYPE
VALUE
------------------------------------ -----------
------------------------------
spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
上述核心文件的文件属性怎样的呢?先记录一下,避免将来拷来拷去,因为属性不对,造成异常。
-rw-r----- 1 oracle oinstall
754982912 Nov 16 17:08 /u01/app/oracle/oradata/orcl/system01.dbf
#datafile的代表
-rw-r----- 1 oracle oinstall
9748480 Nov 16 17:08 /u01/app/oracle/oradata/orcl/control01.ctl
#控制文件代表
-rw-r----- 1 oracle oinstall
9748480 Nov 16 17:08
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
#同上
-rw-r----- 1 oracle oinstall
2560 Nov 16 16:57
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
#参数文件
三、执行冷备份
SQL> shutdown
immediate;
Database closed.
Database
dismounted.
ORACLE instance shut
down.
[root@ora11 ~]# df
-h
Filesystem Size Used
Avail Use% Mounted on
/dev/sda1 29G
12G 16G 43%
/
tmpfs 1.9G 0
1.9G 0% /dev/shm
/dev/sda2 9.7G 150M
9.0G 2% /tmp
从上面看,/下还有16GB,足可以完成备份了。
[root@ora11 ~]#mkdir
/coldbackup
root@ora11 /]# tar -cvf
/coldbackup/orcl.tar
/u01/app/oracle/oradata/orcl
tar: Removing leading `/' from
member names
/u01/app/oracle/oradata/orcl/
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/temp01.dbf
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/redo03.log
[root@ora11 /]# cp -p
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
/coldbackup
[root@ora11 /]# df
-h
Filesystem Size Used
Avail Use% Mounted on
/dev/sda1 29G
14G 14G 50%
/
从上面的空间看,说明备份上面的文件用了2GB。
如果采用压缩,效果如何呢?
root@ora11 /]# tar -zcvf
/coldbackup/orcl88.tar.gz
/u01/app/oracle/oradata/orcl
#打包后,以gzip压缩
root@ora11 /]# tar -jcvf
/coldbackup/orcl99.tar.bz2
/u01/app/oracle/oradata/orcl
#打包后,以bzip2压缩
[root@ora11 coldbackup]# du -sh
*
315M orcl88.tar.gz #仅是orcl.tar的15%
261M orcl99.tar.bz2 #仅是orcl.tar的13%
2.0G orcl.tar
4.0K spfileorcl.ora
以下将开始模拟各种文件损坏的情况下,启动Oracle有哪些提示?
(3.1)参数文件丢失的情形
mv
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/abc.ora
SQL>
startup
ORA-01078: failure in
processing system parameters
LRM-00109: could not open
parameter file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
mv
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/abc.ora
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
(3.2)控制文件丢失的情形
SQL> shutdown
immediate
Database closed.
Database
dismounted.
ORACLE instance shut
down.
mv
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/abc.ctl
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
ORA-00205: error in identifying
control file, check alert log for more info
SQL> select
status from v$instance;
STATUS
------------
STARTED
启动数据库到第一个台阶nomount(STARTED)状态做了如下的工作:
1.读参数文件
2.分配内存
3.启动后台进程
4.初始化部分v$视图
SQL> ho ps
-ef|grep ora_
oracle 4488 1
0 18:36 ? 00:00:00
ora_pmon_orcl
oracle 4490 1
0 18:36 ? 00:00:00
ora_psp0_orcl
oracle 4492 1
2 18:36 ? 00:00:08
ora_vktm_orcl
oracle 4496 1
0 18:36 ? 00:00:00
ora_gen0_orcl
oracle 4498 1
0 18:36 ? 00:00:00
ora_diag_orcl
oracle 4500 1
0 18:36 ? 00:00:00
ora_dbrm_orcl
oracle 4502 1
0 18:36 ? 00:00:00
ora_dia0_orcl
oracle 4504 1
0 18:36 ? 00:00:02
ora_mman_orcl
oracle 4506 1
0 18:36 ? 00:00:00
ora_dbw0_orcl
oracle 4508 1
0 18:36 ? 00:00:00
ora_lgwr_orcl
oracle 4510 1
0 18:36 ? 00:00:00
ora_ckpt_orcl
oracle 4512 1
0 18:36 ? 00:00:00
ora_smon_orcl
oracle 4514 1
0 18:36 ? 00:00:00
ora_reco_orcl
oracle 4516 1
0 18:36 ? 00:00:00
ora_mmon_orcl
oracle 4518 1
0 18:36 ? 00:00:00
ora_mmnl_orcl
oracle 4520 1
0 18:36 ? 00:00:00
ora_d000_orcl
oracle 4522 1
0 18:36 ? 00:00:00
ora_s000_orcl
oracle 4570 1940 0
18:42 pts/0 00:00:00 /bin/bash
-c ps -ef|grep ora_
oracle 4572 4570 7
18:42 pts/0 00:00:00 grep
ora_
SQL> ho
ipcs
------ Shared Memory Segments
--------
key shmid
owner
perms
bytes
nattch
status
0x00000000 294912
oracle 640 4096 0
0x00000000 327681
oracle 640 4096 0
0x42e38fd0 360450
oracle 640 4096 0
------ Semaphore Arrays
--------
key semid
owner
perms
nsems
0x89a83438 425986
oracle 640 154
------ Message Queues
--------
key msqid
owner
perms
used-bytes
messages
看下alert中有什么警告?
SQL>
select value from v$diag_info where name ='Diag
Trace';
VALUE
-------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[root@ora11 trace]# cd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
vi alert_orcl.log
发现末尾有:
ORACLE_BASE from environment =
/u01/app/oracle
Sat Nov 16 18:36:37
2013
ALTER DATABASE
MOUNT
ORA-00210: cannot open the
specified control file
ORA-00202: control file:
'/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain
file status
Linux-x86_64 Error: 2: No such
file or directory
Additional information:
3
Sat Nov 16 18:36:38
2013
Checker run found 1 new
persistent data failures
ORA-205 signalled during: ALTER
DATABASE MOUNT...
SQL> shutdown
immediate
ORA-01507: database not
mounted
ORACLE instance shut
down.
[root@ora11 ~]# mv
/u01/app/oracle/oradata/orcl/abc.ctl
/u01/app/oracle/oradata/orcl/control01.ctl
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
(3.3)数据文件丢失的情形
SQL> shutdown
immediate
Database closed.
Database
dismounted.
ORACLE instance shut
down.
mv
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/system01-bye.dbf
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-01157: cannot identify/lock
data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/orcl/system01.dbf'
SQL> select
status from v$instance;
STATUS
------------
MOUNTED
[root@ora11 trace]# cd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[root@ora11 trace]# tail -n 60
alert_orcl.log
查到关键信息如下:
Completed: ALTER DATABASE
MOUNT
Sat Nov 16 18:54:37
2013
ALTER DATABASE
OPEN
Errors in file
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_4783.trc:
ORA-01157: cannot identify/lock
data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27037: unable to obtain
file status
Linux-x86_64 Error: 2: No such
file or directory
Additional information:
3
Errors in file
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4834.trc:
ORA-01157: cannot identify/lock
data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-1157 signalled during:
ALTER DATABASE OPEN...
Sat Nov 16 18:54:37
2013
Checker run found 1 new
persistent data failures
[root@ora11 trace]# vi
orcl_ora_4834.trc
Trace file
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4834.trc
Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME =
/u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: ora11
Release: 2.6.32-279.el6.x86_64
Version: #1 SMP Wed
Jun 13 18:24:36 EDT 2012
Machine: x86_64
VM name: VMWare
Version: 6
Instance name:
orcl
Redo thread mounted by this
instance: 1
Oracle process number:
19
Unix process pid: 4834, image:
oracle@ora11 (TNS V1-V3)
*** 2013-11-16
18:54:37.504
*** SESSION ID:(191.3)
2013-11-16 18:54:37.504
*** CLIENT ID:() 2013-11-16
18:54:37.504
*** SERVICE NAME:() 2013-11-16
18:54:37.504
*** MODULE NAME:(sqlplus@ora11
(TNS V1-V3)) 2013-11-16 18:54:37.504
*** ACTION NAME:() 2013-11-16
18:54:37.504
DDE: Problem Key 'ORA 1110' was
flood controlled (0x1) (no incident)
ORA-01110: data file 1:
'/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-01157: cannot identify/lock
data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/orcl/system01.dbf'
mv
/u01/app/oracle/oradata/orcl/system01-bye.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
SQL> shutdown
immediate
ORA-01109: database not
open
Database
dismounted.
ORACLE instance shut
down.
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
(4)重做日志(REDO)丢失的情形
SQL> ARCHIVE LOG
LIST
Database log mode
No Archive Mode
Automatic archival
Disabled
Archive destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence
7
Current log sequence
9
SQL> shutdown
immediate
Database closed.
Database
dismounted.
ORACLE instance shut
down.
ls -l
/u01/app/oracle/oradata/orcl/redo*.log
[root@ora11 trace]# ls -l
/u01/app/oracle/oradata/orcl/redo*.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:01
/u01/app/oracle/oradata/orcl/redo01.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:01
/u01/app/oracle/oradata/orcl/redo02.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:03
/u01/app/oracle/oradata/orcl/redo03.log
mv
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo01.log.bye
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-03113: end-of-file on
communication channel
Process ID: 5121
Session ID: 191 Serial number:
3
SQL> select
status from v$instance;
ERROR:
ORA-03114: not connected to
ORACLE
SQL> ho ps
-ef|grep ora_
oracle 5134 1940 0
19:10 pts/0 00:00:00 /bin/bash
-c ps -ef|grep ora_
oracle 5136 5134 0
19:10 pts/0 00:00:00 grep
ora_
SQL> ho
ipcs
------ Shared Memory Segments
--------
key shmid
owner
perms
bytes
nattch
status
------ Semaphore Arrays
--------
key semid
owner
perms
nsems
------ Message Queues
--------
key msqid
owner
perms
used-bytes
messages
看来,即使在非归档模式下,Oracle的归档日志文件也很重要,如果丢失,连nomount(STARTED)都无法完成。
cp -p
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-03113: end-of-file on
communication channel
Process ID: 5247
Session ID: 191 Serial number:
3
SQL> select
status from v$instance;
ERROR:
ORA-03114: not connected to
ORACLE
说明redo1.log与redo2.log并不是境像的关系。
[root@ora11 trace]# cd
/u01/app/oracle/oradata/orcl
[root@ora11 orcl]# rm
/u01/app/oracle/oradata/orcl/redo01.log
[root@ora11 orcl]#mv
/u01/app/oracle/oradata/orcl/redo01.log.bye
/u01/app/oracle/oradata/orcl/redo01.log
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
突发奇想,如果所有的redo全没了,会提示什么?
SQL> shutdown
immediate
Database closed.
Database
dismounted.
ORACLE instance shut
down.
[root@ora11 orcl]# mv
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo01.log.bye
[root@ora11 orcl]# mv
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo02.log.bye
[root@ora11 orcl]# mv
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo03.log.bye
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-03113: end-of-file on
communication channel
Process ID: 5504
Session ID: 191 Serial number:
3
看来和缺少redo01.log的报错是一样的。
[root@ora11 orcl]# mv
/u01/app/oracle/oradata/orcl/redo01.log.bye
/u01/app/oracle/oradata/orcl/redo01.log
[root@ora11 orcl]# ls -rlt
redo*
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:19 redo02.log.bye
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:19 redo01.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:20 redo03.log.bye
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-03113: end-of-file on
communication channel
Process ID: 5613
Session ID: 191 Serial number:
3
看来就算“归还”了第一个redo1.log,其它未“归还”,报错是一样的!
[root@ora11 orcl]# mv
/u01/app/oracle/oradata/orcl/redo02.log.bye
/u01/app/oracle/oradata/orcl/redo02.log
[root@ora11 orcl]# mv
/u01/app/oracle/oradata/orcl/redo03.log.bye
/u01/app/oracle/oradata/orcl/redo03.log
[root@ora11 orcl]# ls -l
redo*
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:24 redo01.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:19 redo02.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:20 redo03.log
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
(3.5)当数据库崩溃,如何利用备份来进行恢复?
SQL>
create table t(x int);
Table created.
SQL> insert into
t values(1);
1 row created.
SQL>
commit;
Commit complete.
SQL> select *
from t;
X
----------
1
SQL> shutdown
immediate;
Database closed.
Database
dismounted.
ORACLE instance shut
down.
[root@ora11 orcl]# cd
/u01/app/oracle/oradata/orcl
[root@ora11 orcl]# ls
-rlt
total 1972952
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:26 redo02.log
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:26 redo01.log
-rw-r----- 1 oracle oinstall
30416896 Nov 16 19:27 temp01.dbf
-rw-r----- 1 oracle oinstall
52429312 Nov 16 19:30 redo03.log
-rw-r----- 1 oracle oinstall
754982912 Nov 16 19:30 system01.dbf
-rw-r----- 1 oracle oinstall
120594432 Nov 16 19:30 undotbs01.dbf
-rw-r----- 1 oracle oinstall
608182272 Nov 16 19:30 sysaux01.dbf
-rw-r----- 1 oracle oinstall
5251072 Nov 16 19:30 users01.dbf
-rw-r----- 1 oracle oinstall
362422272 Nov 16 19:30 example01.dbf
-rw-r----- 1 oracle oinstall
9748480 Nov 16 19:30 control01.ctl
[root@ora11 orcl]# rm -rf
/u01/app/oracle/oradata/orcl
[root@ora11 orcl]# ls -rlt
/u01/app/oracle/oradata/
total 0
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
ORA-00205: error in identifying
control file, check alert log for more info
SQL> select
status from v$instance;
STATUS
------------
STARTED
SQL> shutdown
immediate;
ORA-01507: database not
mounted
ORACLE instance shut
down.
利用先前的备份包执行恢复:
[root@ora11 coldbackup]# cd
/coldbackup
[root@ora11 coldbackup]# ls
-rlt
total 2589716
-rw-r----- 1 oracle oinstall
2560 Nov
16 16:57 spfileorcl.ora
-rw-r--r-- 1 root
root 2048901120 Nov 16 17:46 orcl.tar
-rw-r--r-- 1 root
root 329811321 Nov 16 18:08 orcl88.tar.gz
-rw-r--r-- 1 root
root 273144065 Nov 16 18:24
orcl99.tar.bz2
[root@ora11 coldbackup]# tar
-xvf orcl.tar
u01/app/oracle/oradata/orcl/
u01/app/oracle/oradata/orcl/system01.dbf
u01/app/oracle/oradata/orcl/sysaux01.dbf
u01/app/oracle/oradata/orcl/temp01.dbf
u01/app/oracle/oradata/orcl/redo02.log
u01/app/oracle/oradata/orcl/users01.dbf
u01/app/oracle/oradata/orcl/undotbs01.dbf
u01/app/oracle/oradata/orcl/redo01.log
u01/app/oracle/oradata/orcl/control01.ctl
u01/app/oracle/oradata/orcl/example01.dbf
u01/app/oracle/oradata/orcl/redo03.log
[root@ora11 orcl]# mkdir
/u01/app/oracle/oradata/orcl
[root@ora11 oradata]# chown
oracle:oinstall orcl
[root@ora11 oradata]# chmod 750
orcl
[root@ora11 orcl]# cp -p
/coldbackup/u01/app/oracle/oradata/orcl/*
/u01/app/oracle/oradata/orcl/
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
ORA-00214: control file
'/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
version 1190 inconsistent with
file
'/u01/app/oracle/oradata/orcl/control01.ctl'
version 1082
SQL> select
status from v$instance;
STATUS
------------
STARTED
[root@ora11 coldbackup]# mv
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl.back
[root@ora11 coldbackup]# cp -p
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
[root@ora11 coldbackup]# ls
-rlt /u01/app/oracle/fast_recovery_area/orcl/
-rw-r----- 1 oracle oinstall
9748480 Nov 16 17:08 control02.ctl
-rw-r----- 1 oracle oinstall
9748480 Nov 16 19:30 control02.ctl.back
SQL>
startup
ORACLE instance
started.
Total System Global Area
1603411968 bytes
Fixed Size 2228784 bytes
Variable Size
989859280
bytes
Database Buffers
603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
SQL> select
status from v$instance;
STATUS
------------
OPEN
SQL> select *
from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view does
not exist
这个t表是在冷备前创建的,因为恢复,一定会丢失的,这就对了!
(4)反思与心得:
(1)如何可以直接tar -xvf
orcl.tar到目标(/u01/app/oracle/oradata/orcl/),而不用下面这样麻烦?
cp -p
/coldbackup/u01/app/oracle/oradata/orcl/*
/u01/app/oracle/oradata/orcl/
(2)虽然控制文件在多处冗余,但恢复时,由于控制文件版本不一致了,闪回区的控制文件需要用备份时的版本进行一个覆盖动作,才可以确保数据库正确Open。
(3)tar的压缩真强悍,还有更强的吗?
(4)commit和是否写数据文件没有关系 ,或者是说,
事务的状态和是否写数据文件没有关系。
(5)归档与不归档都写REDO,归挡模式下,只是当REDO满时就生成一个归挡备份文件。
(5)附一些文中提及到的Ora警告:
ORA-00210:
cannot open the specified control file
ORA-00202:
control file:
'/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037:
unable to obtain file status
ORA-205
signalled during: ALTER DATABASE MOUNT...
ORA-01507:
database not mounted
ORA-01157:
cannot identify/lock data file 1 - see DBWR trace file
ORA-01110:
data file 1:
'/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27037:
unable to obtain file status
ORA-01109:
database not open
ORA-03113:
end-of-file on communication channel
ORA-03114:
not connected to ORACLE
ORA-00205:
error in identifying control file, check alert log for more
info
ORA-01507:
database not mounted
ORA-00214:
control version不对
ORA-00942:
table or view does not exist