当前位置 博文首页 > LuciferLiu_DBA：ADG单实例系列搭建之（RMAN备份恢复）

最大化 缩小

    LuciferLiu_DBA：ADG单实例系列搭建之（RMAN备份恢复）

    作者：[db:作者] 时间：2021-06-15 18:11

    参考文章：Data Guard Physical Standby Setup in Oracle Database 11g Release 2

    环境准备?

    ?主机名ipDB Versiondb_namedb_unique_name
    主库orcl192.168.56.12011.2.0.4orclorcl
    备库orcl_stby192.168.56.12111.2.0.4orclorcl_stby

    Notes：

    1、db_unique_name主备库不能相同。

    2、db_name主备库需保持一致。

    3、主备库DB版本需保持一致。

    一、Preparing the Primary Database for Standby Database Creation

    1.Enable Archiving

    SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SELECT LOG_MODE FROM V$DATABASE;

    2.Enable Forced Logging

    ALTER DATABASE FORCE LOGGING;

SELECT FORCE_LOGGING FROM V$DATABASE;

    3.Create a Backup Copy of the Primary Database Datafiles(RMAN)

    ##create backup directory
mkdir -p /backup
chown -R oracle:oinstall /backup
chmod 775 /backup

##create rman backup script
su - oracle
echo '#!/bin/sh' >>/backup/rman_backup_forstby.sh
echo 'source ~/.bash_profile' >>/backup/rman_backup_forstby.sh
echo 'backtime=`date +"20%y%m%d%H%M%S"`' >>/backup/rman_backup_forstby.sh
echo "rman target / log=/backup/rman_backup_forstby_\${backtime}.log<<EOF" >>/backup/rman_backup_forstby.sh
echo 'run {' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c1 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c2 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c3 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c4 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'crosscheck backup;' >>/backup/rman_backup_forstby.sh
echo 'crosscheck archivelog all;' >>/backup/rman_backup_forstby.sh
echo 'sql "alter system switch logfile";' >>/backup/rman_backup_forstby.sh
echo 'delete noprompt expired backup;' >>/backup/rman_backup_forstby.sh
echo 'delete noprompt obsolete device type disk;' >>/backup/rman_backup_forstby.sh
echo "backup database format '/backup/FULL_%U.bak';" >>/backup/rman_backup_forstby.sh
echo "backup archivelog all format '/backup/ARC_%U.bak';" >>/backup/rman_backup_forstby.sh
echo 'release channel c1;' >>/backup/rman_backup_forstby.sh
echo 'release channel c2;' >>/backup/rman_backup_forstby.sh
echo 'release channel c3;' >>/backup/rman_backup_forstby.sh
echo 'release channel c4;' >>/backup/rman_backup_forstby.sh
echo '}' >>/backup/rman_backup_forstby.sh
echo 'EOF' >>/backup/rman_backup_forstby.sh

##begin rman backup
chmod +x /backup/rman_backup_forstby.sh
nohup /backup/rman_backup_forstby.sh >/dev/null 2>&1 &

    4.Create a Control File for the Standby Database

    ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stby_ctrl.ctl';

    5.Create a Parameter File for the Standby Database

    CREATE PFILE='/tmp/initstby.ora' FROM SPFILE;

    6.Copy Files from the Primary System to the Standby System

    a.Copy the Backup datafiles

    ##Standby create /backup dir
mkdir /backup
chown oracle:oinstall /backup
chmod 775 /backup

##Primary copy backup files
scp *bak /backup/oracle@orcl_stby:/backup

    b.Copy the remote login password file

    ##ORACLE_HOME must be the same dir on primary and standby first

scp $ORACLE_HOME/dbs/orapworcl oracle@orcl_stby:$ORACLE_HOME/dbs

    c.Copy the Standby control file

    scp stby_ctrl.ctl oracle@orcl_stby:/tmp

    d.Copy the?Initialization parameter file

    scp initstby.ora oracle@orcl_stby:/tmp/

    7.Configure listeners for the primary and standby databases

    Configure hosts?for the primary and standby databases

    ##Botn Primary and standby Set

vi /etc/hosts

#Add Public IP
192.168.56.120  orcl
192.168.56.121 orcl_stby

    ##Botn Primary and standby Set listener.ora

vi $TNS_ADMIN/listener.ora

##ADD
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcl)
    )
  )


##Then restart listener
lsnrctl stop
lsnrctl start

    8.Create Oracle Net service names.

    ##Botn Primary and standby Set tnsnames.ora

vi $TNS_ADMIN/tnsnames.ora


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

ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl_stby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_stby)
    )
  )

##Test tnsping and sqlplus
tnsping orcl
tnsping orcl_stby
sqlplus sys/oracle@orcl as sysdba
sqlplus sys/oracle@orcl_stby as sysdba

    9.Add Standby redo log?for the Standby Database

    set line222
col member for a60
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#;
 
   THREAD#     GROUP# MEMBER                                                       T2.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ ------------------
         1          3 /oradata/orcl/redo03.log                                                    120
         1          2 /oradata/orcl/redo02.log                                                    120
         1          1 /oradata/orcl/redo01.log                                                    120
 
--需要注意：
--1.stanby log日志大小与redo log日志保持一致
--2.stanby log数量： 
standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile.
--3.thread要与redo log保持一致，如果是rac，需要增加多个thread对应的standby log
 
ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 4 ('/oradata/orcl/standby_redo04.log') SIZE 120M,
group 5 ('/oradata/orcl/standby_redo05.log') SIZE 120M,
group 6 ('/oradata/orcl/standby_redo06.log') SIZE 120M,
group 7 ('/oradata/orcl/standby_redo07.log') SIZE 120M;

    二、Preparing the Standby Database for Standby Database Creation

    1.Configure?Parameter File for the Standby Database

    vi /tmp/initstby.ora

##ADD
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=orcl_stby
*.fal_client='ORCL_STBY'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL_STBY,ORCL)'
*.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='/oradata/orcl','/oradata/orcl'
*.db_file_name_convert='/oradata/orcl','/oradata/orcl'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4

    2.Create a server parameter file for the standby database

    create spfile from pfile='/tmp/initstby.ora';

    三、Create?Physical Standby Database

    1.Start the Physical Standby Database nomount

    startup nomount

    2.Restore Standby From RMAN Backup

    a.Restore Standby Controlfile from RMAN Backup

    restore standby controlfile from '/tmp/stby_ctrl.ctl';

    b.mount Standby database

    alter database mount;

    c.Restore standby database

    ##create rman restore script
su - oracle
echo '#!/bin/sh' >>/backup/rman_restore_forstby.sh
echo 'source ~/.bash_profile' >>/backup/rman_restore_forstby.sh
echo 'backtime=`date +"20%y%m%d%H%M%S"`' >>/backup/rman_restore_forstby.sh
echo "rman target / log=/backup/rman_restore_forstby_\${backtime}.log<<EOF" >>/backup/rman_restore_forstby.sh
echo 'run {' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c1 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c2 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c3 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c4 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'restore database;' >>/backup/rman_restore_forstby.sh
echo 'release channel c1;' >>/backup/rman_restore_forstby.sh
echo 'release channel c2;' >>/backup/rman_restore_forstby.sh
echo 'release channel c3;' >>/backup/rman_restore_forstby.sh
echo 'release channel c4;' >>/backup/rman_restore_forstby.sh
echo '}' >>/backup/rman_restore_forstby.sh
echo 'EOF' >>/backup/rman_restore_forstby.sh

##begin rman backup
chmod +x /backup/rman_restore_forstby.sh
nohup /backup/rman_restore_forstby.sh >/dev/null 2>&1 &

    d.check standby database file

    select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile;

    3.Configure Primary database Parameter

    --设置DG参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCL_STBY;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

    4.Open Standby Database and?Start Redo Apply

    alter database open;

alter database recover managed standby database using current logfile disconnect from session;

    5.check Standby database

    set line222
col member for a60

select open_mode,database_role from v$database;

select process,group#,thread#,sequence# from v$managed_standby;

select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;

select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;

    上一篇：LuciferLiu_DBA：Oracle DG系列：STANDBY DATABASE的前世今生
    下一篇：没有了