IT开放社区

表空间热备故障恢复实验

环境:

OS:Oracle Linux Server release 5.6

DB:Oracle 11.2.0.1

1.首先确认数据库为open状态并开启了归档;

itkaifang@SYS> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     30
Current log sequence           32
itkaifang@SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
itkaifang@SYS> startup mount;
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          306184192 bytes
Redo Buffers                2433024 bytes
Database mounted.
itkaifang@SYS> alter database archivelog;
Database altered.
itkaifang@SYS> alter database open;
Database altered.
itkaifang@SYS> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32
itkaifang@SYS>

2.创建新的表空间t_lob和新的表txt用于实验;

itkaifang@SYS> create tablespace t_a datafile'/u01/app/oracle/oradata/itkaifang/t_a01.dbf' size 50M autoextend on maxsize 2G;
Tablespace created.
itkaifang@SYS> create table t1 tablespace t_a as select * from dba_objects;
Table created.
itkaifang@SYS> select file#,name,status from v$datafile;
     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/itkaifang/system01.dbf     SYSTEM
         2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf     ONLINE
         3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf    ONLINE
         4 /u01/app/oracle/oradata/itkaifang/users01.dbf      ONLINE
         5 /u01/app/oracle/oradata/itkaifang/example01.dbf    ONLINE
         6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf        ONLINE
6 rows selected.
itkaifang@SYS> select count(*) from t1;
  COUNT(*)
----------
     72562
itkaifang@SYS>

3.在sqlplus中进行热备;

itkaifang@SYS> alter tablespace t_a begin backup;
Tablespace altered.
itkaifang@SYS> !cp /u01/app/oracle/oradata/itkaifang/t_a01.dbf /home/oracle/t_a01.dbf
itkaifang@SYS> !ls /home/oracle/t_a01.dbf
/home/oracle/t_a01.dbf
itkaifang@SYS> alter tablespace t_a end backup;
Tablespace altered.
itkaifang@SYS> alter system switch logfile;
System altered.
itkaifang@SYS>

4.模拟故障;

itkaifang@SYS> !rm /u01/app/oracle/oradata/itkaifang/t_a01.dbf
itkaifang@SYS> !ls /u01/app/oracle/oradata/itkaifang/t_a01.dbf
ls: /u01/app/oracle/oradata/itkaifang/t_a01.dbf: No such file or directory
itkaifang@SYS>

5.1以shutdown abort方式模拟断电关闭数据库恢复;

itkaifang@SYS> shutdown abort;
ORACLE instance shut down.
itkaifang@SYS> startup 
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          306184192 bytes
Redo Buffers                2433024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/itkaifang/t_a01.dbf'

itkaifang@SYS> select status from v$instance;
STATUS
------------
MOUNTED
itkaifang@SYS> !cp /home/oracle/t_a01.dbf /u01/app/oracle/oradata/itkaifang/t_a01.dbf
itkaifang@SYS> recover datafile 6;
Media recovery complete.
itkaifang@SYS> alter database open;
Database altered.
itkaifang@SYS> select count(*) from t1;
  COUNT(*)
----------
     72562
itkaifang@SYS> select file#,name,status from v$datafile;
     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/itkaifang/system01.dbf     SYSTEM
         2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf     ONLINE
         3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf    ONLINE
         4 /u01/app/oracle/oradata/itkaifang/users01.dbf      ONLINE
         5 /u01/app/oracle/oradata/itkaifang/example01.dbf    ONLINE
         6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf        ONLINE
6 rows selected.
itkaifang@SYS>

5.2以shutdown immediate一致性关闭数据库恢复;

itkaifang@SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
itkaifang@SYS> startup
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          306184192 bytes
Redo Buffers                2433024 bytes
Database mounted.
Database opened.
itkaifang@SYS> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/itkaifang/t_a01.dbf'

itkaifang@SYS> select status from v$instance;
STATUS
------------
OPEN
itkaifang@SYS> select file#,name,status from v$datafile;
     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/itkaifang/system01.dbf     SYSTEM
         2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf     ONLINE
         3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf    ONLINE
         4 /u01/app/oracle/oradata/itkaifang/users01.dbf      ONLINE
         5 /u01/app/oracle/oradata/itkaifang/example01.dbf    ONLINE
         6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf        RECOVER
6 rows selected.
itkaifang@SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
itkaifang@SYS> startup mount
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          306184192 bytes
Redo Buffers                2433024 bytes
Database mounted.
itkaifang@SYS> alter database datafile '/u01/app/oracle/oradata/itkaifang/t_a01.dbf' offline;
Database altered.
itkaifang@SYS> !cp /home/oracle/t_a01.dbf /u01/app/oracle/oradata/itkaifang/t_a01.dbf
itkaifang@SYS> recover datafile 6;
Media recovery complete.
itkaifang@SYS> alter database open;
Database altered.
itkaifang@SYS> select file#,name,status from v$datafile;
     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/itkaifang/system01.dbf     SYSTEM
         2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf     ONLINE
         3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf    ONLINE
         4 /u01/app/oracle/oradata/itkaifang/users01.dbf      ONLINE
         5 /u01/app/oracle/oradata/itkaifang/example01.dbf    ONLINE
         6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf        OFFLINE
6 rows selected.
itkaifang@SYS> alter database datafile '/u01/app/oracle/oradata/itkaifang/t_a01.dbf' online;
Database altered.
itkaifang@SYS> select count(*) from t1;
  COUNT(*)
----------
     72562
itkaifang@SYS>

---END---Good Luck---

相关文章

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
    随机文章
      站点信息
      • 文章总数:113
      • 页面总数:2
      • 分类总数:3
      • 标签总数:1
      • 评论总数:0
      • 浏览总数:45518
      左邻右舍

      BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

      IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:admin@itkaifang.com

      分享:

      支付宝

      微信