oracle查看master节点,oracle physical standby10g

物理 data guard

primary

standby

创建相同的数据库文件，在物理模式下，连存储目录都一致

主库创建数据库，备库创建数据库软件

1

master 产生控制文件

Alter database create standby controlfile as '/oracle/standby.ctl'(mount)

standby ftp-mget controlfiel.*.tar

2配置监听netmgr

master:

listener :192.168.78.111

本地命名

tom_master  192.168.78.111

tom_standby 192.168.78.222

standby:

listener :192.168.78.222

本地命名

tom_standby   192.168.78.222

tom_master    192.168.78.111

3.主节点备份并生成备用数据库控制文件

设置主节点为force Logging模式(为了双向切换,建议备用节点也设置为force logging模式

ALTER DATABASE FORCE LOGGING;

设置主节点为归档模式

登陆主节点,进行数据库备份，并生成备用数据库控制文件standby.ctl

master: shutdown immediate;

tar -cvf orahome.tar /oracle/ora_home

tar -cvf oradata.tar /oracle/ora_data(控制文件另外的目录)

tar -cvf oradump.tar /oracle/admin

standby:mget *.tar

4主节点创建spfile;参数文件(建议追加的在master开启时更改)

mget spfile;修改参数文件，归档

创建归档日志目录

mkdir /10garchive      /*.log_archive_dest1,log_archive_dest2*/

mkdir standbyachive   /*.standby_archive_dest*/

chown -R oracle.dba /10garchive /standbyarchive

master 后追加：注：追加的在数据库open更改master产生pfile;

*.DB_UNIQUE_NAME='tommaster'(名字随意取)

*.log_archive_config='DG_CONFIG=(tommaster,tomstandby) /*master,standby的别名，就上面名字*/

*.log_archive_dest_1='LOCATION=/10garchive/VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=tommaster'

*.log_archive_dest_2='SERVICE=tomstandby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=tomstandby'                           /*日志在standby别名的备机存放路径,tnsname连接标志符一致@tomstandby*/

#*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

#*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='toms_tandby'                              /*主机的服务器为standby*/

*.FAL_CLIENT='tom_master '                              /主机的别名，第一行一样*/

*.standby_archive_dest='/standbyarchive'

*.standby_file_management='AUTO'

以下可有可无,前面有就不写

#*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

#*.LOG_ARCHIVE_MAX_PROCESS=30

standby 追加

*.DB_UNIQUE_NAME='tomstandby'(名字随意取)

*.log_archive_config='DG_CONFIG=(tommaster,tomstandby) /*master,standby的别名，就上面名字*/

*.log_archive_dest_1='LOCATION=/10garchive/VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=tomstandby'

*.log_archive_dest_2='SERVICE=tommaster LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)       DB_UNIQUE_NAME=tomstandby'                           /*日志在standby别名的备机存放路径,tnsname连接标志符号一致,在次为@tommaster*/

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='tomstandby'                              /*主机的服务器为standby*/

*.FAL_CLIENT='tommaster '                              /主机的别名，第一行一样*/

*.standby_archive_dest='/standbyarchive'

*.standby_file_management='AUTO'

以下可有可无

#*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

#*.LOG_ARCHIVE_MAX_PROCESS=30

5.启动备库

master  open;

standby mount ;

standby 连接master是否正常

standby : sqlplu/nolog

conn / as sysdba;

startup mount;

standby:start redo Apply;(保证主库的lgwr进程日志更新在备库的目录)

Alter database recover managed standby database disconnect from session;

/*让备机恢复主库传来的日志，并且应用更新到本机)*/

6测试物理standby;

master :Alter system switch logfile;

standby:验证物理日志的正常传输工作；(由1，2可以确定是否正常工作3更直观)

desc v$archived_log;

1)select sequence#,applied from v$archived_log order by sequence#;

2)Select sequence#,first_time,next_time from v$archived_log order by sequence#;

SEQUENCE#      FIRST_TIME              NEXT_TIME

5             08-08-29:12:02:35       08-08-30:12:34:12

6             08-08-30:12:34:12       08-08-30:12:49:36

master:主库归档日志 alter system switch logfile;

standby: Select sequence#,first_time,next_time from v$archived_log order by sequence#

SEQUENCE#      FIRST_TIME              NEXT_TIME

5             08-08-29:12:02:35       08-08-30:12:34:12

6             08-08-30:12:34:12       08-08-30:12:49:36

7             08-08-30:12:49:36       08-08-30:12:58:14

(说明如果日期不正常，则to_char(first_time,'YY-MM-DD:hh24:mi:ss'))

3)主库建表，如果备库也产生该表，则正常

master: create user tompson identified by sys;grant connect,resource to tompson;

conn tompson/sys

create table test(id number,name varchar(10))

insert into test values (001,'oracle');

insert into test values (003,'linux');

commit;

select * from test;

sys:Alter system switch logfile;

standby:

mount:可以接受应用更新master传来的日志，不能查询

open read only:可以接受master日志，不能及时应用更新,但是可以查询,数据不能更新

目前standby是mount状态，但是不能查询，因此需要将数据库open read only状态,才能查询刚才创建的用户表，是否生效

以read_only状态打开数据库

1)停掉日志的应用更新(mount)

Alert database recover managed standby database cancel;

2)Alter database open;/*read only状态*/

3)conn tompson/sys    /*创建的用户生效*/

4)select * from test  /*master 创建数据生效*/

从read_only状态切换到及时更新状态(mount):

Alter database recover managed standby database disconnect from session;

7切换

1)参看状态

standby(mount):  select name,database_role from v$database;

NAME            DATABASE_ROLE

TOM            PHYSICAK STANDBY

master(open):    select name,database_role from v$database;

NAME            DATABASE_ROLE

TOM            PRIMARY

2)

master(open)

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

Shutdown immediate;

Startup nomount;

Alter database mount standby database;

Recover managed standby database disconnect;

参看角色

select name,database_role from v$database;

NAME           DATABASE_ROLE

TOM            PHYSICAL STANDBY            /*此时主机切换成备机,且及时接受更新日志状态

standby:(mount)直接运行

Alter database commit to switchover to primary;/*直接切换角色*/

Shutdown immediate;

Startup;

查看角色

select name,database_role from v$database;

NAME             DATABASE_ROLE

TOM               PRIMARY                /*此时备机切换成主机*/

Select sequence#,first_time,next_time from v$archived_log order by sequence#;

可以做下创建表的测试