Data Guard 主端OFFLINE資料檔案和表空間

張玉龍發表於2016-09-23


主端

col FILE_NAME format a40
select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                              MB ONLINE_
---------- ------------------------------ ---------- -------
         4 /data/orcl/users01.dbf                  5 ONLINE
         3 /data/orcl/undotbs01.dbf               70 ONLINE
         2 /data/orcl/sysaux01.dbf               510 ONLINE
         1 /data/orcl/system01.dbf               740 SYSTEM
         5 /data/orcl/ceshi1.dbf                 100 ONLINE
         6 /data/orcl/ceshi2.dbf                 100 ONLINE
備端

SQL> col FILE_NAME format a40
SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                                        MB ONLINE_
---------- ---------------------------------------- ---------- -------
         4 /data/orcldg/datafile/users01.dbf                 5 ONLINE
         3 /data/orcldg/datafile/undotbs01.dbf              70 ONLINE
         2 /data/orcldg/datafile/sysaux01.dbf              510 ONLINE
         1 /data/orcldg/datafile/system01.dbf              740 SYSTEM
         5 /data/orcldg/datafile/ceshi1.dbf                100 RECOVER
         6 /data/orcldg/datafile/ceshi2.dbf                100 RECOVER

主端

SQL> create user ceshi identified by ceshi default tablespace ceshi1;

SQL> grant connect,resource,create view to ceshi;

SQL> create table ceshi.ceshitab as select * from scott.emp;

SQL> select count(*) from ceshi.ceshitab;

  COUNT(*)
----------
        14
備端

SQL> select count(*) from ceshi.ceshitab;

  COUNT(*)
----------
        14

主端OFFLINE資料檔案

SQL> alter database datafile 5 offline;

Database altered.

SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                              MB ONLINE_
---------- ------------------------------ ---------- -------
         4 /data/orcl/users01.dbf                  5 ONLINE
         3 /data/orcl/undotbs01.dbf               70 ONLINE
         2 /data/orcl/sysaux01.dbf               510 ONLINE
         1 /data/orcl/system01.dbf               740 SYSTEM
         5 /data/orcl/ceshi1.dbf                     RECOVER
         6 /data/orcl/ceshi2.dbf                 100 ONLINE

SQL> select * from ceshi.ceshitab;
select * from ceshi.ceshitab
                    *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/data/orcl/ceshi1.dbf'

備端仍然能查出資料

SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                                        MB ONLINE_
---------- ---------------------------------------- ---------- -------
         4 /data/orcldg/datafile/users01.dbf                 5 ONLINE
         3 /data/orcldg/datafile/undotbs01.dbf              70 ONLINE
         2 /data/orcldg/datafile/sysaux01.dbf              510 ONLINE
         1 /data/orcldg/datafile/system01.dbf              740 SYSTEM
         5 /data/orcldg/datafile/ceshi1.dbf                100 RECOVER
         6 /data/orcldg/datafile/ceshi2.dbf                100 RECOVER

6 rows selected.

SQL> select count(*) from ceshi.ceshitab;

  COUNT(*)
----------
        14

主端ONNLINE資料檔案

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.


SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                              MB ONLINE_
---------- ------------------------------ ---------- -------
         4 /data/orcl/users01.dbf                  5 ONLINE
         3 /data/orcl/undotbs01.dbf               70 ONLINE
         2 /data/orcl/sysaux01.dbf               510 ONLINE
         1 /data/orcl/system01.dbf               740 SYSTEM
         5 /data/orcl/ceshi1.dbf                 100 ONLINE
         6 /data/orcl/ceshi2.dbf                 100 ONLINE

6 rows selected.

SQL> select count(*) from ceshi.ceshitab;

  COUNT(*)
----------
        14


主端OFFLINE表空間

SQL> alter tablespace ceshi1 offline;

Tablespace altered.

SQL> select count(*) from ceshi.ceshitab;
select count(*) from ceshi.ceshitab
                           *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/data/orcl/ceshi1.dbf'


SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                              MB ONLINE_
---------- ------------------------------ ---------- -------
         4 /data/orcl/users01.dbf                  5 ONLINE
         3 /data/orcl/undotbs01.dbf               70 ONLINE
         2 /data/orcl/sysaux01.dbf               510 ONLINE
         1 /data/orcl/system01.dbf               740 SYSTEM
         5 /data/orcl/ceshi1.dbf                     OFFLINE
         6 /data/orcl/ceshi2.dbf                 100 ONLINE

備端不能查出資料

SQL> select count(*) from ceshi.ceshitab;
select count(*) from ceshi.ceshitab
                           *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/data/orcldg/datafile/ceshi1.dbf'


SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                                        MB ONLINE_
---------- ---------------------------------------- ---------- -------
         4 /data/orcldg/datafile/users01.dbf                 5 ONLINE
         3 /data/orcldg/datafile/undotbs01.dbf              70 ONLINE
         2 /data/orcldg/datafile/sysaux01.dbf              510 ONLINE
         1 /data/orcldg/datafile/system01.dbf              740 SYSTEM
         5 /data/orcldg/datafile/ceshi1.dbf                    RECOVER
         6 /data/orcldg/datafile/ceshi2.dbf                100 RECOVER

主端ONLINE表空間

SQL> alter tablespace ceshi1 online;

Tablespace altered.

SQL> 
SQL> 
SQL>  select count(*) from ceshi.ceshitab;

  COUNT(*)
----------
        14

SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                              MB ONLINE_
---------- ------------------------------ ---------- -------
         4 /data/orcl/users01.dbf                  5 ONLINE
         3 /data/orcl/undotbs01.dbf               70 ONLINE
         2 /data/orcl/sysaux01.dbf               510 ONLINE
         1 /data/orcl/system01.dbf               740 SYSTEM
         5 /data/orcl/ceshi1.dbf                 100 ONLINE
         6 /data/orcl/ceshi2.dbf                 100 ONLINE

6 rows selected.

備端可以檢視資料

SQL> select file_id, file_name ,bytes/1024/1024 as MB,online_status from dba_data_files;

   FILE_ID FILE_NAME                                        MB ONLINE_
---------- ---------------------------------------- ---------- -------
         4 /data/orcldg/datafile/users01.dbf                 5 ONLINE
         3 /data/orcldg/datafile/undotbs01.dbf              70 ONLINE
         2 /data/orcldg/datafile/sysaux01.dbf              510 ONLINE
         1 /data/orcldg/datafile/system01.dbf              740 SYSTEM
         5 /data/orcldg/datafile/ceshi1.dbf                    RECOVER
         6 /data/orcldg/datafile/ceshi2.dbf                100 RECOVER

6 rows selected.

SQL> select count(*) from ceshi.ceshitab;

  COUNT(*)
----------
        14

所以對於一些資料檔案級別的操作還是需要謹慎,主庫對資料檔案做OFFLINE之類的操作,對於備庫是敏感的。
















相關文章