oracle实验记录 (oracle 10G dataguard(7)physical dg转换)
physical dg 角色转换
SQL> select table_name from dict where table_name like '%DATAGUARD%';
TABLE_NAME
------------------------------
V$DATAGUARD_STATUS
V$DATAGUARD_CONFIG
V$DATAGUARD_STATS
GV$DATAGUARD_STATUS
GV$DATAGUARD_CONFIG
SQL> desc v$dataguard_stats;
名称 是否为空? 类型
----------------------------------------- -------- --------------------------
NAME VARCHAR2(32)
VALUE VARCHAR2(64)
UNIT VARCHAR2(30)
TIME_COMPUTED VARCHAR2(30)
NAME VARCHAR2(32) The parameter name can be any of the following:
APPLY FINISH TIME: Estimated time before log apply services will finish applying the redo data available on the standby database. If there are gaps in the log files, this parameter shows the time it will take to resolve the gap with the lowest SCN.
APPLY LAG: Amount of time that the application of redo data on the standby database lags behind the primary database.
TRANSPORT LAG: How much redo data (in time units) generated by the primary database is not available or applicable on the standby database at the time of computation.
Note: This parameter includes the redo data that has not been transmitted to the standby database and redo data that is available on the standby database but has not been applied yet, such as the redo data for resolving a gap.
ESTIMATED STARTUP TIME: Estimated time the physical standby database was started. This is not displayed for logical standby databases.
STANDBY HAS BEEN OPEN: Indicates if the physical standby database was opened in read-only mode at any time since the last time the database was started. The parameter value indicates if the instance must be stopped and restarted after a failover. If the physical standby database has been opened in read- only mode since the last time it was started and it is the target of a failover, you must shut down the database and restart it in read/write mode. If the primary database must be shutdown and restarted, use the sum of APPLY FINISH TIME and ESTIMATED STARTUP TIME to obtain an estimated failover time (the estimate assumes SHUTDOWN ABORT).
VALUE VARCHAR2(64) Value of the parameter. For example, the APPLY FINISH TIME parameter value +00 00:00:01.7 indicates the standby database needs 1.7 seconds to finish applying the remaining redo data.
UNIT VARCHAR2(30) Time unit for the parameter, displayed as day (2) to second (n) interval, where n is 0 or 1.
TIME_COMPUTED VARCHAR2(30) Local time on the standby database when the value of the parameter is computed or sampled.
SQL> col name format a20
SQL> col value format a15
SQL> col time_computed format a20
SQL> col unit format a10
SQL> select * from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED
-------------------- --------------- ---------- --------------------
apply finish time +00 00:00:06.3 day(2) to 10-11月-2009 13:51:5
second(1) 4
interval
apply lag +00 02:50:37 day(2) to 10-11月-2009 13:51:5
second(0) 4
interval
estimated startup ti 13 second 10-11月-2009 13:51:5
me 4
NAME VALUE UNIT TIME_COMPUTED
-------------------- --------------- ---------- --------------------
standby has been ope N 10-11月-2009 13:51:5
n 4
transport lag +00 00:00:00 day(2) to 10-11月-2009 13:51:5
second(0) 4
interval
分析
apply finish time :oracle认为应用完现在的日志需要6S
apply lag ：在过去2小时 50分中PRIMARY产生传输的redo 都没应用
当角色切换时候 将出现一个 db_role_change的事件(与startup,shutdown ,database error 一样) 是4种系统事件
audit 对于 sysdba sysoper startup,shutdown conn 等操作 defautlt 都在 记录
win下 在事件查看器中可以看
unix 在 $ORACLE_HOME/rdbms/audit
若想启动对sysdba or sysoper 的其他审记 则
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE 改为true(default false)
检查 是否可以切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;(primary)
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
primary应该为to standby，如果不是检查是否参数配置有问题，或 若是sessions active 则需要加witch session shutdown (关闭所有活动会话)
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
SQL> select process,client_process,status,sequence# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CLOSING 110
ARCH ARCH CLOSING 111
RFS LGWR IDLE 112
RFS ARCH IDLE 0
RFS UNKNOWN IDLE 0
MRP0 N/A WAIT_FOR_LOG 112
已选择6行。
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED～～～～～～～～～～～～需要primary先切换
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdo (primary)
wn;
数据库已更改。
这句命令的意义：
1.shutdown primary,终止所有活动会话,shutdown 会产生一个full checkpoint
2.传任何未归档的redo(primary online redo file )或者primary 还未传到standby的archivedlog到 standby并应用，这是为了和primary 同步一致
3.添加重做终止标记到要归档的最后一个日志文件头（primary）
4.创建current controlfile备份
5.将current controlfile转换为standby controlfile
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;(standby )
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;(standby)
数据库已更改。
SQL> select db_unique_name,database_role ,open_mode from v$database
select db_unique_name,database_role ,open_mode from v$database
*
第 1 行出现错误:
ORA-01507: 未装载数据库
SQL> alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-01100: 数据库已装载
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01507: 未装载数据库
切换完后 都是mount状态 但需要重起 ,startup force，如果physiacal standby自从上次启动过后曾经以只读模式打开，必须关闭目标备数据
库并重启
SQL> select db_unique_name,database_role ,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xh PHYSICAL STANDBY READ ONLY
SQL> select db_unique_name,database_role ,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PRIMARY READ WRITE
SQL> show parameter log_archive_dest_2 (primary standby1)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=xh valid_for=(online_l
ogfiles,primary_role) db_uniqu
e_name=xh
SQL> select * from t2; (primary standby1)
A
----------
1
3
2
4
5
5
6
888
888888
999999
已选择10行。
SQL> delete from t2; (primary standby1)
已删除10行。
SQL> commit; (primary standby1)
提交完成。
SQL> alter system switch logfile; (primary standby1)
系统已更改。
SQL> select process,client_process,status,sequence# from v$managed_standby;(standby xh)
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
SQL> alter database recover managed standby database disconnect from session; (standby xh)
数据库已更改。
SQL> select process,client_process,status,sequence# from v$managed_standby; (standby xh)
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
MRP0 N/A WAIT_FOR_LOG 0
SQL> alter database recover managed standby database cancel;(standby xh)
数据库已更改。
SQL> alter database open;(standby xh)
数据库已更改。
SQL> select *from t2;(standby xh)
未选定行
关于有损切换
primary突然崩溃
1.首先查询v$archive_gap 看 是否崩溃时候有未发送到standby 的archivedlog
有的话，手动复制过来(v$archive_gap中low_sequence#-high_sequence# 这之间的都考过来)
alter database register physical logfile 'xxxxx' 手工注册到standby
2.可以通过v$archived_log.sequence# 查看 standby的最高 archive log sequence#，将primary 中未传送到standby的 archived log 都 手动复制过来 并注册
3.在standby 执行alter database recover managed standby finish force
4.转换physical standby 到primary
alter database commit to switchover to primary
（这中转换的结果是单方向的，这个physical standby不能够在转换成 standby ，故障转移添加一个重做结束的标识到最后一个归档的日志文件的头部，并发送重做到所
有允许的对于主数据库有效的目的地（使用VALID_FOR=(PRIMARY_ROLE, *_LOGFILES)
或VALID_FOR=(ALL_ROLE, *_LOGFILES)属性指定））
5.如果这个standby(现在转换成的primary)以前未被 read only mode打开过
可以执行alter database open 打开这个新 primary
或shutdown immediate startup重启这个新primary
关于primary重命名文件
看下环境
SQL> select db_unique_name,database_role ,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PHYSICAL STANDBY READ ONLY
SQL> select db_unique_name,database_role ,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xh PRIMARY READ WRITE
SQL> select name from v$datafile;(primary)
NAME
--------------------------------------------------------------------------------
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSTEM01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\UNDOTBS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSAUX01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\USERS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\EXAMPLE01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR.DBF
G:\XHTR2.DBF
G:\XHTR3.DBF
G:\XHTR4.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR5.DBF
已选择10行。
SQL> select name from v$datafile; (standby)
NAME
--------------------------------------------------------------------------------
E:\STANDBY\DATAFILE\SYSTEM01.DBF
E:\STANDBY\DATAFILE\UNDOTBS01.DBF
E:\STANDBY\DATAFILE\SYSAUX01.DBF
E:\STANDBY\DATAFILE\USERS01.DBF
E:\STANDBY\DATAFILE\EXAMPLE01.DBF
E:\STANDBY\DATAFILE\XHTR.DBF
E:\STANDBY\DATAFILE\XHTR2.DBF
E:\STANDBY\DATAFILE\XHTR3
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR4_5H2778OZ_.DBF
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR5_5H28JPVT_.DBF
已选择10行。
SQL> show parameter standby_fi (standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter tablespace xhtr offline;(primary)
表空间已更改。
SQL> host copy G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR.DBF G:\ORACLE\PRODUCT\10 (primary)
.2.0\ORADATA\XH\XHTRM.DBF
已复制 1 个文件。
SQL> alter tablespace xhtr rename datafile 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\ (primary)
XHTR.DBF' to 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTRM.DBF'
2 ;
表空间已更改。
SQL> alter tablespace xhtr online; (primary)
表空间已更改。
SQL> select name from v$datafile; (primary)
NAME
--------------------------------------------------------------------------------
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSTEM01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\UNDOTBS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSAUX01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\USERS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\EXAMPLE01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTRM.DBF*****************主库rename datafile
G:\XHTR2.DBF
G:\XHTR3.DBF
G:\XHTR4.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR5.DBF
已选择10行。
此时 standby需要也手动指向那个， standby_file_management为auto也没用
SQL> startup force mount
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 79692480 bytes
Database Buffers 201326592 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> host copy E:\STANDBY\DATAFILE\XHTR.DBF E:\STANDBY\DATAFILE\XHTRM.DBF
已复制 1 个文件。
SQL> alter database rename file 'E:\STANDBY\DATAFILE\XHTR.DBF' to 'E:\STANDBY\DA
TAFILE\XHTRM.DBF'
2 ;
alter database rename file 'E:\STANDBY\DATAFILE\XHTR.DBF' to 'E:\STANDBY\DATAFIL
E\XHTRM.DBF'
*
第 1 行出现错误:
ORA-01511: 重命名日志/数据文件时出错
ORA-01275: 自动进行备用文件管理时, 不允许进行 RENAME 操作。
SQL> alter system set standby_file_management='MANUAL';
系统已更改。
SQL> alter database rename file 'E:\STANDBY\DATAFILE\XHTR.DBF' to 'E:\STANDBY\DA
TAFILE\XHTRM.DBF'
2 ;
数据库已更改。
SQL> alter system set standby_file_management='auto';
系统已更改。
SQL> alter database open;
数据库已更改。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\STANDBY\DATAFILE\SYSTEM01.DBF
E:\STANDBY\DATAFILE\UNDOTBS01.DBF
E:\STANDBY\DATAFILE\SYSAUX01.DBF
E:\STANDBY\DATAFILE\USERS01.DBF
E:\STANDBY\DATAFILE\EXAMPLE01.DBF
E:\STANDBY\DATAFILE\XHTRM.DBF
E:\STANDBY\DATAFILE\XHTR2.DBF
E:\STANDBY\DATAFILE\XHTR3
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR4_5H2778OZ_.DBF
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR5_5H28JPVT_.DBF
已选择10行。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-619717/ ，如需转载，请注明出处，否则将追究法律责任。
