以下是模拟数据库在归档模式(archivelog)下丢失系统关键性system表空间及其数据文件。
该测试也提醒我们,在生成当中一定要将数据库开启到归档模式下,其次是有计划地进行有效的备份。
因为一旦丢失数据库系统关键的数据文件或者表空间之后,若没有备份,或者没有开启归档模式,导致
丢失部分数据。严重时候,导致控制文件或者系统关键数据文件丢失数据而导致数据库无法启动。
以下是测试的过程:
---查看数据库的归档模式:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
---打开rman备份工具:
[oracle@enmo ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 31 22:36:37 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=338469376) #这里显示的是目标库的信息#
---为了方便管理备份, 配置部分备份参数:
--设置备份文件的存放路径:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/backup/db_%U.rmn';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/backup/db_%U.rmn';
new RMAN configuration parameters are successfully stored
--打开自动备份控制文件与参数文件:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
---测试恢复系统关键数据文件:
---通过rman备份系统表空间:
RMAN> backup tablespace system; #只备份系统表空间
Starting backup at 31-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 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/PROD/system01.dbf
channel ORA_DISK_1: starting piece 1 at 31-OCT-16
channel ORA_DISK_1: finished piece 1 at 31-OCT-16
piece handle=/u01/app/backup/ db_01rjp9rn_1_1.rmn tag=TAG20161031T224511 comment=NONE #系统表空间备份的句柄
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:38
Finished backup at 31-OCT-16
Starting Control File and SPFILE Autobackup at 31-OCT-16 #因为上面已经设置自动备份控制文件
piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2016_10_31/o1_mf_s_926722009_d1gpbvth_.bkp comment=NONE
#控制文件以及参数参数文件备份的句柄
Finished Control File and SPFILE Autobackup at 31-OCT-16
#备份完成。
---查看两个生成的备份文件:
--system表空间的备份文件:
[oracle@enmo backup]$ ls
db_01rjp9rn_1_1.rmn
[oracle@enmo backup]$ ll
total 660032
-rw-r----- 1 oracle oinstall 675209216 Oct 31 22:46 db_01rjp9rn_1_1.rmn
[oracle@enmo backup]$
--控制文件与参数文件的备份文件:
[oracle@enmo backup]$ cd /u01/app/oracle/fast_recovery_area/PROD/
[oracle@enmo PROD]$ ls
autobackup control02.ctl onlinelog
[oracle@enmo PROD]$
[oracle@enmo PROD]$ cd autobackup/
[oracle@enmo autobackup]$ ls
2016_10_31
[oracle@enmo autobackup]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Oct 31 22:46 2016_10_31
[oracle@enmo autobackup]$
--尝试删除系统表空间system表空间:
SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/system01.dbf
--查看系统表空间数据文件的路径:
SQL> !ls /u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/system01.dbf
--删除系统表空间的数据文件:
!rm /u01/app/oracle/oradata/PROD/system01.dbf
#已删除。
--关闭数据库并尝试打开:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/system01.dbf'
#打开数据库时候报错,发现没有系统表空间的数据文件。
---恢复系统表空间数数据文件:
--通过RMAN的备份恢复system表空间:
[oracle@enmo ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 31 22:57:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=338469376, not open)
RMAN>
--加载系统表表空间:
RMAN> restore tablespace system;
Starting restore at 31-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_01rjp9rn_1_1.rmn
channel ORA_DISK_1: piece handle=/u01/app/backup/db_01rjp9rn_1_1.rmn tag=TAG20161031T224511
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:57
Finished restore at 31-OCT-16
#加载完成。
--恢复系统表空间:
RMAN> recover tablespace system;
Starting recover at 31-OCT-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-OCT-16
#恢复系统表空间及其数据文件完成。
--尝试打开数据库:
RMAN> alter database open;
database opened
RMAN>
SQL> select status from v$instance;
STATUS
------------
OPEN
#数据库已经恢复并打开。
---查看系统表空间的数据文件:
SQL> !ls /u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/system01.dbf
系统表空间及其数据文件恢复完成。
该测试也提醒我们,在生成当中一定要将数据库开启到归档模式下,其次是有计划地进行有效的备份。
因为一旦丢失数据库系统关键的数据文件或者表空间之后,若没有备份,或者没有开启归档模式,导致
丢失部分数据。严重时候,导致控制文件或者系统关键数据文件丢失数据而导致数据库无法启动。
以下是测试的过程:
---查看数据库的归档模式:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
---打开rman备份工具:
[oracle@enmo ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 31 22:36:37 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=338469376) #这里显示的是目标库的信息#
---为了方便管理备份, 配置部分备份参数:
--设置备份文件的存放路径:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/backup/db_%U.rmn';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/backup/db_%U.rmn';
new RMAN configuration parameters are successfully stored
--打开自动备份控制文件与参数文件:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
---测试恢复系统关键数据文件:
---通过rman备份系统表空间:
RMAN> backup tablespace system; #只备份系统表空间
Starting backup at 31-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 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/PROD/system01.dbf
channel ORA_DISK_1: starting piece 1 at 31-OCT-16
channel ORA_DISK_1: finished piece 1 at 31-OCT-16
piece handle=/u01/app/backup/ db_01rjp9rn_1_1.rmn tag=TAG20161031T224511 comment=NONE #系统表空间备份的句柄
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:38
Finished backup at 31-OCT-16
Starting Control File and SPFILE Autobackup at 31-OCT-16 #因为上面已经设置自动备份控制文件
piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2016_10_31/o1_mf_s_926722009_d1gpbvth_.bkp comment=NONE
#控制文件以及参数参数文件备份的句柄
Finished Control File and SPFILE Autobackup at 31-OCT-16
#备份完成。
---查看两个生成的备份文件:
--system表空间的备份文件:
[oracle@enmo backup]$ ls
db_01rjp9rn_1_1.rmn
[oracle@enmo backup]$ ll
total 660032
-rw-r----- 1 oracle oinstall 675209216 Oct 31 22:46 db_01rjp9rn_1_1.rmn
[oracle@enmo backup]$
--控制文件与参数文件的备份文件:
[oracle@enmo backup]$ cd /u01/app/oracle/fast_recovery_area/PROD/
[oracle@enmo PROD]$ ls
autobackup control02.ctl onlinelog
[oracle@enmo PROD]$
[oracle@enmo PROD]$ cd autobackup/
[oracle@enmo autobackup]$ ls
2016_10_31
[oracle@enmo autobackup]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Oct 31 22:46 2016_10_31
[oracle@enmo autobackup]$
--尝试删除系统表空间system表空间:
SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/system01.dbf
--查看系统表空间数据文件的路径:
SQL> !ls /u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/system01.dbf
--删除系统表空间的数据文件:
!rm /u01/app/oracle/oradata/PROD/system01.dbf
#已删除。
--关闭数据库并尝试打开:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/system01.dbf'
#打开数据库时候报错,发现没有系统表空间的数据文件。
---恢复系统表空间数数据文件:
--通过RMAN的备份恢复system表空间:
[oracle@enmo ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 31 22:57:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=338469376, not open)
RMAN>
--加载系统表表空间:
RMAN> restore tablespace system;
Starting restore at 31-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_01rjp9rn_1_1.rmn
channel ORA_DISK_1: piece handle=/u01/app/backup/db_01rjp9rn_1_1.rmn tag=TAG20161031T224511
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:57
Finished restore at 31-OCT-16
#加载完成。
--恢复系统表空间:
RMAN> recover tablespace system;
Starting recover at 31-OCT-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-OCT-16
#恢复系统表空间及其数据文件完成。
--尝试打开数据库:
RMAN> alter database open;
database opened
RMAN>
SQL> select status from v$instance;
STATUS
------------
OPEN
#数据库已经恢复并打开。
---查看系统表空间的数据文件:
SQL> !ls /u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/system01.dbf
系统表空间及其数据文件恢复完成。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2127384/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2127384/