一:windg信息,主备库目录一致
1.操作系统:
主库物理机:主机名:PRIMARY RAM:128G
备库虚拟机:主机名:STANDBY RAM:32G
2.数据库配置:
1.主库:db_name:orcl db_unique_name:orcl tnsname:orcl
2.备库:db_name:orcl db_unique_name:orcldg tnsname:orcldg
3.安装路径,数据文件路径完全一致
二:操作步骤
主库操作
1.开启强制日志,打开powershell
PS C:\Users\Administrator> sqlplus / as sysdba
SQL> alter database force logging;
2.主库配置参数,
SQL> alter system set log_archive_config = 'dg_config=(orcl,orcldg)';
SQL> alter system set log_archive_dest_2 = 'SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';
SQL> alter system set log_archive_dest_state_2 = ENABLE;
SQL> alter system set standby_file_management = auto;
3.生成pfile作为备库启动的参数文件
create pfile='d:\INITorcl.ORA' from spfile;
4.修改listener.ora,添加静态监听(可以不添加)
# listener.ora Network Configuration File: D:\app\admin\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\admin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_NAME = orcl)
(ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\app\admin
5.修改tnsnames.ora文件
# tnsnames.ora Network Configuration File: D:\app\admin\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#新增内容
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
6.备份主库,进入rman target /
PS C:\Users\Administrator> rman target /
RMAN> run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
backup filesperset 10 database format 'D:\rmanbak\dbbak_%d_%s_%p_%T';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
7.备份控制文件
RMAN> backup current controlfile for standby format 'D:\rmanbak\standby_controlfile.bak';
备库操作
1.打开powershell,执行命令
PS C:\Users\Administrator> oradim -new -sid orcl
2.拷贝listener.ora,tnsnames.ora到D:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN下,
2.1修改listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\admin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_NAME = orcldg)
(ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\app\admin
2.2修改tnsnames(主库直接拷贝过来上传)
# tnsnames.ora Network Configuration File: D:\app\admin\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
#新增内容
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
2.3启动监听
lsnrctl start
3.拷贝INITorcl.ORA,PWDorcl.ora到D:\app\admin\product\11.2.0\dbhome_1\database
3.1修改INITorcl.ORA
*.audit_file_dest='D:\app\admin\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='D:\APP\ADMIN\ORADATA\ORCL\CONTROL01.CTL','D:\APP\ADMIN\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\admin\fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcldg'
*.diagnostic_dest='D:\app\admin'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orcldg,orcl)'
*.log_archive_dest_1='location=D:\app\admin\arch'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_2='ENABLE'
*.memory_target=17179869184
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
4.创建相应的文件夹(包括数据文件的文件夹)
d:\app\admin\admin\orcl\adump
d:\app\admin\oradata\orcl\
d:\app\admin\fast_recovery_area\orcl
d:\app\admin\oradata\orcl
5.启动数据库到nomount状态(先用pfile起,然后再重启用spfile重启到nomount)
PS C:\Users\Administrator> sqlplus / as sysdba
SQL> startup pfile='D:\app\admin\product\11.2.0\dbhome_1\database\INITorcl.ORA' nomount;
ORACLE 例程已经启动。
Total System Global Area 1.7103E+10 bytes
Fixed Size 2298352 bytes
Variable Size 8657047056 bytes
Database Buffers 8422162432 bytes
Redo Buffers 21655552 bytes
SQL> create spfile from pfile;
文件已创建。
SQL> shu immediate;
ORA-01507: ??????
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 1.7103E+10 bytes
Fixed Size 2298352 bytes
Variable Size 8657047056 bytes
Database Buffers 8422162432 bytes
Redo Buffers 21655552 bytes
6.恢复控制文件,进入rman target /
PS C:\Users\Administrator> rman target /
RMAN> restore standby controlfile from 'D:\RMANBAK\STANDBY_CONTROLFILE.BAK';
7.注册备份文件(如果主备库的备份目录一致,可以跳过这一步)
RMAN> catalog start with 'D:\RMANBAK\';
8.启动数据到mount状态
RMAN> alter database mount;
9.添加redo日志(方便以后可能出现的主备切换)
拷贝主库redo数据文件到备库相应的目录
10.重储数据文件
RMAN> run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
restore database;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
11.添加standby_redo.log
SQL> alter system set standby_file_management = manual;
SQL> alter database add standby logfile group 11 'd:\app\admin\oradata\orcl\standby_redo11.log' size 50m ;
SQL> alter database add standby logfile group 12 'd:\app\admin\oradata\orcl\standby_redo12.log' size 50m ;
SQL> alter database add standby logfile group 13 'd:\app\admin\oradata\orcl\standby_redo13.log' size 50m ;
SQL> alter database add standby logfile group 14 'd:\app\admin\oradata\orcl\standby_redo14.log' size 50m ;
12.应用日志,打开数据库,
# 应用日志
alter database recover managed standby database disconnect from session;
# 查看应用状态,并在主库切换日志,查看序列变化
select max(sequence#),applied,archived from v$archived_log group by applied,archived;
# 打开数据库
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database disconnect from session using current logfile;
13.设置standby_redo.log为auto
SQL> alter system set standby_file_management = auto;
14.查看主库信息
1.归档信息
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 D:\app\admin\arch
最早的联机日志序列 21571
下一个存档日志序列 21573
当前日志序列 21573
2.查看进程信息
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
------------------ ------------------------ ----------
ARCH CLOSING 21573
ARCH CLOSING 21571
ARCH OPENING 21544
ARCH CLOSING 21572
LNS WRITING 21574
3.查看备库进程
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
------------------ ------------------------ ----------
ARCH CLOSING 21573
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 21574
MRP0 APPLYING_LOG 21574
14.备库添加归档删除脚本
center.bat --总的调度中心,设置环境变量,调用其他脚本
@echo off
echo ================================================
echo Windows环境下Oracle数据库的自动清理归档脚本
echo 删除已成功应用的备库的归档日志
echo ================================================
::以“YYYYMMDD”格式取出当前时间。
SET BACKUPDATE=%date:~0,4%-%date:~5,2%-%date:~8,2%
SET ORACLE_HOME=D:\app\admin\product\11.2.0\dbhome_1
SET ORACLE_SID=orcldg
SET NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
SET LOG_DIR=D:\rman\del_arch_log
SET LOG_NAME=%LOG_DIR%\del_adg_arc_%ORACLE_SID%_%BACKUPDATE%.log
SET SQL_NAME=%LOG_DIR%\del_adg_arc.sql
sqlplus -s sys/oracle@orcldg as sysdba @D:\\rman\pro_sql.bat
rman target sys/oracle@orcldg cmdfile='D:\rman\del_arc.bat' LOG=%LOG_NAME%
pro_sql.bat --生成删除归档的语句到指定目录并保存成SQL脚本,此处是删除所有应用过的归档日志,如需保留几天,修改数字0
set feedback off heading off pagesize 0 linesize 100
col exec_sql format a50
spool %SQL_NAME%
SELECT 'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' || A.THREAD# || ';' EXEC_SQL
FROM V$ARCHIVED_LOG A
WHERE (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN
(SELECT b.THREAD#,
b.SEQUENCE#,
b.RESETLOGS_CHANGE#
FROM V$ARCHIVED_LOG B
WHERE B.APPLIED = 'YES'
AND b.COMPLETION_TIME <= SYSDATE - 0)
AND a.NAME NOT IN (SELECT b.DESTINATION
FROM v$archive_dest b
WHERE b.DESTINATION IS NOT NULL)
AND A.COMPLETION_TIME <= SYSDATE - 0
ORDER BY A.THREAD#,
A.SEQUENCE#;
spool off
exit;
del_arc.bat --清理归档日志并调用上段中生成的SQL脚本
crosscheck archivelog all;
delete noprompt expired archivelog all;
@D:\rman\del_arch_log\del_adg_arc.sql
exit;
exit;