系统表空间的热备及模拟故障恢复
恢复系统表空间只能关闭数据库进行,系统表空间不能offline
1.检查是否开启归档模式
SYS@PROD1>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2.查看表空间及对应的数据文件
SYS@PROD1>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- -------------------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
2 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
4 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
5 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
6 TS_TEST /u01/app/oracle/oradata/PROD1/ts_01.dbf
6 rows selected.
3.备份系统表空间
SYS@PROD1>alter tablespace system begin backup;
Tablespace altered.
4.拷贝数据文件到备份位置
SYS@PROD1>! cp /u01/app/oracle/oradata/PROD1/system01.dbf /home/oracle/files/hot_backup
查看备份状态
SYS@PROD1>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 1115764 15-DEC-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 1110120 15-DEC-16
5 NOT ACTIVE 1107830 15-DEC-16
6 NOT ACTIVE 0
6 rows selected.
5.结束备份
SYS@PROD1>alter tablespace system end backup;
Tablespace altered.
查看状态,已完成
SYS@PROD1>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 1115764 15-DEC-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 1110120 15-DEC-16
5 NOT ACTIVE 1107830 15-DEC-16
6 NOT ACTIVE 0
6 rows selected.
删除系统表空间数据文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/system01.dbf
系统表空间不能offline
SYS@PROD1>alter database datafile '/u01/app/oracle/oradata/PROD1/system01.dbf' offline;
alter database datafile '/u01/app/oracle/oradata/PROD1/system01.dbf' offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
关闭数据库,模拟故障
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>
启动数据库,提示找不到数据文件
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 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/PROD1/system01.dbf'
SYS@PROD1>! cp /home/oracle/files/hot_backup/system01.dbf /u01/app/oracle/oradata/PROD1/system01.dbf
恢复system表空间
SYS@PROD1>recover datafile 1;
Media recovery complete.
打开数据库
SYS@PROD1>alter database open;
Database altered.
YS@PROD1>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_TEST ONLINE
7 rows selected.
SYS@PROD1>select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS
-------------------------------------------------- -------------------- ---------
/u01/app/oracle/oradata/PROD1/users01.dbf USERS AVAILABLE
/u01/app/oracle/oradata/PROD1/undotbs01.dbf UNDOTBS1 AVAILABLE
/u01/app/oracle/oradata/PROD1/sysaux01.dbf SYSAUX AVAILABLE
/u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM AVAILABLE
/u01/app/oracle/oradata/PROD1/example01.dbf EXAMPLE AVAILABLE
/u01/app/oracle/oradata/PROD1/ts_01.dbf TS_TEST AVAILABLE
恢复系统表空间只能关闭数据库进行,系统表空间不能offline
1.检查是否开启归档模式
SYS@PROD1>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2.查看表空间及对应的数据文件
SYS@PROD1>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- -------------------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
2 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
4 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
5 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
6 TS_TEST /u01/app/oracle/oradata/PROD1/ts_01.dbf
6 rows selected.
3.备份系统表空间
SYS@PROD1>alter tablespace system begin backup;
Tablespace altered.
4.拷贝数据文件到备份位置
SYS@PROD1>! cp /u01/app/oracle/oradata/PROD1/system01.dbf /home/oracle/files/hot_backup
查看备份状态
SYS@PROD1>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 1115764 15-DEC-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 1110120 15-DEC-16
5 NOT ACTIVE 1107830 15-DEC-16
6 NOT ACTIVE 0
6 rows selected.
5.结束备份
SYS@PROD1>alter tablespace system end backup;
Tablespace altered.
查看状态,已完成
SYS@PROD1>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 1115764 15-DEC-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 1110120 15-DEC-16
5 NOT ACTIVE 1107830 15-DEC-16
6 NOT ACTIVE 0
6 rows selected.
删除系统表空间数据文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/system01.dbf
系统表空间不能offline
SYS@PROD1>alter database datafile '/u01/app/oracle/oradata/PROD1/system01.dbf' offline;
alter database datafile '/u01/app/oracle/oradata/PROD1/system01.dbf' offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
关闭数据库,模拟故障
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>
启动数据库,提示找不到数据文件
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 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/PROD1/system01.dbf'
SYS@PROD1>! cp /home/oracle/files/hot_backup/system01.dbf /u01/app/oracle/oradata/PROD1/system01.dbf
恢复system表空间
SYS@PROD1>recover datafile 1;
Media recovery complete.
打开数据库
SYS@PROD1>alter database open;
Database altered.
YS@PROD1>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_TEST ONLINE
7 rows selected.
SYS@PROD1>select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS
-------------------------------------------------- -------------------- ---------
/u01/app/oracle/oradata/PROD1/users01.dbf USERS AVAILABLE
/u01/app/oracle/oradata/PROD1/undotbs01.dbf UNDOTBS1 AVAILABLE
/u01/app/oracle/oradata/PROD1/sysaux01.dbf SYSAUX AVAILABLE
/u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM AVAILABLE
/u01/app/oracle/oradata/PROD1/example01.dbf EXAMPLE AVAILABLE
/u01/app/oracle/oradata/PROD1/ts_01.dbf TS_TEST AVAILABLE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31400681/viewspace-2130718/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31400681/viewspace-2130718/