在linux AS4 单机上配dataguard
在两台机器以前在一个项目配过9i 的dataguard,感觉比较简单,这两天抽空在一台linux AS4机上配了一个10g dataguard. 开始有些参数一直没有配对,出现日志不能传到standby的库,日志不能应用,还有一个不能以readonly方式打开,还好都解决了下面为大致的步骤:
一, 库的配置:
主库:maindb
standby 库: standb
二, 建立的过程:
1.dbca库建好后.
alter database force logging;
alter system set log_archive_dest_1='LOCATION=/primary_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=maindb';
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
create pfile from spfile; ----用于参数修改
--建立standby库的controlfile:
!mkdir /dgdata/standb
alter database create standby controlfile as '/dgdata/standb/standbcontrol01.ctl'
shutdown immediate;
2. copy maindb 的数据文件到 standby 库standb的目录:
cp /primarydata/maindata/* /dgdata/standb/
rm /dgdata/standb/control01.ctl /dgdata/standb/control02.ctl /dgdata/standb/control03.ctl
cp /dgdata/standb/standbcontrol01.ctl /dgdata/standb/standbcontrol02.ctl
cp /dgdata/standb/standbcontrol01.ctl /dgdata/standb/standbcontrol03.ctl
3.配listener.ora 和tnsnames.ora
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = maindb)
(ORACLE_HOME = /app/oracle/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = standb)
(ORACLE_HOME = /app/oracle/product/10.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
tnsnames.ora 为:
MAINDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = maindb)
)
)
STANDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standb)
)
)
lsnrctl start
tnsping maindb --保证连通
tnsping standb --保证连通
4.配主库maindb的参数文件:
1.cp initmaindb.ora intstandb.ora ---standb使用
2. maindb 的参数文件initmaindb.ora增加以下部分:
##maindb 处于primary生效部分:
*.log_archive_dest_1='LOCATION=/primary_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=maindb'
*.log_archive_dest_2='SERVICE=standb LGWR ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_config='DG_CONFIG=(maindb,standb)'
*.remote_login_passwordfile=exclusive
*.log_archive_format='maindb%t_%s_%r.arc'
*.log_archive_max_processes=5
*.db_unique_name=maindb
*.db_file_name_convert='/primarydata/maindb','/dgdata/standb'
*.log_file_name_convert='/primarydata/maindb','dgdata/standb'
### maindb 切换到standby role时生效的部分
*.standby_archive_dest='/primary_arch/standby_arch'
*.standby_file_management=AUTO
*.FAL_CLIENT=maindb
*.FAL_SERVER=standb
3. standb 参数的创始过程:
修改 intstandb.ora中的
*.audit_file_dest='/app/oracle/admin/standb/adump'
*.background_dump_dest='/app/oracle/admin/standb/bdump'
*.control_files='/dgdata/standb/standbcontrol01.ctl','/dgdata/standb/standbcontrol02.ctl','/dgdata/standb/standbcontrol03.ctl'
*.core_dump_dest='/app/oracle/admin/standb/cdump'
*.db_recovery_file_dest='/dg_flashback'
*.user_dump_dest='/app/oracle/admin/standb/udump'
standb 的参数文件 intstandb.ora增加以下部分:
###standb 处于standby role 时生效
*.log_archive_dest_1='LOCATION=/standby_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_config='DG_CONFIG=(maindb,standb)'
*.remote_login_passwordfile=exclusive
*.log_archive_format='standb%t_%s_%r.arc'
*.log_archive_max_processes=5
*.db_unique_name=standb
*.lock_name_space=standb
*.FAL_SERVER=maindb
*.FAL_client=standb
*.standby_archive_dest='/standby_arch/standby_arch'
*.standby_file_management=AUTO
*.db_file_name_convert='/primarydata/maindb','/dgdata/standb'
*.log_file_name_convert='/primarydata/maindb','/dgdata/standb'
##standb 变为primary role进生效:
*.log_archive_dest_2='SERVICE=maindb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=maindb'
5. 启动maindb和standb
1.maindb正常启动:
create spfile from pfile;
startup
2.standb 库的启动:
create spfile from pfile;
startup nomount
alter database mount standby database ;
alter database recover managed standby database disconnect from session;---- 让stnadb 库处于日志恢复状态
切换到readonly:
alter database recover managed standby database cancel;
alter database open read only;
到这就配好了,可以通过主maindb:
alter system switch logfile;
select * from v$log;---查看 SEQUENCE#
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 25 52428800 1 YES INACTIVE 158773 22-SEP-08
2 1 26 52428800 1 NO CURRENT 159328 22-SEP-08
3 1 24 52428800 1 YES INACTIVE 158644 22-SEP-08
####在standb 上也查: 可以看到两个库的SEQUENCE#是一样的,当然也可以在主库建一张表,切换日志后到从库来查这张表是否已经过来.
SQL> set linesize 181
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 25 52428800 1 YES CLEARING 158773 22-SEP-08
2 1 26 52428800 1 YES CLEARING_CURRENT 159328 22-SEP-08
3 1 24 52428800 1 YES CLEARING 158644 22-SEP-08
---附建立时遇到的的ERROR:
1.主库在从库未配好时产生了一个archivelog 导致lag, standb 数据打开到readonly时一直报:后将miandb的那个archivelog cp到这个库的下面, 注册并recover 后解决:
SQL>alter database open read only;
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dgdata/standb/system01.dbf'
解决方法:
SQL>alter system register logfile '/standby_arch/standby_arch/maindb1_9_666068154.arc';
SQL>alter system recover standby database disconnect from session;
2. stand 的密码文件orapwd建成的时候有问题: 重建后解决
Errors in file /app/oracle/admin/maindb/bdump/maindb_arc0_5346.trc:
ORA-12154: TNS: 无法解析指定的连接标识符
FAL[server, ARC0]: Error 12154 creating remote archivelog file 'standb'
FAL[server, ARC0]: FAL archive failed, see trace file.
Mon Sep 22 04:07:08 2008
Errors in file /app/oracle/admin/maindb/bdump/maindb_arc0_5346.trc:
ORA-16055: 已拒绝 FAL 请求
ARCH: FAL archive failed. Archiver continuing
Mon Sep 22 04:07:08 2008
ORACLE Instance maindb - Archival Error. Archiver continuing.
Mon Sep 22 04:13:28 2008
Error 12154 received logging on to the standby
Mon Sep 22 04:13:28 2008
在trace 中:/app/oracle/admin/maindb/bdump/maindb_arc0_5346.trc
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: 权限不足
'
*** 2008-09-22 04:18:29.500 61283 kcrr.c
Error 1031 received logging on to the standby
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'standb'
Error 1031 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standb'
ORA-01031: 权限不足
*** 2008-09-22 04:18:29.501 61283 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'standb'. Error is 1031.
*** 2008-09-22 04:18:29.501 59526 kcrr.c
kcrrfail: dest:2 err:1031 force:0 blast:1
*** 2008-09-22 04:23:29.595
Redo shipping client performing standby login
OCIServerAttach failed -1
3. 参数写成:*.log_file_name_convert='/primarydata/maindb','dgdata/standb' 改成:*.log_file_name_convert='/primarydata/maindb','dgdata/standb' 后问题解决:
Media Recovery add redo thread 1
*** 2008-09-22 04:18:50.390 1102 krsm.c
Managed Recovery: Active posted.
ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
ORA-00312: 联机日志 1 线程 1: 'dgdata/standb/redo01.log'
ORA-27037: 无法获得文件状态
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
ORA-00312: 联机日志 1 线程 1: 'dgdata/standb/redo01.log'
ORA-27037: 无法获得文件状态
Linux Error: 2: No such file or directory
Additional information: 3
*** 2008-09-22 04:18:50.395 61283 kcrr.c
Clearing online redo logfile 1 dgdata/standb/redo01.log
ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
ORA-00312: 联机日志 1 线程 1: 'dgdata/standb/redo01.log'
ORA-27037: 无法获得文件状态
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00344: 无法重新创建联机日志 'dgdata/standb/redo01.log'
ORA-27040: 文件创建错误, 无法创建文件
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10834762/viewspace-462509/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10834762/viewspace-462509/
扫一扫
2025
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
