怎么查看oracle primary keydataguard里面primary中 standby
点击联系发帖人
时间：2017-05-12 03:43
博客访问： 3324365
博文数量： 176
注册时间：
认证徽章：
Happy is the man who is living by his hobby.
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
&&&&&&DATAGUARD是通过建立一个PRIMARY和STANDBY组来确立其参照关系。
&&&&&&STANDBY一旦创建，DATAGUARD就会通过将主数据库(PRIMARY)的REDO传递给STANDBY数据库，然后在STANDBY中应用REDO实现数据库的同步。
&&&&&&有两种类型的STANDBY：物理STANDBY和逻辑STANDBY
&&&&&&物理STANDBY提供与主数据库完全一样的拷贝（块到块），数据库SCHEMA，包括索引都是一样的。它是直接应用REDO实现同步的。
&&&&&&逻辑STANDBY则不是这样，在逻辑STANDBY中，逻辑信息是相同的，但物理组织和数据结构可以不同，它和主库保持同步的方法是将接收的REDO转换成SQL语句，然后在STANDBY上执行SQL语句。逻辑STANDBY除灾难恢复外还有其它用途，比如用于用户进行查询和报表。
1、安装环境
在primary搭建数据库软件，建立lsnrctl监听，采用dbca搭建实例，在standby上搭建数据库软件，建立监听，但是不需要采用dbca建立实例。
如何在linux上搭建oracle数据库，请参考以前的blog实验：
操作系统：
都是centos6.4
oracle软件版本： oracle 11.2.0.1.0
primary库（192.168.121.217）、standby库（192.168.121.218）
db_unique_name： primary库（pdunq）、standby库（pdunq_dg）
2、准备工作 在primary上操作
2.1、打开Forced Logging 模式
先确认primary库处于归档模式
Database log mode
& & & Archive Mode
Automatic archival
& & & Enabled
Archive destination
& & & USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence & & 362
Next log sequence to archive & 364
Current log sequence
SQL> alter da
Database altered.
确认primary库是归档模式
添加standby文件
alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 20M;
alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 20M;
alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
select * from v$logfile order by 1;
2.3 准备参数文件
2.3.1 生成pfile
shutdown immediate
2.3.2 修改pfile
cp $ORACLE_HOME/dbs/initpowerdes.ora $ORACLE_HOME/dbs/initpowerdes.ora.bak
vim $ORACLE_HOME/dbs/initpowerdes.ora
*.db_unique_name=pdunq
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/pwerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg &lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'
2.3.3 生成spfile
startup #这里可以启动也可以不启动，这里不启动，后面就要记得让新的参数文件生效
2.4 修改监听文件
[oracle@powerlong4 admin]$ vim listener.ora&
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
& (SID_LIST =
& & (SID_DESC =
& & & (SID_NAME = PLSExtProc)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
& & & (PROGRAM = extproc)
& & (SID_DESC =
& & & (SID_NAME = powerdes)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
LISTENER =
& (DESCRIPTION_LIST =
& & (DESCRIPTION =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
& & & (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
ADR_BASE_LISTENER = /oracle/app/oracle
INBOUND_CONNECT_TIMEOUT_listener=10
2.5，修改tns配置文件
[oracle@powerlong4 admin]$ vim tnsnames.ora&
& (DESCRIPTION =
& & (ADDRESS_LIST =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
& & (CONNECT_DATA =
& & & (SERVICE_NAME = pdunq)
& (DESCRIPTION =
& & (ADDRESS_LIST =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
& & (CONNECT_DATA =
& & & (SID = powerdes)
& & & & (SERVER = DEDICATED)
EXTPROC_CONNECTION_DATA =
& (DESCRIPTION =
& & (ADDRESS_LIST =
& & & (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
& & (CONNECT_DATA =
& & & (SID = PLSExtProc)
& & & (PRESENTATION = RO)
2.6 监听服务重启
lsnrctl stop
lsnrctl start
2.7 primary上配置最大可用模式：
SQL>startup
SQL>alter database set standby database to m
2.8 备份数据库
backup data
backup current con
备份结束后会在闪回区产生备份文件
3，数据库配置 standby上
3.1 建立相应的文件目录
包括dump文件目录，数据文件目录，通过查看，保持和primary一样的路径地址
3.2 从primary上copy数据文件到standby上
在主库上执行：
ps：在primary上执行
copy闪回区内容
copy闪回文件
cd /oracle/app/oracle/flash_recovery_area/
scp -r ./* 192.168.121.218:/oracle/app/oracle/flash_recovery_area/
copy参数文件
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
copy监听文件
cd /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
3.3 在standby库 修改配置文件 在standby上修改
[oracle@powerlong5 admin]$ vim listener.ora&
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
& (SID_LIST =
& & (SID_DESC =
& & & (SID_NAME = PLSExtProc)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
& & & (PROGRAM = extproc)
& & (SID_DESC =
& & & (SID_NAME = powerdes)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
LISTENER =
& (DESCRIPTION_LIST =
& & (DESCRIPTION =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
& & & (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
在standby修改tns文件&
3.4，修改参数文件
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdes)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg &lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'
PS:将*.log_archive_dest_2=后面的DB_UNIQUE_NAME改成primary的DB_UNIQUE_NAME值改为pdunq，这样在做switchover的时候，新的primary能通过这个将redo日志传到新的standby上面去。
log_archive_dest_N 目的是告诉数据库，把归档放到那里去可选项，首先是本地，然后考虑远程的从库，所以，假设A是主库，B是从库，切换之后B是主库，A是从库，所以，log_archive_dest_N需要设置为对方
3.5，重启监听 standby
[oracle@powerlong5 dbs]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-:36
Copyright (c) , Oracle. &All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
The command completed successfully
[oracle@powerlong5 dbs]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-:41
Copyright (c) , Oracle. &All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias & & & & & & & & & & LISTENER
Version & & & & & & & & & TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date & & & & & & & &10-FEB-:41
Uptime & & & & & & & & & &0 days 0 hr. 0 min. 0 sec
Trace Level & & & & & & & off
Security & & & & & & & & &ON: Local OS Authentication
SNMP & & & & & & & & & & &OFF
Listener Parameter File & /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File & & & & /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening Endpoints Summary...
& (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
& (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
& Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "powerdes" has 1 instance(s).
& Instance "powerdes", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@powerlong5 dbs]$
3.6，恢复数据库
在standby库上操作
[oracle@powerlong5 admin]$ rman target sys/syxxlxxxx58@PD1 auxiliary /
Argument & & Value & & & & &Description
-----------------------------------------------------------------------------
target & & & quoted-string &connect-string for target database
catalog & & "ed-string &connect-string for recovery catalog
nocatalog & &none & & & & & if specified, then no recovery catalog
cmdfile & & "ed-string &name of input command file
log & & & & "ed-string &name of output message log file
trace & & & "ed-string &name of output debugging message log file
append & & & none & & & & & if specified, log is opened in append mode
debug & & & &optional-args &activate debugging
msgno & & & &none & & & & & show RMAN-nnnn prefix for all messages
send & & & & quoted-string &send a command to the media manager
pipe & & & & string & & & & building block for pipe names
timeout & & &integer & & & &number of seconds to wait for pipe input
checksyntax &none & & & & & check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "end-of-file": expecting one of: "double-quoted-string, identifier, single-quoted-string, "
RMAN-01007: at line 0 column 0 file: command line arguments
[oracle@powerlong5 admin]$&
[oracle@powerlong5 admin]$&
[oracle@powerlong5 admin]$
报错，看下是否standby没有启动导致？
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
[root@powerlong5 ~]# mount -t tmpfs shmfs -o size=12g /dev/shm
[root@powerlong5 ~]# df -h
Filesystem & & & & & &Size &Used Avail Use% Mounted on
/dev/sda3 & & & & & & &57G & 45G &8.9G &84% /
tmpfs & & & & & & & & &12G & & 0 & 12G & 0% /dev/shm
/dev/sda1 & & & & & & 190M & 51M &129M &29% /boot
/dev/sr0 & & & & & & &4.1G &4.1G & & 0 100% /media/CentOS_6.4_Final
shmfs & & & & & & & & &12G & & 0 & 12G & 0% /dev/shm
[root@powerlong5 ~]#&
SQL> startup
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> startup nomount
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
去primary库上查询下audit路径
SQL> show parameter audit_file_dest
------------------------------------ ----------- ------------------------------
audit_file_dest
& & string
/oracle/app/oracle/admin/powerdes/adump
然后在standby上操作
SQL> startup nomount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.1358E+10 bytes
Fixed Size
& &2216744 bytes
Variable Size
Database Buffers
Redo Buffers
去primary修改sys密码：
SQL> alter user sys identified by "syxxlxxxx58";
User altered.
在standby库执行rman target sys/syspl1758@PD1 auxiliary /，如下所示：
[oracle@powerlong5 ~]$ rman target sys/syspl1758@PD1 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 7 19:08:16 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: POWERDES (DBID=)
connected to auxiliary database: POWERDES (not mounted)
RMAN> run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=767 device type=DISK
allocated channel: c2
channel c2: SID=1150 device type=DISK
Starting Duplicate Db at 07-FEB-15
contents of Memory Script:
&&&set until scn
&&&restore clone standby controlfile;
executing Memory Script
executing command: SET until clause
Starting restore at 07-FEB-15
channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece /oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_ncsnf_TAG252_bfct20tb_.bkp
channel c1: piece handle=/oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_ncsnf_TAG252_bfct20tb_.bkp tag=TAG252
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
output file name=/oracle/data_ora/powerdes/control01.ctl
output file name=/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl
Finished restore at 07-FEB-15
contents of Memory Script:
&&&sql clone \'alter database mount standby database\';
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
&&&set until scn
&&&set newname for datafile
&\"/home/oradata/pwerdes/system01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/sysaux01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/undotbs01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/users01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/plas01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/pl01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/help01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/adobelc01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/sms01.dbf\";
&&&restore
&&&clone database
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-FEB-15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /home/oradata/pwerdes/system01.dbf
channel c1: restoring datafile 00002 to /home/oradata/pwerdes/sysaux01.dbf
channel c1: restoring datafile 00003 to /home/oradata/pwerdes/undotbs01.dbf
channel c1: restoring datafile 00004 to /home/oradata/pwerdes/users01.dbf
channel c1: restoring datafile 00006 to /home/oradata/pwerdes/plas01.dbf
channel c1: restoring datafile 00007 to /home/oradata/pwerdes/pl01.dbf
channel c1: restoring datafile 00008 to /home/oradata/pwerdes/help01.dbf
channel c1: restoring datafile 00009 to /home/oradata/pwerdes/adobelc01.dbf
channel c1: restoring datafile 00010 to /home/oradata/pwerdes/sms01.dbf
channel c1: reading from backup piece /oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_nnndf_TAG252_bfcsvxoz_.bkp
channel c1: piece handle=/oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_nnndf_TAG252_bfcsvxoz_.bkp tag=TAG252
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:04:05
Finished restore at 07-FEB-15
contents of Memory Script:
&&&switch clone datafile all;
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP= file name=/home/oradata/pwerdes/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP= file name=/home/oradata/pwerdes/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP= file name=/home/oradata/pwerdes/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP= file name=/home/oradata/pwerdes/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP= file name=/home/oradata/pwerdes/plas01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP= file name=/home/oradata/pwerdes/pl01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP= file name=/home/oradata/pwerdes/help01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP= file name=/home/oradata/pwerdes/adobelc01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP= file name=/home/oradata/pwerdes/sms01.dbf
contents of Memory Script:
&&&set until scn
&&&recover
&&&standby
&&&clone database
&&&&delete archivelog
executing Memory Script
executing command: SET until clause
Starting recover at 07-FEB-15
starting media recovery
archived log for thread 1 with sequence 302 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_302_.dbf
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_302_.dbf thread=1 sequence=302
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-FEB-15
Finished Duplicate Db at 07-FEB-15
released channel: c1
released channel: c2
RMAN> exit
3.7 &standby上修改参数文件
先关闭oracle
shutdown immediate
然后开始修改参数文件
cd &$ORACLE_HOME/dbs&
vim initpowerdes.ora
# 主要是修改db_unique_name
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='PD1'
*.fal_server='PD2'
*.global_names=FALSE
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(pdunq,pddgunq)'
重新创建参数文件
3.8 启动数据库
alter database mo
alter database
alter database
alter database
alter database recover managed standby database using current logfile dis
4，检查看到归档没有过来
Database log mode
& & & Archive Mode
Automatic archival
& & & Enabled
Archive destination
& & & ?/dbs/arch
Oldest online log sequence & & 0
Next log sequence to archive & 0
Current log sequence
看到归档信息为0，说明primary上的redo日志没有传到standby上来。
4.1，查看alert日志
[oracle@powerlong5 trace]$ tail -f /oracle/app/oracle/diag/rdbms/pdunq_dg/powerdes/trace/alert_powerdes.log&
MRP0 started with pid=41, OS id=21243&
MRP0: Background Managed Standby Recovery process started (powerdes)
&started logmerger process
Sat Feb 07 20:12:18 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 303
Completed: alter database recover managed standby database disconnect from session
&查看日志传输情况
& & select sequence#,first_time,next_time from v$archived_
SELECT sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') first_time,to_char(next_time,'yyyy-mm-dd hh24:mi:ss') next_time from v$archived_
问题分析解决：
primary主库上的alert日志有错：
Error 12154 received logging on to the standby
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc2_22609.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC2]: Heartbeat failed to connect to standby 'pdunq_dg'. Error is 12154.
错误很清晰了，主库无法检测到从库存在
tns 12154 错误，主库无法 tnsping pdunq_dg&
tnsping standby库报错
[oracle@powerlong4 admin]$ tnsping pdunq_dg
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-FEB-:26
Copyright (c) , Oracle. &All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
[oracle@powerlong4 admin]$
参数文件里面
*.log_archive_dest_2='SERVICE=pdunq_dg &lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
SERVICE=pdunq_dg 要和tnsnames.ora里面的保持一致。
4.2，去tnsnames.ora里面修改配置
去把tnsnames.ora里面的改成pdunq_dg即可。
重启lsnrctl，然后查看从库归档日志，有日志了，如下所示：
Database log mode
& & & Archive Mode
Automatic archival
& & & Enabled
Archive destination
& & & USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence & & 350
Next log sequence to archive & 0
Current log sequence
备库切换到open状态：
退出redo应用状态
SQL> alter database recover managed sta
Database altered.
PS：停止standby的redo应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;注意，此时只是暂时redo 应用，并不是停止Standby 数据库，standby 仍会保持接收只不过不会再应用接收到的归档，直到你再次启动redo 应用为止。类似mysql里面的stop slave功能;
打开standby上的oracle库
Database altered.&
再应用redo日志
SQL> alter database recover managed standby database using current&
Database altered.
去primary、standby库上面执行检查
SQL> select sequence#,applied from v$archived_
查看最新的scn：
SQL> select max(sequence#) from v$archived_
primary和standby都保持一致，OK，dataguard搭建完成。
&----------------------------------------------------------------------------------------------------------------
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
原博客地址： & & &
原作者：黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
阅读(43552) | 评论(0) | 转发(8) |
相关热门文章
给主人留下些什么吧！~~
你好，请问以下命令是什么意思？&（alter&database&drop&standby&logfile&)为什么要执行删除日志制作呢？添加standby文件alter&database&add&standby&logfile&group&4&('/home/oradata/powerdes/redo_dg_021.log')&size&20M;alter&database&add&standby&logfile&group&5&('/home/oradata/powerdes/redo_dg_022.log')&size&20M;alter&database&add&standby&logfile&group&6&('/home/oradata/powerdes/redo_dg_023.log')&size&20M;alter&
请登录后评论。}
博文数量： 176
注册时间：
认证徽章：
Happy is the man who is living by his hobby.
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
&&&&&&DATAGUARD是通过建立一个PRIMARY和STANDBY组来确立其参照关系。
&&&&&&STANDBY一旦创建，DATAGUARD就会通过将主数据库(PRIMARY)的REDO传递给STANDBY数据库，然后在STANDBY中应用REDO实现数据库的同步。
&&&&&&有两种类型的STANDBY：物理STANDBY和逻辑STANDBY
&&&&&&物理STANDBY提供与主数据库完全一样的拷贝（块到块），数据库SCHEMA，包括索引都是一样的。它是直接应用REDO实现同步的。
&&&&&&逻辑STANDBY则不是这样，在逻辑STANDBY中，逻辑信息是相同的，但物理组织和数据结构可以不同，它和主库保持同步的方法是将接收的REDO转换成SQL语句，然后在STANDBY上执行SQL语句。逻辑STANDBY除灾难恢复外还有其它用途，比如用于用户进行查询和报表。
1、安装环境
在primary搭建数据库软件，建立lsnrctl监听，采用dbca搭建实例，在standby上搭建数据库软件，建立监听，但是不需要采用dbca建立实例。
如何在linux上搭建oracle数据库，请参考以前的blog实验：
操作系统：
都是centos6.4
oracle软件版本： oracle 11.2.0.1.0
primary库（192.168.121.217）、standby库（192.168.121.218）
db_unique_name： primary库（pdunq）、standby库（pdunq_dg）
2、准备工作 在primary上操作
2.1、打开Forced Logging 模式
先确认primary库处于归档模式
Database log mode
& & & Archive Mode
Automatic archival
& & & Enabled
Archive destination
& & & USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence & & 362
Next log sequence to archive & 364
Current log sequence
SQL> alter da
Database altered.
确认primary库是归档模式
添加standby文件
alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 20M;
alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 20M;
alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
select * from v$logfile order by 1;
2.3 准备参数文件
2.3.1 生成pfile
shutdown immediate
2.3.2 修改pfile
cp $ORACLE_HOME/dbs/initpowerdes.ora $ORACLE_HOME/dbs/initpowerdes.ora.bak
vim $ORACLE_HOME/dbs/initpowerdes.ora
*.db_unique_name=pdunq
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/pwerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg &lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'
2.3.3 生成spfile
startup #这里可以启动也可以不启动，这里不启动，后面就要记得让新的参数文件生效
2.4 修改监听文件
[oracle@powerlong4 admin]$ vim listener.ora&
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
& (SID_LIST =
& & (SID_DESC =
& & & (SID_NAME = PLSExtProc)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
& & & (PROGRAM = extproc)
& & (SID_DESC =
& & & (SID_NAME = powerdes)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
LISTENER =
& (DESCRIPTION_LIST =
& & (DESCRIPTION =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
& & & (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
ADR_BASE_LISTENER = /oracle/app/oracle
INBOUND_CONNECT_TIMEOUT_listener=10
2.5，修改tns配置文件
[oracle@powerlong4 admin]$ vim tnsnames.ora&
& (DESCRIPTION =
& & (ADDRESS_LIST =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
& & (CONNECT_DATA =
& & & (SERVICE_NAME = pdunq)
& (DESCRIPTION =
& & (ADDRESS_LIST =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
& & (CONNECT_DATA =
& & & (SID = powerdes)
& & & & (SERVER = DEDICATED)
EXTPROC_CONNECTION_DATA =
& (DESCRIPTION =
& & (ADDRESS_LIST =
& & & (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
& & (CONNECT_DATA =
& & & (SID = PLSExtProc)
& & & (PRESENTATION = RO)
2.6 监听服务重启
lsnrctl stop
lsnrctl start
2.7 primary上配置最大可用模式：
SQL>startup
SQL>alter database set standby database to m
2.8 备份数据库
backup data
backup current con
备份结束后会在闪回区产生备份文件
3，数据库配置 standby上
3.1 建立相应的文件目录
包括dump文件目录，数据文件目录，通过查看，保持和primary一样的路径地址
3.2 从primary上copy数据文件到standby上
在主库上执行：
ps：在primary上执行
copy闪回区内容
copy闪回文件
cd /oracle/app/oracle/flash_recovery_area/
scp -r ./* 192.168.121.218:/oracle/app/oracle/flash_recovery_area/
copy参数文件
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
copy监听文件
cd /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
3.3 在standby库 修改配置文件 在standby上修改
[oracle@powerlong5 admin]$ vim listener.ora&
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
& (SID_LIST =
& & (SID_DESC =
& & & (SID_NAME = PLSExtProc)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
& & & (PROGRAM = extproc)
& & (SID_DESC =
& & & (SID_NAME = powerdes)
& & & (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
LISTENER =
& (DESCRIPTION_LIST =
& & (DESCRIPTION =
& & & (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
& & & (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
在standby修改tns文件&
3.4，修改参数文件
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdes)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg &lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'
PS:将*.log_archive_dest_2=后面的DB_UNIQUE_NAME改成primary的DB_UNIQUE_NAME值改为pdunq，这样在做switchover的时候，新的primary能通过这个将redo日志传到新的standby上面去。
log_archive_dest_N 目的是告诉数据库，把归档放到那里去可选项，首先是本地，然后考虑远程的从库，所以，假设A是主库，B是从库，切换之后B是主库，A是从库，所以，log_archive_dest_N需要设置为对方
3.5，重启监听 standby
[oracle@powerlong5 dbs]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-:36
Copyright (c) , Oracle. &All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
The command completed successfully
[oracle@powerlong5 dbs]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-:41
Copyright (c) , Oracle. &All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias & & & & & & & & & & LISTENER
Version & & & & & & & & & TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date & & & & & & & &10-FEB-:41
Uptime & & & & & & & & & &0 days 0 hr. 0 min. 0 sec
Trace Level & & & & & & & off
Security & & & & & & & & &ON: Local OS Authentication
SNMP & & & & & & & & & & &OFF
Listener Parameter File & /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File & & & & /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening Endpoints Summary...
& (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
& (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
& Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "powerdes" has 1 instance(s).
& Instance "powerdes", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@powerlong5 dbs]$
3.6，恢复数据库
在standby库上操作
[oracle@powerlong5 admin]$ rman target sys/syxxlxxxx58@PD1 auxiliary /
Argument & & Value & & & & &Description
-----------------------------------------------------------------------------
target & & & quoted-string &connect-string for target database
catalog & & "ed-string &connect-string for recovery catalog
nocatalog & &none & & & & & if specified, then no recovery catalog
cmdfile & & "ed-string &name of input command file
log & & & & "ed-string &name of output message log file
trace & & & "ed-string &name of output debugging message log file
append & & & none & & & & & if specified, log is opened in append mode
debug & & & &optional-args &activate debugging
msgno & & & &none & & & & & show RMAN-nnnn prefix for all messages
send & & & & quoted-string &send a command to the media manager
pipe & & & & string & & & & building block for pipe names
timeout & & &integer & & & &number of seconds to wait for pipe input
checksyntax &none & & & & & check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "end-of-file": expecting one of: "double-quoted-string, identifier, single-quoted-string, "
RMAN-01007: at line 0 column 0 file: command line arguments
[oracle@powerlong5 admin]$&
[oracle@powerlong5 admin]$&
[oracle@powerlong5 admin]$
报错，看下是否standby没有启动导致？
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
[root@powerlong5 ~]# mount -t tmpfs shmfs -o size=12g /dev/shm
[root@powerlong5 ~]# df -h
Filesystem & & & & & &Size &Used Avail Use% Mounted on
/dev/sda3 & & & & & & &57G & 45G &8.9G &84% /
tmpfs & & & & & & & & &12G & & 0 & 12G & 0% /dev/shm
/dev/sda1 & & & & & & 190M & 51M &129M &29% /boot
/dev/sr0 & & & & & & &4.1G &4.1G & & 0 100% /media/CentOS_6.4_Final
shmfs & & & & & & & & &12G & & 0 & 12G & 0% /dev/shm
[root@powerlong5 ~]#&
SQL> startup
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> startup nomount
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
去primary库上查询下audit路径
SQL> show parameter audit_file_dest
------------------------------------ ----------- ------------------------------
audit_file_dest
& & string
/oracle/app/oracle/admin/powerdes/adump
然后在standby上操作
SQL> startup nomount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.1358E+10 bytes
Fixed Size
& &2216744 bytes
Variable Size
Database Buffers
Redo Buffers
去primary修改sys密码：
SQL> alter user sys identified by "syxxlxxxx58";
User altered.
在standby库执行rman target sys/syspl1758@PD1 auxiliary /，如下所示：
[oracle@powerlong5 ~]$ rman target sys/syspl1758@PD1 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 7 19:08:16 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: POWERDES (DBID=)
connected to auxiliary database: POWERDES (not mounted)
RMAN> run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=767 device type=DISK
allocated channel: c2
channel c2: SID=1150 device type=DISK
Starting Duplicate Db at 07-FEB-15
contents of Memory Script:
&&&set until scn
&&&restore clone standby controlfile;
executing Memory Script
executing command: SET until clause
Starting restore at 07-FEB-15
channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece /oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_ncsnf_TAG252_bfct20tb_.bkp
channel c1: piece handle=/oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_ncsnf_TAG252_bfct20tb_.bkp tag=TAG252
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
output file name=/oracle/data_ora/powerdes/control01.ctl
output file name=/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl
Finished restore at 07-FEB-15
contents of Memory Script:
&&&sql clone \'alter database mount standby database\';
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
&&&set until scn
&&&set newname for datafile
&\"/home/oradata/pwerdes/system01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/sysaux01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/undotbs01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/users01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/plas01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/pl01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/help01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/adobelc01.dbf\";
&&&set newname for datafile
&\"/home/oradata/pwerdes/sms01.dbf\";
&&&restore
&&&clone database
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-FEB-15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /home/oradata/pwerdes/system01.dbf
channel c1: restoring datafile 00002 to /home/oradata/pwerdes/sysaux01.dbf
channel c1: restoring datafile 00003 to /home/oradata/pwerdes/undotbs01.dbf
channel c1: restoring datafile 00004 to /home/oradata/pwerdes/users01.dbf
channel c1: restoring datafile 00006 to /home/oradata/pwerdes/plas01.dbf
channel c1: restoring datafile 00007 to /home/oradata/pwerdes/pl01.dbf
channel c1: restoring datafile 00008 to /home/oradata/pwerdes/help01.dbf
channel c1: restoring datafile 00009 to /home/oradata/pwerdes/adobelc01.dbf
channel c1: restoring datafile 00010 to /home/oradata/pwerdes/sms01.dbf
channel c1: reading from backup piece /oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_nnndf_TAG252_bfcsvxoz_.bkp
channel c1: piece handle=/oracle/app/oracle/flash_recovery_area/PDUNQ/backupset//o1_mf_nnndf_TAG252_bfcsvxoz_.bkp tag=TAG252
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:04:05
Finished restore at 07-FEB-15
contents of Memory Script:
&&&switch clone datafile all;
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP= file name=/home/oradata/pwerdes/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP= file name=/home/oradata/pwerdes/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP= file name=/home/oradata/pwerdes/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP= file name=/home/oradata/pwerdes/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP= file name=/home/oradata/pwerdes/plas01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP= file name=/home/oradata/pwerdes/pl01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP= file name=/home/oradata/pwerdes/help01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP= file name=/home/oradata/pwerdes/adobelc01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP= file name=/home/oradata/pwerdes/sms01.dbf
contents of Memory Script:
&&&set until scn
&&&recover
&&&standby
&&&clone database
&&&&delete archivelog
executing Memory Script
executing command: SET until clause
Starting recover at 07-FEB-15
starting media recovery
archived log for thread 1 with sequence 302 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_302_.dbf
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_302_.dbf thread=1 sequence=302
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-FEB-15
Finished Duplicate Db at 07-FEB-15
released channel: c1
released channel: c2
RMAN> exit
3.7 &standby上修改参数文件
先关闭oracle
shutdown immediate
然后开始修改参数文件
cd &$ORACLE_HOME/dbs&
vim initpowerdes.ora
# 主要是修改db_unique_name
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='PD1'
*.fal_server='PD2'
*.global_names=FALSE
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(pdunq,pddgunq)'
重新创建参数文件
3.8 启动数据库
alter database mo
alter database
alter database
alter database
alter database recover managed standby database using current logfile dis
4，检查看到归档没有过来
Database log mode
& & & Archive Mode
Automatic archival
& & & Enabled
Archive destination
& & & ?/dbs/arch
Oldest online log sequence & & 0
Next log sequence to archive & 0
Current log sequence
看到归档信息为0，说明primary上的redo日志没有传到standby上来。
4.1，查看alert日志
[oracle@powerlong5 trace]$ tail -f /oracle/app/oracle/diag/rdbms/pdunq_dg/powerdes/trace/alert_powerdes.log&
MRP0 started with pid=41, OS id=21243&
MRP0: Background Managed Standby Recovery process started (powerdes)
&started logmerger process
Sat Feb 07 20:12:18 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 303
Completed: alter database recover managed standby database disconnect from session
&查看日志传输情况
& & select sequence#,first_time,next_time from v$archived_
SELECT sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') first_time,to_char(next_time,'yyyy-mm-dd hh24:mi:ss') next_time from v$archived_
问题分析解决：
primary主库上的alert日志有错：
Error 12154 received logging on to the standby
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc2_22609.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC2]: Heartbeat failed to connect to standby 'pdunq_dg'. Error is 12154.
错误很清晰了，主库无法检测到从库存在
tns 12154 错误，主库无法 tnsping pdunq_dg&
tnsping standby库报错
[oracle@powerlong4 admin]$ tnsping pdunq_dg
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-FEB-:26
Copyright (c) , Oracle. &All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
[oracle@powerlong4 admin]$
参数文件里面
*.log_archive_dest_2='SERVICE=pdunq_dg &lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
SERVICE=pdunq_dg 要和tnsnames.ora里面的保持一致。
4.2，去tnsnames.ora里面修改配置
去把tnsnames.ora里面的改成pdunq_dg即可。
重启lsnrctl，然后查看从库归档日志，有日志了，如下所示：
Database log mode
& & & Archive Mode
Automatic archival
& & & Enabled
Archive destination
& & & USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence & & 350
Next log sequence to archive & 0
Current log sequence
备库切换到open状态：
退出redo应用状态
SQL> alter database recover managed sta
Database altered.
PS：停止standby的redo应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;注意，此时只是暂时redo 应用，并不是停止Standby 数据库，standby 仍会保持接收只不过不会再应用接收到的归档，直到你再次启动redo 应用为止。类似mysql里面的stop slave功能;
打开standby上的oracle库
Database altered.&
再应用redo日志
SQL> alter database recover managed standby database using current&
Database altered.
去primary、standby库上面执行检查
SQL> select sequence#,applied from v$archived_
查看最新的scn：
SQL> select max(sequence#) from v$archived_
primary和standby都保持一致，OK，dataguard搭建完成。
&----------------------------------------------------------------------------------------------------------------
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
原博客地址： & & &
原作者：黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
阅读(43552) | 评论(0) | 转发(8) |
相关热门文章
给主人留下些什么吧！~~
你好，请问以下命令是什么意思？&（alter&database&drop&standby&logfile&)为什么要执行删除日志制作呢？添加standby文件alter&database&add&standby&logfile&group&4&('/home/oradata/powerdes/redo_dg_021.log')&size&20M;alter&database&add&standby&logfile&group&5&('/home/oradata/powerdes/redo_dg_022.log')&size&20M;alter&database&add&standby&logfile&group&6&('/home/oradata/powerdes/redo_dg_023.log')&size&20M;alter&
请登录后评论。}
我要回帖
更多推荐
- ·为什么酷狗会员最低是多少钱听歌要钱？
- ·为什么听歌免费还要收费？？
- ·请问大德这个八字怎样解读。男：辛丑日甲午时出生的人年甲午月丁亥日甲辰时？
- ·有哪些关于适合五四青年节的歌曲运动的歌呀？
- ·一个梗说自己是战狼是什么意思
- ·丁酮用来粘合ABS塑料。为什么涂了丁酮的位置发白了？
- ·两数相减哪个是被减数，被减数减少88，要使差不变，减数是多少
- ·肠胃不好拉肚子吃什么？
- ·好的意思好的成语的成语有哪些
- ·文科高三后能提高多少分
- ·德塑PPR冷.ppr热水管管由(2o管到65管)多少钱一米
- ·壅穆亲穆存心什么意思思
- ·奥迪A6 1.8主继电器在什么位置
- ·哪个团体能实现美国梦是如何发展的？为什么
- ·得了重度抑郁症去哪里好去哪看的好？
- ·领导讲话提纲气势不上是何原因
- ·学正宗重庆卤菜凉菜培训技术
- ·一个正方形的面积是36平方厘米，边长是多少厘米
- ·宏讯论坛新用户在哪开户的呢
- ·怎么查看oracle primary keydataguard里面primary中 standby
- ·树懒gps定位导航防水对讲机手持机质量如何，怎么使用？
- ·ubuntu安装php 16.04 php7.0源里面安装的怎么往里面添加模块
- ·说说厦门去哪做韩式半永久纹眉培训好吧？求问百度网友回答谢谢！！
- ·8彩真人注册如何领取赠送的79元体验金
- ·为什么ghost系统总是会在其他盘占据容量
- ·如何在word文档标题两边各加一个python 打印实心菱形形块
- ·如何打造一台专业型和学术型的区别 Surface
- ·开廊坊大学城电脑耗材店店需要多少资金
- ·新人婚前物品筹备明细提问：对于开设淘宝店，物品售卖不出去，流量不行，点击率也不行。该怎么办，如和调整？运营如何做
- ·怎么用cad熟练画木门的图我拿诚心待人求教
- ·unix 系统下怎样如何查看系统重启信息上上次的重启动时间
- ·如何发新闻到腾讯新闻视频下载
- ·鸿合电子白板软件白板翻到下一页时幕布能否自动出现
- ·对朋友不公wf在线wf是什么意思思
- ·把220v调速器怎么接线去掉，怎么接线
