ORACLE主从库
ORACLE主从库1. 基础设置：介绍Oracle主从配置的基本环境和前提条件。2. 详细步骤：逐步讲解配置过程，包括每个命令的详细说明。3. 常见问题及解决方案：分享在配置过程中可能遇到的问题及其解决方法。4. 后续维护和管理：提供主从数据库维护和管理的建议和技巧。基础配置准备开库顺序 先启备库，再启主库（启动监听，打开告警日志）关库顺序 先关主库再关备库· 查看数据库版本主库ip：xxx.xxx.xxx.xxx；备库ip：xxx.xxx.xxx.xxx；查看数据库版本，必须是企业版否则不支持oracle data guardSQL>select * from v$version;备库只安装软件file:///C:/Users/john/AppData/Local/Temp/msohtmlclip1/01/clip_image001.jpg· 确保备库安装路径、实例名与主库完全一致，避免同步出错· 一、主库配置1、在/oracl11下创建interlib文件夹(自定义文件夹名),在创建文件夹log日志文件路径：'logUrl=E:\oracl11\interlib\log'控制文件路径：'standbyUrl=E:\oracl11\interlib'2、开启归档模式# 在主库上启动数据库到mount模式，开启归档模式与force loggingsqlplus / as sysdbaSQL>shutdown immediate;SQL>startup mount;修改为归档模式SQL>alter databasearchivelog; SQL>alter database open; 设置强制归档模式SQL> alter database forcelogging;查看命令：select log_mode,force_loggingfrom v$database;查看是否归档命令：Archive log list ;
file:///C:/Users/john/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg3、为备库创建日志文件cleartlogUrl=E:\oracl11\interlib\log根据实际的“日志文件路径”改变SQL> alter database addstandby logfile group 4 ('E:/oracl11/interlib/log/STAN04.LOG') size 50m;SQL>alter database add standby logfile group 5('E:/oracl11/interlib/log/STAN05.LOG') size 50m;SQL> alter database addstandby logfile group 6 ('E:/oracl11/interlib/log/STAN06.LOG') size 50m; 4、创建standby控制文件SQL> alter database createstandby controlfile as 'E:\oracl11\interlib\standby.ctl'; 5、导出当前数据库参数并修改SQL> createpfile='E:/oracl11/interlib/initora.ora' from spfile;修改相关路径，以及增加没有的参数orcl11.__db_cache_size=24293408768orcl11.__java_pool_size=134217728orcl11.__large_pool_size=134217728orcl11.__oracle_base='E:\oracl11'#ORACLE_BASEset fromenvironmentorcl11.__pga_aggregate_target=22011707392orcl11.__sga_target=32883343360orcl11.__shared_io_pool_size=0orcl11.__shared_pool_size=7918845952orcl11.__streams_pool_size=0*.archive_lag_target=1800*.audit_file_dest='E:\oracl11\admin\orcl11\adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='E:\oracl11\oradata\orcl11\control01.ctl','E:\oracl11\flash_recovery_area\orcl11\control02.ctl'*.db_block_size=8192*.db_cache_size=536870912*.db_domain=''*.DB_FILE_NAME_CONVERT='E:\oracl11\oradata\orcl11','E:\oracl11\oradata\orcl11'*.db_name='orcl11'*.db_recovery_file_dest='E:\oracl11\flash_recovery_area'*.db_recovery_file_dest_size=4102029312*.db_unique_name='primary'*.diagnostic_dest='E:\oracl11'*.dispatchers='(PROTOCOL=TCP)(SERVICE=orcl11XDB)'*.fal_client='standby'*.fal_server='primary'*.log_archive_config='DG_CONFIG=(primary,standby)'*.log_archive_dest_1='LOCATION=E:\oracl11\interlib\log\VALID_FOR=(all_logfiles,all_roles) db_unique_name=primary'设置为异步复制模式async*.log_archive_dest_2='service=standby arch asyncvalid_for=(online_logfiles,primary_role)db_unique_name=standby'*.log_archive_dest_state_1='enable'*.log_archive_dest_state_2='enable'*.log_archive_format='%t_%s_%r.dbf'*.LOG_FILE_NAME_CONVERT='E:\oracl11\interlib\log','E:\oracl11\interlib\log'*.memory_target=54828990464*.open_cursors=300*.processes=30000*.remote_login_passwordfile='EXCLUSIVE'*.sessions=3350*.standby_file_management='auto'*.undo_tablespace='UNDOTBS1'6、重新加载配置启动服务停止服务SQL> shutdown immediate;使用新参数文件启动数据库SQL> startuppfile='E:\oracl11\interlib\initora.ora' nomount;创建新的 spfile 文件SQL> create spfile frompfile='E:\oracl11\interlib\initora.ora';停止服务SQL> shutdown immediate;启动服务SQL> startup;7、创建密码文件如果有此步，存在,密码设置为：mstchina*2022密码文件可不操作在DOS窗口执行，不需要登录sqlplus，路径不能加引号，否则会报opw-00001错误 orapwdfile=E:\oracl11\product\11.2.0\dbhome_1\database\PWDorcl11.orapassword=mstchina*2022 entries=10# 密码文件存放路径：密码文件存放路径：passwordUrl=E:\oracl11\product\11.2.0\dbhome_1\database\PWDorcl11.ora#注意密码文件命名规则.ora命名规则为PWD+sid，我这里的sid为orcl11，所以为PWDorcl11，不然会出现远程登录权限不足
8、配置监听和访问服务修改 listener.ora# listener.ora Network Configuration File:E:\oracl11\product\11.2.0\dbhome_1\network\admin\listener.ora# Generated byOracle configuration tools.
SID_LIST_LISTENER=(SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME =E:\oracl11\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS ="EXTPROC_DLLS=ONLY:E:\oracl11\product\11.2.0\dbhome_1\bin\oraclr11.dll") )添加SID_DESC (SID_DESC = (GLOBAL_DBNAME =orcl11) (ORACLE_HOME =E:\oracl11\product\11.2.0\dbhome_1) (SID_NAME = orcl11) ))
LISTENER=(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521)) (ADDRESS = (PROTOCOL =TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521)) ))
ADR_BASE_LISTENER= E:\oracl11
修改tnsname.ora文件tnsnames.ora NetworkConfiguration File:E:\oracl11\product\11.2.0\dbhome_1\network\admin\tnsnames.ora# Generated byOracle configuration tools.
ORACLR_CONNECTION_DATA=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ))
orcl11=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-QEDP6N8PVGK)(PORT= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl11) ))增加主库配置PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl11) )) 增加备库配置STANDBY= (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx) (PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl11) ))
9、测试监听停止服务SQL> shutdown immediate;SQL> startup mount;alter database open是数据库处于mount状态，从mount状态打开数据库的命令。SQL> alter database open;在DOS窗口执行，不需要登录sqlplus，lsnrctl stoplsnrctl start测试配置是否正确SQL> sqlplus sys/mstchina*2022@primary as sysdbaPS:若连接失败，检查防火墙设置或者ping下IP地址是否正常。10、关闭主数据库SQL> shutdown immediate;PS：在配置备库前，需要关闭主数据库，以免产生其他的log二、备库配置1、拷贝数据到备库主库和备库创建 E:\oracl11\interlib\tmp 文件夹，并把interlib其余目录也拷贝过去将主库oracle目录下的oradata文件夹下内容复制到备库相同目录将E:\oracl11 目录下的admin,cfgtollogs,diag,flash_recover_area目录以及密码文件(‘E:\oracl11\product\11.2.0\dbhome_1\database\PWDorcl11.ora’)拷贝到备用库的相同路径。可直接覆盖将standby.ctl文件拷贝到备库的数据文件夹内（E:\oracl11\oradata\orcl11\）和E:\oracl11\flash_recovery_area\orcl11\下。在两个路径下将文件复制为control01.ctl,control02.ctl。将主库的listener.ora和tnsname.ora拷贝到备库相同路径，并修改linstener.ora的ip为备库ip(‘E:\oracl11\product\11.2.0\dbhome_1\NETWORK\ADMIN’)LISTENER =(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521)) (ADDRESS = (PROTOCOL =TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521)) ))2、备库新建实例备库新建实例，如果备库也安装了数据库，实例也是orcl11这步可跳过在备库上注册oracle实例到服务中，DOS下执行oradim -new -sid orcl11lsnrctl start3、修改备库参数并创建实例将从主库拷贝的 E:\oracl11\interlib\initora.ora修改#下面是要修改的地方orcl11.__db_cache_size=24293408768orcl11.__java_pool_size=134217728orcl11.__large_pool_size=134217728orcl11.__oracle_base='E:\oracl11'#ORACLE_BASEset fromenvironmentorcl11.__pga_aggregate_target=22011707392orcl11.__sga_target=32883343360orcl11.__shared_io_pool_size=0orcl11.__shared_pool_size=7918845952orcl11.__streams_pool_size=0*.archive_lag_target=1800*.audit_file_dest='E:\oracl11\admin\orcl11\adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='E:\oracl11\oradata\orcl11\control01.ctl','E:\oracl11\flash_recovery_area\orcl11\control02.ctl'*.db_block_size=8192*.db_cache_size=536870912*.db_domain=''*.DB_FILE_NAME_CONVERT='E:\oracl11\oradata\orcl11','E:\oracl11\oradata\orcl11'*.db_name='orcl11'*.db_recovery_file_dest='E:\oracl11\flash_recovery_area'*.db_recovery_file_dest_size=4102029312*.db_unique_name='standby'*.diagnostic_dest='E:\oracl11'*.dispatchers='(PROTOCOL=TCP)(SERVICE=orcl11XDB)'*.fal_client='primary'*.fal_server='standby'*.log_archive_config='DG_CONFIG=(primary,standby)'*.log_archive_dest_1='LOCATION=E:\oracl11\interlib\log\VALID_FOR=(all_logfiles,all_roles) db_unique_name=standby'*.log_archive_dest_2='service=primaryarch async valid_for=(online_logfiles,primary_role)db_unique_name=primary'*.log_archive_dest_state_1='enable'*.log_archive_dest_state_2='enable'*.log_archive_format='%t_%s_%r.dbf'*.LOG_FILE_NAME_CONVERT='E:\oracl11\interlib\log','E:\oracl11\interlib\log'*.memory_target=54828990464*.open_cursors=300*.processes=30000*.remote_login_passwordfile='EXCLUSIVE'*.sessions=3350*.standby_file_management='auto'*.undo_tablespace='UNDOTBS1'
使用新参数文件建立备库实例SQL> startup nomountpfile='E:\oracl11\interlib\initora.ora';SQL> create spfile frompfile='E:\oracl11\interlib\initora.ora';SQL> shutdown immediate; （此步骤可能会报错 01507,暂时忽略）SQL> startup nomount;
此时登录 sqlplus会报错：ORA-12560: TNS: 协议适配器错误解决方案如下：· 检查OracleOraDb11g_home1TNSlistener服务是否存在，如果存在看看有没有启动该服务，如果不存在，在E:\oracl11\product\11.2.0\dbhome_1\BIN路径下的emca.bat双击启动· 打开环境变量：属性–高级–环境变量—系统变量–新建，变量名=oracle_sid,变量值=XXX,XXX就是你的 database SID，我这里sid是orcl11。三、主库执行相关语句1、建立主库备份在备库执行，DOS下执行sqlplus / as sysdbaSQL> startup;复制主库，使用RMAN建立备份，DOS下执行rman target /RMAN> backup full databaseformat='E:\oracl11\interlib\tmp\FOR_STANDBY_%u%p%s,RMN' include currentcontrolfile for standby;将当前archivelog归档，执行sql语句RMAN> sql 'alter system archive log current'; 主库处于mount或nomount状态时会出现错误，主语sql后的空格2、复制数据库复制数据库；将主库E:\oracl11\interlib\tmp下产生的的备份集拷贝到备库的相同路径下拷贝完成后在主库刚才的RMAN中执行，注意这时备库需要处于nomount状态RMAN> connect auxiliary sys/mstchina*2022@standbyRMAN> duplicate target database forstandby nofilenamecheck;执行完后会在备库130的 E:\ORACL11\ORADATA目录出现一些DBF文件。3、配置备库为备用库模式启动备用数据库为挂载备库模式SQL>startup nomount;SQL>alter database mountstandby database;可能会报错 01100不管#在备用服务器上启动日志传送服务SQL>alter database recover managed standby databasedisconnect from session;SQL>shutdown immediate;SQL>startup;四、检查测试1、状态查看测试 主库备库分别执行如果APPLIED 列的值为 yes,表示重做应用成功SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BYSEQUENCE#;或者查看切换归档，归档日志记录会+1select max(sequence#) from v$archived_log;altersystem switch logfile;select max(sequence#) from v$archived_log;2、日志查看测试主库上执行(手动归档日志，手动同步)alter system switch logfile;通过select name from v$archived_log; 可以看到主库和备库都增加了一个log文件(.DBF)3、查看主备库状态执行sql语句selectopen_mode,protection_mode,database_role,switchover_status from v$database;五、DG切换主库执行# 先将主库切换成备库，然后将原主库启动到物理库的状态alter database commit toswitchover to physical standby with session shutdown;关闭主库shutdown immediate;开数据库nomountstartup nomount;更改主库为备库alter database mount standby database;alter databaserecover managed standby database disconnect from session;如果配置了 standby redo log 并需要启用实时同步则执行以下代码alter database recover managed standby database usingcurrent logfile disconnect from session;
备库执行,switchover到primary更改备库为主库alter database commit to switchover to primary withsession shutdown;如果备库还有未应用的日志则执行alter database recover managed standby databasedisconnect from session;shutdown immediate;startup 六、DG切换后再恢复最初即原主库切换为备库，再从备库切换为主库开库顺序先启备库，再启主库（启动监听，打开告警日志）关库顺序先关主库再关备库lsnrctl stoplsnrctl start
1、主库操作登录原主库rman target /RMAN> connect auxiliarysys/123.com@standbyRMAN> duplicate target database for standbynofilenamecheck;
2、备库stnadby在备库执行sqlplus / as sysdbaSQL>alter database mount standbydatabase; 可能会报错 01100不管SQL>alter database recovermanaged standby database disconnect from session;3、状态监测或者查看切换归档，归档日志记录会+1SQL>select max(sequence#) fromv$archived_log;SQL>alter system switch logfile;SQL>select max(sequence#)from v$archived_log; 4、复制模式切换异步复制，主库上操作**## 设置归档目的地参数（LOG_ARCHIVE_DEST_n）**SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db_nameASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby_db_unique_name';设置日志传输服务（Log Transport Services）SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db_name LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby_db_unique_name';确认配置SQL> SHOW PARAMETER LOG_ARCHIVE_DEST;检查输出中的LOG_ARCHIVE_DEST_n参数值，确保它们包含ASYNC关键字。
同步复制，主库上操作## 设置归档目的地参数（LOG_ARCHIVE_DEST_n）SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db_nameSYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby_db_unique_name';设置同步复制使用LGWR SYNC模式，它指示日志写入器进程（Log Writer Process）以同步方式传输重做日志SQL> ALTER SYSTEM SETLOG_ARCHIVE_DEST_2='SERVICE=standby_db_name LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby_db_unique_name';## 确认配置SQL>SHOW PARAMETER LOG_ARCHIVE_DEST;检查输出中的LOG_ARCHIVE_DEST_n参数值，确保它们包含SYNC关键字。七、常见错误处理办法1、ORA-10456
错误号 错误描述
ORA-01507数据库未安装
可忽略不处理2、ORA-10456
错误号 错误描述
ORA-10456无法打开备用数据库；媒体恢复会话可能正在进行中
解决办法alter database recover managed standby database cancel;alter database open; alter database recover managed standby database usingcurrent logfile disconnect ; 3、ORA-01031
错误号 错误描述
ORA-01031权限不足
解决方法：检查登录的sid密码文件是否存在，或者命名规则是否正确，注意密码文件命名规则.ora命名规则为PWD+sid，我这里的sid为orcl11，所以为PWDorcl11，不然会出现远程登录权限不足4、ORA-01031
错误号 错误描述
ORA-01031权限不足
解决方法：检查登录的sid密码文件是否存在，或者命名规则是否正确，注意密码文件命名规则.ora命名规则为PWD+sid，我这里的sid为orcl11，所以为PWDorcl11，不然会出现远程登录权限不足5、ORA-01110
错误号 错误描述
ORA-01110数据文件 1:'E:\ORACL11\ORADATA\ORCL11SYSTEM01.DBF'
解决方法：一般情况是因为数据文件不存在，可通过重新主库备份，可参考以下命令在备库执行，DOS下执行sqlplus / as sysdbaSQL>startup;复制主库，使用RMAN建立备份，DOS下执行rman target /RMAN> backup full databaseformat='E:\oracl11\interlib\tmp\FOR_STANDBY_%u%p%s,RMN' include currentcontrolfile for standby;# 将当前archivelog归档，执行sql语句RMAN> sql 'alter systemarchive log current'; RMAN> connect auxiliary sys/mstchina*2022@standbyRMAN> duplicate target database forstandby nofilenamecheck;# 执行完后会在备库130的 E:\ORACL11\ORADATA目录出现一些DBF文件 6、ORA-02232
错误号 错误描述
ORA-02232???MOUNT？？
解决办法主库生成的standby.ctl控制文件拷贝到initora.ora中控制文件指定的地方，再重新使用initora.ora nomount启动数据库即可，对于备库原有的control01.ctl文件进行覆盖，必须保证主库和备库的control01文件一致。
可参考前面的standby.ctl流程，备库需要复制standby.ctl到具体位置，并修改名字将standby.ctl文件拷贝到备库的数据文件夹内（E:\oracl11\oradata\orcl11\）和E:\oracl11\flash_recovery_area\orcl11\下。在两个路径下将文件复制为control01.ctl,control02.ctl。7、ORA-01090
错误号 错误描述
ORA-01090过程中关闭－不允许连接
解决办法再开一个会话执行shutdown abortstartupshutdown immediate8、ORA-01100可忽略不处理9、ORA-01153
错误号 错误描述
ORA-01153激活了不兼容的介质恢复
解决办法再开一个会话执行shutdown abortstartupshutdown immediate
页:
[1]