|
代码语言：javascript 复制 DG环境介绍 一、备库执行备份 二、在新主机执行恢复操作 2.1、恢复spfile 2.2、恢复控制文件 2.3、注册备份信息 2.4、还原数据文件 2.5、恢复数据库 2.6、激活备库为主库，并启动数据库 在部署完ADG（Active Data Guard）后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源。 本文演示的是利用Oracle DG环境的备库执行备份，然后异机还原恢复成一个新的主库的过程。 DG环境介绍项目 primary db physical standby db 新库 数据库类型（rac或单实例） 单实例 数据库版本 11.2.0.3.0 platform_name Linux x86 64-bit ORACLE_SID oradg11g oradgphy LHRDB db_name/GLOBAL_DBNAME oradg11g db_unique_name oradg11g oradgphy LHRDB ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 一、备库执行备份 代码语言：javascript 复制 cat > /rman/rman_backup_oradgphy_full.sh <<"EOF0" #!/bin/ksh export ORACLE_SID=oradgphy export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" MYDATE=`date +'%Y%m%d%H%M%S'` rman target / log /rman/backup_oradgphy_full_$MYDATE.log append <<EOF run { sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter session set NLS_LANGUAGE="AMERICAN"'; allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate channel c7 type disk; allocate channel c8 type disk; allocate channel c9 type disk; allocate channel c10 type disk; allocate channel c11 type disk; allocate channel c12 type disk; backup as compressed backupset FILESPERSET 10 database format '/rman/FULL_%d_%U.full' section size 100G; backup as compressed backupset archivelog from time 'sysdate-1' format '/rman/ARC_%d_%U.arc' section size 100G; backup current controlfile format '/rman/standby_%U.ctl'; backup spfile format '/rman/spfile_%d_%U.ora'; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; release channel c11; release channel c12; } EOF EOF0 赋予可执行权限： 代码语言：javascript 复制 chmod +x /rman/rman_backup_oradgphy_full.sh 执行备份，或添加定时任务： 代码语言：javascript 复制 nohup sh /rman/rman_backup_oradgphy_full.sh & 备份结果：数据库790G（非分配大小），压缩备份后140G，用时30分钟。 接下来就是把/rman备份目录下的内容都拷贝到新主机上，方法很多，例如scp、磁盘卸载重新挂载等。 二、在新主机执行恢复操作2.1、恢复spfile代码语言：javascript 复制 export ORACLE_SID=LHRDB rman target / startup nomount; restore spfile to pfile '?/dbs/initLHRDB.ora' from '/rman/spfile_ORADG11G_3lv6cd9m_1_1.ora'; -- 修改pfile，去除dg相关参数 vi $ORACLE_HOME/dbs/initLHRDB.ora 需要根据情况对pfile做相关的修改，最终的参数文件内容： *.audit_file_dest='/u01/app/oracle/admin/LHRDB/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/LHRDB/crontal01.ctl','/u01/app/oracle/oradata/LHRDB/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='oradg11g' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4322230272 *.db_unique_name='LHRDB' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' *.memory_max_target=209715200 *.memory_target=209715200 *.open_cursors=300 *.processes=3000 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' 根据pfile文件内容创建相关目录 mkdir -p /u01/app/oracle/admin/LHRDB/adump mkdir -p /u01/app/oracle/oradata/LHRDB/ 根据pfile创建spfile，并启动到nomout create spfile from pfile; startup force nomount 2.2、恢复控制文件代码语言：javascript 复制 -- 因为要恢复为主库，所以需要加上primary关键字 restore primary controlfile from '/rman/standby_3kv6cd9k_1_1.ctl'; 2.3、注册备份信息代码语言：javascript 复制 -- 启动到mout阶段 alter database mount; -- 清除之前的备份信息 EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12); /** CLEAR V$BACKUP_SET */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); /** CLEAR V$BACKUP_PIECE */ --重新注册,注意路径最后一定需要加上/ catalog start with '/rman/'; 2.4、还原数据文件代码语言：javascript 复制 # 创建日志路径 mkdir -p /home/oracle/rman_log/ chown oracle.dba /home/oracle/rman_log/ cat > /home/oracle/rman_restore_LHRDB.sh <<"EOF0" #!/bin/ksh export ORACLE_SID=LHRDB export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH MYDATE=`date +'%Y%m%d%H%M%S'` export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" rman target / log /home/oracle/rman_log/rman_restore_LHRDB_$MYDATE.log append <<EOF run { sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter session set NLS_LANGUAGE="AMERICAN"'; allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate channel c7 type disk; allocate channel c8 type disk; allocate channel c9 type disk; allocate channel c10 type disk; allocate channel c11 type disk; allocate channel c12 type disk; SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/LHRDB/%b'; restore database; switch datafile all; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; release channel c11; release channel c12; } EOF EOF0 开始后台执行还原： 代码语言：javascript 复制 chmod +x /home/oracle/rman_restore_LHRDB.sh nohup sh /home/oracle/rman_restore_LHRDB.sh & 还原时间大概2小时。 2.5、恢复数据库首先使用如下命令找到最大的日志序列号： 代码语言：javascript 复制 list backupset of archivelog from time "sysdate - 1"; 准备恢复数据库的脚本： 代码语言：javascript 复制 cat > /home/oracle/rman_recover_LHRDB.sh <<"EOF0" #!/bin/ksh export ORACLE_SID=LHRDB export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH MYDATE=`date +'%Y%m%d%H%M%S'` export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" rman target / log /home/oracle/rman_log/rman_recover_LHRDB_$MYDATE.log append <<EOF run{ sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter session set NLS_LANGUAGE="AMERICAN"'; allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate channel c7 type disk; allocate channel c8 type disk; allocate channel c9 type disk; allocate channel c10 type disk; allocate channel c11 type disk; allocate channel c12 type disk; set until sequence 230 thread 1; recover database; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; release channel c11; release channel c12; } EOF EOF0 开始后台执行恢复： 代码语言：javascript 复制 chmod +x /home/oracle/rman_recover_LHRDB.sh nohup sh /home/oracle/rman_recover_LHRDB.sh & 恢复时间大概30分钟。 2.6、激活备库为主库，并启动数据库代码语言：javascript 复制 -- 1、查询数据库状态 select open_mode , database_role, flashback_on from v$database; -- 2、激活备库为主库，如果已经是主库了，那就跳过该步骤 alter database activate standby database; -- ALTER DATABASE CLEAR LOGFILE GROUP 4; -- alter database drop logfile group 4; -- 3、启动数据库 alter database open resetlogs; 结果： 代码语言：javascript 复制 SYS@LHRDB > alter database open resetlogs; Database altered. SYS@LHRDB > archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SYS@LHRDB > select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ READ WRITE PRIMARY NO 本文结束。 • 微信公众号：DB宝，作者：小麦苗 • 作者博客地址： 代码语言：javascript 复制 ★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag ★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w 本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。 原始发表：2020-08-06，如有侵权请联系 cloudcommunity@tencent.com 删除 oracle 数据库 sql 本文分享自 DB宝 微信公众号，前往查看 如有侵权，请联系 cloudcommunity@tencent.com 删除。 (责任编辑：) |