标准配置：11gR2 [11.2.0.]利用SQL[Node-Node]的参数配置_V1.0.txt
本帖最后由 zluo 于 2014-1-10 13:28 编辑标准配置：11gR2 利用SQL的参数配置_V1.0.txt
这是Oracle 11gR2 最新SQL版的Active Data Guard 的配置，现在和大家一起分享一下 你这样开主题 是 要先付费才能看这个主题 ，然后再付费才能买附件
玩论坛是一个IT人的基本功吧？ /* 创建文档资料时间：2013-12-26
搭建11gR2(11.2.0.)：利用SQL设定单机&单机的参数配置--V1.0
#====================================================================================#
#配置环境： #
#OS version：Linux 5.8 64 #
#Lvm version:LVM & ASM #
#Oracle version: Oracle 11g #
#====================================================================================#
#Environment Information
OS: Linux x64-64
Hostname (Primary): TSTPRIM
Hostname (Standby): TSTSTBY
Database Version: 11.2.0.x
DB_NAME (Primary and Standby): prim_db
SID/DB_UNIQUE_NAME (Primary): prim_db
SID/DB_UNIQUE_NAME (Standby): stby_db
Listener Port (Primary and Standby): 1521
Primary Database Files Location: /oracle/oradata/prim_db
Standby Database Files Location: /oracle/oradata/stby_db
*/
====================================================================================================
#Prerequisites and Assumptions to get started
- There already exists a Primary Database you want to create your Standby Database for
- Primary Database is in ARCHIVELOG-Mode and local Archiving is using Fast Recovery Area
- FORCE LOGGING is enabled on the Primary Database
- The Primary Database is using a SPFILE
- On the Standby Site ORACLE_HOME is installed using the same Oracle Release and Patchlevel
- The Platform of the Standby Database is the same as the Primary Site or a supported Combination as per Note 413484.1
- There is Network Connectivity between the Primary and Standby System
- Listener is configured and running on the Primary and Standby Site
- We are creating a Standby Database using default Log Transport Services in Maximum Performance Mode
#===========================================================#
#Dataguard 配置过程步骤 #
#===========================================================#
Step 1、打开主库DB系统强制归档
===================================
--#Set the following Initialization Parameters on the Primary Database:
col DBID for 9999999999
col name for a30
col force_logging for a35
select dbid,name,force_logging from gv$database;
startup mount;
alter database force logging;
alter database open;
select force_logging,dataguard_brker,guard_status from V$database;
Step 2、在备库上安装与主库一样版本Oracle Software
==================================================
command:
opatch lsinventory
Step 3、在备库上使用NETCA配置好监听
===================================
Step 4、在主备库上配置TNSNAMES.ORA
===================================
command:
cd $ORACLE_HOME/network/admin/
Example Entries in the tnsnames.ora Files：
==========================================
Primary Net Service Names:
--------------------------
prim_db =
(description =
(address = (protocol = tcp)(host = 168.8.19.130)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = prim_db)
(instance_name = prim_db)
)
)
Standby Net Service Name:
-------------------------
stby_db =
(description =
(address_list =
(address = (protocol = tcp)(host = 168.8.19.27)(port = 1521))
)
(connect_data =
(service_name = stby_db)
)
)
Step 5、修改主备DB上parameter参数
===================================
#Create Folders in ADMIN- and ORADATA-Folders to host the Database Files and Dumpfiles
(can use $ORACLE_BASE/admin of the Primary as a Reference which Folders to create
typically those are the ‘adump’- and ‘dpdump’-Folders)
$ cd $ORACLE_BASE/admin
$ mkdir stby_db
$ cd stby_db
$ mkdir adump
$ mkdir dpdump
command:
cd $ORACLE_BASE/admin/<SID>/pfile
export ORACLE_SID=<SID>
SQL>create pfile='<SID>_pfile.ora' from spfile;
# Primary Initialization parameters:
==================================
-- #设定使用FAST_BACKUP功能
alter system set db_recovery_file_dest_size= 400G scope=spfile sid='*';
alter system set db_recovery_file_dest='+ASMDATA' scope=spfile sid='*';
===============================================================================
/*#为primary database设定的参数
*.log_archive_dest_1 = ‘location=USE_DB_RECOVERY_FILE_DEST’
*.log_archive_dest_2 = ’service=stby_db async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=stby_db’
*.log_archive_dest_state_2 = ’defer’
*.log_archive_config= ’dg_config=(prim_db,stby_db)’
*.log_archive_max_processes = 8
*/
-- #设定Active Dataguard 相关参数
alter system set db_unique_name = 'prim_db' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(prim_db,stby_db)' scope=spfile sid='*';
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=prim_db' scope=spfile sid='*';
alter system set log_archive_dest_2='service=stby_db lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=stby_db compression=enable' scope=spfile sid='*';
alter system set log_archive_format='prim_db_%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_dest_state_2='defer' scope=spfile sid='*';
alter system set log_archive_max_processes = 8 scope=spfile sid='*';
Standby Initialization parameters
===================================
#提前规划并设定好USE_DB_RECOVERY_FILE_DEST目录
-- #设定使用FAST_BACKUP功能
alter system set db_recovery_file_dest_size= 400G scope=spfile sid='*';
alter system set db_recovery_file_dest='+ASMDATA' scope=spfile sid='*';
/*
#Modify the following Initialization Parameters in the PFILE (initstby_db.ora) for the Standby Database we created before:
*.db_name='prim_db'
*.db_unique_name = 'stby_db'
*.log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_config= 'dg_config=(prim_db,stby_db)'
*.log_archive_max_processes = 8
*.fal_server = 'prim_db'
*.log_file_name_convert = 'primary online redolog files','standby online redolog files'
*.db_file_name_convert = 'primary daafiles','standby datafiles'
*/
-- #设定Active Dataguard 相关参数
alter system set db_unique_name = 'stby_db' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(prim_db,stby_db)' scope=spfile sid='*';
alter system set log_archive_format='stby_db_%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes = 8 scope=spfile sid='*';
alter system set fal_server = 'prim_db' scope=spfile sid='*';
alter system set log_file_name_convert = 'primary online redolog files','standby online redolog files' scope=spfile sid='*';
alter system set db_file_name_convert = 'primary daafiles','standby datafiles' scope=spfile sid='*';
Step 6、Copy database orapw口令文件
===================================
- Copy the orapw<sid>.ora file from the OH/dbs directory of the primary to the same location on the standby.
$ export ORACLE_SID=zhtfkb
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwzhtfkb password=oracle
也可以从生产系统上直接Copy到Standby Database服务器上，然后再根据实例名，重命名此orapw* 文件
cp $ORACLE_HOME/dbs/orapw<SID>* .
Step 7、Create the standby controlfile
=======================================
#Primary :
=========
sql>startup mount;
sql>alter database create standby controlfile as ‘/backup/control01.ctl’;
sql>alter database open;
- ftp / scp the stdby.ctl file to the standby site. we can multiplex this by making
copies of this stdby.ctl on different mountpoints on the standby and editing the
control_files parameter in the init.ora to reflect this.
dd if=/tmp/stdby_controlfile.ctl of=/dev/rzhtfkb_ctl01
dd if=/tmp/stdby_controlfile.ctl of=/dev/rzhtfkb_ctl02
dd if=/tmp/stdby_controlfile.ctl of=/dev/rzhtfkb_ctl03
也可以通过RMAN备份集，使用rman 恢复出standby controlfile
In RMAN, connect to primary database and create standby control file backup:
RMAN> backup current controlfile for standby;
将备库启动到nomount状态，并且确定好raw上的controlfile文件的位置。
sql> show parameter control_files
RMAN> restore standby controlfile from '................'
RMAN> alter database mount; Step 8、停止主库，完成数据文件迁移
8.1 文件系统：
8.1.1、停止主库系统
8.1.2、scp迁移数据文件
8.1.3、scp控制文件
#For a Cold Backup we simply perform a clean shutdown of the Primary Database and
#copy all Datafiles to a temporary Location, then startup the Primary Database again:
SQL> shutdown immediate;
$ cp /oracle/oradata/prim_db/ .dbf /backup/
SQL> startup
#If you want to use a Host Backup, we have to put the Primary Database into
#Backup Mode and copy the Datafiles:
SQL> alter database begin backup;
$ cp /oracle/oradata/prim_db/ .dbf /backup/
SQL> alter database end backup;
8.2 裸设备：
8.2.1、备份主库
8.2.2、迁移备份集
8.2.3、如果备份集目录与原主库备份目录不一致，刚需要注册所有备份集。
RMAN> catalog archivelog '/arc/log1'
RMAN> catalog datafilecopy '/oracle/copy/tbs_2.dbf'
RMAN> catalog start with '<directory for archived log backup>';
8.2.4、利用standby controlfile恢复datafile
将备库启动到nomount状态，并且确定好raw上的controlfile文件的位置。
sql> show parameter control_files
RMAN> restore standby controlfile from '................'
RMAN> alter database mount;
RMAN> run {
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
allocate channel a4 type disk;
set newname for datafile 1 to '/dbfile/database/stbesb/stbesb-system.dbf';
set newname for datafile 2 to '/dbfile/database/stbesb/stbesb-undotbs1.dbf';
set newname for datafile 3 to '/dbfile/database/stbesb/stbesb-sysaux.dbf';
set newname for datafile 4 to '/dbfile/database/stbesb/stbesb-undotbs2.dbf';
set newname for datafile 5 to '/dbfile/database/stbesb/stbesb-users.dbf';
set newname for datafile 6 to '/dbfile/database/stbesb/stbesb-data_ts01.dbf';
set newname for datafile 7 to '/dbfile/database/stbesb/stbesb-index_ts01.dbf';
set newname for datafile 8 to '/dbfile/database/stbesb/stbesb-data_ts02.dbf';
restore database;
switch datafile all;
}
#At this Point we can now start the RMAN Duplicate to create the Standby Database
$ export ORACLE_SID = stby_db
RMAN> connect target sys/<Password>@prim_db
RMAN> connect auxiliary /
RMAN> duplicate target database for standby nofilenamecheck;
Step 9、检查并修改数据文件名称和路径
9.1 检查文件名称和路径
9.1.1 检查datafile
select name from V$datafile;
9.1.2 检查tempfile
select name from V$tempfile;
9.1.3 检查日志文件
单机环境：
select member from V$logfile;
RAC环境：
select a.group#,A.MEMBER from V$logfile a, V$log b where A.GROUP#=B.GROUP# and B.THREAD#=1;
select a.group#,A.MEMBER from V$logfile a, V$log b where A.GROUP#=B.GROUP# and B.THREAD#=2;
9.2 修改文件路径或名称
9.2.1 修改logfile文件
For example:
alter database rename file '/dbfile/database/stbecif/stbecif-redoa1.rdo' to '/dbfile/database/stbecif/thread1-redoa1.rdo';
Step 10、配置完成Standby redo logfile
10.1 先在主库上完成Standby Redo Log (SRL) creation创建和计算大小
计算公式：
(maximum # of logfiles +1) * maximum # of threads
For example:
sql>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
------ ------- ------- ----------------------------------------------------------------------------
1 ONLINE /u01/app/oracle/admin/v102p/redo01.log NO
2 ONLINE /u01/app/oracle/admin/v102p/redo02.log NO
3 ONLINE /u01/app/oracle/admin/v102p/redo03.log NO
SQL>select bytes/1024/1024 as "BYTES(MB)" from v$log;
BYTES(MB)
=========
2048
10.2 创建redolog file for Standby database
10.2.1 针对primary database是单机环境
Create the SRL's :
thread redo logfile count(*) + 1
alter database add logfile group 10 ('/dbfile1/database/frdb1/sr-frdb104.redo') size 1024m;
10.2.2 针对primary Database是RAC环境
Create the SRL's :
thread redo logfile count(*) + 1
alter database add standby logfile thread 1 group 13 size 2048m;
alter database add standby logfile thread 2 group 20 size 2048m;
10.3 清理online redo logfile
10.3.1 计算primary database redo logfile日志文件组
select group# from V$logfile;
10.3.2 清理online redo logfile
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
Step 11、tarting the standby database
sql>startup mount
1.在standby数据库上开始real_time apply
sql> alter database recover managed standby database disconnect from session;
2.在standby数据库上开始redo apply
sql> alter database recover managed standby database using current logfile disconnect;
提示：
==========
disconnect from session子句并非必须，该子句用于指定启动完应用后自动退出到命令操作符前，如果不指定的话，当前session就会一直停留处理redo应用，如果想做其它操作，就只能新建一个连接。
Step 12、停止standby
正常情况下，我们停止也应该是先停止redo应用，可以通过下列语句：
sql> alter database recover managed standby database cancel;
然后再停止standby数据库
sql> shutdown immediate;
提示：
==========
当然你非要直接shutdown也没问题，dg本来就是用于容灾的，别说你生停standby，就是直接拔电源也不怕。
Step 13、若缺少生产DB的归档日志处理
13.1 查看Standby database的alert*.log，确定缺少的归档日志
13.2 查看primary database的备份集，找到已经备份的归档日志文件
list backup of archivelog all;
list backup;
13.3 恢复已经备份的归档日志文件
For example:
restore archivelog sequence 1495 thread 2;
restore archivelog sequence 430 thread 1;
Step 14、检查测试，归档日志应用情况
alter system archive log current;
Step 15、生产DB，配置归档有效应用参数
在生产database的RMAN配置参数里，配置如下参数：
configure archivelog deletion policy to applied on standby;
注意事项：
==========
解决如下alert报错处理办法：
ORA-19527: Message 19527 not found; No message file for product=RDBMS, facility=ORA
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments:
设定如下参数：
alter system set log_file_name_convert='/dbfile2/database/frdb2/','/dbfile2/database/frdb2/' scope=spfile;
========================================================================================================================
问题如下alert_<SID>日志：
Warning: OS async I/O limit 128 is lower than recovery batch 625
Thu Nov 24 10:53:07 2011
Warning: OS async I/O limit 128 is lower than recovery batch 625
Thu Nov 24 10:53:07 2011
解决报错处理办法:
On Redhat Linux 4 and 5 the recommended action is to raise the value of /proc/sys/fs/aio-max-nr.
eg: Set the value to 1048576 or higher.
============================
解决Warning: OS async I/O limit 128 is lower than recovery batch 1024
问题：起动Oracle数据库时，特别是在一台机器上有多个库时alert文件中报这个错误
平台: REHL 5.2 linux x86-64 DB:ORACLE 10.2.0.4
解决：调整OS参数，在/etc/sysctl.conf加入fs.aio-max-nr = 1048576,然后执行sysctl -p生效，重启数据
检查参数:
# sysctl -p
fs.aio-max-size = 1048576
fs.file-max = 327680
fs.aio-max-nr = 1048576
看看文件：/etc/sysctl.conf
file according to Example 3-1. Specifying the parameters in the sysctl.conf file causes the parameters to be set upon reboot.
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
fs.aio-max-size = 1048576
fs.file-max = 327680
fs.aio-max-nr = 1048576
=================================================================
RE: 标准配置：11gR2 [11.2.0.]利用SQL[Node-Node]的参数配置_V1.0.txt
大家，不好意思了，我不太会玩这个东西，设置好，又无法修改。现在把内容发给大家
页:
[1]