win dg搭建（rman恢复备库）

一: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;