ORACLE 11G 部署物理DG

最新推荐文章于 2022-04-28 17:52:59 发布

congjiong3047

最新推荐文章于 2022-04-28 17:52:59 发布

阅读量152

点赞数

文章标签：网络数据库

1. 配置主库允许强制记日志
SQL> alter database force logging;

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR
---
YES

2. 把主库的密码文件复制到备库并修改所属用户为ORACLE所属组为OINSTALL
$cd $ORACLE_HOME/dbs
$ scp -P 17698 orcl1 root@192.168.1.13:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orcl_dg
--在备库操作
[root@fsdb02 tmp]# chown oracle.oinstall /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orcl_dg

3. 主库配置DG参数

--修改主库初始化参数

SQL>
alter system set log_archive_config='DG_CONFIG=(orcl,orcl_dg)' scope=both;
alter system set log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both;
alter system set log_archive_dest_2='SERVICE=orcl_dg LGWR ASYNC valid_for=(online_logfiles,primary_role) COMPRESSION=ENABLE db_unique_name=orcl_dg' scope=both;

--设置主库备角色参数
SQL>
alter system set FAL_SERVER=orcl_dg scope=both;
alter system set FAL_CLIENT=orcl scope=both;
alter system set DB_FILE_NAME_CONVERT='orcl','orcl_dg' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/data/orclarch/','+ARCH' scope=spfile;
alter system set standby_file_management=AUTO scope=both;

4. 在主库创建 PFILE，然后把PFILE复制到备库修改参数作为备库的参数文件

SQL> CREATE pfile='/tmp/orcl.ora' from spfile;

scp -P 17698 /tmp/orcl.ora root@192.168.1.13:/tmp

--在备库修改PFILE
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/orcl/control01.ctl','/data/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl_dg'
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=1000
*.log_archive_dest_1='location=/data/orclarch'
*.open_cursors=500
*.open_links=100
*.pga_aggregate_target=4724464025
*.processes=2000
*.sessions=2205
*.sga_target=18897856102
*.undo_tablespace='UNDOTBS1'

备库创建相关目录
--创建存放数据文件的目录
su - oracle
$mkdir -p /data/oradata/orcl
--创建audit_file_dest 目录
su - oracle
$ mkdir /home/oracle/app/oracle/admin/orcl/adump -p
--创建归档日志目录
mkdir /data/orclarch

创建备控制文件
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl.ctl';

scp -P 17698 /tmp/orcl.ctl root@192.168.1.13:/tmp

在备库复制控制文件到指定的位置
#cp /tmp/orcl.ctl /data/oradata/orcl/control01.ctl
#cp /tmp/orcl.ctl /data/oradata/orcl/control02.ctl
# chown -R oracle.oinstall /data/oradata/orcl

使用PFIEL启动数据库实例，创建SPFILE
export ORACLE_SID=orcl_dg
SQL> startup mount pfile='/tmp/orcl.ora';
SQL> create spfile from pfile='/tmp/orcl.ora';
SQL> shutdown immediate;
SQL> startup mount;

修改备库DG参数
alter system set log_archive_config='DG_CONFIG=(orcl,orcl_dg)' scope=both;
alter system set DB_FILE_NAME_CONVERT='orcl','orcl_dg' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/data/orclarch/','+ARCH' scope=spfile;

alter system set log_archive_dest_1='location=/data/orclarch valid_for=(all_logfiles,all_roles) db_unique_name=orcl_dg' scope=both;
alter system set log_archive_dest_2='SERVICE=orcl LGWR ASYNC valid_for=(online_logfiles,primary_role) COMPRESSION=ENABLE db_unique_name=orcl' scope=both;

alter system set FAL_SERVER=orcl scope=both;
alter system set FAL_CLIENT=orcl_dg scope=both;
alter system set standby_file_management=AUTO scope=both;

把前一天的RMAN备份复制到备库 /home/oracle/lom 目录下,并设置备份文件的所属用户为
oracle所属组为 oinstall

删除控制文件中原来的备份信息，注册新的备份信息

RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt expired archivelog all;
RMAN> delete backup;
RMAN>delete expired backup of archivelog all;

RMAN> catalog start with '/home/oracle/olm';
---注意此次不能使用 catalog start with '/home/oracle/olm/'，后面多了个斜线会导致无法找到文件

--生成RMAN数据文件交换语句的SQL
set pagesize 0
set line 200
select 'Set NewName For DataFile   '||file_id
||'   to '||''''||'/data/oradata/orcl'||REGEXP_REPLACE(file_name,
'\+DATA/orcl/datafile','')||''''||';'
from dba_data_files;

rman>
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
allocate channel ch7 device type disk;
Set NewName For DataFile   1   to '/data/oradata/orcl/system01.dbf';
Set NewName For DataFile   2   to '/data/oradata/orcl/sysaux02.dbf';
Set NewName For DataFile   3   to '/data/oradata/orcl/undotbs101.dbf';
Set NewName For DataFile   4   to '/data/oradata/orcl/undotbs201.dbf';
Set NewName For DataFile   5   to '/data/oradata/orcl/users01.dbf';
Set NewName For DataFile   6   to '/data/oradata/orcl/posp01.dbf';
Set NewName For DataFile   7   to '/data/oradata/orcl/posp02.dbf';
Set NewName For DataFile   8   to '/data/oradata/orcl/posp03.dbf';
Set NewName For DataFile   9   to '/data/oradata/orcl/undotbs202.dbf';
Set NewName For DataFile   10   to '/data/oradata/orcl/undotbs102.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/data/oradata/orcl/temp01.dbf';
restore database ;
switch datafile all;
recover database;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
}

--RMAN手册中的使用 SET NEWNAME FOR DATABASE 的示例
RUN {
SET NEWNAME FOR DATABASE TO '/oradata/%U';
DUPLICATE TARGET DATABASE TO dupdb
SKIP TABLESPACE tools
LOGFILE
   GROUP 1 ('/duplogs/redo01a.log',
            '/duplogs/redo01b.log') SIZE 4M REUSE,
   GROUP 2 ('/duplogs/redo02a.log',
             '/duplogs/redo02b.log') SIZE 4M REUSE;
}

--使用RMAN备份主库当天的归档日志，然后在备库进行恢复
--备份主库归档日志,并把备份复制到备库的 /home/oracle/olm

RMAN>

run
{
        ALLOCATE CHANNEL CH1 DEVICE TYPE DISK format '/home/oracle/olm-%U.BKP';
        ALLOCATE CHANNEL CH2 DEVICE TYPE DISK format '/home/oracle/olm-%U.BKP';
        backup as compressed backupset archivelog all not backed up 1 times;
        RELEASE CHANNEL CH1;
        RELEASE CHANNEL CH2;
}

--在备库重新注册新的归档日志备份,进行恢复
RMAN> catalog start with '/home/oracle/olm';
RMAN> recover database;

主备库添加备重做日志
SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/data/oradata/orcl/group_9_1') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/data/oradata/orcl/group_10_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/data/oradata/orcl/group_11_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/data/oradata/orcl/group_12_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/data/oradata/orcl/group_13_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/data/oradata/orcl/group_14_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/data/oradata/orcl/group_15_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/data/oradata/orcl/group_16_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/data/oradata/orcl/group_17_1') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ('/data/oradata/orcl/group_18_1') SIZE 500M;

--检查备重做日志已经创建
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM
V$STANDBY_LOG;

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM
2 V$STANDBY_LOG;

    GROUP#    THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         9          0          0 YES UNASSIGNED
        10          0          0 YES UNASSIGNED
        11          0          0 YES UNASSIGNED
        12          0          0 YES UNASSIGNED
        13          0          0 YES UNASSIGNED
        14          0          0 YES UNASSIGNED
        15          0          0 YES UNASSIGNED
        16          0          0 YES UNASSIGNED
        17          0          0 YES UNASSIGNED
        18          0          0 YES UNASSIGNED

10 rows selected.

主库配置 $ORACLE_HOME/network/admin/tnsnames.ora ,RAC 两个节点都要配置

orcl_dg =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

--备库配置 $ORACLE_HOME/network/admin/tnsnames.ora
[oracle@fsdb02 ~]$ cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@fsdb02 admin]$ ls
samples shrept.lst tnsnames.ora
[oracle@fsdb02 admin]$ vi tnsnames.ora

orcl =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.25)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.26)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      #(INSTANCE_NAME =orcl1)
    )
)

--备库配置 $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.13)(PORT=1521))
        (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
#                       #BEQUEATH CONFIG
#          (GLOBAL_DBNAME=salesdb.mycompany)
          (SID_NAME=orcl_dg)
          (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
          (SERVER=DEDICATED)
#                       #PRESPAWN CONFIG
#         (PRESPAWN_MAX=20)
#         (PRESPAWN_LIST=
#           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
        )
       )

--备库删除日志组，再重新添加日志组
alter database drop logfile group 1;
alter database add logfile group 1 ('/data/oradata/orcl/group_1_1') size 500M;

alter database drop logfile group 2;
alter database add logfile group 2 ('/data/oradata/orcl/group_2_1') size 500M;

alter database drop logfile group 3;
alter database add logfile THREAD 2 group 3 ('/data/oradata/orcl/group_3_1') size 500M;

alter database drop logfile group 4;
alter database add logfile THREAD 2 group 4 ('/data/oradata/orcl/group_4_1') size 500M;

alter database drop logfile group 5;
alter database add logfile THREAD 1 group 5 ('/data/oradata/orcl/group_5_1') size 500M;

alter database drop logfile group 6;
alter database add logfile THREAD 1 group 6 ('/data/oradata/orcl/group_6_1') size 500M;

alter database drop logfile group 7;
alter database add logfile THREAD 2 group 7 ('/data/oradata/orcl/group_7_1') size 500M;

alter database drop logfile group 8;
alter database add logfile THREAD 2 group 8 ('/data/oradata/orcl/group_8_1') size 500M;

--在备库启用日志应用
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

--配置备库删除2天前的归档日志
[oracle@fsdb02 rman]$ vi del_standby_drp_arch.sh

umask 022
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl_dg

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin

export PATH=${PATH}:$ORACLE_HOME/OPatch

rman target / log=/home/oracle/log/rman_del_drp_standby_arch_`date +%y%m%d`.log <<EOF
DELETE FORCE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-2';
EOF
exit

[oracle@fsdb02 trace]$ crontab -e

#00 12 * * * /home/oracle/app/orale/scripts/rman_del_orcl_arch.sh
01 00 * * * /home/oracle/rman/del_standby_drp_arch.sh

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2123871/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2123871/