DG实验同一机器自己总结

今天终于把DG实验做完中间出了不少问题，在网络和同事的帮助下，一一把问题解决
实验环境：
实验类型：同一主机上用RMAN备份建立物理备用数据库
OS：WindowsXP
实例名：主库orcl 备库standby

前提准备

. 设置主数据库为force logging 模式

SQL>sqlplus "/as sysdba"

SQL>alter database force logging;

. 设置主数据库为归档模式

SQL> archive log list

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list

1、RMAN全备份主库(orcl):

rman target sys/oracle@orcl

backup database plus archivelog;

backup current controlfile for standby;
2、建立存放standby库相关的OS目录
mkdir d:\standby
mkdir d:\standby\bdump
mkdir d:\standby\udump
mkdir d:\standby\archive
mkdir d:\standby\d:\standby\flash_recovery_area
3、建立列程服务standby实例以及密码
oradim -new -sid standby -intpwd oracle
4、配置监听程序和网络服务名（在这里我把配置好的贴出来）主库的
listener.ora
# listener.ora Network Configuration File: d:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
   )

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dell-jason)(PORT = 1521))
    )
)

tnsname.ora

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dell-jason)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dell-jason)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

5、准备主数据参数文件。

sqlplus sys/oracle@orcl as sysdba

create pfile from spfile;

编辑参数文件initorcl.ora 具体在D:\oracle\product\10.2.0\db_1\database目录下如下（我这里只添加要加的内容）：

db_unique_name=orcl
log_archive_dest_1='location=D:\oracle\product\10.2.0\flash_recovery_area valid_for=(all_logfiles,all_roles) db_unique_name=orcl'
log_archive_dest_4='SERVICE=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby'
standby_file_management=AUTO
log_archive_config='dg_config=(orcl,standby)'
fal_server=standby
fal_client=orcl

建立服务器参数文件spfile

shutdown immediate

create spfile from pfile='C:\oracle\product\10.2.0\db_1\database\initorcl.ora';

startup;
6、准备备用参数文件。

sqlplus sys/oracle@orcl as sysdba

create pfile='D:\oracle\product\10.2.0\db_1\database\initstandby.ora' from spfile;

建好standby的参数文件后打开，在D:\oracle\product\10.2.0\db_1\database\initstandby.ora下编辑参数文件（我这里只填需要加的）

db_unique_name=standby
service_names=standby
instance_name=standby
control_files=d:\standby\control01.ctl
log_archive_dest_1='location=d:\standby\archive valid_for=(all_logfiles,all_roles) db_unique_name=standby'
log_archive_dest_2='SERVICE=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
db_file_name_convert='D:\oracle\product\10.2.0\oradata\orcl','d:\standby'
log_file_name_convert='D:\oracle\product\10.2.0\oradata\orcl','d:\standby'
log_archive_config='dg_config=(orcl,standby)'
fal_server=orcl
fal_client=standby
standby_file_management=AUTO
standby_archive_dest='d:\standby\archive'
background_dump_dest='d:\standby\bdump'
user_dump_dest='d:\standby\udump'
建立服务器参数文件（在主库）
sqlplus sys/oracle@orcl as sysdba
create spfile='D:\oracle\product\10.2.0\db_1\database\spfilestandby.ora' from pfile= 'D:\oracle\product\10.2.0\db_1\database\initstandby.ora';
7、启用备用例程并建立备用数据库，在建立备用数据库时备用数据库必须以nomount方式启动如下：
set oracle_sid=standby
sqlplus sys/oracle as sysdba
startup nomount
启用例程后用RMAN恢复到备库如下：
这步骤之前先必须把主库日志切换：在主库中执行alter system switch logfile;
set oracle_sid=standby
rman target sys/oracle@orcl auxiliary sys/oracle
rman下执行：duplicate target database for standby dorecover;
这些备库就完全恢复
8、启用备用数据库
set oracle_sid=standby
sqlplus sys/oracle as sysdba
startup force mount;
conn sys/oracle@orcl as sysdba
alter system switchi logfile;
conn sys/oracle@standby as sysdba
select name from v$archivelog;看看有没有日志传过来，传过来表示DG配置成功，若没有仔细检查你的参数文件。
9、在备库上增加临时表空间
alter database open
alter tablespace temp add tempfile 'd:\standby\temp.dbf' size 5m reuse;

测试在主库中建一张表后执行alter system switch logfile;
后在备库将备库转为重做应用状态：alter database recover managed standby database disconnect from session;
然后取消重做应用：alter database recover managed standby database cancel
这样就能在备库查到你在主库建的表了