#acl merlyn:read,write All:read <> = 前提条件 = 1. db_unique_name 在主备服务器不能相同。 2. hosts文件中定义各自的主机名 {{{ 127.0.0.1 localhost 192.168.1.30 gjserver 192.168.1.131 gjposdg }}} == 主服务器信息 == {{{ OEL/RHEL 6.6 Server name: gjserver IP: 192.168.1.30 Oracle 11.2.0.4 software with oracle instance Oracle SID/Global_name: gjserver Oracle db_unique_name: gjserver }}} == 备服务器信息 == {{{ OEL/RHEL 6.6 Server name: gjposdg IP: 192.168.1.131 Oracle 11.2.0.4 software only Oracle SID/Global_name: gjserver Oracle db_unique_name: gjposdg }}} = 主服务器配制 = SGA: (96*0.8)*0.6 PGA: (96*0.8)*0.4 {{{ # disable AMM alter system reset memory_target; SQL> alter system set sga_target=0; #alter system reset memory_max_target; SHUTDOWN IMMEDIATE STARTUP alter system set memory_max_target=77G scope=spfile; alter system set memory_target=77G scope=spfile; alter system set sga_target=45G scope=both; alter system set pga_aggregate_target=20G scope=both; SHUTDOWN IMMEDIATE STARTUP }}} == 启用/禁用FRA == {{{ SQL> show parameter recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +JDPOS_DATA db_recovery_file_dest_size big integer 4182M SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+JDPOS_ARCH' SCOPE=BOTH SID='*'; System altered. SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH SID='*'; SQL> show parameter recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +JDPOS_ARCH db_recovery_file_dest_size big integer 100G # disable DB_RECOVERY SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*'; }}} == 启用归档 == {{{ SQL> alter system set log_archive_dest_1='LOCATION=+JDPOS_ARCH' scope = both; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> alter system switch logfile; 确认归档配制 SQL> archive log list; }}} == 启用logging == {{{ SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> SELECT FORCE_LOGGING FROM v$database; FOR --- YES }}} == 检查初始化参数 == {{{ SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string gjserver SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string gjserver }}} == 配制LOG_ARCHIVE_CONFIG参数 == {{{ SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(gjserver,gjposdg)'; 确认LOG_ARCHIVE_CONFIG配制 SQL> show parameter LOG_ARCHIVE_CONFIG NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(gjserver,gjposdg) }}} == 设置对端LOG ARCHIVE == {{{ SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=gjposdg NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gjposdg'; 确认以上配制 SQL> show parameter LOG_ARCHIVE_DEST_2 SQL> show parameter LOG_ARCHIVE_DEST_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=gjposdg NOAFFIRM ASYN C VALID_FOR=(ONLINE_LOGFILES,P RIMARY_ROLE) DB_UNIQUE_NAME=go posstb log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string NAME TYPE VALUE ------------------------------ ----------------------------- log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string 启用LOG_ARCHIVE_DEST_2 ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 确认状态 SQL> show parameter LOG_ARCHIVE_DEST_STATE_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string ENABLE log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable }}} == 设置LOG ARCHIVE == {{{ ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; show parameter log_archive_format; show parameter log_archive_max_processes; show parameter remote_login_passwordfile; SQL> show parameter log_archive_max_processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_max_processes integer 30 SQL> show parameter remote_login_passwordfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE }}} == 设置fal_server == {{{ ALTER SYSTEM SET FAL_SERVER=gjposdg; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='gjposdg','gjserver' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='gjposdg','gjserver' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; show parameter fal_server; show parameter standby_file_management; SQL> show parameter fal_server; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_server string JDPOS_ST SQL> show parameter standby_file_management; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO }}} == 配制listener.ora & tnsnames.ora == {{{ SQL> select value from v$parameter where name='service_names'; VALUE -------------------------------------------------------------------------------- gjserver }}} tnsnames.ora {{{ JDPOS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = gjserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = gjserver) (UR = A) ) ) JDPOS_ST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gjposdg)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = gjserver) (UR = A) ) ) }}} listener.ora # in gjposdg host!!! Login with oracle, run 'netca' command to create #1 /u01/app/11.2.0/grid/network/admin/listener.ora {{{ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = gjserver) (ORACLE_HOME = /u01/app/11.2.0/grid) (SID_NAME = gjserver) ) ) }}} #2 {{{ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = JDPOS) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = JDPOS) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JDPOS)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle }}} 确认主备都能ping通 {{{ tnsping JDPOS tnsping JDPOS_ST }}} == 备份主数据库 == $ rman target / {{{ CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/mnt/sdb1/rman/full_%u_%s_%p'; RMAN> BACKUP DATABASE PLUS ARCHIVELOG; # 默认将备份到Flash Recovery Area. }}} == 为备数据库创建控制文件和PFILE == {{{ ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/gjposdg.ctl'; CREATE PFILE='/home/oracle/initgjposdg.ora' FROM SPFILE; SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '~/gjposdg.ctl'; Database altered. SQL> CREATE PFILE='~/initgjposdg.ora' FROM SPFILE; File created. }}} 修改PFILE ~/initgjposdg.ora {{{ *.audit_file_dest='/u01/app/oracle/admin/gjposdg/adump' *.control_files='+JDPOS_DATA/gjserver/controlfile/current.276.917608579','+JDPOS_DATA/gjserver/controlfile/current.277.917608579' *.db_file_name_convert='+JDPOS_DATA','+JDPOS_DATA' *.db_unique_name='gjposdg' *.fal_server='gjserver' *.log_archive_dest_2='SERVICE=gjserver ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gjserver' *.log_file_name_convert='+JDPOS_REDO','+JDPOS_REDO' }}} 首先登录备数据库创建以下目录 {{{ #mkdir -p /mnt/sdb1/oracleData/gjserver #mkdir -p /u01/app/oracle/fast_recovery_area/gjserver #mkdir -p /mnt/sdb1/oracleData/gjserver/adump mkdir -pv /u01/app/oracle/admin/gjposdg/adump }}} 复制两个文件至备数据库 {{{ scp ~/initgjposdg.ora oracle@gjposdg:/u01/app/oracle/product/11.2.0/db_1/dbs/ scp ~/gjposdg.ctl oracle@gjposdg:/mnt/sdb1/oracleData/gjserver/control01.ctl scp ~/gjposdg.ctl oracle@gjposdg:/mnt/sdb1/oracleData/rman/control02.ctl }}} 复制password file和备份文件 {{{ cd /u01/app/oracle/product/11.2.0/db_1/dbs/ scp orapwgjserver oracle@gjposdg:/u01/app/oracle/product/11.2.0/db_1/dbs/ cd /mnt/sdb1/rman/ scp -r gjserver oracle@gjposdg:/mnt/sdb1/rman/ }}} = 备数据库服务器操作 = 注：可选？！首先更改db_unique_name为对应的名称。 {{{ alter system set db_unique_name='gjposdg' SCOPE=SPFILE; select db_unique_name from v$database; # OR: show parameter unique; }}} == 确认listener启动正常 == lsnrctl status == 从备份中恢复数据库(可选方法） == 恢复控制文件 {{{ export ORACLE_SID=gjserver sqlplus / as sysdba SQL> CREATE SPFILE FROM PFILE='~/initgjposdg.ora'; }}} 恢复数据库 {{{ export ORACLE_SID=gjserver rman target / RMAN> STARTUP MOUNT; RMAN> list backup of database summary; RMAN> RESTORE DATABASE; }}} Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database. Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554 == 使用RMAN自动复制主数据库至备数据库 == === For duplicate listener.ora === {{{ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = JDPOS) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = JDPOS) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JDPOS_ST)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle }}} {{{ ~$ sqlplus / as sysdba SQL> startup nomount pfile='$ORACLE_HOME/dbs/initgjposdg.ora'; SQL> startup nomount pfile='$ORACLE_HOME/dbs/initgjposdg.ora'; ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 2: No such file or directory Additional information: 9925 SQL> show parameter dump ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 SQL> startup nomount pfile='$ORACLE_HOME/dbs/initgjposdg.ora'; ORA-01081: cannot start already-running ORACLE - shut it down first SQL> shutdown abort ORACLE instance shut down. SQL> startup nomount pfile='$ORACLE_HOME/dbs/initgjposdg.ora'; ORACLE instance started. Total System Global Area 7465926656 bytes Fixed Size 2267744 bytes Variable Size 1375733152 bytes Database Buffers 6073352192 bytes Redo Buffers 14573568 bytes [oracle@gjposdg ~]$ rman target sys@gjserver auxiliary sys@gjposdg RMAN> duplicate target database for standby from active database nofilenamecheck; }}} 在主备创建日志文件 {{{ set echo off set feedback off set linesize 120 set pagesize 35 set trim on set trims on set lines 120 col group# format 999 col thread# format 999 col member format a70 wrap col status format a10 col archived format a10 col fsize format 999 heading "Size (MB)" select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize from v$log l, v$logfile f where f.group# = l.group# order by 1,2; set linesize 300 column REDOLOG_FILE_NAME format a50 SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP# ASC; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; ALTER DATABASE ADD LOGFILE GROUP 1 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo01.log') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 2 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo02.log') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 3 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo03.log') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 4 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo04.log') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 5 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo05.log') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 6 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo06.log') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 2 ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 3 ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 4 ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 5 ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE GROUP 6 ('+JDPOS_REDO') SIZE 4G; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; ALTER DATABASE ADD LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#; SQL> ALTER DATABASE DROP LOGFILE GROUP 4; ALTER DATABASE DROP LOGFILE GROUP 4 * ERROR at line 1: ORA-01623: ��־ 4 ��ʵ�� gjserver (�߳� 1) �ĵ�ǰ��־ - �޷�ɾ�� ORA-00312: �����־ 4 �߳� 1: '+JDPOS_REDO/gjserver/onlinelog/group_4.262.917925363' SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; GROUP# ARC STATUS ---------- --- ---------------- 1 YES INACTIVE 2 YES INACTIVE 4 NO CURRENT SQL> ALTER SYSTEM CHECKPOINT GLOBAL; System altered. select member from v$logfile; }}} 在主和备数据库中创建STANDBY LOGFILE( +1 redo file ) ALTER DATABASE ADD LOGFILE GROUP 6 ('+JDPOS_REDO',' /u01/app/oracle/redo_local/redo06.log') SIZE 4G; {{{ ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL'; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb07.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb08.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb09.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb10.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb11.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb12.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('+JDPOS_REDO','/u01/app/oracle/redo_local/redo_stb13.log') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 2 ('+JDPOS_REDO','+REDO_LOCAL') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('+JDPOS_REDO','+REDO_LOCAL') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('+JDPOS_REDO','+REDO_LOCAL') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('+JDPOS_REDO','+REDO_LOCAL') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('+JDPOS_REDO','+REDO_LOCAL') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+JDPOS_REDO','+REDO_LOCAL') SIZE 4G; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL'; ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 ('+JDPOS_REDO','+REDO_LOCAL) SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER DATABASE ADD STANDBY LOGFILE ('+JDPOS_REDO') SIZE 4G; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; select member from v$logfile where type='STANDBY'; }}} {{{ ALTER DATABASE DROP LOGFILE GROUP 10; /u01/app/11.2.0/grid/bin [grid@gjposdg bin]$ ./setasmgidwrap -o oracle }}} == 应用传输进程 == {{{ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION; # Foreground redo apply. Session never returns until cancel. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; # Background redo apply. Control is returned to the session once the apply process is started. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; }}} 停止传输 {{{ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; }}} == 确认Dataguard保护模式 == 默认为 maximum performance 模式 {{{ SELECT protection_mode FROM v$database; }}} {{{ select name,open_mode,database_role from v$database; select message from v$dataguard_status; select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2'; dgmgrl sys/oracle36#^@gjposdg DGMGRL> show configuration; }}} == 测试主备服务器ARCHIVELOG == 主: {{{ ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'; SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#; ALTER SYSTEM SWITCH LOGFILE; }}} 备: {{{ ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'; SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; }}} == 主备数据库切换操作 == === Enable Data Guard Broker === http://www.juliandyke.com/Research/DataGuard/DataGuardBroker.php * Both gjserver/gjposdg {{{ SQL> ALTER SYSTEM SET dg_broker_start = TRUE; }}} * Start DGMGRL dgmgrl * Create Data Guard Broker Configuration {{{ dgmgrl> CONNECT sys; CREATE CONFIGURATION DG1 AS PRIMARY DATABASE IS 'gjserver' CONNECT IDENTIFIER IS 'gjserver'; Configuration "DG1" created with primary database "gjserver" }}} * Add standby database(s) {{{ dgmgrl ADD DATABASE 'gjposdg' AS CONNECT IDENTIFIER IS 'gjposdg'; }}} * Enable the configuration {{{ dgmgrl ENABLE CONFIGURATION }}} * Switchover to standby {{{ dgmgrl DGMGRL> SWITCHOVER TO 'gjposdg' }}} * Switchback {{{ dgmgrl DGMGRL> SWITCHOVER TO 'gjserver' }}} === 手动从主gjserver切换至备gjposdg === {{{ CONNECT / AS SYSDBA ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; }}} 关闭主gjserver数据库，挂载主数据库为“备”数据库工作 {{{ STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; }}} === 备数据库(gjposdg)执行接管操作 === {{{ CONNECT / AS SYSDBA ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; SHUTDOWN IMMEDIATE; STARTUP; }}} 切换回去的话，重启执行一遍，反方向。 == 主数据库故障手动切换操作 == 在备gjposdg数据库执行 {{{ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE ACTIVATE STANDBY DATABASE; }}} == Read-Only Standby and Active Data Guard == To switch the standby database into read-only mode, do the following. {{{ SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY; select name,open_mode,database_role from v$database; }}} To resume managed recovery, do the following. {{{ SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; select name,open_mode,database_role from v$database; }}} == Remove dataguard == {{{ alter database recover managed standby database disconnect from session; alter database recover managed standby database cancel; }}} == dup log == {{{ [oracle@gjposdg ~]$ lsnrctl start [oracle@gjposdg ~]$ rman target sys/oracle36#^@gjserver auxiliary sys/oracle36#^@gjposdg Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 8 01:33:11 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: JDPOS (DBID=1444008119) connected to auxiliary database: JDPOS (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 08-JUL-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwgjserver' auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwgjserver' ; } executing Memory Script Starting backup at 08-JUL-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=68 device type=DISK Finished backup at 08-JUL-16 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/mnt/sdb1/oracleData/gjserver/control01.ctl'; restore clone controlfile to '/mnt/sdb1/oracleData/rman/control02.ctl' from '/mnt/sdb1/oracleData/gjserver/control01.ctl'; } executing Memory Script Starting backup at 08-JUL-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/mnt/sdb1/oracleData/rman/snapcf_gjserver.f tag=TAG20160708T013323 RECID=5 STAMP=916623203 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 08-JUL-16 Starting restore at 08-JUL-16 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 08-JUL-16 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/mnt/sdb1/oracleData/gjserver/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/mnt/sdb1/oracleData/gjserver/system01.dbf"; set newname for datafile 2 to "/mnt/sdb1/oracleData/gjserver/sysaux01.dbf"; set newname for datafile 3 to "/mnt/sdb1/oracleData/gjserver/undotbs01.dbf"; set newname for datafile 4 to "/mnt/sdb1/oracleData/gjserver/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/mnt/sdb1/oracleData/gjserver/system01.dbf" datafile 2 auxiliary format "/mnt/sdb1/oracleData/gjserver/sysaux01.dbf" datafile 3 auxiliary format "/mnt/sdb1/oracleData/gjserver/undotbs01.dbf" datafile 4 auxiliary format "/mnt/sdb1/oracleData/gjserver/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /mnt/sdb1/oracleData/gjserver/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 08-JUL-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/mnt/sdb1/oracleData/gjserver/system01.dbf output file name=/mnt/sdb1/oracleData/gjserver/system01.dbf tag=TAG20160708T013331 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/mnt/sdb1/oracleData/gjserver/sysaux01.dbf output file name=/mnt/sdb1/oracleData/gjserver/sysaux01.dbf tag=TAG20160708T013331 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/mnt/sdb1/oracleData/gjserver/undotbs01.dbf output file name=/mnt/sdb1/oracleData/gjserver/undotbs01.dbf tag=TAG20160708T013331 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/mnt/sdb1/oracleData/gjserver/users01.dbf output file name=/mnt/sdb1/oracleData/gjserver/users01.dbf tag=TAG20160708T013331 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 08-JUL-16 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=5 STAMP=916623257 file name=/mnt/sdb1/oracleData/gjserver/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=6 STAMP=916623257 file name=/mnt/sdb1/oracleData/gjserver/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=916623257 file name=/mnt/sdb1/oracleData/gjserver/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=916623257 file name=/mnt/sdb1/oracleData/gjserver/users01.dbf Finished Duplicate Db at 08-JUL-16 }}} = Troubleshooting = == ora 01666 == control file is for a standby database - failover over standby as primary http://facedba.blogspot.com/2015/06/ora-01666-control-file-is-for-standby.html http://oracleinaction.com/recover-standby-datafile-primary/ == copy controlfile from localdisk to asm == {{{ asmcmd cp /tmp/file +JDPOS_DATA/test }}} == ORA 01624 == {{{ SQL> alter database clear logfile group 2; alter database clear logfile group 2 * ERROR at line 1: ORA-01624: ��־ 2 �ǽï¿ï¿½Ö¸ï¿½Êµï¿½ï¿½ gjserver (�߳� 1) ������ ORA-00312: �����־ 2 �߳� 1: '+JDPOS_REDO/gjserver/onlinelog/group_2.268.918342065' SQL> alter system checkpoint; SQL> alter database clear logfile group 2; }}} == ORA 01378 == {{{ ORA-01378: The logical block size (4096) of file +JDPOS_REDO is not compatible with the disk sector size (media sector size is 512 and host sector size is 512) # The Resolve is: SQL> alter system set "_disk_sector_size_override"=TRUE scope=both; }}} == TNS-01189: The listener could not authenticate the user == == ORA-12514 TNS:listener does not currently know of service requested in connect descriptor == http://stackoverflow.com/questions/10786782/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect-d == auxiliary duplicate error == {{{ RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ORA-00600: internal error code, arguments: [kfdskAlloc0], [], [], [], [], [], [], [], [], [], [], [] }}} {{{ /u01/app/11.2.0/grid/bin [grid@gjposdg bin]$ ./setasmgidwrap -o oracle }}} == ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started. == * Note that the FAL_CLIENT parameter is deprecated in 11g and not needed. == ORA-01665: control file is not a standby control file == {{{ SQL> STARTUP MOUNT ORACLE instance started. … Database mounted. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; ERROR at line 1: ORA-01665: control file is not a standby control file SQL> SELECT database_role FROM v$database; DATABASE_ROLE —————- PRIMARY SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. SQL> STARTUP MOUNT ORACLE instance started. … Database mounted. SQL> SELECT database_role FROM v$database; DATABASE_ROLE —————- PHYSICAL STANDBY SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Database altered. }}} == ORA 16139 == {{{ 1:alter database recover managed standby database finish; 2:alter database commit to switchover to physical primary; 3:shutdown 4:startup 5:stop listener to point to crashed db primary and point it to standby and restart. 6:try to connect as user@to_primary }}} https://community.oracle.com/thread/315787?start=0&tstart=0 == ORA-00328 == {{{ ORA-00283: recovery session canceled due to errors ORA-00328: archived log ends at change 1019342, need later change 1050270 ORA-00334: archived log: '+JDPOS_ARCH/gjposdg/archivelog/2016_07_21/thread_1_seq_18.263.917821673' }}} Metalink ID: 864364.1 {{{ 1-Run the following query to determine the needed logfile SQL> select name, thread#, sequence#, archived, applied, status from v$archived_log \ where 1050270 between FIRST_CHANGE# and NEXT_CHANGE#; 2-Recopy the logfile from the primary to the standby server 3-Register the logfile SQL> alter database register or replace logfile ''; 4-start media recovery SQL> alter database recover automatic managed standby database disconnect from session; }}} == alert failed to establish dependency between database and diskgroup == ALTER DISKGROUP "GOWSM_ARCH" CHECK ALL; select instance_name,db_name,status from v$asm_client; tail -f alert+ASM.log == ERROR: failed to establish dependency between database gjposdg and diskgroup resource ora.JDPOS_ARCH.dg == This error is harmless. It is documented in the internal Bug 8304720 "ERROR: FAILED TO ESTABLISH DEPENDENCY BETWEEN DB & DISKGROUP" "The db-dg dependency will be created later on if the diskgroup is used by the db again; the dependency can also be created manually through cmdline tool. Automatically it will resolve itself later on when the db tries to use the diskgroup. Or we can manually establish the dependancy through any command line utility." Doc ID 1474954.1 == ORA-01153: an incompatible media recovery is active == {{{ This indicates that currently running media recovery process i.e. standby active SQL> alter database recover managed standby database cancel; --- Cancel first Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered. }}} == NOTE: ASM client gjserver:gjserver disconnected unexpectedly. == just note = drop database = {{{ 1- Export database SID if not yet Already defined export ORACLE_SID=database 2- Connect as sysdba [oracle@Oracle11 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 1 17:38:02 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. 3- Start The database SQL> startup ORACLE instance started. Total System Global Area 3340451840 bytes Fixed Size 2217952 bytes Variable Size 1828718624 bytes Database Buffers 1493172224 bytes Redo Buffers 16343040 bytes Database mounted. Database opened. 4- Shutdown the database SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 5- Start in Exclusive mode SQL> startup mount exclusive restrict ORACLE instance started. Total System Global Area 3340451840 bytes Fixed Size 2217952 bytes Variable Size 1828718624 bytes Database Buffers 1493172224 bytes Redo Buffers 16343040 bytes Database mounted. 6- Drop the database SQL> drop database; Database dropped. }}} == Database mount ID mismatch == {{{ tabopen RFS[5]: Assigned to RFS process 22536 RFS[5]: Database mount ID mismatch [0x9c16d303:0x9c16a0a9] (2618741507:2618728617) RFS[5]: Not using real application clusters Fri Jul 22 22:32:38 2016 PING[ARC2]: Heartbeat failed to connect to standby 'gjposdg'. Err }}} {{{ col DESTINATION format a35 col ERROR format a65 set lines 130 set pages 100 SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST; }}} == ORA-01665: control file is not a standby control file == https://odenysenko.wordpress.com/2012/07/02/quick-solution-for-ora-01665/ = TEST = == gjposdg == {{{ SQL> STARTUP; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 4.9840E+10 bytes Fixed Size 2264776 bytes Variable Size 2.5770E+10 bytes Database Buffers 2.4025E+10 bytes Redo Buffers 42663936 bytes Database mounted. Database opened. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- JDPOS READ ONLY PHYSICAL STANDBY SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION; Database altered. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- JDPOS READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- JDPOS READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select message from v$dataguard_status; MESSAGE -------------------------------------------------------------------------------- ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC3: Archival started ARC4: Archival started ARC5: Archival started ARC6: Archival started ARC7: Archival started ARC8: Archival started ARC9: Archival started ARCa: Archival started MESSAGE -------------------------------------------------------------------------------- ARCb: Archival started ARCc: Archival started ARCd: Archival started ARCe: Archival started ARCf: Archival started ARCg: Archival started ARCh: Archival started ARCi: Archival started ARCj: Archival started ARCk: Archival started ARCl: Archival started MESSAGE -------------------------------------------------------------------------------- ARCm: Archival started ARCn: Archival started ARCo: Archival started ARCp: Archival started ARCq: Archival started ARCr: Archival started ARCs: Archival started ARC1: Becoming the 'no FAL' ARCH ARC2: Becoming the heartbeat ARCH ARC2: Becoming the active heartbeat ARCH ARCt: Archival started MESSAGE -------------------------------------------------------------------------------- Attempt to start background Managed Standby Recovery process MRP0: Background Managed Standby Recovery process started Managed Standby Recovery not using Real Time Apply Media Recovery Waiting for thread 1 sequence 22 RFS[1]: Assigned to RFS process 30784 RFS[2]: Assigned to RFS process 30787 RFS[3]: Assigned to RFS process 30789 ARC1: Beginning to archive thread 1 sequence 24 (1384796-1385180) Primary database is in MAXIMUM PERFORMANCE mode Re-archiving standby log 10 thread 1 sequence 24 RFS[4]: Assigned to RFS process 30791 MESSAGE -------------------------------------------------------------------------------- ARC1: Completed archiving thread 1 sequence 24 (0-0) ARC4: Beginning to archive thread 1 sequence 22 (1015024-1384520) ARC4: Completed archiving thread 1 sequence 22 (0-0) Media Recovery Log +JDPOS_ARCH/gjposdg/archivelog/2016_07_26/thread_1_seq_22.29 6.918240807 Media Recovery Log +JDPOS_ARCH/gjposdg/archivelog/2016_07_26/thread_1_seq_23.29 4.918240803 Media Recovery Log +JDPOS_ARCH/gjposdg/archivelog/2016_07_26/thread_1_seq_24.29 5.918240807 MESSAGE -------------------------------------------------------------------------------- Media Recovery Waiting for thread 1 sequence 25 (in transit) 51 rows selected. }}} == gjserver == {{{ SQL> select message from v$dataguard_status; MESSAGE -------------------------------------------------------------------------------- ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC3: Archival started ARC4: Archival started ARC5: Archival started ARC6: Archival started ARC7: Archival started ARC8: Archival started ARC9: Archival started ARCa: Archival started MESSAGE -------------------------------------------------------------------------------- ARCb: Archival started ARCc: Archival started ARCd: Archival started ARCe: Archival started ARCf: Archival started ARCg: Archival started ARCh: Archival started ARCi: Archival started ARCj: Archival started ARCk: Archival started ARCl: Archival started MESSAGE -------------------------------------------------------------------------------- ARCm: Archival started ARCn: Archival started ARCo: Archival started ARCp: Archival started ARCq: Archival started ARCr: Archival started ARCs: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Error 1034 received logging on to the standby MESSAGE -------------------------------------------------------------------------------- PING[ARC2]: Heartbeat failed to connect to standby 'gjposdg'. Error is 1034. ARCl: Beginning to archive thread 1 sequence 22 (1015024-1384520) ARCl: Completed archiving thread 1 sequence 22 (1015024-1384520) ARCt: Archival started Error 1034 received logging on to the standby FAL[server, ARC3]: Error 1034 creating remote archivelog file 'gjposdg' ARC4: Beginning to archive thread 1 sequence 23 (1384520-1384796) ARC4: Completed archiving thread 1 sequence 23 (1384520-1384796) ARC6: Standby redo logfile selected for thread 1 sequence 22 for destination LOG _ARCHIVE_DEST_2 MESSAGE -------------------------------------------------------------------------------- ARC9: Beginning to archive thread 1 sequence 24 (1384796-1385180) ARC9: Completed archiving thread 1 sequence 24 (1384796-1385180) ARCa: Standby redo logfile selected for thread 1 sequence 24 for destination LOG _ARCHIVE_DEST_2 LNS: Standby redo logfile selected for thread 1 sequence 25 for destination LOG_ ARCHIVE_DEST_2 LNS: Beginning to archive log 7 thread 1 sequence 25 47 rows selected. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- JDPOS READ WRITE PRIMARY }}} = Rebuild standby REDO = {{{ SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG UNION SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG ORDER BY 1; 1. Stop the log transport from the primary. SQL> alter system set log_archive_dest_state_2 = defer scope = memory; System altered. 2. Stop the recover from the standby databas. SQL> alter database recover managed standby database cancel; Database altered. 3. Verify that you are not getting the data in standby by doing the logswitch in primry. SQL> select thread#,max(sequence#) from v$archived_log group by thread#; 4. Drop the standby redologs in the standby and primary (for the switchover purpose). SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4; Database altered. SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 5; Database altered. SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6; Database altered. SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7; Database altered. same in primary. 5. Recreate the stnadby redologs. SQL> alter database add standby logfile THREAD 1 group 4 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo01.log') size 100m; Database altered. SQL> alter database add standby logfile THREAD 1 group 5 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo02.log') size 100m; Database altered. SQL> alter database add standby logfile THREAD 1 group 6 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo03.log') size 100m; Database altered. SQL> alter database add standby logfile THREAD 1 group 7 2 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo04.log') size 100m; 6. Enable the log transport from the primary database. SQL> alter system set log_archive_dest_state_2 = enable scope = both; System altered. 7. Start the managed recovery from the standby database. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. 8. Check the status of the standby redologs SQL> select GROUP#,BYTES/1024/1024/1024 , STATUS, FIRST_TIME,NEXT_TIME from v$standby_log; GROUP# BYTES/1024/1024/1024 STATUS FIRST_TIME NEXT_TIME ---------- -------------------- ---------- -------------------- -------------------- 4 .09765625 ACTIVE 03-OCT-2012 02:45:11 03-OCT-2012 02:45:49 5 .09765625 UNASSIGNED 6 .09765625 UNASSIGNED 7 .09765625 UNASSIGNED SQL> / GROUP# BYTES/1024/1024/1024 STATUS FIRST_TIME NEXT_TIME ---------- -------------------- ---------- -------------------- -------------------- 4 .09765625 ACTIVE 03-OCT-2012 02:45:11 03-OCT-2012 02:49:09 5 .09765625 UNASSIGNED 6 .09765625 UNASSIGNED 7 .09765625 UNASSIGNED Its active , so RFS attached the standby redologs now. Let's verify the verbose once again to see if the gap has been resolved- DGMGRL> show database verbose dg1_b Database - dg1_b Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds Apply Lag: 0 seconds Real Time Query: OFF Instance(s): dg1 Properties: DGConnectIdentifier = 'dg1_b' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '10' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '30' LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'dg1_b, dg1_a' LogFileNameConvert = 'dg1_b, dg1_a' FastStartFailoverTarget = 'dg1_a' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' SidName = 'dg1' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.amazon.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dg1_b_DGMGRL.amazon.com)(INSTANCE_NAME=dg1)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.arc' TopWaitEvents = '(monitor)' Database Status: SUCCESS }}}