标准配置：11gR2 [11.2.0.]利用SQL[Node-Node]的参数配置_V1.0.txt

zluo

标准配置：11gR2 [11.2.0.]利用SQL[Node-Node]的参数配置_V1.0.txt


这是Oracle 11gR2 最新SQL版的Active Data Guard 的配置，现在和大家一起分享一下

Liu Maclean(刘相兵

你这样开主题 是 要先付费才能看这个主题 ，然后再付费才能买附件

玩论坛是一个IT人的基本功吧？

zluo

/* 创建文档资料时间：2013-12-26
搭建11gR2(11.2.0.)：利用SQL设定单机&单机的参数配置--V1.0

#====================================================================================#
#配置环境：                                                                          #
#OS version：Linux 5.8  64                                                           #
#Lvm version:LVM & ASM                                                               #
#Oracle version: Oracle 11g[11.2.0.4/5]                                              #
#====================================================================================#

#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;

zluo

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: [1] [1] [/dbfile2/database/frdb2/frdb2-redoa1.log]

设定如下参数：
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
=================================================================

zluo

大家，不好意思了，我不太会玩这个东西，设置好，又无法修改。现在把内容发给大家