<<OCM实验选讲>> 第十课 DG实验

最新推荐文章于 2021-02-07 11:05:05 发布
最新推荐文章于 2021-02-07 11:05:05 发布
阅读量2.4k 收藏 1
点赞数
分类专栏： Oracle - 认证/杂项
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/t0nsha/article/details/20800225
版权

根据视频演示，请自己手动完成视频中的实验内容并把关键步骤截图

一 主库LEO1配置
LEO1

1.启动归档模式并打开force logging

[oracle@odd ~]$ export ORACLE_SID=LEO1
[oracle@odd ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 17:29:56 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size            1219784 bytes
Variable Size          167772984 bytes
Database Buffers      247463936 bytes
Redo Buffers            2973696 bytes
Database mounted.
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/oracle/arcdata
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15
SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

SQL> alter database open;

Database altered.

SQL> select member from v$logfile where rownum=1;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/redo01.log

SQL> !ls /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/redo*
/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/redo01.log  /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/redo03.log
/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/redo02.log

SQL> select group# from v$log;

    GROUP#
----------
     1
     2
     3

2.添加standby logfile 用于接收来自主库的redo日志
SQL> alter database add standby logfile group 4 ('/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog4a.log','/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog4b.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 5 ('/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog5a.log','/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog5b.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 6 ('/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog6a.log','/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog6b.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 7 ('/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog7a.log','/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog7b.log') size 100m;

Database altered.

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
     4 UNASSIGNED
     5 UNASSIGNED
     6 UNASSIGNED
     7 UNASSIGNED

SQL>


3.配置网络
在tnsnames.ora文件中添加LEO3连接串
[oracle@odd ~]$ vim /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
[oracle@odd ~]$ tail -n 8 /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LEO3=
    (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)(HOST=even)(PORT=1521))
       (CONNECT_DATA=
           (SERVER=DEDICATED)
           (SERVICE_NAME=LEO3)
       )
    )
[oracle@odd ~]$
[oracle@odd ~]$ mkdir -p /home/oracle/arch/LEO1
[oracle@odd ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 18:10:17 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


4.修改主库LEO1参数文件

[oracle@odd ~]$ vim /home/oracle/oracle/product/10.2.0/db_1/dbs/initLEO1.ora
[oracle@odd ~]$ tail -n 18 /home/oracle/oracle/product/10.2.0/db_1/dbs/initLEO1.ora


##parameter for Primary Database
db_name='LEO1'
db_unique_name=LEO1
log_archive_format=%t_%s_%r.arc
log_archive_config='DG_CONFIG=(LEO1,LEO3)'
log_archive_dest_1='location=/home/oracle/arch/LEO1 valid_for=(all_logfiles,all_roles) db_unique_name=LEO1'
log_archive_dest_2='service=LEO3 lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=LEO3'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_passwordfile=exclusive
##parameter for Standby Database
fal_server=LEO3
fal_client=LEO1
standby_file_management=auto
db_file_name_convert='LEO3','LEO1'
log_file_name_convert='LEO3','LEO1'
[oracle@odd ~]$
[oracle@odd ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 18:18:28 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter listener

NAME                     TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
local_listener                 string
LISTENER_LEO1
remote_listener              string

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

SQL>

二 备库LEO3配置

1.创建密码文件
注意密码文件需要保持与主库一致，我们采用复制主库密码文件到备库的方式，如果用重建的方式可能引起两边不一致报错

[oracle@odd ~]$ scp /home/oracle/oracle/product/10.2.0/db_1/dbs/orapwLEO1 even:/home/oracle/oracle/product/10.2.0/db_2/dbs/orapwLEO3
oracle@even's password:
orapwLEO1                                                                                         100% 1536     1.5KB/s   00:00    
[oracle@odd ~]$

2.创建转储目录和归档目录
[oracle@even ~]$ mkdir -p /home/oracle/oracle/admin/LEO3/{a,b,c,u}dump
[oracle@even ~]$ mkdir -p /home/oracle/arch/LEO3
[oracle@even ~]$


3.冷备主库
[oracle@odd ~]$ cd /home/oracle/oracle/product/10.2.0/db_1/oradata/
[oracle@odd oradata]$ ls
LEO1
[oracle@odd oradata]$ tar -czvf LEO1.tar LEO1
LEO1/
LEO1/control01.ctl
LEO1/redo01.log
LEO1/standbylog6b.log
LEO1/sysaux01.dbf
LEO1/standbylog6a.log
LEO1/control02.ctl
LEO1/part2_01.dbf
LEO1/standbylog7a.log
LEO1/standbylog5a.log
LEO1/redo03.log
LEO1/undotbs01.dbf
LEO1/leo101.dbf
LEO1/part3_01.dbf
LEO1/move_tbs.dbf
LEO1/standbylog5b.log
LEO1/users01.dbf
LEO1/standbylog4a.log
LEO1/redo02.log
LEO1/standbylog7b.log
LEO1/system01.dbf
LEO1/assm01.dbf
LEO1/standbylog4b.log
LEO1/part4_01.dbf
LEO1/part1_01.dbf
LEO1/tsport01.dbf
LEO1/temp01.dbf
LEO1/control03.ctl
LEO1/mssm01.dbf
[oracle@odd oradata]$ scp LEO1.tar even:/home/oracle/oracle/oradata/
oracle@even's password:
LEO1.tar                                                                                          100%  105MB  52.5MB/s   00:02    
[oracle@odd oradata]$ scp /home/oracle/oracle/product/10.2.0/db_1/dbs/initLEO1.ora even:/home/oracle/oracle/product/10.2.0/db_2/dbs/initLEO3.ora
oracle@even's password:
initLEO1.ora                                                                                      100% 1995     2.0KB/s   00:00    
[oracle@odd oradata]$

[oracle@even ~]$ cd /home/oracle/oracle/oradata/
[oracle@even oradata]$ ls
LEO1.tar  LEO2  TEST
[oracle@even oradata]$ mkdir LEO3
[oracle@even oradata]$ tar -zxvf LEO1.tar -C LEO3
LEO1/
LEO1/control01.ctl
LEO1/redo01.log
LEO1/standbylog6b.log
LEO1/sysaux01.dbf
LEO1/standbylog6a.log
LEO1/control02.ctl
LEO1/part2_01.dbf
LEO1/standbylog7a.log
LEO1/standbylog5a.log
LEO1/redo03.log
LEO1/undotbs01.dbf
LEO1/leo101.dbf
LEO1/part3_01.dbf
LEO1/move_tbs.dbf
LEO1/standbylog5b.log
LEO1/users01.dbf
LEO1/standbylog4a.log
LEO1/redo02.log
LEO1/standbylog7b.log
LEO1/system01.dbf
LEO1/assm01.dbf
LEO1/standbylog4b.log
LEO1/part4_01.dbf
LEO1/part1_01.dbf
LEO1/tsport01.dbf
LEO1/temp01.dbf
LEO1/control03.ctl
LEO1/mssm01.dbf
[oracle@even oradata]$ ls LEO3/
LEO1
[oracle@even oradata]$ mv LEO3/LEO1/* LEO3/
[oracle@even oradata]$ ls LEO3
assm01.dbf     LEO1          part1_01.dbf  redo01.log        standbylog4b.log  standbylog6b.log  system01.dbf   users01.dbf
control01.ctl  leo101.dbf    part2_01.dbf  redo02.log        standbylog5a.log  standbylog7a.log  temp01.dbf
control02.ctl  move_tbs.dbf  part3_01.dbf  redo03.log        standbylog5b.log  standbylog7b.log  tsport01.dbf
control03.ctl  mssm01.dbf    part4_01.dbf  standbylog4a.log  standbylog6a.log  sysaux01.dbf      undotbs01.dbf
[oracle@even oradata]$ rm LEO3/LEO1/ -r
[oracle@even oradata]$ rm LEO3/control0*.ctl -f
[oracle@even oradata]$ ls LEO3
assm01.dbf    part1_01.dbf  redo01.log        standbylog4b.log  standbylog6b.log  system01.dbf   users01.dbf
leo101.dbf    part2_01.dbf  redo02.log        standbylog5a.log  standbylog7a.log  temp01.dbf
move_tbs.dbf  part3_01.dbf  redo03.log        standbylog5b.log  standbylog7b.log  tsport01.dbf
mssm01.dbf    part4_01.dbf  standbylog4a.log  standbylog6a.log  sysaux01.dbf      undotbs01.dbf
[oracle@even oradata]$


4.生成备库的控制文件@LEO1
[oracle@odd oradata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 20:14:37 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size            1219784 bytes
Variable Size          167772984 bytes
Database Buffers      247463936 bytes
Redo Buffers            2973696 bytes
Database mounted.
SQL> alter database create standby controlfile as '/home/oracle/control01.ctl';

Database altered.

SQL> !
[oracle@odd oradata]$ scp /home/oracle/control01.ctl even:/home/oracle/oracle/oradata/
oracle@even's password:
control01.ctl                                                                                     100% 6896KB   6.7MB/s   00:00    
[oracle@odd oradata]$

[oracle@even oradata]$ pwd
/home/oracle/oracle/oradata
[oracle@even oradata]$ ls
control01.ctl  LEO1.tar  LEO2  LEO3  TEST
[oracle@even oradata]$ cp control01.ctl LEO3/control01.ctl
[oracle@even oradata]$ cp control01.ctl LEO3/control02.ctl
[oracle@even oradata]$ cp control01.ctl LEO3/control03.ctl
[oracle@even oradata]$


5.准备备库的参数文件initLEO3.ora,这里仅列出需要修改的部分
[oracle@even oradata]$ vim /home/oracle/oracle/product/10.2.0/db_2/dbs/initLEO3.ora
[oracle@even ~]$ tail -n22 /home/oracle/oracle/product/10.2.0/db_2/dbs/initLEO3.ora
*.control_files='/home/oracle/oracle/oradata/LEO3/control01.ctl','/home/oracle/oracle/oradata/LEO3/control02.ctl','/home/oracle/oracle/oradata/LEO3/control03.ctl'
#as primary
DB_NAME='LEO1'                                  
DB_UNIQUE_NAME=LEO3
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_CONFIG='DG_CONFIG=(LEO1,LEO3)'
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/LEO3  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=LEO3'
LOG_ARCHIVE_DEST_2='SERVICE=LEO1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LEO1'                        
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#as standby
FAL_SERVER=LEO1                                  
FAL_CLIENT=LEO3                                  
DB_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1','/home/oracle/oracle/oradata/LEO3'
LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1','/home/oracle/oracle/oradata/LEO3'
STANDBY_FILE_MANAGEMENT=AUTO
local_listener='LISTENER_LEO3'
audit_file_dest='/home/oracle/oracle/admin/LEO3/adump'
background_dump_dest='/home/oracle/oracle/admin/LEO3/bdump'
core_dump_dest='/home/oracle/oracle/admin/LEO3/cdump'
user_dump_dest='/home/oracle/oracle/admin/LEO3/udump'
[oracle@even ~]$

[oracle@even ~]$ tail -n12 /home/oracle/oracle/product/10.2.0/db_2/network/admin/tnsnames.ora
LEO3=
    (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)(HOST=even)(PORT=1521))
       (CONNECT_DATA=
           (SERVER=DEDICATED)
           (SERVICE_NAME=LEO3)
       )
    )

LISTENER_LEO3 =
  (ADDRESS = (PROTOCOL = tcp)(HOST = even)(PORT = 1521))

[oracle@even ~]$

@LEO1

SQL> alter database open;

Database altered.

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP
---------- ---
    10 NO
    11 NO
    12 NO
    13 NO
    14 NO
    15 NO
    16 NO
    16 YES

8 rows selected.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PRIMARY      SESSIONS ACTIVE

SQL> select process,status,group#,sequence# from v$managed_standby;

PROCESS   STATUS       GROUP#                     SEQUENCE#
--------- ------------ ---------------------------------------- ----------
ARCH      CLOSING      1                        16
ARCH      CONNECTED    N/A                         0
LGWR      WRITING      2                        17

SQL> !
[oracle@odd LEO1]$ tail -50f /home/oracle/oracle/product/10.2.0/db_1/admin/LEO1/bdump/alert_LEO1.log
...
alter database open
Sat Mar  1 23:10:00 2014
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=17162
Sat Mar  1 23:10:00 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=16, OS id=17164
Sat Mar  1 23:10:00 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNSb started with pid=17, OS id=17166
Sat Mar  1 23:10:03 2014
LGWR: Standby redo logfile selected for thread 1 sequence 17 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 17
Sat Mar  1 23:10:03 2014
ARC1: STARTING ARCH PROCESSES
Sat Mar  1 23:10:03 2014
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Sat Mar  1 23:10:03 2014
Thread 1 opened at log sequence 17
  Current log# 2 seq# 17 mem# 0: /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/redo02.log
Successful open of redo thread 1
Sat Mar  1 23:10:03 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar  1 23:10:03 2014
SMON: enabling cache recovery
Sat Mar  1 23:10:03 2014
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=17170
Sat Mar  1 23:10:03 2014
ARC0: Standby redo logfile selected for thread 1 sequence 16 for destination LOG_ARCHIVE_DEST_2
Sat Mar  1 23:10:05 2014
Successfully onlined Undo Tablespace 1.
Sat Mar  1 23:10:05 2014
SMON: enabling tx recovery
Sat Mar  1 23:10:05 2014
Database Characterset is US7ASCII
Sat Mar  1 23:10:06 2014
Incremental checkpoint up to RBA [0x11.3.0], current log tail at RBA [0x11.28.0]
Sat Mar  1 23:10:07 2014
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=17172
Sat Mar  1 23:10:09 2014
Completed: alter database open
Sat Mar  1 23:16:06 2014
Shutting down archive processes
Sat Mar  1 23:16:11 2014
ARCH shutting down
ARC2: Archival stopped

6.启动备库LEO3到mount状态
@LEO3

[oracle@even ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 23:14:32 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> alter database recover managed standby database disconnect from session parallel 2;


Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE

SQL> select process,status,group#,sequence# from v$managed_standby;

PROCESS   STATUS       GROUP#                     SEQUENCE#
--------- ------------ ---------------------------------------- ----------
ARCH      CLOSING      5                        16
ARCH      CONNECTED    N/A                         0
RFS      IDLE           2                        17
RFS      IDLE           N/A                         0
RFS      IDLE           N/A                         0
MRP0      WAIT_FOR_LOG N/A                        17

6 rows selected.

SQL> !
[oracle@even ~]$ tail /home/oracle/oracle/admin/LEO3/bdump/alert_LEO3.log

...

Completed: ALTER DATABASE   MOUNT
Sat Mar  1 23:10:03 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /home/oracle/arch/LEO3
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 31791
RFS[1]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Sat Mar  1 23:10:03 2014
RFS LogMiner: Client disabled from further notification
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 4: '/home/oracle/oracle/oradata/LEO3/standbylog4a.log'
Sat Mar  1 23:10:03 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 31793
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Successfully opened standby log 5: '/home/oracle/oracle/oradata/LEO3/standbylog5a.log'
Sat Mar  1 23:11:01 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 31895
RFS[3]: Identified database type as 'physical standby'
Sat Mar  1 23:14:34 2014
alter database recover managed standby database disconnect from session parallel 2
Sat Mar  1 23:14:34 2014
Attempt to start background Managed Standby Recovery process (LEO3)
MRP0 started with pid=20, OS id=32201
Sat Mar  1 23:14:34 2014
MRP0: Background Managed Standby Recovery process started (LEO3)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Clearing online redo logfile 1 /home/oracle/oracle/oradata/LEO3/redo01.log
Clearing online log 1 of thread 1 sequence number 17
Sat Mar  1 23:14:40 2014
Completed: alter database recover managed standby database disconnect from session parallel 2
Sat Mar  1 23:14:41 2014
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /home/oracle/oracle/oradata/LEO3/redo02.log
Clearing online log 2 of thread 1 sequence number 17
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /home/oracle/oracle/oradata/LEO3/redo03.log
Clearing online log 3 of thread 1 sequence number 15
Clearing online redo logfile 3 complete
Media Recovery Log /home/oracle/arch/LEO3/1_16_837172792.arc
Media Recovery Waiting for thread 1 sequence 17 (in transit)

三 第一次切换(LEO1=>LEO3)
换前LEO1库上创建swtich_tbs表空间 -> 切换 -> 检查表空间和对应的数据文件是否应用到了LEO3库
创建表空间
SQL> create tablespace swtich_tbs datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/swtich_tbs.dbf' size 10m;

Tablespace created.

SQL> alter system archive log current;

System altered.

SQL>


@LEO3
SQL> select * from v$tablespace where name='SWTICH_TBS';

       TS# NAME               INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
    14 SWTICH_TBS              YES NO  YES

SQL> !
[oracle@even oradata]$ tail /home/oracle/oracle/admin/LEO3/bdump/alert_LEO3.log
...
Sat Mar  1 23:32:40 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 4: '/home/oracle/oracle/oradata/LEO3/standbylog4a.log'
Sat Mar  1 23:32:44 2014
Media Recovery Log /home/oracle/arch/LEO3/1_18_837172792.arc
Recovery created file /home/oracle/oracle/oradata/LEO3/swtich_tbs.dbf
Successfully added datafile 14 to media recovery
Datafile #14: '/home/oracle/oracle/oradata/LEO3/swtich_tbs.dbf'
Media Recovery Waiting for thread 1 sequence 19 (in transit)

查看LEO1库状态
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PRIMARY      SESSIONS ACTIVE

SQL> 


查看LEO3库状态
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE

SQL>

主库切换成备库，并终止存活的会话
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PRIMARY      SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> select status,instance_name from v$instance;

STATUS         INSTANCE_NAME
------------ ----------------
STARTED      LEO1

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size            1219784 bytes
Variable Size          171967288 bytes
Database Buffers      243269632 bytes
Redo Buffers            2973696 bytes
Database mounted.
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SQL> select status,instance_name from v$instance;

STATUS         INSTANCE_NAME
------------ ----------------
MOUNTED      LEO1

SQL> alter database recover managed standby database disconnect from session parallel 2;

Database altered.

SQL>


备库切换成主库，并终止存活的会话
SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active


SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PRIMARY      SESSIONS ACTIVE

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP
---------- ---
    16 YES
    17 YES
    18 YES
    19 YES
    20 YES
    21 NO
    21 YES

7 rows selected.

SQL> select process,status,group#,sequence# from v$managed_standby;

PROCESS   STATUS       GROUP#                     SEQUENCE#
--------- ------------ ---------------------------------------- ----------
ARCH      CLOSING      5                        18
ARCH      CLOSING      N/A                        21
ARCH      CONNECTED    N/A                         0
LNS      WRITING      1                        22

SQL>


四 第二次切换(LEO3=>LEO1)

切换前LEO3库上创建一张表 -> 切换 -> 检查表和记录是否应用到LEO1库上
SQL> create table leo3 as select * from dba_objects;

Table created.

SQL> select count(*) from leo3;

  COUNT(*)
----------
      9895

SQL> alter system archive log current;

System altered.

SQL> select process,status,group#,sequence# from v$managed_standby;

PROCESS   STATUS       GROUP#                     SEQUENCE#
--------- ------------ ---------------------------------------- ----------
ARCH      CLOSING      5                        18
ARCH      CLOSING      N/A                        21
ARCH      CONNECTED    N/A                         0
LNS      WRITING      3                        23

SQL>

      
备库LEO1的alert中记录着恢复的过程
[oracle@odd bdump]$ tail -50f /home/oracle/oracle/product/10.2.0/db_1/admin/LEO1/bdump/alert_LEO1.log
...
Sat Mar  1 23:42:53 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 18578
RFS[3]: Identified database type as 'physical standby'
Sat Mar  1 23:44:41 2014
RFS[2]: Possible network disconnect with primary database
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 18761
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog5a.log'
Sat Mar  1 23:44:52 2014
Fetching gap sequence in thread 1, gap sequence 22-22
Sat Mar  1 23:44:55 2014
RFS[3]: Successfully opened standby log 4: '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/standbylog4a.log'
Sat Mar  1 23:45:22 2014
Media Recovery Log /home/oracle/arch/LEO1/1_22_837172792.arc
Media Recovery Waiting for thread 1 sequence 23 (in transit)

LEO1上检查leo3表，如果可以查到说明我们传递成功
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>  select count(*) from leo3;   

  COUNT(*)
----------
      9895

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size            1219784 bytes
Variable Size          171967288 bytes
Database Buffers      243269632 bytes
Redo Buffers            2973696 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session parallel 2;

Database altered.

SQL>


LEO3库切换成备库状态
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PRIMARY      SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size            1219784 bytes
Variable Size          176161592 bytes
Database Buffers      239075328 bytes
Redo Buffers            2973696 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session parallel 2;

Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE

SQL>


LEO1库切换成主库状态

SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active


SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;


Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS
---------------- --------------------
PRIMARY      TO STANDBY

SQL>


--EOF--

t0nsha
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
OracleRAC数据库集群视频教程（10讲）
07-23
教程名称：Oracle RAC数据库集群视频教程（10讲）课程目录：【】1.OracleRAC集群体系结构_drm【】10.测试OracleRAC数据库集群功能【】2.安装OracleRAC数据库（一）【】3.安装OracleRAC数据库（二）_drm【】4.安装OracleRAC数据库（三）【】5.OracleCRS的管理与维护_drm 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
OCM考试实验笔记之八_RAC安装.pdf
05-19
OCM考试实验笔记之八_RAC安装.pdf
Oracle 10g应用指导
05-29
Oracle是当今各领域中应用比较广泛的一个大型关系型数据库产品，它以其所拥有的稳定、可伸缩性强以及安全可靠等特性受到越来越多的系统开发人员的青睐，正成为企业开发信息系统的首选产品。同其他数据库管理系统相比，Oracle产品较为庞大而复杂，因此，令许多数据库管理系统的学习者在Oracle面前止步不前，究其原因，主要是无法掌握Oracle技术的脉络。本书正是根据作者在高校和各种数据库培训班执教多年的教学经验，结合开发应用Oracle数据库的实践经验编撰而成的，书中编排的内容次序非常适合于读者学习和把握Oracle的脉络，书中所选择的问题都是在实际开发应用Oracle过程中经常遇到和所要解决的。通过学习解决这些问题的方法，可以使读者比较深入地认识和掌握Oracle技术的内涵，快速地进入开发角色。本书共分11章。第1章Oracle的安装卸载与使用环境，主要包括Oracle安装环境及方法，安装后的基本环境及服务，Oracle卸载，还包括验证数据库的安装，系统临时空间不足、监听服务无法启动以及Oracle默认帐户/口令等问题的精讲。第2章 Oracle体系结构，包括Oracle数据库逻辑结构和数据库物理结构以及Oracle文件的目录结构等。在案例精讲中对数据块校验、表的移动、数据文件丢失或损坏、丢失控制文件、数据文件更名以及数据库内文件的复制传输等问题的解决做了全面细致的讲解。第3章SQL及PL/SQL的运行环境及其他开发工具。第4章 Oracle数据字典管理与数据类型。包括数据字典的分类、使用方法以及各种对象的具体查询等做了详细的介绍。第5章 数据库对象的创建与使用。主要包括各种类型的表创建以及适用情形，如外部表、分区表、嵌套表、全局临时表等；完整性约束的管理；索引，包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在案例精讲中，对表压缩、约束的使能与失能、表的层次结构查询、防止删除表及对象、提取创建外键约束的脚本以及在线重新定义表结构的方法做了详细讲解。第6章 PL/SQL程序设计。介绍了PL/SQL中常用的函数、异常处理等，还有对随机数生成、分析函数、多表合并、多表插入等问题的解决方法。第7章 子程序和触发器，包括函数、存储过程、包以及触发器等。对子程序的调用者权限、管道表函数、传递触发器标识:new和:old以及自治事务也给出了具体的解决方法。第8 章 LOB与面向对象的数据管理. 第9章 Oracle的监听器和网络设置。包括Oracle网络体系结构，Oracle Net参数文件的管理，监听器与网络连接配置等。针对应用中经常出现问题，如保护与设置监听器，监听器远程管理，端口号，客户机与服务器的连接，外部过程调用，不能解析服务名以及没有监听器等都给出了详细的解决方法。第10章 数据库管理，包括导出数据库模式的DDL脚本，管理Oracle数据库实例的方法、数据恢复与Flashback以及更改数据库字符集等具体方法。第11章 数据库的安全管理。包括加密Oracle子程序，存储应用程序用户名和口令，禁止修改删除数据库对象，Oracle数据加密以及丢失SYSMAN及资料档案库用户口令的解决方法。书中给出了丰富的图表，多数图例是作者根据多年实践总结出来的，图示简练准确，易于理解，并附有解决问题的具体步骤方法和相应的脚本。读者对象：面向各种培训班学员，高校相关专业的学生， Oracle应用开发人员以及Oracle数据库的学习者。
oracle实验.ppt
04-25
oracle实验.ppt 1.了解Oracle数据库的逻辑结构和物理结构; 2.了解Oracle Enterprise Manager的使用情况; 3.学习关闭和启动数据库实例的方法; 4.学习使用SQL语句创建数据库的方法; 5.学习使用SQL语句删除数据库的方法. 。。。。。。。。。。。。。。。。。
OCM考试实验笔记之七_手动建库.pdf
05-19
OCM考试实验笔记之七_手动建库.pdf
一个oracle bug的简单验证
congbao6525的博客
03-21 168
最近碰到了一个oracle bug，但是我感觉还是有些运气的成分，虽然错误日志和bug描述吻合，版本也完全对应，现在有几个问题在我脑海中翻腾，就是这个问题是不是一个特例，是不是一些额外的原因导致的，于是我翻出了日志重新来看。 ...
一个备库中ORA错误信息的分析 (r6笔记第69天)
杨建荣的学习笔记
09-25 272
最近也在处理一些遗留的问题，所以对于使用orabbix的报警还是心怀敬畏之心，一方面是我们让它能够做全方位的监控，另一方面也让我发现我们还是存在不少的小问题，小问题虽小，...
mysql ora-09925_Oracle data guard 10g 搭建(示例代码)
weixin_34183908的博客
02-07 173
Oracle data guard 10g 搭建1系统常规参数检查硬盘内核版本[[emailprotected] ~]# cat /etc/redhat-release物理内存[[emailprotected] ~]# grep MemTotal /proc/meminfo虚拟内存[[emailprotected] ~]# grep SwapTotal /proc/meminfo主机名ip地...
<< Oracle高可用>>部分书面作业 - 第八课 Data Gaurd 故障分析和处理
t0nsha's Blog
10-13 3589
1.模拟由于网络，监听器的原因导致归档无法传到standby上的情形，分别从相应的主备数据库的视图和alert文件定位故障的原因；当故障排除后，看Oracle是否能自动恢复归档的传递，模拟：备库故意没起监听，其他正常[oracle@dg2 ~]$ lsnrctl statusLSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2
一次Oracle DG错误警示（生产库）
天涯客*Blog
03-31 9385
生产库DG ,防火墙限制，导致归档传送失败 主库在北京，备库在云南，主备、库网络通道之间有防火墙，由于防火墙对数据包传输的大小做了限制，导致北京和云南之间的主备库传输归档日志失败。1、告警日志错误主库：（北京）WARN: ARC3: Terminating ARCH (pid 9608) hung on a network operationWARN: ARC3: Terminating A
OCM考试实验笔记之三_数据管理.pdf
05-19
OCM考试实验笔记之三_数据管理.pdf
OCM考试实验笔记之二_数据迁移.pdf
05-19
OCM考试实验笔记之二_数据迁移.pdf
OCM考试实验笔记之五_GridControl与计划作业.pdf
05-19
OCM考试实验笔记之五_GridControl与计划作业.pdf
Oracle OCM 考试 实验手册
09-06
3． OCM考试最重要的分三部分，第一部分是要求手工建库，第二部分是建EM，第三部分部是建rman库做catalog备份。第一部分和第二部分是后续考试的前提，可以大大的提高考试的效率。在考试过程中每做一次操作，请记得...
OCM考试实验笔记之一_物化视图.pdf
05-19
OCM考试实验笔记之一_物化视图.pdf
OCM考试实验笔记之六_备份恢复.pdf
05-19
OCM考试实验笔记之六_备份恢复.pdf
OCM考试实验笔记之四_性能管理.pdf
05-19
OCM考试实验笔记之四_性能管理.pdf
Oracle Database 11g性能优化 OCM课第10章
12-10
Oracle Database 11g r2性能调优官方中文版OCM培训教材。
Oracle Database 11g性能优化 OCM课第13章
12-10
Oracle Database 11g r2性能调优官方中文版OCM培训教材。
OCA/OCP/OCM
最新发布
08-19
OCA、OCP和OCM是Oracle公司的认证考试。OCA是Oracle Certified Associate，不需要原厂培训，需要通过两门考试。OCP是Oracle Certified Professional，可以与OCA认证一起考试，需要通过三门考试，并且至少需要参加一门原厂培训或WDP（Workforce Development Program）的培训。OCM是Oracle Certified Master，需要至少再参加两门原厂高级课程培训或者WDP培训课程，并通过两天的上机考试。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [最新Oracle 11g OCA/OCP/OCM 认证考试体系和认证途径](https://blog.csdn.net/u012245006/article/details/12091151)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* [关于OCA,OCP,OCM证书的区别](https://blog.csdn.net/ShyTan/article/details/119742150)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值