开始之前先把主库和备库的tnsnames.ora文件创建好(如下),并且启动数据库服务和监听服务
然后测试联通性,测试方法为:
sqlplus /nolog
conn system/oracle@primary
conn system/oracle@standby
1.设置主库为 force logging,并建立pfile文件
SQL> alter database force logging;
SQL> create pfile from spfile;
2.设置主库为归档模式:
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
SQL> shutdown immediate
3.创建口令文件(删除原来口令文件)
在/oracle/product/10g/dbs目录中执行以下操作
orapwd file=orawdorcl.ora password=oracle entries=5
4.修改主参数文件
文件末尾处添加
log_archive_dest_1='LOCATION=/oracle/oradata/archive/' 此目录需要创建
log_archive_dest_state_1=enable
log_archive_dest_2='SERVICE=standby'
log_archive_dest_state_2=enable
log_archive_format='%t_%s_%r.arc'
log_archive_max_processes=2
fal_server=standby
fal_client=primary
db_unique_name=orcl
5.主库创建控制文件
SQL>startup mount;
SQL>alter database create standby controlfile as
2 ‘/oracle/oradata/orcl/standby.ctl’;
SQL>alter database open;
SQL>shutdown immedaite
6.删除备库上相关目录
删除/oracle/oradata目录下的orcl目录和/oracle/admin/orcl目录
7.拷贝主库上的相关目录和文件到备库
scp -r /oracle/oradata/orcl 192.168.10.3:/oracle/oradata
scp -r /oracle/admin/orcl 192.168.10.3:/oracle/admin
scp initorcl.ora 192.168.10.3:/oracle/product/10g/dbs/
scp orawdorcl.ora 192.168.10.3:/oracle/product/10g/dbs/
8.删除备库上control1,2,3.ctl文件(/oracle/oradata/orcl/)
rm -f control0*
mv standby.ctl control01.ctl
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl
9.更改备库initorcl.ora文件
log_archive_dest_1='LOCATION=/oracle/oradata/archive/' 此目录需要创建
log_archive_dest_state_1=enable
log_archive_dest_2='SERVICE=primay'
log_archive_dest_state_2=enable
log_archive_format='%t_%s_%r.arc'
log_archive_max_processes=2
fal_server=primary
fal_client=standby
db_unique_name=orcl
10.移除主库上的spfile文件
rm -f spfileorcl.ora(/oracle/product/10g/dbs)
11.主库备库都应用pfile文件
SQL> create spfile from pfile;
12.测试主库和备库的监听是否都开启
Ps -ef | grep tns
Sqlplus system/oracle@primary
Sqlplus system/oracle@standby
13.备库执行DG命令
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> select name,database_role from v$database;
SQL>alter database recover managed standby database disconnect from session; 接收来自主库的数据
SQL> alter system switch logfile; 主库加速日志传输到备库(主库执行)
SQL> /
SQL> /
14.主库创建表,测试备库是否同步
SQL> Create table dgtest (id int, name char(10));
SQL> insert into dgtest values (0, ‘test’);
SQL> insert into dgtest values (1, ‘test’);
SQL> commit
SQL> Select * from dgtest;
然后执行上边的命令加速传输,执行三次。
15.备库查询数据是否同步过来
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
SQL> select sequence#,applied from v$archived_log order by sequence#;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select * from dgtest;
SQL> alter database recover managed standby database disconnect from session;
附DG物理备库常用命令
Startup nomount;
Alter database mount standby database;
Select name,database_role from v$database;
Alter database recover managed standby database disconnect from session;
Alter system archvie log current;
Alter database recover managed standby database cancel;
Alter database open read only;
Select sequence#,first_time,next_time from v$archived_log order by sequence#;
Select sequence#,applied from v$archived_log order by sequence#;
注:两种模式只能运行一种,第一种是接受数据模式。第二种是查询数据模式,查询中,并没有接受来自主库的数据。
转载于:https://blog.51cto.com/sunliyan/933364