[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;
Tablespace altered.
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 NO
7 rows selected.
--//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 ;
Database altered.
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;
Table created.
--//現在轉換為physical standby
--//備庫:
SYS@bookdg> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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 database convert to physical standby ;
alter database convert to physical standby
*
ERROR at line 1:
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;
Tablespace altered.
$ scp /mnt/ramdisk/book/tea01.dbf oracle@192.168.100.40:/mnt/ramdisk/book/
tea01.dbf 100% 40MB 40.0MB/s 00:01
--//備庫:
SYS@bookdg> alter database convert to physical standby ;
alter database convert to physical standby
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 6; no flashback log data.
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
--//不行:
SYS@bookdg> alter tablespace tea flashback on;
Tablespace altered.
SYS@bookdg> alter database convert to physical standby ;
alter database convert to physical standby
*
ERROR at line 1:
ORA-19926: Database cannot be converted at this time
--//根本不能這樣轉換.
SYS@bookdg> alter tablespace tea offline;
alter tablespace tea offline
*
ERROR at line 1:
ORA-01109: database not open
SYS@bookdg> alter database datafile 6 offline;
Database altered.
--//關閉備庫重來.
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 database convert to physical standby ;
Database altered.
SYS@bookdg> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
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 database datafile 6 online;
Database altered.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
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
7 rows selected.
--//日誌開始傳輸並應用.
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.
SYS@bookdg> alter database open read only;
Database altered.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> select count(*) from scott.empx;
COUNT(*)
----------
14
SYS@bookdg> select count(*) from scott.tt1;
select count(*) from scott.tt1
*
ERROR at line 1:
ORA-00942: table or view does not exist
--//測試時建立的表不存在.
--//主庫執行:
SCOTT@book> alter tablespace tea read write;
Tablespace altered.
SCOTT@book> delete from empx where rownum=1;
1 row deleted.
SCOTT@book> commit ;
Commit complete.
--//在備庫檢查,發現少一條記錄.
SYS@bookdg> select count(*) from scott.empx;
COUNT(*)
----------
13
總結:
--//這個在以後工作中注意,在轉換snapshot standby,注意表空間flaashback是否在on狀態.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2153207/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180423]關於閃回表與主外來鍵約束.txt
- [20230425]注意snapshot standby與activate standby的區別.txt
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- Oracle資料庫閃回區空間不足Oracle資料庫
- 臨時表空間和回滾表空間使用率查詢
- rac使用預設閃回區歸檔空間滿
- 16、表空間 建立表空間
- DM7閃回與閃回查詢
- MySQL 中的共享表空間與獨立表空間如何選擇MySql
- oracle 表空間Oracle
- PostgreSQL 表空間SQL
- Oracle表空間Oracle
- PostgreSQL：表空間SQL
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- 表空間利用率及表空間的補充
- UNDO表空間空間回收及切換
- [20210527]rman與undo表空間備份.txt
- KingbaseES的表空間
- 表空間限額
- 3.2. 表空間
- 只讀表空間
- oracle temp 表空間Oracle
- undo表空間容量
- 增加oracle表空間Oracle
- Configure innodb 表空間
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- Oracle回收站表閃回機制研究Oracle
- 12C關於CDB、PDB 回滾undo表空間的總結
- 刪使用者刪表空間的操作還能flashback回來嗎？
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- Postgresql表空間詳解SQL
- MySQL 傳輸表空間MySql
- MySQL InnoDB表空間加密MySql加密
- oracle表空間的整理Oracle