[20180423]表空間閃回與snapshot standby

[20180423]flashback tablespace與snapshot standby.txt

--//預設建立表空間是打開flashback on,如果某個表空間flashback off,在dg啟動snapshot standby時注意,可能"回不來",

--//通過測試說明問題.

1.環境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.設定備庫tea表空間關閉flashback.

SCOTT@book> alter tablespace tea flashback off;

Tablespace altered.

SCOTT@book> select * from v$tablespace;

         TS# NAME      INC BIG FLA ENC

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

           0 SYSTEM    YES NO  YES

           1 SYSAUX    YES NO  YES

           2 UNDOTBS1  YES NO  YES

           4 USERS     YES NO  YES

           3 TEMP      NO  NO  YES

           6 EXAMPLE   YES NO  YES

           7 TEA       YES NO  NO

7 rows selected.

--//tea表空間FLASHBACK_ON設定為NO.注意這些資訊應該記錄在控制檔案,你可以發現備庫還是On .

--//備庫:

SYS@bookdg> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SYS@bookdg> select * from v$tablespace;

       TS# NAME                 INC BIG FLA ENC

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

         0 SYSTEM               YES NO  YES

         1 SYSAUX               YES NO  YES

         2 UNDOTBS1             YES NO  YES

         4 USERS                YES NO  YES

         3 TEMP                 NO  NO  YES

         6 EXAMPLE              YES NO  YES

         7 TEA                  YES NO  YES

7 rows selected.

--//備庫還是yes,tea表空間.

SYS@bookdg> alter tablespace tea flashback off;

alter tablespace tea flashback off

                     *

ERROR at line 1:

ORA-16000: database open for read-only access

--//無法在open read only修改.

SYS@bookdg> startup mount

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

SYS@bookdg> alter tablespace tea flashback off;

         7 TEA                  YES NO  NO

--//OK,現在成功.

3.備庫打開snapshot standby:

--//參考http://blog.itpub.net/267265/viewspace-2134547/

--//實際上就是保證存儲點,隻要閃回區足夠,許多dml操作沒有問題,在轉換physical standby時,傳回原來的存儲點;

--//備庫:

SYS@bookdg> alter database convert to snapshot standby;

Database altered.

SYS@bookdg> select database_role from v$database;

DATABASE_ROLE

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

SNAPSHOT STANDBY

SYS@bookdg> alter database open ;

SCOTT@bookdg> create table tt1 tablespace tea as select * from dba_objects;

Table created.

--//在主庫也産生一些日志對于tea表空間:

SCOTT@book> create table empx tablespace tea as select * from emp;

--//現在轉換為physical standby

SYS@bookdg> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Database mounted.

SYS@bookdg> alter database convert to physical standby ;

alter database convert to physical standby

*

ORA-38753: Cannot flashback data file 6; no flashback log data.

ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

--//可以發現備庫的資料檔案6無法轉換,這個應該引起足夠重視,在備庫轉換為snapshot standby時,注意檢查表空間是否flashvback是否都是on的狀态.

$ oerr ora 38753

38753, 00000, "Cannot flashback data file %s; no flashback log data."

// *Cause:  An attempt to perform a FLASHBACK DATABASE failed because the file

//          does not have enough flashback log data to cover the time to

//          flash back.  Either the file did not have flashback generation

//          enabled for it, or had flashback generation turned off for it

//          some time during the time span of the flashback.

// *Action: The file cannot be flashed back.  The file must be taken offline

//          or the tablespace dropped before continuing with the FLASHBACK

//          DATABASE command.

4.恢複:

--//簡單一點,主庫tea表空間設定為read only;

--//主庫:

SCOTT@book> alter tablespace tea read only;

$ scp /mnt/ramdisk/book/tea01.dbf [email protected]:/mnt/ramdisk/book/

tea01.dbf                            100%   40MB  40.0MB/s   00:01

SYS@bookdg> alter database convert to physical standby ;

ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

--//不行:

SYS@bookdg> alter tablespace tea flashback on;

ORA-19926: Database cannot be converted at this time

--//根本不能這樣轉換.

SYS@bookdg> alter tablespace tea offline;

alter tablespace tea offline

ORA-01109: database not open

SYS@bookdg> alter database datafile 6 offline;

--//關閉備庫重來.

SYS@bookdg> startup mount

SYS@bookdg> shutdown immediate

ORA-01507: database not mounted

SYS@bookdg> alter database datafile 6 online;

SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;

SYS@bookdg> @ &r/dg/dg

PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS

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

ARCH         2394 CONNECTED    ARCH     N/A          0          0          0          0          0

ARCH         2396 CONNECTED    ARCH     N/A          0          0          0          0          0

ARCH         2398 CONNECTED    ARCH     N/A          0          0          0          0          0

RFS          2403 IDLE         UNKNOWN  N/A          0          0          0          0          0

RFS          2405 IDLE         LGWR     2            1        789         50          1          0

ARCH         2400 CLOSING      ARCH     4            1        788          1        183          0

MRP0         2407 APPLYING_LOG N/A      N/A          1        789         50     102400          0

--//日志開始傳輸并應用.

SYS@bookdg> alter database recover managed standby database cancel ;

SYS@bookdg> alter database open read only;

SYS@bookdg> select count(*) from scott.empx;

  COUNT(*)

----------

        14

SYS@bookdg> select count(*) from scott.tt1;

select count(*) from scott.tt1

                           *

ORA-00942: table or view does not exist

--//測試時建立的表不存在.

--//主庫執行:

SCOTT@book> alter tablespace tea read write;

SCOTT@book> delete from empx where rownum=1;

1 row deleted.

SCOTT@book> commit ;

Commit complete.

--//在備庫檢查,發現少一條記錄.

SYS@bookdg> select count(*) from scott.empx;

        13

總結:

--//這個在以後工作中注意,在轉換snapshot standby,注意表空間flaashback是否在on狀态.