部署同服务名，同实例名dg

技术2022-07-11 95

借鉴博客（参数详解和备库控制文件恢复）： https://blog.csdn.net/u011016933/article/details/107063991 https://blog.csdn.net/u011016933/article/details/107059359 一.环境模拟 1，主库环境： ip地址：192.168.6.30 hosts文件添加 192.168.6.30 enmotech1 oracle 192.168.6.31 enmotech2 oracle $ORACLE_BASE=/u01/app/oracle $ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 数据文件路径：/u02/oradata/orcl/ 归档路径：/u01/app/oracle/archivelog/ rman备份路径:/home/oracle/rmanbak/ 服务名：orcl 实例名：orcl db_name=orcl db_unique_name=orcl 2，备库环境： ip地址：192.168.6.31 hosts文件添加 192.168.6.30 enmotech1 oracle 192.168.6.31 enmotech2 oracle $ORACLE_BASE=/u01/app/oracle $ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 数据文件路径：/u02/oradata/orcl/ 归档路径：/u01/app/oracle/archivelog/ rman备份路径:/home/oracle/rmanbak/ 服务名：orcl 实例名：orcl db_name=orcl db_unique_name=orcl 2.操作步骤主库（192.168.6.30）操作： 1.1创建orcl实例 dbca -silent \ -createDatabase \ -templateName General_Purpose.dbc \ -databaseType OLTP \ -gdbname orcl \ -sid orcl \ -emConfiguration NONE \ -sysPassword abc123 \ -systemPassword abc123 \ -responseFile NO_VALUE \ -storageType FS \ -datafileDestination /u02/oradata \ -redoLogFileSize 500 \ -recoveryAreaDestination /u01/app/oracle/fast_recovery_area \ -sampleSchema false \ -memoryPercentage 40 \ -characterSet ZHS16GBK \ -nationalCharacterSet AL16UTF16 \ -initParams processes=1000,audit_trail=none 1.2开启归档和强制日志 alter database archivelog;(mount状态下执行) alter database force logging;（open状态下执行） 2.主库（192.168.6.30）操作： alter system set log_archive_config=''; alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles)'; alter system set log_archive_dest_2='service=orcldg lgwr async affirm valid_for=(online_logfiles,primary_role)' alter system set standby_file_management=auto; alter system set fal_server='orcldg'; 3.创建pfile文件 create pfile from spfile; 4.修改监听listener.ora和tnsnames.ora文件修改监听文件listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = enmotech1)(PORT = 1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/) (SID_NAME=orcl) ) ) ADR_BASE_LISTENER = /u01/app/oracle 修改tnsnames.ora文件 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = enmotech2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = enmotech1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) 启动监听：lsnrctl start 4.主库rman备份 RMAN> run{ allocate channel c1 type disk; allocate channel c2 type disk; backup database tag='db_full_bak' format='/home/oracle/rmanbak/db_%d_%T_%s_%p.bkp'; sql 'alter system archive log current'; backup archivelog all delete input tag='arch_bak' format='/home/oracle/rmanbak/log_%d_%T_%s_%p.bkp'; backup current controlfile tag='ctl_bak' format='/home/oracle/rmanbak/ctl_%d_%T_%s_%p.bkp'; release channel c1; release channel c2; } 5.拷贝文件拷贝listener.ora和tnsnames.ora文件到192.168.6.31:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/ 拷贝initorcl.ora和orapworcl文件到192.168.6.31:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ 拷贝备份到192.168.6.31:/home/oracle/rmanbak/ 备库（192.168.6.31）操作 1.1修改initorcl.ora文件（对应的目录，自行创建） orcl.__large_pool_size=394264576 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=16777216 orcl.__sga_target=746586112 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=209715200 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='none' *.compatible='11.2.0.4.0' *.control_files='/u02/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=8631877632 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.fal_server='orcl' *.log_archive_config='' *.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles)' *.log_archive_dest_2='service=orcl lgwr async affirm valid_for=(online_logfiles,primary_role)' *.memory_target=761266176 *.open_cursors=300 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' 1.2创建spfile文件 create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora' 2.修改监听listener LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = enmotech2)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1/) (SID_NAME = orcl) ) ) ADR_BASE_LISTENER = /u01/app/oracle 启动监听：lsnrctl start 2.启动数据库到nomount状态 startup nomount; 3.恢复备库控制文件 RMAN> restore standby controlfile from '/home/oracle/rmanbak/ctl_%d_%T_%s_%p.bkp' 4.启动数据库文件到mount状态 RMAN> alter database mount; 5.恢复数据库 run{ allocate channel c1 type disk; allocate channel c2 type disk; restore database; recover database; release channel c1; release channel c2; } recover database until scn XXXXX;(可能会报错，有scn号，重新执行recover database) 6.备库添加standby_redo.log alter database add standby logfile group 11 '/u02/oradata/orcl/standby_redo11.log' size 500M; alter database add standby logfile group 12 '/u02/oradata/orcl/standby_redo12.log' size 500M; alter database add standby logfile group 13 '/u02/oradata/orcl/standby_redo13.log' size 500M; alter database add standby logfile group 14 '/u02/oradata/orcl/standby_redo14.log' size 500M; 7.打开备库 alter database recover managed standby database disconnect from session; alter database recover managed standby database cancel; alter database open; alter database recover managed standby database disconnect from session; 检测同步. 1.查看主库状态 SYS@orcl> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/archivelog Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 2.查看备库应用日志 [oracle@enmotech2 orcl]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 1 17:20:37 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> select process, status, sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------------------------- ------------------------------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 6 MRP0 WAIT_FOR_LOG 6 8 rows selected. 注意：细心的哥们会发现主库缺少standby_redo.log,备库缺少redo.log，（极端操作，可以相互拷贝日志文件到主备库的数据文件路径） 1.备库重建redo日志 alter database recover managed standby database cancel; alter system set log_file_name_convert='/u02/oradata/orcl/','/u02/oradata/orcl/' scope=spfile; startup force mount; recover managed standby database disconnect from session; 到此备库redo日志就自动重建了关闭数据库重建spfile（如过log_file_name_convert不置为空的话，rfs进程起不来） create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora' 打开数据库 alter database recover managed standby database disconnect from session; 2.主库添加standby_redo.log alter database add standby logfile group 11 '/u02/oradata/orcl/standby_redo11.log' size 500M; alter database add standby logfile group 12 '/u02/oradata/orcl/standby_redo12.log' size 500M; alter database add standby logfile group 13 '/u02/oradata/orcl/standby_redo13.log' size 500M; alter database add standby logfile group 14 '/u02/oradata/orcl/standby_redo14.log' size 500M; #!/bin/sh # Create_time:2019.11.27 # Author: # Description:Delete the archived logs on Standby which had been applied # Usage: # Last modify: 2019.11.27 # Note: #Initial Parameters _SCRIPTPATH=/usr/local/shells/del_applied_archivelog/ _DAYBEFOR=0 #OSTYPE OSTYPE=`uname -s` if [ $OSTYPE = "AIX" ] then . ~/.profile else . ~/.bash_profile fi #Main cd ${_SCRIPTPATH} echo "rman target / log=rman_delete_arch.log << EOF" > tmp_delete_archive_rman.tmp echo /dev/null > tmp_delete_archive_rman.sh sqlplus -s "/ as sysdba" >> tmp_delete_archive_rman.tmp << EOF set head off set feedback off SELECT 'delete noprompt archivelog until logseq ' || MAX(D.SEQUENCE#) ||' thread ' || D.THREAD# || ';' FROM V\$ARCHIVED_LOG D,(SELECT MAX(A.COMPLETION_TIME) -${_DAYBEFOR} COMPLETION_TIME, A.THREAD# FROM V\$ARCHIVED_LOG A WHERE APPLIED = 'YES' GROUP BY A.THREAD#) TMP WHERE D.THREAD# = TMP.THREAD# AND D.COMPLETION_TIME < TMP.COMPLETION_TIME GROUP BY D.THREAD#; exit EOF echo "exit" >> tmp_delete_archive_rman.tmp echo "EOF" >> tmp_delete_archive_rman.tmp sed '/^$/d' tmp_delete_archive_rman.tmp > tmp_delete_archive_rman.sh #execute delete script sh tmp_delete_archive_rman.sh

Processed: 0.011, SQL: 9