情况一,数据库崩溃,只剩下磁带上的通过rman 的全备份,有controlfile和spfile都在rman备份中。
通过重新安装一个同名的SID之后再进行恢复。
注意事项:
1 rman的保存格式前后要一样.
2 archive 的格式也要和原来一样。
oracle 10g
情况一,数据库崩溃,只剩下磁带上的通过rman 的全备份,有controlfile和spfile都在rman备份中。
通过重新安装一个同名的SID之后再进行恢复。
注意事项:
1 rman的保存格式前后要一样.
2 archive 的格式也要和原来一样。
oracle 10g
[oracle@standby oracle]$ rman target /
启动归档
SQL>alter database archivelog;
System altered.
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>
重启数据库,归档就会自动启动。
启动自动备份控制文件
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> configure channel device type disk format 'F:\backup%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\backup%U';
new RMAN configuration parameters are successfully stored
SQL> select name from v$datafile;
NAME
---------------------------------------------------
F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\SYSTEM01.DBF
F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\UNDOTBS01.DBF
F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\SYSAUX01.DBF
F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\USERS01.DBF
确定原控制文件的路径
SQL> select name from v$controlfile;
查看log位置
SQL>select name from V$ARCHIVED_LOG;
查看log的信息
SQL>select * from v$log_history;
插入数据
SQL> select * from ttt;
NAME EMAIL
---------- ----------
aa [email protected]
bb [email protected]
cc [email protected]
RMAN备份,
RMAN>backup database;
再插入数据
SQL> select * from ttt;
NAME EMAIL
---------- ----------
aa [email protected]
bb [email protected]
cc [email protected]
dd [email protected]
用DBCA删除 数据库和相关数据文件。
再重新建个相同实例名的SID,再开始恢复。
配置RMAN
RMAN> configure channel device type disk format 'F:\backup%U'; //注意和原来的格式一样。
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\backup%U';
new RMAN configuration parameters are successfully stored
先需要从自动备份中恢复控制文件
RMAN> startup nomount;
RMAN> restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup;
重建本地认证文件
C:>orapwd file=F:\oracle\product.1.0\Db_1\DATABASE\PWDtest.ORA password=123456 entries=2;
RMAN>shutdown immediate
RMAN> startup mount
database is already started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/03/2005 20:00:30
RMAN-06189: current DBID 1863531774 does not match target mounted database (1862908416)
RMAN>
重新设置DBID
RMAN> set DBID=1862908416
executing command: SET DBID
你可能需要修改spfile文件,如果文件位置改变。
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> startup mount;
ORACLE instance started.
RMAN> restore database;
转入SQLPLUS,sys登陆sqlplus
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 356534 generated at 08/02/2005 19:14:40 needed for thread 1
ORA-00289: suggestion :
F:\ORACLE\PRODUCT.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG05_08_03\O1_MF_1_
2_%U_.ARC
ORA-00280: change 356534 for thread 1 is in sequence #2
Specify log: {
1<ret>=suggested | filename | AUTO | CANCEL}
2cancel //因为log丢失,此步选cancel
3Media recovery cancelled.
4
5
6SQL> alter database open resetlogs;
7
8Database altered.
9
10SQL> select * from ttt;
11
12NAME EMAIL
13\---------- ----------
14aa [email protected]
15bb [email protected]
16cc [email protected]
17
18SQL>
19
20数据回来了,但是由于归档和redo log丢了,丢失一部分数据。
21
22
23情况二 数据库崩溃,但是还剩下归档和redo log是好的。
24
25
26启动归档
27SQL>alter database archivelog;
28
29System altered.
30
31SQL> archive log list;
32Database log mode No Archive Mode
33Automatic archival Disabled
34Archive destination USE_DB_RECOVERY_FILE_DEST
35Oldest online log sequence 7
36Current log sequence 9
37SQL>
38
39重启数据库,归档就会自动启动。
40
41启动自动备份控制文件
42
43RMAN> configure controlfile autobackup on;
44
45new RMAN configuration parameters:
46CONFIGURE CONTROLFILE AUTOBACKUP ON;
47new RMAN configuration parameters are successfully stored
48
49配置RMAN的格式和存储位置。
50RMAN> configure channel device type disk format 'F:\backup\%U';
51
52new RMAN configuration parameters:
53CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\backup\%U';
54new RMAN configuration parameters are successfully stored
55
56查看数据文件的位置
57SQL> select name from v$datafile;
58
59
60确定原控制文件的路径
61SQL> select name from v$controlfile;
62
63查看log位置
64
65SQL>select name from V$ARCHIVED_LOG;
66
67查看log的信息
68SQL>select * from v$log_history;
69
70插入数据
71SQL> select * from sss;
72
73AA BB CC
74\---------- ---------- ---
75aa bb cc
76aa jj mm
77vv ss tt
78
79完全备份数据库
80RMAN>backup data
81
82再插入数据
83
84SQL> select * from sss;
85
86AA BB CC
87\---------- ---------- ---
88aa bb cc
89aa jj mm
90vv ss tt
91mm mm mm
92
93
94用DBCA删除数据库,删除相关的数据文件,保留redo log和归档日志。
95
96再用DBCA重建一个SID一样的数据库。
97
98RMAN>startup
99
100配置RMAN
101RMAN> configure channel device type disk format 'F:\backup\%U'; //注意和原来的格式一样。
102
103new RMAN configuration parameters:
104CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\backup\%U';
105new RMAN configuration parameters are successfully stored
106
107
108RMAN>startup nomount
109RMAN>restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup;
110再copy几份和原来一样的路径文件名。
111
112RMAN>shutdown immediate
113
114RMAN> startup mount
115
116connected to target database (not started)
117Oracle instance started
118RMAN-00571: ===========================================================
119RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
120RMAN-00571: ===========================================================
121RMAN-03002: failure of startup command at 08/03/2005 19:55:46
122ORA-01991: invalid password file 'F:\oracle\product.1.0\Db_1\DATABASE\PWDtest.ORA'
123
124
125重建本地认证文件
126C:\>orapwd file=F:\oracle\product.1.0\Db_1\DATABASE\PWDtest.ORA password=123456 entries=2;
127
128RMAN>shutdown immediate
129
130RMAN> startup mount
131
132database is already started
133RMAN-00571: ===========================================================
134RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
135RMAN-00571: ===========================================================
136RMAN-03002: failure of startup command at 08/03/2005 20:00:30
137RMAN-06189: current DBID 1863531774 does not match target mounted database (1862908416)
138
139RMAN>
140
141重新设置DBID
142
143RMAN> set DBID=1862908416
144
145executing command: SET DBID
146
147RMAN> startup mount
148
149database is already started
150
151RMAN> restore database;
152
153
154RMAN> recover database;
155
156Starting recover at 03-8月 -05
157using channel ORA_DISK_1
158
159starting media recovery
160
161archive log thread 1 sequence 1 is already on disk as file F:\ORACLE\PRODUCT.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\
1622005_08_03\O1_MF_1_1_1H2D39KL_.ARC
163archive log thread 1 sequence 2 is already on disk as file F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\REDO02.LOG
164archive log thread 1 sequence 3 is already on disk as file F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\REDO03.LOG
165archive log thread 1 sequence 4 is already on disk as file F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\REDO01.LOG
166archive log filename=F:\ORACLE\PRODUCT.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG05_08_03\O1_MF_1_1_1H2D39KL_.ARC thr
167ead=1 sequence=1
168archive log filename=F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\REDO02.LOG thread=1 sequence=2
169archive log filename=F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\REDO03.LOG thread=1 sequence=3
170archive log filename=F:\ORACLE\PRODUCT.1.0\ORADATA\TEST\REDO01.LOG thread=1 sequence=4
171media recovery complete
172Finished recover at 03-8月 -05
173
174RMAN>
175
176
177由于控制文件是用备份还原的需要resetlogs打开。
178SQL> alter database open resetlogs;
179
180
181SQL> select * from sss;
182
183AA BB CC
184\---------- ---------- ---
185aa bb cc
186aa jj mm
187vv ss tt
188mm mm mm //此行数据回来了,说明没有丢数据.</ret>