环境:
主库:192.168.160.2
物理备库:192.168.160.128
逻辑备库:192.168.160.129
实验思路:在160.2上配置db,随后在128和129上创建2个一样的物理备库,随后将129上的物理备份转换成逻辑备库。
操作步骤:
0. 在3个虚拟机里安装redhat 6.2,并分别创建数据库。
1. 配置监听,以达到主库和2个备库都可以通信。
2. 主库改force logging,add SUPPLEMENTAL LOG
SQL> alter database force logging;
SQL> alter database add SUPPLEMENTAL LOG data;
3. 配置两边参数文件
不同的地方(主库):
*.db_unique_name='dong_pri'
*.service_names='dong_pri'
*.log_archive_config='DG_CONFIG=(dong_pri,mm_stb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dong_pri'
*.LOG_ARCHIVE_DEST_2='SERVICE=mm_stb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb'
*.LOG_ARCHIVE_DEST_3='SERVICE=mm_stb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb2'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_DEST_STATE_3=ENABLE
*.FAL_SERVER=mm_stb
*.FAL_CLIENT=dong_pri
*.STANDBY_FILE_MANAGEMENT=AUTO
备注:主库是需要写LOG_ARCHIVE_DEST3个的,而2个备库只需要写自己的就行。
不同的地方(物理备库128):
*.db_unique_name='mm_stb'
*.service_names='mm_stb'
*.log_archive_config='DG_CONFIG=(mm_stb,dong_pri)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mm_stb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.FAL_SERVER=dong_pri
*.FAL_CLIENT=mm_stb
*.STANDBY_FILE_MANAGEMENT=AUTO
不同的地方(物理备库129(一会要改为逻辑备库)):
*.db_unique_name='mm_stb2'
*.service_names='mm_stb2'
*.log_archive_config='DG_CONFIG=(mm_stb2,dong_pri)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mm_stb2'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.FAL_SERVER=dong_pri
*.FAL_CLIENT=mm_stb2
*.STANDBY_FILE_MANAGEMENT=AUTO
4. 主库创建备库控制文件
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/mmstb.ctl';
5. 主库增加备库日志文件(此处主要是为了以后切换到备库时使用)
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/dong/std_redo04.log') SIZE 50M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/dong/std_redo05.log') SIZE 50M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dong/std_redo06.log') SIZE 50M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/dong/std_redo07.log') SIZE 50M;
6. 将主库shutdown,并将文件copy到物理备库和逻辑备库的服务器上
此处为寻求方便,我将所有数据文件、主备的控制文件、主备的日志文件和密码文件、主的参数文件 都 copy到从服务器上了
7. 启动主库和备库
主startup open pfile='$ORACLE_HOME/dbs/initdong.ora';
物理备startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';
逻辑备startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';---这里我们先要将它搭建成物理备库
8. 将2个物理备库启动到应用日志状态
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
9. 测试物理备库是否可以用
10.将mm_stb2(129,第二个物理备库)转成逻辑备库
10.1 129停止物理备库的应用redo
SQL> alter database recover managed standby database cancel;
Database altered.
10.2 主库生成LogMiner字典信息
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 79
Next log sequence to archive 81
Current log sequence 81
备注:做完DBMS_LOGSTDBY.BUILD不用切换日志。
10.3 转换物理standby为逻辑standby
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 79
Next log sequence to archive 0
Current log sequence 81
SQL> select name,dest_id,STATUS,APPLIED,ARCHIVED from v$archived_log;
NAME
--------------------------------------------------------------------------------
DEST_ID S APPLIED ARC
---------- - --------- ---
/u01/app/oracle/diag/rdbms/dong/1_76_833209595.dbf
1 A YES YES
/u01/app/oracle/diag/rdbms/dong/1_77_833209595.dbf
1 A YES YES
/u01/app/oracle/diag/rdbms/dong/1_78_833209595.dbf
1 A NO YES
NAME
--------------------------------------------------------------------------------
DEST_ID S APPLIED ARC
---------- - --------- ---
/u01/app/oracle/diag/rdbms/dong/1_79_833209595.dbf
1 A NO YES
/u01/app/oracle/diag/rdbms/dong/1_80_833209595.dbf
1 A NO YES
备注:78-80这几个日志已经传到备库129这边来,但并没有应用,因为这里我们已经取消了日志的应用了。
SQL> alter database recover to logical standby donglog; --这里donglog是129这个备库的新的DB_NAME
Database altered.
SQL> archive log list
ORA-01507: database not mounted
SQL> !ps -ef |grep smon
oracle 3530 1 0 01:30 ? 00:00:00 ora_smon_dong
oracle 3905 3875 1 02:01 pts/6 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 3907 3905 0 02:01 pts/6 00:00:00 grep smon
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 213911964 bytes
Database Buffers 197132288 bytes
Redo Buffers 6103040 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string DONGLOG
db_unique_name string mm_stb2
global_names boolean FALSE
instance_name string dong
lock_name_space string
log_file_name_convert string
service_names string mm_stb2
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
10.4 修改参数文件
逻辑备库:
SQL> alter system set LOG_ARCHIVE_DEST_4='LOCATION=/u01/app/oracle/diag/rdbms/dong/lgc_dong/ VALID_for=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=mm_stb2';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_4='ENABLE';
备注:这里只需要修改逻辑备库的参数文件即可。
10.5 打开备库到open
SQL> alter database open resetlogs;
Database altered.
10.6 备库应用redo
SQL> alter database start logical standby apply immediate;
Database altered.
10.7 验证
主库:
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONG PRIMARY READ WRITE MAXIMUM PERFORMANCE NONE
128物理备库:
SQL> SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONG PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE ALL
129逻辑备库:
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONGLOG LOGICAL STANDBY READ WRITE MAXIMUM PERFORMANCE ALL
主库:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 103
Next log sequence to archive 105
Current log sequence 105
物理备库:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 58
Next log sequence to archive 0
Current log sequence 105
逻辑备库:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/lgc_dong/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
备注:注意这里逻辑备库的目录lgc_dong是逻辑备库自己的归档目录,下面的current log sequence也是自己的。而逻辑备库接受主库的归档是放在/u01/app/oracle/diag/rdbms/dong下的。
11. 测试逻辑备库
对于物理备库,我们可以在主库上建议一个表并切换日志,随后将备库拉起到readonly状态,查看是否可以发现。而逻辑备库来说就简简单单多了,由于逻辑备库一直都是open状态的,所以备库切换完日志后,备库直接查询即可,以下为逻辑备库的测试。
主库:
SQL> insert into mao.t_mao select * from mao.t_mao;
2 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select * from mao.t_mao;
ID
----------
1
1
1
1
逻辑备库:
SQL> select * from mao.t_mao;
ID
----------
1
1
1
1
12. 小知识点(查询):
逻辑备库:
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;---查看日志应用情况
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
79 1541405 1542804 06-JAN-14 YES
80 1542804 1542820 06-JAN-14 YES
81 1542820 1543693 06-JAN-14 YES
82 1543693 1544457 06-JAN-14 YES
83 1544457 1544492 06-JAN-14 YES
84 1544492 1544539 06-JAN-14 YES
85 1544539 1545119 06-JAN-14 YES
86 1545119 1548402 06-JAN-14 YES
87 1548402 1551318 06-JAN-14 YES
88 1551318 1554336 06-JAN-14 YES
89 1554336 1557214 06-JAN-14 YES
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
90 1557214 1560147 06-JAN-14 YES
91 1560147 1563171 06-JAN-14 YES
92 1563171 1565585 06-JAN-14 YES
93 1565585 1585835 06-JAN-14 YES
94 1585835 1585844 06-JAN-14 YES
95 1585844 1586161 06-JAN-14 YES
96 1586161 1586631 06-JAN-14 YES
97 1586631 1586983 06-JAN-14 YES
98 1586983 1587322 06-JAN-14 YES
99 1587322 1587665 06-JAN-14 YES
100 1587665 1588188 06-JAN-14 YES
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
101 1588188 1589321 06-JAN-14 YES
102 1589321 1593152 06-JAN-14 YES
103 1593152 1593219 06-JAN-14 YES
104 1593219 1594249 06-JAN-14 YES
26 rows selected.
备注:这里需要为YES才可以,如果为NO的话,需查看是否打开日志应用。
SQL> select session_id,state from v$logstdby_state; --查看日志应用状态
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 IDLE
备注:这里的状态有很多种,如果是APPLYING代表正在应用日志,如果是IDLE的话,表示已经开启应用,但目前没有新的日志需要应用。
SQL> select event_time,status,event from dba_logstdby_events order by event_timestamp;-----查看报错
EVENT_TIME STATUS EVENT
------------------------------ ------------------------------ ------------------------------
06-JAN-14 Apply LWM 1545390, HWM 1545390
, SCN 1565584
06-JAN-14 ORA-16226: DDL skipped due to ALTER DATABASE OPEN
lack of support
06-JAN-14 ORA-16226: DDL skipped due to alter database guard none
lack of support
EVENT_TIME STATUS EVENT
------------------------------ ------------------------------ ------------------------------
06-JAN-14 ORA-16226: DDL skipped due to alter database add SUPPLEMENTA
lack of support L LOG data
SQL> select THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS,FIRST_TIME,LAST_TIME from v$standby_log;--查看逻辑standbylog
THREAD# SEQUENCE# USED ARC STATUS FIRST_TIM LAST_TIME
---------- ---------- ---------- --- ------------------------------ --------- ---------
1 0 512 NO UNASSIGNED
1 105 341504 YES ACTIVE 06-JAN-14 06-JAN-14
1 0 512 NO UNASSIGNED
0 0 512 YES UNASSIGNED
SQL> select * from v$archive_gap;-------查看是否有没有应用的日志
no rows selected
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;-----查看各进程状态
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 68 CLOSING
ARCH ARCH 60 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 69 WAIT_FOR_LOG
RFS LGWR 69 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
9 rows selected.
实验中所遇到的问题:
1. 关于alter database guard none和alter database guard all
答:等同于alter system disable|enable guard,用于允许或禁止用户修改逻辑standby 中的结构。对于主库来说,那肯定要设置为none的,对于逻辑备库来说,如果你不想让备库的结果被修改,那就设置为all,我的实验中,逻辑备库是ALL的。在ALL下如果执行dml或者ddl操作则会抛出ORA-16224: Database Guard is enabled报错。解决办法只需要改为disable guard即可。
2. 在执行物理备库像逻辑备库切换时命令hang住
答:在执行alter database recover to logical standby donglog时hang住,半天没有反应,最后发现正确的顺序是备库先取消应用redo,然后主库生成LogMiner字典信息,主库不需要执行切换日志,最后备库执行 alter database recover to logical standby donglog。
3. 密码文件问题
答:由于3个库的密码一样,都是oracle,所以我就在3个库上分别执行了orapwd命令来生成密码文件,而且也可以通过网络链接对方库成功。但再传输日志时却发现警告日志里报错说Heartbeat失败,于是我将主库的密码文件分别scp到2个备库,最后问题解决。
主库:192.168.160.2
物理备库:192.168.160.128
逻辑备库:192.168.160.129
实验思路:在160.2上配置db,随后在128和129上创建2个一样的物理备库,随后将129上的物理备份转换成逻辑备库。
操作步骤:
0. 在3个虚拟机里安装redhat 6.2,并分别创建数据库。
1. 配置监听,以达到主库和2个备库都可以通信。
2. 主库改force logging,add SUPPLEMENTAL LOG
SQL> alter database force logging;
SQL> alter database add SUPPLEMENTAL LOG data;
3. 配置两边参数文件
不同的地方(主库):
*.db_unique_name='dong_pri'
*.service_names='dong_pri'
*.log_archive_config='DG_CONFIG=(dong_pri,mm_stb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dong_pri'
*.LOG_ARCHIVE_DEST_2='SERVICE=mm_stb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb'
*.LOG_ARCHIVE_DEST_3='SERVICE=mm_stb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb2'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_DEST_STATE_3=ENABLE
*.FAL_SERVER=mm_stb
*.FAL_CLIENT=dong_pri
*.STANDBY_FILE_MANAGEMENT=AUTO
备注:主库是需要写LOG_ARCHIVE_DEST3个的,而2个备库只需要写自己的就行。
不同的地方(物理备库128):
*.db_unique_name='mm_stb'
*.service_names='mm_stb'
*.log_archive_config='DG_CONFIG=(mm_stb,dong_pri)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mm_stb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.FAL_SERVER=dong_pri
*.FAL_CLIENT=mm_stb
*.STANDBY_FILE_MANAGEMENT=AUTO
不同的地方(物理备库129(一会要改为逻辑备库)):
*.db_unique_name='mm_stb2'
*.service_names='mm_stb2'
*.log_archive_config='DG_CONFIG=(mm_stb2,dong_pri)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mm_stb2'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.FAL_SERVER=dong_pri
*.FAL_CLIENT=mm_stb2
*.STANDBY_FILE_MANAGEMENT=AUTO
4. 主库创建备库控制文件
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/mmstb.ctl';
5. 主库增加备库日志文件(此处主要是为了以后切换到备库时使用)
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/dong/std_redo04.log') SIZE 50M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/dong/std_redo05.log') SIZE 50M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dong/std_redo06.log') SIZE 50M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/dong/std_redo07.log') SIZE 50M;
6. 将主库shutdown,并将文件copy到物理备库和逻辑备库的服务器上
此处为寻求方便,我将所有数据文件、主备的控制文件、主备的日志文件和密码文件、主的参数文件 都 copy到从服务器上了
7. 启动主库和备库
主startup open pfile='$ORACLE_HOME/dbs/initdong.ora';
物理备startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';
逻辑备startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';---这里我们先要将它搭建成物理备库
8. 将2个物理备库启动到应用日志状态
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
9. 测试物理备库是否可以用
10.将mm_stb2(129,第二个物理备库)转成逻辑备库
10.1 129停止物理备库的应用redo
SQL> alter database recover managed standby database cancel;
Database altered.
10.2 主库生成LogMiner字典信息
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 79
Next log sequence to archive 81
Current log sequence 81
备注:做完DBMS_LOGSTDBY.BUILD不用切换日志。
10.3 转换物理standby为逻辑standby
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 79
Next log sequence to archive 0
Current log sequence 81
SQL> select name,dest_id,STATUS,APPLIED,ARCHIVED from v$archived_log;
NAME
--------------------------------------------------------------------------------
DEST_ID S APPLIED ARC
---------- - --------- ---
/u01/app/oracle/diag/rdbms/dong/1_76_833209595.dbf
1 A YES YES
/u01/app/oracle/diag/rdbms/dong/1_77_833209595.dbf
1 A YES YES
/u01/app/oracle/diag/rdbms/dong/1_78_833209595.dbf
1 A NO YES
NAME
--------------------------------------------------------------------------------
DEST_ID S APPLIED ARC
---------- - --------- ---
/u01/app/oracle/diag/rdbms/dong/1_79_833209595.dbf
1 A NO YES
/u01/app/oracle/diag/rdbms/dong/1_80_833209595.dbf
1 A NO YES
备注:78-80这几个日志已经传到备库129这边来,但并没有应用,因为这里我们已经取消了日志的应用了。
SQL> alter database recover to logical standby donglog; --这里donglog是129这个备库的新的DB_NAME
Database altered.
SQL> archive log list
ORA-01507: database not mounted
SQL> !ps -ef |grep smon
oracle 3530 1 0 01:30 ? 00:00:00 ora_smon_dong
oracle 3905 3875 1 02:01 pts/6 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 3907 3905 0 02:01 pts/6 00:00:00 grep smon
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 213911964 bytes
Database Buffers 197132288 bytes
Redo Buffers 6103040 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string DONGLOG
db_unique_name string mm_stb2
global_names boolean FALSE
instance_name string dong
lock_name_space string
log_file_name_convert string
service_names string mm_stb2
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
10.4 修改参数文件
逻辑备库:
SQL> alter system set LOG_ARCHIVE_DEST_4='LOCATION=/u01/app/oracle/diag/rdbms/dong/lgc_dong/ VALID_for=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=mm_stb2';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_4='ENABLE';
备注:这里只需要修改逻辑备库的参数文件即可。
10.5 打开备库到open
SQL> alter database open resetlogs;
Database altered.
10.6 备库应用redo
SQL> alter database start logical standby apply immediate;
Database altered.
10.7 验证
主库:
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONG PRIMARY READ WRITE MAXIMUM PERFORMANCE NONE
128物理备库:
SQL> SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONG PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE ALL
129逻辑备库:
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONGLOG LOGICAL STANDBY READ WRITE MAXIMUM PERFORMANCE ALL
主库:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 103
Next log sequence to archive 105
Current log sequence 105
物理备库:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 58
Next log sequence to archive 0
Current log sequence 105
逻辑备库:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/lgc_dong/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
备注:注意这里逻辑备库的目录lgc_dong是逻辑备库自己的归档目录,下面的current log sequence也是自己的。而逻辑备库接受主库的归档是放在/u01/app/oracle/diag/rdbms/dong下的。
11. 测试逻辑备库
对于物理备库,我们可以在主库上建议一个表并切换日志,随后将备库拉起到readonly状态,查看是否可以发现。而逻辑备库来说就简简单单多了,由于逻辑备库一直都是open状态的,所以备库切换完日志后,备库直接查询即可,以下为逻辑备库的测试。
主库:
SQL> insert into mao.t_mao select * from mao.t_mao;
2 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select * from mao.t_mao;
ID
----------
1
1
1
1
逻辑备库:
SQL> select * from mao.t_mao;
ID
----------
1
1
1
1
12. 小知识点(查询):
逻辑备库:
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;---查看日志应用情况
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
79 1541405 1542804 06-JAN-14 YES
80 1542804 1542820 06-JAN-14 YES
81 1542820 1543693 06-JAN-14 YES
82 1543693 1544457 06-JAN-14 YES
83 1544457 1544492 06-JAN-14 YES
84 1544492 1544539 06-JAN-14 YES
85 1544539 1545119 06-JAN-14 YES
86 1545119 1548402 06-JAN-14 YES
87 1548402 1551318 06-JAN-14 YES
88 1551318 1554336 06-JAN-14 YES
89 1554336 1557214 06-JAN-14 YES
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
90 1557214 1560147 06-JAN-14 YES
91 1560147 1563171 06-JAN-14 YES
92 1563171 1565585 06-JAN-14 YES
93 1565585 1585835 06-JAN-14 YES
94 1585835 1585844 06-JAN-14 YES
95 1585844 1586161 06-JAN-14 YES
96 1586161 1586631 06-JAN-14 YES
97 1586631 1586983 06-JAN-14 YES
98 1586983 1587322 06-JAN-14 YES
99 1587322 1587665 06-JAN-14 YES
100 1587665 1588188 06-JAN-14 YES
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
101 1588188 1589321 06-JAN-14 YES
102 1589321 1593152 06-JAN-14 YES
103 1593152 1593219 06-JAN-14 YES
104 1593219 1594249 06-JAN-14 YES
26 rows selected.
备注:这里需要为YES才可以,如果为NO的话,需查看是否打开日志应用。
SQL> select session_id,state from v$logstdby_state; --查看日志应用状态
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 IDLE
备注:这里的状态有很多种,如果是APPLYING代表正在应用日志,如果是IDLE的话,表示已经开启应用,但目前没有新的日志需要应用。
SQL> select event_time,status,event from dba_logstdby_events order by event_timestamp;-----查看报错
EVENT_TIME STATUS EVENT
------------------------------ ------------------------------ ------------------------------
06-JAN-14 Apply LWM 1545390, HWM 1545390
, SCN 1565584
06-JAN-14 ORA-16226: DDL skipped due to ALTER DATABASE OPEN
lack of support
06-JAN-14 ORA-16226: DDL skipped due to alter database guard none
lack of support
EVENT_TIME STATUS EVENT
------------------------------ ------------------------------ ------------------------------
06-JAN-14 ORA-16226: DDL skipped due to alter database add SUPPLEMENTA
lack of support L LOG data
SQL> select THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS,FIRST_TIME,LAST_TIME from v$standby_log;--查看逻辑standbylog
THREAD# SEQUENCE# USED ARC STATUS FIRST_TIM LAST_TIME
---------- ---------- ---------- --- ------------------------------ --------- ---------
1 0 512 NO UNASSIGNED
1 105 341504 YES ACTIVE 06-JAN-14 06-JAN-14
1 0 512 NO UNASSIGNED
0 0 512 YES UNASSIGNED
SQL> select * from v$archive_gap;-------查看是否有没有应用的日志
no rows selected
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;-----查看各进程状态
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 68 CLOSING
ARCH ARCH 60 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 69 WAIT_FOR_LOG
RFS LGWR 69 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
9 rows selected.
实验中所遇到的问题:
1. 关于alter database guard none和alter database guard all
答:等同于alter system disable|enable guard,用于允许或禁止用户修改逻辑standby 中的结构。对于主库来说,那肯定要设置为none的,对于逻辑备库来说,如果你不想让备库的结果被修改,那就设置为all,我的实验中,逻辑备库是ALL的。在ALL下如果执行dml或者ddl操作则会抛出ORA-16224: Database Guard is enabled报错。解决办法只需要改为disable guard即可。
2. 在执行物理备库像逻辑备库切换时命令hang住
答:在执行alter database recover to logical standby donglog时hang住,半天没有反应,最后发现正确的顺序是备库先取消应用redo,然后主库生成LogMiner字典信息,主库不需要执行切换日志,最后备库执行 alter database recover to logical standby donglog。
3. 密码文件问题
答:由于3个库的密码一样,都是oracle,所以我就在3个库上分别执行了orapwd命令来生成密码文件,而且也可以通过网络链接对方库成功。但再传输日志时却发现警告日志里报错说Heartbeat失败,于是我将主库的密码文件分别scp到2个备库,最后问题解决。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24500180/viewspace-1067528/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24500180/viewspace-1067528/