【物理热备】（下）备份恢复系统表空间 手工备份恢复

三：系统表空间的热备及模拟故障恢复

恢复数据库只能选择关闭恢复，因为系统表空间只能online；

1. （前提）物理热备必须开启归档，查看：

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     13

Next log sequence to archive   15

Current log sequence           15

SYS@ORA11GR2>

 

2. 查看备份文件绝对路径（重要）

SYS@ORA11GR2>select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORA11GR2/control01.ctl

/u01/app/oracle/oradata/ORA11GR2/control02.ctl

/u01/app/FRA/control03.ctl

 

SYS@ORA11GR2>select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORA11GR2/system01.dbf

/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

/u01/app/oracle/oradata/ORA11GR2/users01.dbf

/u01/app/oracle/oradata/ORA11GR2/example01.dbf

/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

/u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

 

7 rows selected.

 

SYS@ORA11GR2>select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORA11GR2/redo03.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/FRA/redo01_b.log

/u01/app/FRA/redo02_b.log

/u01/app/FRA/redo03_b.log

 

9 rows selected.

 

3. 查看数据库的表空间及对应文件

SYS@ORA11GR2>select tablespace_name,file_name from dba_data_files;

TABLESPACE FILE_NAME

---------- -------------------------------------------------------

USERS      /u01/app/oracle/oradata/ORA11GR2/users01.dbf

UNDOTBS1   /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

SYSAUX     /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

SYSTEM     /u01/app/oracle/oradata/ORA11GR2/system01.dbf

EXAMPLE    /u01/app/oracle/oradata/ORA11GR2/example01.dbf

TS_ORA11GR2 /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

UNDOTBS2   /u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

 

7 rows selected.

 

4.备份系统表空间：

SYS@ORA11GR2>alter tablespace SYSTEM begin backup;

 

Tablespace altered.

 

SYS@ORA11GR2>

 

——查看备份状态：

SYS@ORA11GR2>select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 ACTIVE                1548675 27-SEP-16

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE                  0

         5 NOT ACTIVE                  0

         6 NOT ACTIVE            1543727 27-SEP-16

         7 NOT ACTIVE                  0

 

7 rows selected.

 

SYS@ORA11GR2>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;

 

   FILE_ID TABLESPACE FILE_NAME

---------- ---------- -------------------------------------------------------

         1 SYSTEM     /u01/app/oracle/oradata/ORA11GR2/system01.dbf

         2 SYSAUX     /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

         3 UNDOTBS1   /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

         4 USERS      /u01/app/oracle/oradata/ORA11GR2/users01.dbf

         5 EXAMPLE    /u01/app/oracle/oradata/ORA11GR2/example01.dbf

         6 TS_ORA11GR2 /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

         7 UNDOTBS2   /u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

 

7 rows selected.

 

5. 回到操作系统层copy 表空间system：

[oracle@wang ORA11GR2]$ pwd

/u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2]$ cp system01.dbf /home/oracle/

[oracle@wang ORA11GR2]$cd

[oracle@wang ~]$ pwd

/home/oracle

[oracle@wang ~]$ ls system01.dbf

system01.dbf

[oracle@wang ~]$

 

6.结束备份：

SYS@ORA11GR2>alter tablespace system end backup;

 

Tablespace altered.

 

——查看备份状态：

SYS@ORA11GR2>select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE            1548675 27-SEP-16

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE                  0

         5 NOT ACTIVE                  0

         6 NOT ACTIVE            1543727 27-SEP-16

         7 NOT ACTIVE                  0

 

7 rows selected.

 

SYS@ORA11GR2>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;

 

   FILE_ID TABLESPACE FILE_NAME

---------- ---------- -------------------------------------------------------

         1 SYSTEM     /u01/app/oracle/oradata/ORA11GR2/system01.dbf

         2 SYSAUX     /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

         3 UNDOTBS1   /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

         4 USERS      /u01/app/oracle/oradata/ORA11GR2/users01.dbf

         5 EXAMPLE    /u01/app/oracle/oradata/ORA11GR2/example01.dbf

         6 TS_ORA11GR2 /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

         7 UNDOTBS2   /u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

 

7 rows selected.

 

SYS@ORA11GR2>

 

1.  操作删除system表空间下的数据文件

[oracle@wang ORA11GR2]$ pwd

/u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2]$ ls system01.dbf

system01.dbf

[oracle@wang ORA11GR2]$ rm system01.dbf

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ ls system01.dbf

ls: system01.dbf: No such file or directory

[oracle@wang ORA11GR2]$

 

——关库：（模拟故障）

SYS@ORA11GR2>shutdown immediate;

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

 

2.  启动数据库：（显示故障）

SYS@ORA11GR2>startup

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             457179200 bytes

Database Buffers          268435456 bytes

Redo Buffers                2842624 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'

 

9.将表空间置于offline进行恢复

SYS@ORA11GR2>alter tablespace system offline;

alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

（system等系统表空间不能置于offline下进行恢复，必须关库后恢复）

 

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

——将备份的system01.dbf复制到/u01/app/oracle/oradata/ORA11GR2/下

[oracle@wang ~]$ pwd

/home/oracle

[oracle@wang ~]$ ls

system01.dbf  ts_ora11gr2_01.dbf

[oracle@wang ~]$

[oracle@wang ~]$ cp system01.dbf /u01/app/oracle/oradata/ORA11GR2/

验证：

[oracle@wang ORA11GR2]$ pwd

/u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ ls system01.dbf

system01.dbf

[oracle@wang ORA11GR2]$

 

——再次启动数据库

SYS@ORA11GR2>startup

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             457179200 bytes

Database Buffers          268435456 bytes

Redo Buffers                2842624 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'

 

——恢复表空间system;（热备开启了归档，所以可以recover）

SYS@ORA11GR2>recover tablespace system

Media recovery complete.

SYS@ORA11GR2>select status from v$instance;

 

STATUS

------------

MOUNTED

 

SYS@ORA11GR2>alter database open;

 

Database altered.

 

SYS@ORA11GR2>select status from v$instance;

 

STATUS

------------

OPEN

 

完成！！！！！！！！！！！！！！！！

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126509/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126509/