1.启动和关闭Physical Standby Database
1)启动Physical Standby Database
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 88083648 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; --启用redo Apply
Database altered.
SQL> select name,database_role from v$database; --查看数据库角色
NAME DATABASE_ROLE
--------- ----------------
MYORCL PHYSICAL STANDBY
在操作系统下,查看redo应用是否启用,如下:
[oracle@xiaoru ~]$ ps -ef |grep ora_mrp
oracle 2155 1 0 13:28 ? 00:00:00 ora_mrp0_orcl --有该进程表示standby启用了redo应用
oracle 2163 1808 0 13:29 pts/2 00:00:00 grep ora_mrp
在SQL命令行下,查看redo应用是否启用,如下:
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG --有MRP0进程在WAIT_FOR_LOG状态时表示启动了Redo应用
SQL> select distinct recovery_mode,protection_mode from v$archive_dest_status
2 where database_mode='MOUNTED-STANDBY';
RECOVERY_MODE PROTECTION_MODE
----------------------- --------------------
MANAGED MAXIMUM PERFORMANCE
批注:recovery_mode为 MANAGED | MANAGED REAL TIME APPLY 就表示启用了Redo应用; 为 IDLE 时表示没有启用Redo应用。
SQL> alter database recover managed standby database cancel; --取消redoApply接收
Database altered.
SQL> alter database open;
Database altered.
批注:alter database open会直接打开到read only 模式,如果要再切换会redo apply模式,直接执行
alter database recover managed standby database disconnect from session即可。
2)关闭physical standby Database
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; --备库查询是否在接收redo 应用
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
SQL> alter system switch logfile; --关闭备库之前先在主库切换日志
System altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --取消redo应用
Database altered.
SQL> SHUTDOWN IMMEDIATE; --关闭数据库
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
2.影响物理standby的主库事件
多数情况下,Primary数据库的修改会随着REDO数据传播到物理Standby数据库端并被应用,不需要在物理Standby端做额外的操作,不过根据实际配置的不同,也会有例外,有些操作不是没有被传播到Standby端,而是传播过去了,但不能正确执行,其中最常见的就是对表空间和日志文件的管理操作,下面通过实例逐一进行说明。
1)创建表空间或数据文件
初始化参数standby_file_management用来控制是否自动将primary数据库增加表空间或数据文件的改动,传播到物理物理standby数据库。该参数有两个值:
AUTO:如果该参数值设置为AUTO,则primary database执行的表空间操作也会传播到physical standby database上执行;
MANUAL:如果设置为MANUAL或未设置任何值(默认值是MANUAL),需要手工复制新创建的数据文件到physical standby database服务器。
批注:STANDBY_FILE_MANAGEMENT参数特指Primary database端的表空间或数据文件创建,如果数据文件是从其他数据库复制而来(比如通过TTS传输表空间),则不管STANDBY_FILE_MANAGEMENT参数值如何设置,都必须同时手工复制到Standby database,并创建physical standby database的控制文件。对于文件系统,我们可以将初始化参数STANDBY_FILE_MANAGMENT设置为AUTO,但是对于裸设备,只能将该参数设置为MANUAL。
示例1:添加一个数据文件
SQL>startup --主库打开数据库
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --主库查询操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> startup mount; --备库启动到mount状态
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; --打开redo应用
Database altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --备库查询操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --备库修改STANDBY_FILE_MANAGEMENT的值
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
在主数据库创建一个表空间如下:
SQL> CREATE TABLESPACE new_ts DATAFILE '/u01/app/oradata/myorcl/new_ts.dbf' SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; --将当前日志归档
System altered.
SQL> SELECT NAME FROM V$DATAFILE; --主库查看创建的表空间指定的数据文件
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
/u01/app/oradata/myorcl/new_ts.dbf
6 rows selected.
在备库操作如下:
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
/u01/app/oradata/myorcl/new_ts.dbf
6 rows selected.
3)删除表空间
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --主库查询操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --备库查询操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> drop tablespace new_ts; --主库操作,删除名为new_ts的表空间
Tablespace dropped.
SQL> alter system switch logfile; --切换日志,使其归档
System altered.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; --备库操作,确定redo 应用实时运行
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
SQL> SELECT NAME FROM V$DATAFILE; --主库查看表空间是否删除
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
SQL> SELECT NAME FROM V$DATAFILE; --备库查询的删除的表空间已经同步
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
在redo应用之后,删除主、备库系统上的数据文件,如下操作:
[oracle@xiaoru myorcl]$ ls
example01.dbf new_ts.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@xiaoru myorcl]$ rm -rf new_ts.dbf --备库操作
[oracle@xiaoru myorcl]$ pwd
/u01/app/oradata/myorcl
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl new_ts.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@dayong myorcl]$ rm -rf new_ts.dbf --主库操作
[oracle@dayong myorcl]$ pwd
/u01/app/oradata/myorcl
4)重命名数据文件
如果Primary数据库重命名了一个或多个数据文件,该项修改并不会自动传输到Standby Database。就算设置了初始化参数STANDBY_FILE_MANAGEMENT等于AUTO也不行,要让Standby的数据文件与Primary 保持一致,只能手工操作。
示例如下:
SQL> CREATE TABLESPACE new_ts DATAFILE '/u01/app/oradata/myorcl/new_ts.dbf' SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created. --首先在主数据库创建一个实验表空间
SQL> alter tablespace new_ts offline; --主数据库操作,将要重命名的表空间设置脱机
Tablespace altered.
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl new_ts.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@dayong myorcl]$ mv new_ts.dbf new.dbf --主数据库操作,手工修改数据文件名字
[oracle@dayong myorcl]$ pwd
/u01/app/oradata/myorcl
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl new.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
SQL> ALTER TABLESPACE new_ts RENAME DATAFILE '/u01/app/oradata/myorcl/new_ts.dbf' TO '/u01/app/oradata/myorcl/new.dbf';
Tablespace altered. --主数据库操作,通过命令修改数据字典中表空间路径
SQL> ALTER TABLESPACE new_ts ONLINE; --主数据库操作,将表空间设置为online
Tablespace altered.
SQL> select name,status from v$datafile; --主数据库查询当前数据库表空间信息
NAME STATUS
-------------------------------------------------- -------
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/undotbs01.dbf ONLINE
/u01/app/oradata/myorcl/sysaux01.dbf ONLINE
/u01/app/oradata/myorcl/users01.dbf ONLINE
/u01/app/oradata/myorcl/example01.dbf ONLINE
/u01/app/oradata/myorcl/new.dbf ONLINE
6 rows selected.
SQL> ALTER SYSTEM SWITCH LOGFILE; --主数据库切换日志
System altered.
SQL> select name,status from v$datafile; --备用数据库查看当前数据库下的数据文件,可知主数据库对数据文件的修改并没有同步
NAME STATUS
-------------------------------------------------- -------
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/undotbs01.dbf ONLINE
/u01/app/oradata/myorcl/sysaux01.dbf ONLINE
/u01/app/oradata/myorcl/users01.dbf ONLINE
/u01/app/oradata/myorcl/example01.dbf ONLINE
/u01/app/oradata/myorcl/new_ts.dbf ONLINE
6 rows selected.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -备用数据库操作,暂停redo apply
Database altered.
SQL> shutdown immediate --关闭备库
[oracle@xiaoru myorcl]$ ls
example01.dbf new_ts.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@xiaoru myorcl]$ mv new_ts.dbf new.dbf --备库操作,手动修改数据文件的名字
[oracle@xiaoru myorcl]$ ls
example01.dbf new.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@xiaoru myorcl]$ pwd
/u01/app/oradata/myorcl
SQL> STARTUP MOUNT; --启动备库至mount状态
SQL> show parameter STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL; --需要将STANDBY_FILE_MANAGEMENT参数值设置为MANUAL
System altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter database rename file '/u01/app/oradata/myorcl/new_ts.dbf' to '/u01/app/oradata/myorcl/new.dbf';
Database altered. --备库操作,通过命令修改数据字典中的表空间路径
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -设置备库重新接收redo应用
Database altered.
SQL> select name,status from v$datafile; --备库查询操作
NAME STATUS
-------------------------------------------------- -------
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/undotbs01.dbf ONLINE
/u01/app/oradata/myorcl/sysaux01.dbf ONLINE
/u01/app/oradata/myorcl/users01.dbf ONLINE
/u01/app/oradata/myorcl/example01.dbf ONLINE
/u01/app/oradata/myorcl/new.dbf ONLINE
6 rows selected.
5)添加或删除在线日志文件
SQL> alter database recover managed standby database cancel; --备库操作,取消redo apply
Database altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> col member for a50;
SQL> select group#,member from v$logfile; --主库操作,查看当前数据库的日志文件
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
4 /u01/app/oradata/orcl/standbyrd01.log
5 /u01/app/oradata/orcl/standbyrd02.log
6 /u01/app/oradata/orcl/standbyrd03.log
6 rows selected.
SQL> ALTER DATABASE ADD LOGFILE group 7 '/u01/app/oradata/myorcl/redo04.log' size 50m; --添加主库日志文件
Database altered.
SQL> alter database add standby logfile group 8 '/u01/app/oradata/orcl/standbyrd04.log' size 50m; --添加备库日志文件
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
4 /u01/app/oradata/orcl/standbyrd01.log
5 /u01/app/oradata/orcl/standbyrd02.log
6 /u01/app/oradata/orcl/standbyrd03.log
7 /u01/app/oradata/myorcl/redo04.log
8 /u01/app/oradata/orcl/standbyrd04.log
8 rows selected.
SQL> col member for a50;
SQL> select group#,member from v$logfile; --备库操作,查看可知并没有实现与主库创建的日志文件实时同步,需要手动添加
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
SQL> ALTER DATABASE ADD LOGFILE group 4 '/u01/app/oradata/myorcl/redo04.log' size 50m; --备库操作
Database altered.
SQL> alter database add standby logfile group 5 '/u01/app/oradata/orcl/standbyrd04.log' size 50m; --备库操作
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session; --备库接收redo apply
Database altered.
SQL> select group#,member from v$logfile; --备库查询
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
4 /u01/app/oradata/myorcl/redo04.log
5 /u01/app/oradata/orcl/standbyrd04.log
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29634949/viewspace-1174134/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29634949/viewspace-1174134/