oracle active dataguard修改密码备库延迟生效

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:oracle active dataguard修改密码备库延迟生效

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在oracle dataguard环境中,当主库修改密码之后,备库不会立即生效,需要flush shared pool之后才会生效
主库创建用户并尝试登录

[oracle@primary ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:24:12 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff;

Grant succeeded.
SQL> conn xff/oracle
Connected.

备库登录信息

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:17:47 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

[oracle@standby ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:25:58 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn xff/oracle
Connected.

主库修改密码

[oracle@primary ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:17:47 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  alter user xff identified by xff;

User altered.

SQL> conn xff/xff
Connected.

备库尝试登录

SQL> conn xff/xff;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn xff/oracle
Connected.

备库原密码可以登录修改之后的密码无法登录

备库刷新shared pool,新密码登录成功

SQL> conn / as sysdba
Connected.
SQL> alter system flush shared_pool;

System altered.

SQL> conn xff/oracle
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn xff/xff
Connected.

win平台rman备份和删除dg备库归档日志脚本

总觉得使用windows跑oracle是不靠谱的事情,可以这个世界上总有很多人喜欢做类似这样的事情,对于数据库比较常见的两件事情:rman和删除dg备库归档日志,在linux/unix平台上使用shell实现很简单,可是跑到win里面,就变的烦了,不是因为其麻烦,而是因为用的人少,不知道怎么下手处理该事情,我编写了简单的实现初级功能的win下面rman备份和删除备库归档日志脚本,供大家参考,也更加欢迎朋友提出来更加好的处理方法(win是真心的不懂)
rman备份脚本

--backup_oracle.bat文件
rman target / cmdfile=D:\backup\rman\backup_db.rman 
log=d:/backup/rman/logfile/rmanlog%date:~0,4%%date:~5,2%%date:~8,2%.log

--backup_db.rman文件
CONFIGURE RETENTION POLICY TO REDUNDANCY = 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';
sql 'alter system archive log current';
backup  filesperset = 50 as compressed backupset archivelog  all format 'd:/backup/rman/arch_%U.rman'  delete input;
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;
backup  format 'd:/backup/rman/ctl_%U.rman' current controlfile;
backup spfile format 'd:/backup/rman/spfile_%U.rman' ;
exit;

backup_oracle.bat文件加入到计划任务即可

删除dg备库归档日志(已经应用)

--delete_dg_archivelog.bat
rem 注意修改 部署目录
cd D:\win_xifenfei
d:
rem 注意delete_archive.sql 查询是否有记录

echo delete archivelog staring > delete_archivelog.bak
sqlplus / as sysdba @delete_archive.sql
echo rman target / cmdfile=rman_checkcross.rman>>delete_archivelog.bat 
delete_archivelog.bat >>delete_dg_archivelog_%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%".log
exit

--delete_archive.sql
set lines 150
col name for a150
set pagesize 0 feedback off verify off heading off echo off
spool delete_archivelog.bat
select 'del '||name from v$archived_log where APPLIED='YES' AND NAME IS NOT NULL and DEST_ID=1;
spool off
exit;

--rman_checkcross.rman
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit

delete_dg_archivelog.bat加入到计划任务即可

ORACLE 12C Windows-Linux 部署DATAGURAD

环境描述
win 64中的ORACLE 12C(primary)与Linux 64中的ORACLE 12C(standby)搭建datagurad

primary force logging

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期六 8月 24 16:59:53 2013

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB_CDB$ROOT@SYS> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

CDB_CDB$ROOT@SYS> alter database force logging ;

数据库已更改。

primary rman backup

backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';

primary 生成standby controlfile

CDB_CDB$ROOT@SYS> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:/control01.ctl';

数据库已更改。

CDB_CDB$ROOT@SYS> create pfile='e:/pfile.txt' from spfile;

文件已创建。

standby 参数文件

DB_CREATE_FILE_DEST='+DATA'
db_create_online_log_dest_1='+DATA'
db_unique_name='cdb_dg'
service_names='cdb'
log_archive_dest_1='LOCATION=/u02/app/oracle/archivelog/ valid_for=(all_logfiles,all_roles)'
log_archive_dest_2='service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb'
log_archive_config='dg_config=(cdb,cdb_dg)'
standby_file_management=auto
db_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\DATAFILE\'
log_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\LOGFILE\'
fal_server=primary

primary 修改参数

CDB_CDB$ROOT@SYS> alter system set log_archive_config='dg_config=(cdb,cdb_dg)';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_archive_dest_2='
 2 service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb_dg';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_archive_dest_1=
  2 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles)';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set fal_server=standby;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set standby_file_management=auto;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set db_file_name_convert='+DATA\CDB_DG\DATAFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_file_name_convert='+DATA\CDB_DG\LOGFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile;

系统已更改。

primary and standby tns

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.32 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.1 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

standby restore controlfile

ASMCMD> cp /tmp/CONTROL01.CTL control01.ctl
copying /tmp/CONTROL01.CTL -> +data/cdb/control01.ctl

standby password file

[oracle@xifenfei dbs]$ pwd
/u01/app/oracle/product/12.1/db_1/dbs
[oracle@xifenfei dbs]$ cp /tmp/rman/PWDcdb.ora  orapwcdb

standby mount

SYS% cdb> create spfile from pfile='initcdb.ora';

File created.

SYS% cdb> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2290264 bytes
Variable Size             314576296 bytes
Database Buffers          197132288 bytes
Redo Buffers                7938048 bytes
Database mounted.

SYS% cdb> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SYS% cdb> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF

9 rows selected.

standby rman restore

--清理控制文件中的备份集垃圾
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;

--注册新备份集
catalog start with '/tmp/rman/';

--还原数据文件
run
{
set newname for database to '+data';
restore database;
switch datafile all;
}

standby clear redo

SYS% cdb> select group# from v$log;

    GROUP#
----------
         4
         6
         5

SYS% cdb> alter database clear logfile group 4;

Database altered.

SYS% cdb> alter database clear logfile group 5;

Database altered.

SYS% cdb> alter database clear logfile group 6;

Database altered.

standby add standby redolog

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13  size 50M;

Database altered.

primary add standby redolog

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo10.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo11.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo12.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo13.log' size 50M;

数据库已更改。

standby readonly

SYS% cdb> ALTER DATABASE OPEN READ ONLY;

Database altered.

standby start mrp

SYS% cdb> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

DATAGURAD 是否正常检查

--primary
CDB_CDB$ROOT@SYS> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     374
下一个存档日志序列   376
当前日志序列           376

--standby 
Mon Aug 12 13:56:51 2013
All non-current ORLs have been archived.
Mon Aug 12 13:56:53 2013
Media Recovery Log /u02/app/oracle/archivelog/1_370_820595806.dbf
Mon Aug 12 13:56:57 2013
Media Recovery Log /u02/app/oracle/archivelog/1_371_820595806.dbf
Mon Aug 12 13:57:02 2013
Media Recovery Log /u02/app/oracle/archivelog/1_372_820595806.dbf
Mon Aug 12 13:57:04 2013
Media Recovery Log /u02/app/oracle/archivelog/1_373_820595806.dbf
Mon Aug 12 13:57:05 2013
Media Recovery Log /u02/app/oracle/archivelog/1_374_820595806.dbf
Media Recovery Waiting for thread 1 sequence 375
Mon Aug 12 13:57:19 2013
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:26114)
RFS[2]: No standby redo logfiles created for thread 1 
RFS[2]: Opened log for thread 1 sequence 376 dbid 1937199326 branch 820595806
Mon Aug 12 13:57:19 2013
RFS[3]: Assigned to RFS process (PID:26118)
RFS[3]: Opened log for thread 1 sequence 375 dbid 1937199326 branch 820595806
Mon Aug 12 13:57:19 2013
Archived Log entry 16 added for thread 1 sequence 375 rlc 820595806 ID 0x7377d8de dest 2:
Mon Aug 12 13:57:22 2013
Media Recovery Log /u02/app/oracle/archivelog/1_375_820595806.dbf
Media Recovery Waiting for thread 1 sequence 376 (in transit)

11G RAC TO 11G RAC ADG配置

RDBMS tnsnames.ora配置
在主备库的数据库的tns中均增加如下配置

q9dbdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

q9adgdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

主库修改参数配置

alter system set DB_UNIQUE_NAME=q9db scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9adg1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db_adg';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9dbdg';
alter system set fal_server='q9adgdg';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

备库修改参数配置

alter system set DB_UNIQUE_NAME=q9db_adg scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db_adg';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9db1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9adgdg';
alter system set fal_server='q9dbdg';
alter system set service_names='q9db';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

主库增加standby redo log

alter database add standby logfile THREAD 1 group 51 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 1 group 61 '+DATA' size 512m;

alter database add standby logfile THREAD 2 group 71 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 2 group 81 '+DATA' size 512m;

主库修改强制日志

SQL> ALTER DATABASE FROCE LOGGING;

RMAN备份主库

rman target /
run{
configure device type disk parallelism 16;
backup database filesperset = 10  format '/q9bak/df_%U';
backup current controlfile format '/q9bak/ctl_%U';
}

创建standby controlfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/q9bak/stb_ctl';

还原standby controlfile

ASMCMD> cp /q9bak/stb_ctl control01.ctl
copying /q9bak/stb_ctl -> +data/Q9DB/control01.ctl
ASMCMD> cp /q9bak/stb_ctl control02.ctl
copying /q9bak/stb_ctl -> +arch/q9db/control02.ctl

还原备库

SQL>startup mount;

rman target / nocatalog <<EOF
run{
configure default device type to disk;
configure device type disk parallelism 16;
restore database;
}
<<EOF

修改备库文件名

RMAN>catalog start with '+DATA/Q9DB/DATAFILE/';
RMAN> switch database to copy;

清理redo

--清理redo(所有的group#)
SQL> select group# from v$log; 
SQL> alter database clear logfile group 1; 
……

--清理standby redo(所有的group#)
SQL> select group# from v$standby_log; 
SQL> alter database clear logfile group 51; 
……

开启备库实时应用模式

SQL> alter database recover managed standby database using current logfile disconnect from session;

备库注册DB服务(oracle用户)

srvctl add database -d q9db_adg -o $ORACLE_HOME 
srvctl add instance -d q9db_adg -i q9db1 -n q9adg01
srvctl add instance -d q9db_adg -i q9db2 -n q9adg02
srvctl modify database -d q9db_adg -a DATA,ARCH

11G RAC TO 11G RAC ADG SWITCHOVER

11G RAC TO 11G RAC ADG切换过程
主库准备工作

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PRIMARY          READ WRITE
         1 PRIMARY          READ WRITE

[oracle@q9db02 ~]$ srvctl stop instance -d q9db -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         1 PRIMARY          READ WRITE

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

备库准备工作

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PHYSICAL STANDBY READ ONLY WITH APPLY
         1 PHYSICAL STANDBY READ ONLY WITH APPL

[oracle@q9adg02 ~]$ srvctl stop instance -d q9db_adg -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         1 PHYSICAL STANDBY READ ONLY WITH APPL


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

主库切换日志,观察备库

--主库
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

--备库
[oracle@q9adg01 trace]$ tail -f alert_q9db1.log 
Tue Jun 25 15:35:27 2013
RFS[10]: Selected log 52 for thread 1 sequence 4777 dbid 844605368 branch 817913807
Tue Jun 25 15:35:28 2013
Archived Log entry 4889 added for thread 1 sequence 4776 ID 0x3545ffea dest 1:
Tue Jun 25 15:35:28 2013
Media Recovery Waiting for thread 1 sequence 4777 (in transit)
Tue Jun 25 15:35:28 2013
RFS[11]: Selected log 72 for thread 2 sequence 1630 dbid 844605368 branch 817913807
Recovery of Online Redo Log: Thread 1 Group 52 Seq 4777 Reading mem 0
  Mem# 0: +DATA/q9db_adg/onlinelog/group_52.1564.818724635
Media Recovery Waiting for thread 2 sequence 1630 (in transit)
Recovery of Online Redo Log: Thread 2 Group 72 Seq 1630 Reading mem 0
  Mem# 0: +DATA/q9db_adg/onlinelog/group_72.1575.818724653
Tue Jun 25 15:35:30 2013
Archived Log entry 4890 added for thread 2 sequence 1629 ID 0x3545ffea dest 1:

几乎同步进行表示主备日志传输应用正常

主库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;


Database altered.

备库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

继续处理主库

SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Data Mining
and Real Application Testing options
[oracle@q9db01 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 25 14:13:58 2013

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+11 bytes
Fixed Size                  2236968 bytes
Variable Size            2.5770E+10 bytes
Database Buffers         1.3422E+11 bytes
Redo Buffers              352468992 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

清理快照

--主库
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

--备库
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+11 bytes
Fixed Size                  2236968 bytes
Variable Size            2.7380E+10 bytes
Database Buffers         1.3261E+11 bytes
Redo Buffers              352468992 bytes
Database mounted.
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


Database altered.

SQL> alter database open;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

启动主备另外节点

--主库
[oracle@q9db01 ~]$ srvctl start instance -d q9db -i q9db2
--备库
[oracle@q9adg02 ~]$ srvctl start instance -d q9db_adg -i q9db2

补充说明:如果出现日志切换暂时不能传输

备库执行(因为重启动态监听没有马上别识别)
alter system register;
主库执行
alter system set log_archive_dest_state_2=enable;