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

Lucifer三思而后行

发布于 2021-08-17 14:21:29

1.1K0

发布于 2021-08-17 14:21:29

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

环境准备

	主机名	ip	DB Version	db_name	db_unique_name
主库	orcl	192.168.56.120	11.2.0.4	orcl	orcl
备库	orcl_stby	192.168.56.121	11.2.0.4	orcl	orcl_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<>/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<>/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;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/04/19 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

oracle

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

数据库

oracle

登录后参与评论

0 条评论

热度

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

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

环境准备

一、Preparing the Primary Database for Standby Database Creation

1.Enable Archiving

2.Enable Forced Logging

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

4.Create a Control File for the Standby Database

5.Create a Parameter File for the Standby Database

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

a.Copy the Backup datafiles

b.Copy the remote login password file

c.Copy the Standby control file

d.Copy the Initialization parameter file

7.Configure listeners for the primary and standby databases

8.Create Oracle Net service names.

9.Add Standby redo log for the Standby Database

二、Preparing the Standby Database for Standby Database Creation

1.Configure Parameter File for the Standby Database

2.Create a server parameter file for the standby database

三、Create Physical Standby Database

1.Start the Physical Standby Database nomount

2.Restore Standby From RMAN Backup

a.Restore Standby Controlfile from RMAN Backup

b.mount Standby database

c.Restore standby database

d.check standby database file

3.Configure Primary database Parameter

4.Open Standby Database and Start Redo Apply

5.check Standby database

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐