全部博文（41）
分类： Oracle
2011-12-07 13:52:41
第一章 搭建Active DG
开始配置dg
Primary db:
1、 判断dataguard是否安装
SQL> select * from v$option where parameter = 'Oracle Data Guard';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Oracle Data Guard
TRUE
2、 修改主库处于归档模式
SQL> startup mount
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
3、 将primary 数据库置为FORCE LOGGING 模式。通过下列语句：
SQL> select FORCE_LOGGING from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
4、 主库创建密码文件
同一个Dataguard中所有数据库必须拥有独立的密码文件，并且保证sys用户拥有相同密码来保证redo的传输。
$ orapwd file=orapworcl password=welcome entries=3
5、 主库配置standby redo log
SQL> startup mount
Database mounted.
SQL> alter database add standby logfile group 4
2 ('/u4/ora11/app/orcl/oradata/orcl/dg_redo1.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5
2 ('/u4/ora11/app/orcl/oradata/orcl/dg_redo2.log') size 50m;
Database altered.
6 配置监听
主库：
$ cat listener.ora
ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.190)(PORT = 1522))
)
)
ADR_BASE_LISTENER = /u4/ora11/app/orcl
$ cat tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1522))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.tools.com)
)
)
DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg.tools.com)
)
)
备库：
$ cat lis*.ora
DG=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
)
)
ADR_BASE_LISTENER = /u4/dg/app/oradg
$ cat tnsnames.ora
LISTENER_DG =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg.tools.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.tools.com)
)
)
7、修改Primary 数据库客户端初始化参数文件
注：主要此处修改项较多，为了方便，我们首先创建并修改pfile，然后再通过pfile 重建spfile，你当然也可以通过alter system set 命令直接修改spfile 内容。
SQL> create pfile from spfile;
File created.
修改主库初始化参数文件，增加下列内容
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u4/ora11/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#--------配置standby 角色的参数用于角色转换
*.FAL_SERVER=dg
*.FAL_CLIENT=orcl
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='/u4/dg/app/oradg/oradata/dg','/u4/ora11/app/orcl/oradata/orcl '
*.LOG_FILE_NAME_CONVERT= '/u4/dg/app/oradg/oradata/dg','/u4/ora11/app/orcl/oradata/orcl '
然后将Pfile 拷贝到备库上
$ cp /u4/ora11/app/orcl/product/11.2.0/dbhome_1/dbs/initorcl.ora .
$ pwd
/u4/dg/app/oradg/product/11.2.0/dbhome_1/dbs
$ id
uid=1010(oradg) gid=110(dba) groups=110(dba)
8 在主库上创建备份库需要的控制文件
SQL> startup mount
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl.ctl';
Database altered.
9、用修改后的init.ora启动主库，然后创建主库的spfile
SQL> create spfile from pfile;
File created.
10、复制数据文件到standby 服务器(方式多样，不详述)
注意需要复制所有数据文件，备份的STANDBY控制文件及客户端初始化参数文件（这里最好把密码文件也复制到备库，否则后面有可能会报错，具体错误后面会体现）
在standby库操作：
$ cp -r /u4/ora11/app/orcl/oradata/orcl .
$ pwd
/u4/dg/app/oradg/oradata
$ ls
dg_bak orcl
$ mv orcl dg
$ cp /tmp/orcl.ctl /u4/dg/app/oradg/oradata/dg
$ cd /u4/dg/app/oradg/oradata/dg
$ ls
control01.ctl dg_redo2.log redo01.log redo03.log system01.dbf undotbs01.dbf
dg_redo1.log orcl.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
复制主库参数文件：
$ cp /u4/ora11/app/orcl/product/11.2.0/dbhome_1/dbs/initorcl.ora .
$ pwd
/u4/dg/app/oradg/product/11.2.0/dbhome_1/dbs
Standby db配置
1、 创建控制文件
$ mv orcl.ctl control01.ctl
$ cp control01.ctl control02.ctl
$ pwd
/u4/dg/app/oradg/oradata/dg
2、配置listener,tnsname
同前面
3、设置备库参数文件
$ cat initdg.ora
dg.__db_cache_size=603979776
dg.__java_pool_size=16777216
dg.__large_pool_size=16777216
dg.__oracle_base='/u4/dg/app/oradg'#ORACLE_BASE set from environment
dg.__pga_aggregate_target=872415232
dg.__sga_target=1308622848
dg.__shared_io_pool_size=0
dg.__shared_pool_size=654311424
dg.__streams_pool_size=0
*.audit_file_dest='/u4/dg/app/oradg/admin/dg/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/u4/dg/app/oradg/oradata/dg/control01.ctl','/u4/dg/app/oradg/oradata/dg/control02.ctl'
*.db_block_size=8192
*.db_domain='tools.com'
*.db_name='dg' ---这里其实应该设置为orcl，否则后面会报错，具体看后面文档
*.db_recovery_file_dest='/u4/dg/app/oradg/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u4/dg/app/oradg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.local_listener='LISTENER_DG'
*.memory_target=2177892352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=dg
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u4/dg/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#--------配置standby 角色的参数用于角色转换
*.FAL_SERVER=orcl
*.FAL_CLIENT=dg
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT ='/u4/ora11/app/orcl/oradata/orcl','/u4/dg/app/oradg/oradata/dg'
*.LOG_FILE_NAME_CONVERT='/u4/ora11/app/orcl/oradata/orcl','/u4/dg/app/oradg/oradata/dg'
4、创建密码文件：---这里其实最好直接复制主库密码文件，免得出错
$ orapwd file=orapwdg password=welcome entries=3
5、在备库上建立spfile
SQL> startup nomount
SQL> create spfile from pfile;
File created.
6、 尝试启动备库
SQL> startup nomount
SQL> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01103: database name 'ORCL' in control file is not 'DG'
故障处理：在standby环境中db_name在主库和从库必须是一样的
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DG
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string dg
SQL> alter system set db_name='orcl' scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup nomount
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> alter database mount standby database;
Database altered.
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string dg
SQL>
但是在open的时候报错了
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u4/dg/app/oradg/oradata/dg/system01.dbf'
故障处理：
查看备库：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
查看主库：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
FAILED DESTINATION
状态怎么是这个呢？
再次查看主库相关参数
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=dg LGWR ASYNC VALID_F
OR=(ONLINE_LOGFILES,PRIMARY_RO
LE) DB_UNIQUE_NAME=dg
没看出有什么问题
察看主库alert.log，发现错误如下
------------------------------------------------------------
Errors in file /u4/ora11/app/orcl/diag/rdbms/orcl/orcl/trace/orcl_arc1_12785.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC1]: Heartbeat failed to connect to standby 'dg'. Error is 16191.
Tue Dec 06 20:43:10 2011
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
莫非是密码文件的问题？
尝试直接ftp主库的密码文件到备用库
$ mv orapwdg orapwdg.bak
$ cp /u4/ora11/app/orcl/product/11.2.0/dbhome_1/dbs/orapworcl .
$ mv orapworcl orapwdg
然后重新启动主库和备库，发现备库居然开始应用日志了
SQL> startup nomount
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select OPEN_MODE from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
看来就是密码文件的问题啊，哈哈
日志是传过去了，但是是否已经apply到备库了呢？
在备库：
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
50 YES
51 YES
52 YES
53 YES
下面检测下在主库建立的表是否应用到了备库
SQL> select * from test;
no rows selected
发现表已经有了
下面尝试插入数据
在主库：
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> !id
uid=1008(ora11) gid=110(dba) groups=110(dba)
SQL>
到备库查询：
SQL> select * from test;
TEL
----------
1
SQL> !id
uid=1010(oradg) gid=110(dba) groups=110(dba)
SQL>
发现已经有了，做到了read only并且可以应用日志！
