2023年7月4日发(作者：)

oracle12创建多个实例,Oracle12cWindows多实例ADGOracle12c Windows 多实例ADGOracle DG英⽂全称Oracle Data Guard，DG提供全⾯的服务包括：创建、维护、管理、以及监控standby数据库，确保数据安全。DG⽤途是构建企业数据⾼可⽤应⽤环境。ADG实际是实现⾃动切换主备的意思，这⾥关于A暂时不做配置。[toc]1. 环境介绍1.1 安装环境在主机1上安装数据库软件，并建监听和实例，在主机2上安装数据库软件，并建监听，可以不建实例。项⽬主机1(主库)主机2(备库)操作系统Windows server 2016Windows server 2016主机名称db31db32IP地址172.16.103.31172.16.103.32Oracle版本Oracle 12.2.0.1Oracle 12.2.0.1BASED:appAdministratorvirtualD:appAdministratorvirtualSIDorcl1,orcl2orcl1,orcl2db_unique_namedb31_orcl1，db31_orcl2db32_orcl1，db32_orcl21.2 多实例启动问题1.2.1 现象先部署orcl1实例，在部署orcl2实例。部署后orcl2能正常shutdown/startup，⽽orcl1在shutdown之后startup不能正常启动，提⽰没有pfile配置⽂件。官⽅解决办法需要指定pfile(Oracle多实例启动)。但是如果只是为了启动实例，后⾯的ADG同样还是配置不下去的，后⾯还有很多报错依旧。1.2.2 分析在分析⽇志报错以及⽇志⽂件，最多的显⽰是“指定⽂件不存在、或⽆法识别、或⽆权限访问”。请牢记这个错误提⽰就是windows下⽂件权限导致的。如当前的环境，orcl1不能直接启动需指定pfile，但明明指定路径下是有spfile⽂件，它为什么⾮要去读pfile⽂件呢？问题就在于我们orcl1的spfile的权限不正常。打开spfile的属性安全设置会发现，只有orcl2实例在权限，没有orcl1的权限。1.2.3 解决办法通过上⾯的分析，我们只有把属于orcl1实例的spfile⽂件的权限修改为everyone修改权限即可。当然其他属于orcl1实例的⽂件也需要修改。这⾥还需要把⽂件权限修改正确。如何修改window⽂件权限，这⾥不阐述。1.3 如何切换实例切换实例在linux下是修改环境配置⽂件。在window下直接使⽤实例名即可。如：// 登⼊orc1sqlplus sys/123456@orc1 as sysdba// 登⼊orc2sqlplus sys/123456@orc2 as sysdba2. 监听配置多实例也好，单实例也罢，监听⽂件只需要⼀份。在配置时需要注意区分开来。⽤做ADG的lis和tns，有多少个实例就配置多组，但⽂件只要⼀份。2.1 (主端)# Network Configuration File:D:appAdministratorvirtualproduct12.2.0dbhome_# Generated by Oracle configuration _LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = CLRExtProc)(ORACLE_HOME = D:appAdministratorvirtualproduct12.2.0dbhome_1)(PROGRAM = extproc)(ENVS = "EXTPROC_DLLS=ONLY:D:appAdministratorvirtualproduct12.2.0dbhome_"))(SID_DESC =(GLOBAL_DBNAME = orcl1)(ORACLE_HOME = D:appAdministratorvirtualproduct12.2.0dbhome_1)(SID_NAME = orcl1)(ENVS = "EXTPROC_DLLS=ONLY:D:appAdministratorvirtualproduct12.2.0dbhome_"))(SID_DESC =(GLOBAL_DBNAME = orcl2)(ORACLE_HOME = D:appAdministratorvirtualproduct12.2.0dbhome_1)(SID_NAME = orcl2)(ENVS = "EXTPROC_DLLS=ONLY:D:appAdministratorvirtualproduct12.2.0dbhome_")))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))2.2 (备端)# Network Configuration File:D:appAdministratorvirtualproduct12.2.0dbhome_# Generated by Oracle configuration _LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = CLRExtProc)(ORACLE_HOME = D:appAdministratorvirtualproduct12.2.0dbhome_1)(PROGRAM = extproc)(ENVS = "EXTPROC_DLLS=ONLY:D:appAdministratorvirtualproduct12.2.0dbhome_"))(SID_DESC =(GLOBAL_DBNAME = orcl1)(ORACLE_HOME = D:appAdministratorvirtualproduct12.2.0dbhome_1)(SID_NAME = orcl1)(ENVS = "EXTPROC_DLLS=ONLY:D:appAdministratorvirtualproduct12.2.0dbhome_"))(SID_DESC =(GLOBAL_DBNAME = orcl2)(ORACLE_HOME = D:appAdministratorvirtualproduct12.2.0dbhome_1)(SID_NAME = orcl2)(ENVS = "EXTPROC_DLLS=ONLY:D:appAdministratorvirtualproduct12.2.0dbhome_")))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))2.3 (主端)# Network Configuration File:D:appAdministratorvirtualproduct12.2.0dbhome_# Generated by Oracle configuration ER_orcl1 =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))LISTENER_orcl2 =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))ORACLR_CONNECTION_DATA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))(CONNECT_DATA =(SID = CLRExtProc)(PRESENTATION = RO)))orcl1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl1)))orcl2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)))orcl1_db31 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl1)))orcl1_db32 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl1)))orcl2_db31 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)))orcl2_db32 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)))2.4 (备端)# Network Configuration File:D:appAdministratorvirtualproduct12.2.0dbhome_# Generated by Oracle configuration ER_ORACLE12 =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))LISTENER_ORCL =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))ORACLR_CONNECTION_DATA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))(CONNECT_DATA =(SID = CLRExtProc)(PRESENTATION = RO)))ORCL1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl1)))ORCL2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)))orcl1_db31 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl1)))orcl1_db32 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl1)))orcl2_db31 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)))orcl2_db32 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.32)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)))2.5 重启监听lsnrctl stoplsnrctl start3. 实例orcl13.1 归档/闪回/强档# 登录(主端)sqlplus sys/123456@orcl1 as sysdbaSQL> select instance_name,status from v$instance;SQL> show pdbs //确定容器数据库# 查看归档/闪回/强档(主端)SQL> archive log list;SQL> select flashback_on from v$database;SQL> select force_logging from v$database;SQL> show parameter db_recovery_file_dest;# 开启强档/归档/闪回(主端)SQL> alter database force logging;SQL> alter system set db_recovery_file_dest='D:appAdministratorvirtualfast_recovery_area';SQL> alter system set db_recovery_file_dest_size='40G';// 此设置会变为临时设置// 正确的做法是收到添加pfile⽂件⾥// *.db_recovery_file_dest='D:appAdministratorvirtualfast_recovery_area'// *.db_recovery_file_dest_size=42949672960SQL> shutdown immediate;SQL> startup pfile=D:appAdministratorvirtualproduct12.2.0dbhome_ mount;SQL> alter database archivelog;SQL> alter database flashback on;SQL> alter database open;SQL> alter pluggable database all open; //开启容器数据库3.2 standby⽇志SQL> SELECT GROUP#,ARCHIVED,STATUS,BYTES/1024/1024 FROM V$LOG;SQL> SELECT GROUP#,MEMBER FROM V$LOGFILE;SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 11'D:APPADMINISTRATORVIRTUALORADATAORCL1redo11_stb01_log' size 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 12'D:APPADMINISTRATORVIRTUALORADATAORCL1redo12_stb02_log' size 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 13'D:APPADMINISTRATORVIRTUALORADATAORCL1redo13_stb03_log' size 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 14'D:APPADMINISTRATORVIRTUALORADATAORCL1redo14_stb04_log' size 200M;3.3 pfile⽂件/pwd密码# pfile调试SQL> set line 1000;SQL> select instance_name,status from v$instance;SQL> show parameter spfile;SQL> show parameter name;SQL> select open_mode from v$database;SQL> select decode(count(*),1,'spfile','pfile') from v$spparameter where rownum=1 and isspecified ='TRUE';# 创建pfile// 如1.2所说，多实例启动指定pfile⽂件。这⾥只需修改pfile⽂件## 主站配置(在末尾添加)*.db_unique_name='orcl1_db31'*.fal_server='orcl1_db32'*.log_archive_config='dg_config=(orcl1_db31,orcl1_db32)'*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=orcl1_db31'*.log_archive_dest_2='service=orcl1_db32 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl1_db32'*.log_archive_dest_state_1=ENABLE*.log_archive_dest_state_2=ENABLE*.standby_file_management='AUTO'*.db_file_name_convert='D:appAdministratorvirtualoradataORCL1','D:appAdministratorvirtualoradataORCL1'*.log_file_name_convert='D:appAdministratorvirtualoradataORCL1','D:appAdministratorvirtualoradataORCL1'## 备站配置(在末尾添加)*.db_unique_name='orcl1_db32'*.fal_server='orcl1_db31'*.log_archive_config='dg_config=(orcl1_db31,orcl1_db32)'*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=orcl1_db32'*.log_archive_dest_2='service=orcl1_db31 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl1_db31'*.log_archive_dest_state_1=ENABLE*.log_archive_dest_state_2=ENABLE*.standby_file_management='AUTO'*.db_file_name_convert='D:appAdministratorvirtualoradataORCL1','D:appAdministratorvirtualoradataORCL1'*.log_file_name_convert='D:appAdministratorvirtualoradataORCL1','D:appAdministratorvirtualoradataORCL1'# ⽣成spfile(主端)SQL> shutdown immediate;SQL> create spfile from pfile;SQL> startup;SQL> alter pluggable database all open;# ⽣成spfile(备端)SQL> shutdown immediate;SQL> create spfile from pfile;SQL> startup nomount;# 将密码⽂件拷贝到备端(注意⽂件权限)D:appAdministratorvirtualproduct12.2.0dbhome_3.4 RMAN复制开启同步# cmd下执⾏RMAN复制(备端)rman target sys/123456@orcl1_db31 auxiliary sys/123456@orcl1_db32# RMAN下执⾏复制RMAN> duplicate target database for standby from active database nofilenamecheck;# SQL下开启同步SQL> alter database recover managed standby database using current logfile disconnect from session;## 取消同步SQL> alter database recover managed standby database cancel;# 备端打开数据库SQL> select status from v$instance;SQL> alter database open;SQL> alter pluggable database all open;# 备库开闪回SQL> select flashback_on from v$database;alter database flashback on;# SQL下开启同步SQL> alter database recover managed standby database using current logfile disconnect from session;3.5 DG验证# 主备库状态SQL> select switchover_status,database_role from v$database;# 主备库归档序号SQL>select thread#, max(sequence#) "Last Standby Seq Applied"from v$archived_log val, v$database vdbwhere ogs_change# = ogs_change#and d in ('YES','IN-MEMORY')group by thread# order by 1;# 备库DG进程SQL> select process,client_process,sequence#,status from v$managed_standby;# 备库模式SQL> select database_role,protection_mode,protection_level,open_mode from v$database;# 主库创建表create table test as select * from dba_objects where rownum < 101;# 备库查询select count(*) from test; ## 结果为100表⽰实时同步成功# 主库删除drop table test purge;4. 实例orcl23.1 归档/闪回/强档# 登录(主端)sqlplus sys/123456@orcl2 as sysdbaSQL> select instance_name,status from v$instance;SQL> show pdbs# 查看归档/闪回/强档(主端)SQL> archive log list;SQL> select flashback_on from v$database;SQL> select force_logging from v$database;SQL> show parameter db_recovery_file_dest;# 开启强档/归档/闪回(主端)SQL> alter database force logging;SQL> alter system set db_recovery_file_dest='D:appAdministratorvirtualfast_recovery_area';SQL> alter system set db_recovery_file_dest_size='40G';SQL> shutdown immediate;SQL> startup mount;SQL> alter database archivelog;SQL> alter database flashback on;SQL> alter database open;SQL> alter pluggable database all open;3.2 standbySQL> SELECT GROUP#,ARCHIVED,STATUS,BYTES/1024/1024 FROM V$LOG;SQL> SELECT GROUP#,MEMBER FROM V$LOGFILE;SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 11'D:APPADMINISTRATORVIRTUALORADATAORCL2redo11_stb01_log' size 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 12'D:APPADMINISTRATORVIRTUALORADATAORCL2redo12_stb02_log' size 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 13'D:APPADMINISTRATORVIRTUALORADATAORCL2redo13_stb03_log' size 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 14'D:APPADMINISTRATORVIRTUALORADATAORCL2redo14_stb04_log' size 200M;3.3 pfile/spfile/密码# pfile调试SQL> set line 1000;SQL> select instance_name,status from v$instance;SQL> show parameter spfile;SQL> show parameter name;SQL> select open_mode from v$database;SQL> select decode(count(*),1,'spfile','pfile') from v$spparameter where rownum=1 and isspecified ='TRUE';SQL> startup pfile='D:appAdministratorvirtualproduct12.2.0dbhome_'SQL> SELECT NAME, VALUE, DISPLAY_VALUE FROM V$PARAMETER WHERE NAME ='spfile';# 创建pfileSQL> create pfile from spfile;# 将pfile⽂件和密码⽂件拷贝到备端D:appAdministratorvirtualproduct12.2.0dbhome_:appAdministratorvirtualproduct12.2.0dbhome_# 修改pfile⽂件，在末尾追加下⾯内容## 主站配置*.db_unique_name='orcl2_db31'*.fal_server='orcl2_db32'*.log_archive_config='dg_config=(orcl2_db31,orcl2_db32)'*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=orcl2_db31'*.log_archive_dest_2='service=orcl2_db32 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl2_db32'*.log_archive_dest_state_1=ENABLE*.log_archive_dest_state_2=ENABLE*.standby_file_management='AUTO'*.db_file_name_convert='D:appAdministratorvirtualoradataorcl2','D:appAdministratorvirtualoradataorcl2'*.log_file_name_convert='D:appAdministratorvirtualoradataorcl2','D:appAdministratorvirtualoradataorcl2'## 备站配置*.db_unique_name='orcl2_db32'*.fal_server='orcl2_db31'*.log_archive_config='dg_config=(orcl2_db31,orcl2_db32)'*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=orcl2_db32'*.log_archive_dest_2='service=orcl2_db31 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl2_db31'*.log_archive_dest_state_1=ENABLE*.log_archive_dest_state_2=ENABLE*.standby_file_management='AUTO'*.db_file_name_convert='D:appAdministratorvirtualoradataorcl2','D:appAdministratorvirtualoradataorcl2'*.log_file_name_convert='D:appAdministratorvirtualoradataorcl2','D:appAdministratorvirtualoradataorcl2'# ⽣成spfile(主端)SQL> shutdown immediate;SQL> create spfile from pfile;SQL> startup;SQL> alter pluggable database all open;# ⽣成spfile(备端)SQL> shutdown immediate;SQL> create spfile from pfile;SQL> startup nomount;# 将密码⽂件拷贝到备端(注意⽂件权限)D:appAdministratorvirtualproduct12.2.0dbhome_3.4 RMAN复制开启同步# cmd下执⾏RMAN复制(备端)rman target sys/123456@orcl2_db31 auxiliary sys/123456@orcl2_db32# RMAN下执⾏复制RMAN> duplicate target database for standby from active database nofilenamecheck;# SQL下开启同步SQL> alter database recover managed standby database using current logfile disconnect from session;## 取消同步SQL> alter database recover managed standby database cancel;# 备端打开数据库SQL> select status from v$instance;SQL> alter database open;SQL> alter pluggable database all open;# 备库开闪回SQL> select flashback_on from v$database;alter database flashback on;# SQL下开启同步SQL> alter database recover managed standby database using current logfile disconnect from session;3.5 DG验证# 主备库状态SQL> select switchover_status,database_role from v$database;# 主备库归档序号SQL>select thread#, max(sequence#) "Last Standby Seq Applied"from v$archived_log val, v$database vdbwhere ogs_change# = ogs_change#and d in ('YES','IN-MEMORY')group by thread# order by 1;# 归档应⽤SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED,DELETED FROM V$ARCHIVED_LOG WHERE DELETED='NO'ORDER BY SEQUENCE#;# 备库DG进程SQL> select process,client_process,sequence#,status from v$managed_standby;# 备库模式SQL> select database_role,protection_mode,protection_level,open_mode from v$database;# 主库创建表create table test as select * from dba_objects where rownum < 101;# 备库查询select count(*) from test; ## 结果为100表⽰实时同步成功# 主库删除drop table test purge;5 ⼿动断开DG# 关闭同步SQL> alter database recover managed standby database cancel;# 强制停⽌redo applySQL> alter database recover managed standby database finish force;# 查看备库状态SQL> select switchover_status,database_role from v$database;SWITCHOVER_STATUS DATABASE_ROLE---------------------------------------- --------------------------------SESSIONS ACTIVE PHYSICAL STANDBY# 切换为primarySQL> alter database commit to switchover to primary with session shutdown;# 打开数据库SQL> alter database open;# 验证数据库模式SQL> select database_role,protection_mode,protection_level,open_mode from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE----------------- --------------------- --------------------- ---------------PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE