备份&恢复之四:非归档模式下的备份与恢复
来自piner:
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 设置成非归档模式.
[oracle@mzl ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 7 18:24:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database 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 16
Current log sequence 18
SQL>
4 创建表插入数据
SQL> drop table test;
Table dropped.
SQL> create table test(a int);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> select * from test;
A
----------
1
SQL> commit;
Commit complete.
5 查看表test在哪个表空间
SQL> select table_name,tablespace_name from dba_tables
2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST SYSTEM
原来在system表空间内
6 运行冷备份脚本
[oracle@mzl BackupDatabase]$ pwd
/home/mzl/BackupDatabase
[oracle@mzl BackupDatabase]$ vi coldbak.sql
#rem script.:coldbak.sql
#rem creater:mengzhaoliang
#rem data:2008/2/4
#rem desc:offline full backup database
#enter database
$ORACLE_HOME/bin/sqlplus "/as sysdba" <#--shutdown database
shutdown immediate
#--Copy Data file
!cp /u01/app/oracle/oradata/orcl/*.dbf /home/mzl/BackupDatabase/
#--Copy Control file
!cp /u01/app/oracle/oradata/orcl/*.ctl /home/mzl/BackupDatabase/
--Copy Log file
!cp /u01/app/oracle/oradata/orcl/*.log /home/mzl/BackupDatabase/
#--startup database
startup
<[oracle@mzl BackupDatabase]$ ./coldbak.sql
7 继续插入数据.
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
2
8 关闭数据库,把system01.dbf数据文件移出
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ ls
control01.ctl create.sql redo01.log risenet.dbf system01.dbf
control02.ctl example01.dbf redo02.log sqlnet.log undotbs01.dbf
control03.ctl perfstat.dbf redo03.log sysaux01.dbf users01.dbf
[oracle@mzl orcl]$ mkdir Old
[oracle@mzl orcl]$ mv system01.dbf Old
9 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 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'
其中alert_ORCL.log信息为:
Wed May 7 18:51:17 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3450.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 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
其中orcl_dbw0_3450.trc的信息为:
*** SESSION ID:(167.1) 2008-05-07 18:51:17.505
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 Error: 2: No such file or directory
Additional information: 3
查看哪些文件需要恢复:
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- ---------
1 ONLINE ONLINE 1091429 07-MAY-08
10 把原来的冷备份数据拷贝过来
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ ls
control01.ctl create.sql perfstat.dbf redo03.log sysaux01.dbf
control02.ctl example01.dbf redo01.log risenet.dbf undotbs01.dbf
control03.ctl Old redo02.log sqlnet.log users01.dbf
[oracle@mzl orcl]$ cp /home/mzl/BackupDatabase/system01.dbf .
11 执行recover操作,打开数据库
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from test;
A
----------
1
2
这里可以发现,数据库恢复成功,但在备份之后与崩溃之前的数据丢失了。
说明:
1、非归档模式下的恢复方案可选性很小,一般情况下只能有一种恢复方式,就是数据库的冷备份的完全恢复,仅仅需要拷贝原来的备份,需要recover。
2、这种情况下的恢复,可以完全恢复到备份的点上,但是可能是丢失数据的,在备份之后与崩溃之前的数据将全部丢失。
3、不管毁坏了多少数据文件或是联机日志或是控制文件,都可以通过这个办法恢复,因为这个恢复过程是Restore所有的冷备份文件,而这个备份点上的所有文件是一致的,与最新的数据库没有关系,就好比把数据库又放到了一个以前的“点”上。
4、对于非归档模式下,最好的办法就是采用OS的冷备份,建议不要用RMAN来作冷备份,效果不好,因为RMAN不备份联机日志,restore不能根本解决问题。
5、如果没有备份联机日志,如RMAN的备份,就需要利用不完全恢复(until cancel)的方法来重新创建联机日志文件
来自piner:
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 设置成非归档模式.
[oracle@mzl ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 7 18:24:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database 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 16
Current log sequence 18
SQL>
4 创建表插入数据
SQL> drop table test;
Table dropped.
SQL> create table test(a int);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> select * from test;
A
----------
1
SQL> commit;
Commit complete.
5 查看表test在哪个表空间
SQL> select table_name,tablespace_name from dba_tables
2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST SYSTEM
原来在system表空间内
6 运行冷备份脚本
[oracle@mzl BackupDatabase]$ pwd
/home/mzl/BackupDatabase
[oracle@mzl BackupDatabase]$ vi coldbak.sql
#rem script.:coldbak.sql
#rem creater:mengzhaoliang
#rem data:2008/2/4
#rem desc:offline full backup database
#enter database
$ORACLE_HOME/bin/sqlplus "/as sysdba" <#--shutdown database
shutdown immediate
#--Copy Data file
!cp /u01/app/oracle/oradata/orcl/*.dbf /home/mzl/BackupDatabase/
#--Copy Control file
!cp /u01/app/oracle/oradata/orcl/*.ctl /home/mzl/BackupDatabase/
--Copy Log file
!cp /u01/app/oracle/oradata/orcl/*.log /home/mzl/BackupDatabase/
#--startup database
startup
<[oracle@mzl BackupDatabase]$ ./coldbak.sql
7 继续插入数据.
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
2
8 关闭数据库,把system01.dbf数据文件移出
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ ls
control01.ctl create.sql redo01.log risenet.dbf system01.dbf
control02.ctl example01.dbf redo02.log sqlnet.log undotbs01.dbf
control03.ctl perfstat.dbf redo03.log sysaux01.dbf users01.dbf
[oracle@mzl orcl]$ mkdir Old
[oracle@mzl orcl]$ mv system01.dbf Old
9 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 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'
其中alert_ORCL.log信息为:
Wed May 7 18:51:17 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3450.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 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
其中orcl_dbw0_3450.trc的信息为:
*** SESSION ID:(167.1) 2008-05-07 18:51:17.505
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 Error: 2: No such file or directory
Additional information: 3
查看哪些文件需要恢复:
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- ---------
1 ONLINE ONLINE 1091429 07-MAY-08
10 把原来的冷备份数据拷贝过来
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ ls
control01.ctl create.sql perfstat.dbf redo03.log sysaux01.dbf
control02.ctl example01.dbf redo01.log risenet.dbf undotbs01.dbf
control03.ctl Old redo02.log sqlnet.log users01.dbf
[oracle@mzl orcl]$ cp /home/mzl/BackupDatabase/system01.dbf .
11 执行recover操作,打开数据库
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from test;
A
----------
1
2
这里可以发现,数据库恢复成功,但在备份之后与崩溃之前的数据丢失了。
说明:
1、非归档模式下的恢复方案可选性很小,一般情况下只能有一种恢复方式,就是数据库的冷备份的完全恢复,仅仅需要拷贝原来的备份,需要recover。
2、这种情况下的恢复,可以完全恢复到备份的点上,但是可能是丢失数据的,在备份之后与崩溃之前的数据将全部丢失。
3、不管毁坏了多少数据文件或是联机日志或是控制文件,都可以通过这个办法恢复,因为这个恢复过程是Restore所有的冷备份文件,而这个备份点上的所有文件是一致的,与最新的数据库没有关系,就好比把数据库又放到了一个以前的“点”上。
4、对于非归档模式下,最好的办法就是采用OS的冷备份,建议不要用RMAN来作冷备份,效果不好,因为RMAN不备份联机日志,restore不能根本解决问题。
5、如果没有备份联机日志,如RMAN的备份,就需要利用不完全恢复(until cancel)的方法来重新创建联机日志文件
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-263078/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-263078/