Dataguard实施文档.docx
《Dataguard实施文档.docx》由会员分享,可在线阅读,更多相关《Dataguard实施文档.docx(14页珍藏版)》请在冰豆网上搜索。
![Dataguard实施文档.docx](https://file1.bdocx.com/fileroot1/2023-2/9/f4047c6f-e759-44e1-ab9b-9fc2592c118a/f4047c6f-e759-44e1-ab9b-9fc2592c118a1.gif)
Dataguard实施文档
配置最大性能模式
简单的规划:
属性
主库
备库
ip
192.168.88.40
192.168.88.50
hostname
src
target
db_unique_name
primary
standby
service_names
primary
standby
instance_name
stu
stu
db_name
stu
stu
ORACLE_HOME
/u01/app/oracle/product/10.2/db_1
/u01/app/oracle/product/10.2/db_1
归档存放路径
/oradata/arch
/oradata/arch
redo存放路径
/oradata/stu
/oradata/stu
1主库相关配置
简单一个checklist:
EnableForcedLogging
CreateaPasswordFile
SetPrimaryDatabaseInitializationParameters
EnableArchiving
Setlistenerandtnsnames
1.1设置forcelogging:
SQL>selectFORCE_LOGGINGfromv$database;
FOR
---
YES
如果没有打开,如下方式打开:
ALTERDATABASEFORCELOGGING;
1.2创建密码文件:
默认Oracle的密码文件是保存在$ORACLE_HOME/dbs下面的:
如果没有的话,需要手动创建一个:
可以直接在$ORACLE_HOME/dbs运行如下命令:
orapwdfile=orapw<实例名>password=*******
1.3设置主库的初始化参数:
修改主库的参数:
*.INSTANCE_NAME=stu
*.DB_UNIQUE_NAME=primary
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_2='SERVICE=standbyLGWRASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.STANDBY_FILE_MANAGEMENT=AUTO
为了长远考虑,假如有一天将主库又切换成备库了,这样发生角色转换,那么免得再去设置,可以现在在主库上设置一些standby的参数,如下(这是可选的噢):
*.FAL_SERVER=standby
*.FAL_CLIENT=primary
*.DB_FILE_NAME_CONVERT='standby,'primary'
*.LOG_FILE_NAME_CONVERT='/oradata/arch/','/oradata/arch/'
*.STANDBY_FILE_MANAGEMENT=AUTO
再创建一个pfile:
SQL>createpfile='/tmp/s.txt'fromspfile;
Filecreated.
然后将该pfile传到远端的备库上,以便后面的使用。
1.4主库需要启用归档模式:
SQL>SHUTDOWNIMMEDIATE;
SQL>STARTUPMOUNT;
SQL>ALTERDATABASEARCHIVELOG;
SQL>ALTERDATABASEOPEN;
1.5配置监听和TNS:
在主库上:
没有监听的话,就写一个监听呗:
vilistener.ora
LISTENER=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=src)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=stu)
(SERVIE_NAME=primary)
(ORACLE_HOME=/u01/app/oracle/product/10.2/db_1)
)
)
tnsname的配置:
primary=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.88.40)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=primary)
)
)
standby=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.88.50)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=standby)
)
)
在备库上:
没有监听的话,就写一个监听呗:
vilistener.ora
LISTENER=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=target)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=standby)
(SERVIE_NAME=standby)
(ORACLE_HOME=/u01/app/oracle/product/10.2/db_1)
)
)
tnsname的配置:
primary=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.88.40)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=primary)
)
)
standby=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.88.50)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=standby)
)
)
2备库数据初始化
2.1在主库备份数据库
1.先做一个全库备份(控制文件最后单独备份)
run
{allocatechannelch1typedisk;
allocatechannelch2typedisk;
backupfilesperset2databaseformat'/home/oracle/rmanbak/full_%d_%T_%s_%p.bak';
sql'altersystemarchivelogcurrent';
sql'altersystemarchivelogcurrent';
sql'altersystemarchivelogcurrent';
backuparchivelogallformat'/home/oracle/rmanbak/arch_%d_%T_%s_%p.bak';
}
2.单独备份下控制文件:
backupcurrentcontrolfileforstandbyformat'/home/oracle/rmanbak/ctl_%d_%T_%s_%p.bak';
3.将备份集传到备库机器上:
[oracle@srcrmanbak]$scp*target:
/home/oracle/rmanbak/
2.2修改备库上的一些参数:
根据主库传来的参数文件进行修改,如下(新的参数文件,红色部分为需要特别注意做修改的部分):
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/stu/control01.ctl','/oradata/stu/control02.ctl','/oradata/stu/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='stu'
*.DB_UNIQUE_NAME='standby'
*.INSTANCE_NAME='standby'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,primary)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primaryLGWRASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=primary'
*.open_cursors=300
*.pga_aggregate_target=395313152
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.SERVICE_NAMES='standby'
*.sga_target=1185939456
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'
根据参数文件,还需要创建一些必备的目录:
mkdir-p/u01/app/oracle/admin/standby/cdump
mkdir-p/u01/app/oracle/admin/standby/adump
mkdir-p/u01/app/oracle/admin/standby/bdump
mkdir-p/u01/app/oracle/admin/standby/udump
另外,将主库的密码文件传到备库上,保证密码一样。
主库上的密码文件:
传到备库上,修改下名字即可:
2.3在备库上恢复数据库:
启动数据库到nomount:
SQL>startupnomountpfile='/tmp/s.txt';
ORACLEinstancestarted.
TotalSystemGlobalArea1191182336bytes
FixedSize2095832bytes
VariableSize318768424bytes
DatabaseBuffers855638016bytes
RedoBuffers14680064bytes
恢复控制文件:
RMAN>restorestandbycontrolfilefrom'/home/oracle/rmanbak/ctl_STU_20141228_13_1.bak';
Startingrestoreat28-DEC-14
allocatedchannel:
ORA_DISK_1
channelORA_DISK_1:
sid=155devtype=DISK
channelORA_DISK_1:
restoringcontrolfile
channelORA_DISK_1:
restorecomplete,elapsedtime:
00:
00:
04
outputfilename=/oradata/stu/control01.ctl
outputfilename=/oradata/stu/control02.ctl
outputfilename=/oradata/stu/control03.ctl
Finishedrestoreat28-DEC-14
RMAN>sql'alterdatabasemountstandbydatabase';
sqlstatement:
alterdatabasemountstandbydatabase
releasedchannel:
ORA_DISK_1
restore数据文件:
命令:
restoredatabase
注意:
这里不需要setnewname,因为主库和备库所存放数据库文件的目录都是一样的,如果不一样的话,那么就需要此处进行setnewname了。
具体操作:
找到最大的一个归档序列号:
RMAN>listbackupofarchivelogall;
ListofBackupSets
===================
BSKeySizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------
106.08MDISK00:
00:
0128-DEC-14
BPKey:
10Status:
AVAILABLECompressed:
NOTag:
TAG20141228T134132
PieceName:
/home/oracle/rmanbak/arch_STU_20141228_10_1.bak
ListofArchivedLogsinbackupset10
ThrdSeqLowSCNLowTimeNextSCNNextTime
-------------------------------------------------
13164965128-DEC-1465205028-DEC-14
BSKeySizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------
111.05MDISK00:
00:
0128-DEC-14
BPKey:
11Status:
AVAILABLECompressed:
NOTag:
TAG20141228T134132
PieceName:
/home/oracle/rmanbak/arch_STU_20141228_11_1.bak
ListofArchivedLogsinbackupset11
ThrdSeqLowSCNLowTimeNextSCNNextTime
-------------------------------------------------
13265205028-DEC-1465321228-DEC-14
13365321228-DEC-1465324228-DEC-14
13465324228-DEC-1465330228-DEC-14
13565330228-DEC-1465421428-DEC-14
BSKeySizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------
1227.00KDISK00:
00:
0128-DEC-14
BPKey:
12Status:
AVAILABLECompressed:
NOTag:
TAG20141228T134132
PieceName:
/home/oracle/rmanbak/arch_STU_20141228_12_1.bak
ListofArchivedLogsinbackupset12
ThrdSeqLowSCNLowTimeNextSCNNextTime
-------------------------------------------------
13665421428-DEC-1465422628-DEC-14
13765422628-DEC-1465425328-DEC-14
13865425328-DEC-1465426128-DEC-14
开始recovery:
RMAN>recoverdatabaseuntilsequence39;
Startingrecoverat28-DEC-14
usingchannelORA_DISK_1
startingmediarecovery
channelORA_DISK_1:
startingarchivelogrestoretodefaultdestination
channelORA_DISK_1:
restoringarchivelog
archivelogthread=1sequence=35
channelORA_DISK_1:
readingfrombackuppiece/home/oracle/rmanbak/arch_STU_20141228_11_1.bak
channelORA_DISK_1:
restoredbackuppiece1
piecehandle=/home/oracle/rmanbak/arch_STU_20141228_11_1.baktag=TAG20141228T134132
channelORA_DISK_1:
restorecomplete,elapsedtime:
00:
00:
01
archivelogfilename=/oradata/arch/1_35_863995916.dbfthread=1sequence=35
channelORA_DISK_1:
startingarchivelogrestoretodefaultdestination
channelORA_DISK_1:
restoringarchivelog
archivelogthread=1sequence=36
channelORA_DISK_1:
restoringarchivelog
archivelogthread=1sequence=37
channelORA_DISK_1:
restoringarchivelog
archivelogthread=1sequence=38
channelORA_DISK_1:
readingfrombackuppiece/home/oracle/rmanbak/arch_STU_20141228_12_1.bak
channelORA_DISK_1:
restoredbackuppiece1
piecehandle=/home/oracle/rmanbak/arch_STU_20141228_12_1.baktag=TAG20141228T134132
channelORA_DISK_1:
restorecomplete,elapsedtime:
00:
00:
01
archivelogfilename=/oradata/arch/1_36_863995916.dbfthread=1sequence=36
archivelogfilename=/oradata/arch/1_37_863995916.dbfthread=1sequence=37
archivelogfilename=/oradata/arch/1_38_863995916.dbfthread=1sequence=38
OracleError:
ORA-01547:
warning:
RECOVERsucceededbutOPENRESETLOGSwouldgeterrorbelow
ORA-01152:
file1wasnotrestoredfromasufficientlyoldbackup
ORA-01110:
datafile1:
'/oradata/stu/system01.dbf'
mediarecoverycomplete,elapsedtime:
00:
00:
01
Finishedrecoverat28-DEC-14
这个的error是正常的,不要理会。
2.4将备库置于recovermanaged模式:
SQL>alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;
Databasealtered.
注意,如果要取消模式,使用如下命令:
ALTERDATABASERECOVERMANAGEDSTANDBYDATABASECANCEL;
4.验证下Dataguard配置是否成功:
在主库端切归档,在备库端可以看到归档的redo:
当前的归档情况:
到备库上查看:
两边保持同步的。