一、主库的配置
1、创建归档目录和standby redo log存放目录
注:如果归档目录使用的是快速恢复区,可以不用为归档再创建单独目录。standby redo log存放目录是为standby redo log使用的,主库是可以不建立standby redo log,但是为了以后角色切换
例:
注:需要注意的是,同一个Dara Guard配置中所有数据库必须拥有独立的秘钥文件,并且必须保证同一个Data配置中,因为REDO传输服务是通过认证的网络回话来传输REDO数据,而回话使用包含在秘钥文件中的SYS用户密码来认证。如果已经存在密码文件可以不建立。
语法:
[oracle@panda oradata]$ orapwd
Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>
where
file - name of password file (required),
password - password for SYS will be prompted if not specified at command line,
entries - maximum number of distinct DBA (optional),
force - whether to overwrite existing file (optional),
ignorecase - passwords are case-insensitive (optional),
nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
There must be no spaces around the equal-to (=) character.
例:
对于最大保护和最高可用性模式,建议为Standby数据库配置Standby Redologs(不配置也可以,Oracle将在Standby数据库端自动创建归档文件,并虚拟为一组Standby Redologs),并使用LGWR SYNC模式传输REDO数据。
Standby Redologs文件大小至少要与Online redologs文件大小相同,并且Standby Redologs日志文件组数至少要比Prlmary文件组数多一个(参考公式:(每线程的日志组数+1)*最大进程数)。
4、将数据库改force loggings模式
二、在备库上的操作
注意:备库要在做下面操作前要先安装好ORACLE软件,这里就不说怎么安装数据库软件了
1、创建归档目录和standby redo log存放目录
注:如果归档目录使用的是快速恢复区,可以不用为归档再创建单独目录。standby redo log存放目录是为standby redo log使用的,主库是可以不建立standby redo log,但是为了以后角色切换
例:
[root@panda ~]# su - oracle
[oracle@panda oracle]$ echo $ORACLE_SID
panda
[oracle@panda oracle]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@panda oracle]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0
[oracle@panda ~]$ cd $ORACLE_BASE
[oracle@panda oracle]$ pwd
/u01/app/oracle
[oracle@panda oracle]$ mkdir -p $ORACLE_BASE/standby/
[oracle@panda oracle]$ ls -ld standby/
drwxr-xr-x 2 oracle oinstall 4096 Feb 6 22:36 standby/
[oracle@panda oracle]$ mkdir -p $ORACLE_BASE/arch
[oracle@panda oracle]$ ls -ld arch/
drwxr-xr-x 2 oracle oinstall 4096 Feb 6 22:39 arch/
2、创建秘钥文件(如果文件不存在的话)
注:需要注意的是,同一个Dara Guard配置中所有数据库必须拥有独立的秘钥文件,并且必须保证同一个Data配置中,因为REDO传输服务是通过认证的网络回话来传输REDO数据,而回话使用包含在秘钥文件中的SYS用户密码来认证。如果已经存在密码文件可以不建立。
语法:
[oracle@panda oradata]$ orapwd
Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>
where
file - name of password file (required),
password - password for SYS will be prompted if not specified at command line,
entries - maximum number of distinct DBA (optional),
force - whether to overwrite existing file (optional),
ignorecase - passwords are case-insensitive (optional),
nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
There must be no spaces around the equal-to (=) character.
例:
orapwd file=$ORACLE_HOME/dbs/orapwpanda password=sys entries=30 force=y
3、配置Standby Redologs
对于最大保护和最高可用性模式,建议为Standby数据库配置Standby Redologs(不配置也可以,Oracle将在Standby数据库端自动创建归档文件,并虚拟为一组Standby Redologs),并使用LGWR SYNC模式传输REDO数据。
Standby Redologs文件大小至少要与Online redologs文件大小相同,并且Standby Redologs日志文件组数至少要比Prlmary文件组数多一个(参考公式:(每线程的日志组数+1)*最大进程数)。
[oracle@panda ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 6 22:47:26 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 1000
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 46 52428800 512 1 YES INACTIVE 1571632 06-FEB-15 1592154 06-FEB-15
2 1 47 52428800 512 1 NO CURRENT 1592154 06-FEB-15 2.8147E+14
3 1 45 52428800 512 1 YES INACTIVE 1545037 05-FEB-15 1571632 06-FEB-15
SQL>
alter database add standby logfile group 11 ('/u01/app/oracle/standby/redo11.log') size 50M;
alter database add standby logfile group 12 ('/u01/app/oracle/standby/redo12.log') size 50M;
alter database add standby logfile group 13 ('/u01/app/oracle/standby/redo13.log') size 50M;
alter database add standby logfile group 14 ('/u01/app/oracle/standby/redo14.log') size 50M;
注:Standby Redologs只有备库使用,在主库配置Standby Redologs是为了有一天要主备角色切换时能快速切换。这里只是自己笔记本环境,所以日志大小是50M,实际生产环境肯定要比这个大的多。
4、将数据库改force loggings模式
alter database force logging;
5、配置初始化参数文件
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! vim /u01/app/oracle/product/11.2.0/dbs/initpanda.ora
#修改或添加下列参数到initpanda.ora
*.log_archive_format='arch_%d_%r_%t_%s.log'
*.service_names='panda'
*.db_unique_name='xiongda'
*.log_archive_config='DG_CONFIG=(xiongda,xionger)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=xiongda'
*.log_archive_dest_2='service=xionger_192.168.1.11 lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=xionger'
*.log_archive_dest_state_2='DEFER'
*.log_archive_max_processes=4
*.standby_file_management='AUTO'
*.remote_login_passwordfile='EXCLUSIVE'
#下面的配置是为了角色转换时使用
*.fal_server='xionger_192.168.1.11'
*.fal_client='xiongda_192.168.1.10'
保存退出,继续下面操作
SQL> create spfile from pfile;
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 339740384 bytes
Database Buffers 176160768 bytes
Redo Buffers 5869568 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.
SQL> alter database set standby database to maximize AVAILABILITY;
Database altered.
SQL> Alter database open;
Database altered.
6、编辑$ORACLE_HOME/network/admin/tnsnames.ora文件,添加下面内容
xiongda_192.168.1.10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SID = panda)
)
)
xionger_192.168.1.11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SID = panda)
)
)
chown -R oracle.oinstall /u01/app/oracle/product/11.2.0/network/admin/tnsnames.ora
lsnrctl stop
lsnrctl start
二、在备库上的操作
注意:备库要在做下面操作前要先安装好ORACLE软件,这里就不说怎么安装数据库软件了
[root@panda ~]# su - oracle
[oracle@panda oracle]$ echo $ORACLE_SID
panda
[oracle@panda oracle]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@panda oracle]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0
1、 创建必要的目录
mkdir -p /u01/app/oracle/admin/panda/adump
mkdir -p /u01/app/oracle/admin/panda/dpdump
mkdir -p /u01/app/oracle/admin/panda/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/panda
mkdir -p /u01/app/oracle/oradata/panda
mkdir -p /u01/app/oracle/product/11.2.0/dbs
chown -R oracle.oinstall /u01/app/oracle
mkdir -p /u01/app/oracle/standby
mkdir -p /u01/app/oracle/arch
chown -R oracle.oinstall /u01/app/oracle/standby /u01/app/oracle/arch
2、创建密码文件
注:和主库的密码文件的密码要一样
su - oracle
orapwd file=$ORACLE_HOME/dbs/orapwpanda password=sys entries=30
3、创建pfile文件
echo "*.db_name='panda'" >>$ORACLE_HOME/dbs/initpanda.ora
chown -R oracle.oinstall $ORACLE_HOME/dbs/initpanda.ora
4、编辑$ORACLE_HOME/network/admin/listener.ora文件,添加下面内容,启动监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = panda)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0)
(SID_NAME = panda)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521
)
重启监听
lsnrctl stop
lsnrctl start
注:在备库配置静态监听是为了后面使用rman时通过tcp协议连接备库时使用,不然后面连接会报错。如果不使用rman的duplicate来生成备库,而是手工生成备库,不需要配置静态监听。
5、编辑$ORACLE_HOME/network/admin/tnsnames.ora文件,添加下面内容
xiongda_192.168.1.10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SID = panda)
)
)
xionger_192.168.1.11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SID = panda)
)
)
chown -R oracle.oinstall $ORACLE_HOME/network/admin/tnsnames.ora
6、使用pfile文件启动数据库
[oracle@panda admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 6 23:43:27 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbs/initpanda.ora
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@panda admin]$
7、创建备库
rman
connect target sys/sys@192.168.1.10:1521/panda
connect auxiliary sys/sys@192.168.1.11:1521/panda
run {
allocate channel c1 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
duplicate target database
for standby nofilenamecheck
from active database
spfile
parameter_value_convert 'xiongda','xionger'
set db_unique_name='xionger'
set control_files='/u01/app/oracle/oradata/panda/control01.ctl','/u01/app/oracle/oradata/panda/control02.ctl','/u01/app/oracle/oradata/panda/control03.ctl'
set fal_server='xiongda_192.168.1.10'
set fal_client='xionger_192.168.1.11'
set log_archive_dest_1='location=/u01/app/oracle/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=xionger'
set log_archive_dest_2='service=xiongda_192.168.1.10 lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=xiongda'
set log_archive_dest_state_2='enable'
set log_file_name_convert='/u01/app/oracle/oradata/panda/','/u01/app/oracle/oradata/panda/','/u01/app/oracle/standby/','/u01/app/oracle/standby/'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(xiongda,xionger)'
;
sql channel c1 "alter system archive log current";
sql channel s1 "alter database recover managed standby database using current logfile disconnect";
}
8、重启备库,设置备库
shutdown immediate
create pfile from spfile;
startup mount
alter database set standby database to maximize availability;
alter database recover managed standby database using current logfile disconnect;
9、连接到主库,设置归档传输目录为enable,并切换日志
sqlplus sys/sys@192.168.1.10 as sysdba
alter system set log_archive_dest_state_2=enable scope=both;
alter system switch logfile;
10、设置备库read only打开
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect;
说明:这个例子是配置的最大可用性(Maximum availability)的dg,如果要配置其他类型的dg,流程是大体一样的只不过一些参数的配置不同。
如:log_archive_dest_2参数的配置,以及是否需要standby redo log等。