#################################################################

主库prod

hostname:prod.localdomain 10.1.1.11

sid：prod 网络服务prod.localdomain 网络服务别名prod

备库standby

hostname:prod.localdomain 10.1.1.12

sid:standby 网络服务standby.localdomain 网络服务别名standby

主库和备库hosts一样

cat /etc/hosts

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

10.1.1.11 prod.localdomain prod

10.1.1.12 standby.localdomain standby

#################################################################

主库：dbca后

设置为归档及force logging

starup mount

alter database archivelog;

alter database force logging;

select log_mode,force_logging from v$database

更改归档目录为

/u01/app/oracle/oradata/prod/archivelog

创建pfile

create pfile from spfile

创建standby controlfile

alter database create standby controlfile as '/u01/app/oracle/product/10.2.0/standby.ctl';

把 pfile 文件、口令文件和备控制文件传输到备库同样目录下（备控制文件只要复制到与prod库的路径相同就行）

[oracle@prod 11.2.0]$ pwd

/u01/app/oracle/11.2.0

[oracle@prod 11.2.0]$ ls

oracle standby.ctl

[oracle@standby 11.2.0]$ pwd

/u01/app/oracle/11.2.0

[oracle@standby 11.2.0]$ ls

oracle standby.ctl

把口令文件和 pfile 名字改成 standby 的

mv orapwprod orapwstandby

mv initprod.ora initstandby.ora

更改 initstandby.ora

1 删除内存部分

prod.__db_cache_size=423624704

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__shared_pool_size=159383552

prod.__streams_pool_size=0

2 所有prod改成standby :%s/prod/standby/g 注意:db_name 的 prod 值不要修改

3 增加

*.db_unique_name='standby'

*.db_file_name_convert='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/standby'

*.log_file_name_convert='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/standby'

*.log_archive_config='dg_config=(prod,standby)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/standby/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=prod lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prod'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=10

*.fal_server=prod

*.fal_client=standby

*.standby_file_management=auto

%%%%%%%%%%%%%%%%%%%%%%%initstandby.ora%%%%%%%%%%%%%%%%%%%%%%%%%%%%

*.audit_file_dest='/u01/app/oracle/admin/standby/adump'

*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/oradata/standby/control02.ctl','/u01/app/oracle/oradata/standby/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'

*.db_block_size=8192

*.db_domain='localdomain'

*.db_file_multiblock_read_count=16

*.db_name='prod'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='location=/u01/app/oracle/oradata/standby/archivelog'

*.open_cursors=300

*.pga_aggregate_target=199229440

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=599785472

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/standby/udump'

*.db_unique_name='standby'

*.db_file_name_convert='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/standby'

*.log_file_name_convert='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/standby'

*.log_archive_config='dg_config=(prod,standby)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/standby/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=prod lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prod'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=10

*.fal_server=prod

*.fal_client=standby

*.standby_file_management=auto

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

创建目录

mkdir -p /u01/app/oracle/admin/standby/adump

mkdir -p /u01/app/oracle/admin/standby/bdump

mkdir -p /u01/app/oracle/admin/standby/cdump

mkdir -p /u01/app/oracle/admin/standby/udump

mkdir -p /u01/app/oracle/admin/standby/dpdump

mkdir -p /u01/app/oracle/oradata/standby/archivelog

配置standby 库的 listener 和静态服务名 netmgr

配置standby 库连接到 prod 的网络服务名

tnsping prod

回到主库配置

配置连接到 standby 的网络服务名 netmgr

tnsping standby

配置spfile

alter system set log_archive_config='dg_config=(prod,standby)';

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/prod/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=prod';

alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby';

alter system set log_archive_dest_state_1=enable;

alter system set log_archive_dest_state_2=enable;

alter system set log_archive_max_processes=10;

alter system set fal_server=standby;

alter system set fal_client=prod;

alter system set standby_file_management=auto;

alter system set db_unique_name='prod'scope=spfile;

alter system set db_file_name_convert='/u01/app/oracle/oracle/standby','/u01/app/oracle/oracle/prod' scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oracle/standby','/u01/app/oracle/oracle/prod' scope=spfile;

%%%%%%%%%%%%%%%initprod.ora%%%%%%%%%%%%%%%%%%%%%%%%%

*.audit_file_dest='/u01/app/oracle/admin/prod/adump'

*.background_dump_dest='/u01/app/oracle/admin/prod/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl','/u01/app/oracle/oradata/prod/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/prod/cdump'

*.db_block_size=8192

*.db_domain='localdomain'

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/u01/app/oracle/oracle/standby','/u01/app/oracle/oracle/prod'

*.db_name='prod'

*.db_unique_name='prod'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

*.fal_client='PROD'

*.fal_server='STANDBY'

*.job_queue_processes=10

*.log_archive_config='dg_config=(prod,standby)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/prod/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=prod'

*.log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=10

*.log_file_name_convert='/u01/app/oracle/oracle/standby','/u01/app/oracle/oracle/prod'

*.open_cursors=300

*.pga_aggregate_target=199229440

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=599785472

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/prod/udump'

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

备份主库

mkdir -p $ORACLE_BASE/backupset

rman target /

run

{

allocate channel d1 type disk format '/u01/app/oracle/backupset/%U';

backup database;

release channel d1;

}

把备份集目录传输到 standby 的/u01/app/oracle/backupset 目录下

开始克隆 standby 库，在 prod 上运行

rman target / auxiliary sys/oracle@standby

run

{

allocate auxiliary channel d1 type disk;

duplicate target database for standby nofilenamecheck;

release channel d1;

}

还原了standby 数据库

以下是记录

######################################################

[oracle@prod ~]$ rman target / auxiliary sys/oracle@standby

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Nov 23 22:36:28 2014

connected to target database: PROD (DBID=276256994)

connected to auxiliary database: PROD (not mounted)

RMAN> run

2> {

3> allocate auxiliary channel d1 type disk;

4> duplicate target database for standby nofilenamecheck;

release channel d1;

5> 6> }

using target database control file instead of recovery catalog

allocated channel: d1

channel d1: sid=156 devtype=DISK

Starting Duplicate Db at 23-NOV-14

contents of Memory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 23-NOV-14

channel d1: restoring control file

channel d1: copied control file copy

input filename=/u01/app/oracle/product/10.2.0/standby.ctl

output filename=/u01/app/oracle/oradata/standby/control01.ctl

output filename=/u01/app/oracle/oradata/standby/control02.ctl

output filename=/u01/app/oracle/oradata/standby/control03.ctl

Finished restore at 23-NOV-14

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/standby/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/standby/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/standby/undotbs01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/standby/sysaux01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/standby/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/standby/example01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/standby/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 23-NOV-14

channel d1: starting datafile backupset restore

channel d1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/standby/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/standby/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/standby/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/standby/users01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/standby/example01.dbf

channel d1: reading from backup piece /u01/app/oracle/backupset/01poc5sl_1_1

channel d1: restored backup piece 1

piece handle=/u01/app/oracle/backupset/01poc5sl_1_1 tag=TAG20141123T221757

channel d1: restore complete, elapsed time: 00:00:48

Finished restore at 23-NOV-14

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=7 stamp=864427158 filename=/u01/app/oracle/oradata/standby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=8 stamp=864427158 filename=/u01/app/oracle/oradata/standby/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=9 stamp=864427158 filename=/u01/app/oracle/oradata/standby/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=10 stamp=864427158 filename=/u01/app/oracle/oradata/standby/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=11 stamp=864427158 filename=/u01/app/oracle/oradata/standby/example01.dbf

Finished Duplicate Db at 23-NOV-14

released channel: d1

RMAN>

##########################################

创建备份的standby日志大小和主库相同数量多一组

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby01.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby02.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby03.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby04.log') size 50m;

####################################################################################################################################

在 prod 库切换几个日志看备库上时候有归档

备库执行日志应用同步语句，开始运行物理 DG

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

#因为有备重做日志，所以可以加 using current logfile 语句,实现实时应用

select sequence#, applied from v$archived_log where applied='YES' order by sequence#; #yes说明日志同步了

主库

conn scott/tiger

create table t(i int);

insert into t values(1);

commit;

conn / as sysdba

alter system switch logfile;

在 standby 库上执行:

alter database recover managed standby database cancel;

alter database open

conn scott/tiger

select * from t;

conn / as sysdba

select open_mode from v$database;

注意:物理备库 open 是默认 readonly 模式的

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

启动数据同步语句后，数据库又变为 mounted 状态

###################################################################3

切换到standby 为主库（此时主库没有standby日志 4组）

p81

主库

select open_mode,database_role,switchover_status from v$database; 显示TO STANDBY

alter database commit to switchover to physical standby; 或者 alter database commit to switchover to physical standby with session shutdown;

shutdown immediate;

startup nomount;

alter database mount standby database; #这条命令就是把主库切成备库

备库

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

alter database recover managed standby database;

alter database commit to switchover to primary ;#这条命令就是把备库切成主库（mount 模式下）

alter database commit to switchover to primary with session shutdown;

shutdown immediate;

startup;

在 prod 库上执

alter database add standby logfile ('/u01/app/oracle/oradata/prod/standby01.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/prod/standby02.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/prod/standby03.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/prod/standby04.log') size 50m;

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

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

alter database recover managed standby database cancel;

alter database open;

#########################################################################################################

dg_change

#########################################################################################################

主库创建表备份同步及确认

primary 窗口

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ WRITE PRIMARY TO STANDBY

sql>sqlplus shihf/oracle

sql>create table t6 as select * from t5;

standby 窗口1

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY RECOVERY NEEDED

SQL> alter database recover managed standby database;

alter database recover managed standby database

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY NOT ALLOWED

sql>alter database open;

Database altered.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ ONLY PHYSICAL STANDBY NOT ALLOWED

SQL> conn shihf/oracle

Connected.

SQL> select * from t6;t6 存在

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 780824576 bytes

Fixed Size 2217424 bytes

Variable Size 486541872 bytes

Database Buffers 285212672 bytes

Redo Buffers 6852608 bytes

Database mounted.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY NOT ALLOWED

###################################################################################################################

主备库切换

主库

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ WRITE PRIMARY TO STANDBY

SQL> alter database commit to switchover to physical standby;

alter database commit to switchover to physical standby

*

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

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 nomount;

ORACLE instance started.

Total System Global Area 780824576 bytes

Fixed Size 2217424 bytes

Variable Size 557845040 bytes

Database Buffers 213909504 bytes

Redo Buffers 6852608 bytes

SQL> alter database mount standby database;

Database altered.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY TO PRIMARY

SQL>

备库

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY SWITCHOVER PENDING

SQL>

select sequence#,applied from

(select sequence#,applied

from v$archived_log

where applied='YES'

order by sequence# desc)

where rownum<=10;

SEQUENCE# APPLIED

---------- ---------

178 YES

177 YES

176 YES

173 YES

172 YES

171 YES

170 YES

169 YES

168 YES

167 YES

10 rows selected.

SQL> alter database commit to switchover to primary;

alter database commit to switchover to primary

*

ERROR at line 1:

ORA-16139: media recovery required

SQL> alter database recover managed standby database;

Database altered.

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup ;

ORACLE instance started.

Total System Global Area 780824576 bytes

Fixed Size 2217424 bytes

Variable Size 486541872 bytes

Database Buffers 285212672 bytes

Redo Buffers 6852608 bytes

Database mounted.

Database opened.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ WRITE PRIMARY RESOLVABLE GAP

SQL>

此时被库注意状态RESOLVABLE GAP（现在是主节点）

此时主库（现在为备节点）

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY RECOVERY NEEDED

SQL> alter database recover managed standby database;

alter database recover managed standby database

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY NOT ALLOWED

此时被库（现在为主节点）

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ WRITE PRIMARY TO STANDBY

######################################################################################

20150511 测试

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY RECOVERY NEEDED

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

Database altered.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

MOUNTED PHYSICAL STANDBY NOT ALLOWED

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in

progress

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 780824576 bytes

Fixed Size 2217424 bytes

Variable Size 494930480 bytes

Database Buffers 276824064 bytes

Redo Buffers 6852608 bytes

Database mounted.

Database opened.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ ONLY PHYSICAL STANDBY NOT ALLOWED

SQL> alter database recover managed standby database using current logfile disconnect from session; #实时同步

Database altered.

SQL> select open_Mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

####################################################################################

总结

主库（切为备）

open：

alter database commit to switchover to physical standby;

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

shutdown immediate

startup nomount;

alter database mount standby database;

备库（切为主）

mount；

alter database commit to switchover to primary;

可能alter database recover managed standby database;

shutdown immediate

startup ;

主库（现为备）

alter database recover managed standby database;

alter database recover managed standby database using current logfile disconnect from session; 实时同步（备库mount/open模式下）

alter database recover managed standby database cancel;

ITPub博客

DataGuard

最新文章