搭建DG过程以及报错问题处理
ORACLE_SID=ECARDCKZ
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
10.138.2.28
10.138.2.29
export DISPLAY=10.148.53.74:0.0
[oracle@localhost admin]$ scp tnsnames.ora oracle@10.39.2.14:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[oracle@localhost admin]$ ssh-keygen -R 10.39.2.14 更新远端密钥，因为重装过
装好备库数据库
建实例:（windows）
oradim -new -sid 实例名
linux 直接export=实例名就OK
tnsping PRIFOXECARD
tnsping STYFOXECARD
sqlplus sys/sys2011admin@PRIFOXECARD as sysdba
sqlplus sys/sys2011admin@STYFOXECARD as sysdba
主庫：
[oracle@EHRMMDB ~]$ cat /opt/oracle/product/11.2.4.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/11.2.4.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = EHRMMDB)
(ORACLE_HOME = /opt/oracle/product/11.2.4.0/db_1)
(SID_NAME = EHRMMDB)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.32.16)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
)
)
ADR_BASE_LISTENER = /opt/oracle
[oracle@EHRMMDB ~]$ cat /opt/oracle/product/11.2.4.0/db_1/network/admin/tnsnames.ora
EHRMMDBPRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.32.16)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EHRMMDB)
)
)
EHRMMDBSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.2.144)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = EHRMMDB)
)
)
備庫：
[oracle@EHRMMDBBK ~]$ cat /opt/oracle/product/11.2.4.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/11.2.4.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/11.2.4.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = EHRMMDB)
(ORACLE_HOME = /opt/oracle/product/11.2.4.0/db_1)
(SID_NAME = EHRMMDB)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.2.144)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
)
)
ADR_BASE_LISTENER = /opt/oracle
select name from v$datafile;
select name from v$controlfile;
select * from v$logfile;
show parameter spfile;
/u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileECARDCKZ.ora
alter system set db_unique_name='ora9ibpm' scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both;
ALTER SYSTEM SET fal_server='ECARDCKZSTD' scope=both;
ALTER SYSTEM SET fal_client='ECARDCKZ' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ECARDCKZSTD,ECARDCKZ)' scope=both;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ECARDCKZ' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ECARDCKZSTD LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ECARDCKZSTD'scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both;
ALTER SYSTEM SET standby_archive_dest='USE_DB_RECOVERY_FILE_DEST' scope=both;
拆DG alter system set log_archive_config='NODG_CONFIG' scope=both;
/data/ECARDCKZ/fast_recovery_area/ECARDCKZ/archivelog USE_DB_RECOVERY_FILE_DEST
CREATE PFILE='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initECARDCKZ.ora' FROM SPFILE;
scp initECARDCKZ.ora oracle@10.138.2.29:/u01/app/oracle/product/11.2.0.4/db_1/dbs
select * from V$DATAGUARD_CONFIG ;
DB_UNIQUE_NAME
------------------------------
hyoms
hyoms01
hyoms02
vi initECARDCKZ.ora
*.db_unique_name='ECARDCKZSTD'
*.fal_server='ECARDCKZ'
*.fal_client='ECARDCKZSTD'
*.log_archive_config='DG_CONFIG=(ECARDCKZ,ECARDCKZSTD)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ECARDCKZSTD'
*.log_archive_dest_2='SERVICE=ECARDCKZ LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ECARDCKZ'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.standby_archive_dest='USE_DB_RECOVERY_FILE_DEST'
*.standby_file_management='AUTO'
主備庫路徑不一致時，應該在備庫init文件加上：（自己庫路徑在後面，數據文件和redo.log路徑）
*.log_file_name_convert='/u01/app/oracle/oradata/TWHR/','/home/u01/app/oracle/oradata/TWHR/'
*.db_file_name_convert='/u01/app/oracle/oradata/TWHR/','/home/u01/app/oracle/oradata/TWHR/'
alter database create standby controlfile as '/data/control01.ctl' reuse;路徑隨意，轉換完scp之後刪除
注意：控制文件必须要即时更新
scp control01.ctl oracle@10.138.2.29:/data/ECARDCKZ/ECARDCKZ
cp control01.ctl /data/ECARDCKZ/fast_recovery_area/ECARDCKZcontrol02.ctl
在primary端用命令创建数据库密码文件，并接拷贝至standby端相同路径下（如已存在，可直接拷贝）
primary端手工创建数据库密码文件
orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=Foxconn$9900 entries=10 force=y
primary端拷贝数据库密码文件至standby端
scp $ORACLE_HOME/dbs/orapwECARDCKZ oracle@10.138.2.29:$ORACLE_HOME/dbs 密碼文件
windows密碼文件
orapwd file=D:\oracle\...\PWD$ORACLE_SID.ora password=Foxconn$9900 entries=10 force=y
windows新建服務
oradim -new -sid NCENTRAL
windows刪除服務
sc delete NCENTRAL *****oradim -delete -sid NCENTRAL
oradim -new -sid orapri----windows上執行
export ORACLE_SID=orapri--linux執行
sqlplus / as sysdba或sqlplus sys/Foxconn99 as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initECARDCKZ.ora'; initECARDCKZ.ora **備庫***
CREATE SPFILE FROM PFILE='$ORACLE_HOME/dbs/initECARDCKZ.ora';
備份完成后，把相應備份文件拷貝到備庫的相應目錄
主庫到2019_09_26目錄下 scp * oracle@10.138.2.29:/data/2019_09_26
備庫
sqlplus下startup mount
rman target /
catalog start with '/opt/oracle/rmanbackup/20200327'; #註冊備份（一般用這個）隨意找個目錄
restore database; 還原物理文件 restore——还原，与backup相对，从备份读出恢复备份的数据
recover database; 註冊到數據字典 recover——恢复，把restore回来的数据经过一番处理变成正常数据。
******
9i無法使用catalog start with命令，只需要建好與主庫所有相同的目錄，拷貝備份后，直接restore
alter database recover managed standby database cancel; 關服務應用再新增redo
新增redo 為了實時傳log，主庫寫入同時備庫也在寫。
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/data/ECARDCKZ/ECARDCKZ/redo15.log') size 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/data/ECARDCKZ/ECARDCKZ/redo16.log') size 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/data/ECARDCKZ/ECARDCKZ/redo17.log') size 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ('/data/ECARDCKZ/ECARDCKZ/redo18.log') size 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 19 ('/data/ECARDCKZ/ECARDCKZ/redo19.log') size 500M;
SQL> alter database drop logfile group 11;
SQL> alter database drop logfile group 12;
（備庫應處於mount狀態）
开启服务(standby端):
SQL> alter database recover managed standby database disconnect from session;
關閉服务(standby端):
SQL> alter database recover managed standby database cancel;
开启 standby 节点数据库同步服务，實時應用redo
alter database recover managed standby database using current logfile disconnect from session;
测试服务是否正常
standby端（看归档日志号）：
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
primary端（强制一个日志切换）：
SQL> alter system switch logfile;
standby端（看归档日志号）：
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
查看備庫日誌有沒有應用：
select sequence#,applied from v$archived_log order by 1;
select max(sequence#) from v$archived_log where applied='YES';
日常
1.停止Standby
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;
2.切换到只读模式
-----由shutdown模式切换到只读模式-------
startup nomount;
alter database mount standby database;
alter database open read only;
-----由应用日志模式切换到只读模式-------
alter database recover managed standby database cancel; -- 取消日志应用
alter database open read only;
3.切换回管理恢复模式
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session; -- 启动日志应用
alter database recover managed standby database using current logfile disconnect from session;
4.主库和备库之间角色切换
4.1 主库切换为备库
primary端：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
primary端（上一步的结果必须是”TO STANDBY”才可以）
alter database commit to switchover to physical standby;
OR（上一步结果为”SESSIONS ACTIVE”,且解决不掉）
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
4.2 从库切换为主库
standby端：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
standby端（上一步的结果必须是”TO STANDBY”才可以）：
alter database commit to switchover to primary;
OR（上一步结果为”SESSIONS ACTIVE”，且解决不掉）
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
shutdown immediate;
startup;
alter system switch logfile;
切换之前先停掉监控
/m01/orabase/core/12.1.0.4.0/bin/emctl status agent
/m01/orabase/core/12.1.0.4.0/bin/emctl stop agent
/m01/orabase/core/12.1.0.4.0/bin/emctl start agent
5.备库自动使用主库传过来的日志进行恢复
alter database recover automatic standby database;
6.更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
7.取消自动恢复模式
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;
在新standby上重新启动log apply services（如果必要）：
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
OR(后台模式)
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
OR（实时应用redo）
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
新primary端（开始发送redo 数据到新standby端）：
SQL> ALTER SYSTEM SWITCH LOGFILE;
********DG应用
查看归档路径
archive log list
show parameter DB_RECOVERY_FILE_DEST
alter system set log_archive_dest_1='location=/data/oracle/log1/archive_log'; *****需要重啟
刪除日誌
crosscheck archivelog all;
delete noprompt force archivelog until time 'sysdate - 7' ;
查看數據庫角色：
select database_role from v$database;
查看备库日志应用：
select sequence#,applied from v$archived_log order by 1;
select max(sequence#) from v$archived_log where applied='YES';
查看备库RAC节点归档应用：
select sequence#,applied from v$archived_log where thread#=1;
select sequence#,applied from v$archived_log where thread#=2;
查看备库当前应用的日志：
select process,status,sequence# from v$managed_standby;
查看数据库的主备库情況和状态：
select database_role,open_mode from v$database;(主备库都可用)
9i版本备库开启：
startup nomount;
alter database mount standby database;
开启服務(standby端):
SQL> alter database recover managed standby database disconnect from session;
开启 standby 节点数据库同步服务，實時應用redo
alter database recover managed standby database using current logfile disconnect from session;
关闭服務(standby端):
SQL> alter database recover managed standby database cancel;
查看数据库dg保护模式狀態(主库用):
select protection_mode,database_role,protection_level,open_mode from v$database;
来快速查看DG的报错信息 主库：
select dest_id,status,destination,error from v$archive_dest where dest_id<=5;
select error from v$archive_dest where target='STANDBY';
select * from v$archive_gap;
註冊日誌
alter database register logfile '/orap1/arch/1_6275_965679726.dbf';
大量
rman> catalog start with '/u01/archlog/';
註冊監聽
SQL> alter system register;
-------redo
alter system switch logfile;
查看standby_log在写哪个日志
select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
10 6256421 1 9325 524288000 626176 YES ACTIVE 1.5675E+13 08-JUN-20 1.5675E+13 08-JUN-20
11 UNASSIGNED 0 0 524288000 512 YES UNASSIGNED 0 0
查看主库log大小 select * from v$log;
查看主备库的log路径 select * from v$logfile;
查看主备库相隔时间
select * from v$DATAGUARD_STATS;
检查 standby redo logifle状态
select GROUP#,BYTES/1024/1024/1024 , STATUS, FIRST_TIME, NEXT_TIME from v$standby_log;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/ecardbk/redo04.log') size 500M;
检查DG同步状态
select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
清除logfile 关闭服務SRL log 21 needs clearing because log has not been created
alter database clear logfile group 21;
active的standby redolog里的内容应该就是主库current的redolog的内容。
应该是只有当备库的应用日志的速度，慢于主库日志切换的速度的情况下，才会出现使用多组standby redo log的情况
检查MRP进程
SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 491138 931840 39
ARCH CLOSING 1 491154 4096 1114
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 491153 817152 100
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 491155 454 1
MR(fg) APPLYING_LOG 1 491155 454 1024000
DG錯誤：
-----Oracle Dataguard报错：ARC1: Becoming the 'no FAL' ARC
1.在备库中查询查询状态如下，其中有后台日志报错信息
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
ARC3: Archival started
7 rows selected.
2.发现备库中有报归档日志错误信息，可以先到主库是查询是否报错
查询结果如下：
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
Error 12154 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'strac'. Error is 1605.
發現是1605錯誤，解決方法見下條
-----Error is 1605 Heartbeat failed to connect to standby 'xyxdbs'.
由于修改参数的时候未保存到spfile重启的的时候直接从spfile导致此错误，配置一定要仔细
1.查询主库
SQL> select dest_id,error,status,log_sequence from v$archive_dest where dest_id=2;
DEST_ID ERROR
---------- -----------------------------------------------------------------
STATUS LOG_SEQUENCE APPLIED_SCN
--------- ------------ -----------
2 ORA-16057: server not in Data Guard configuration
ERROR 211 5446747
2.查询备库参数
SQL> sho parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(xyxdbp,xyxdbs)
这里指定的是主库备库的db_unique_name
备库操作
SQL> alter system set log_archive_config='DG_CONFIG=(xyxdb,xyxdbs)';
3.修改后主库操作
SQL> alter system set log_archive_dest_state_2=defer;
SQL> alter system set log_archive_dest_state_2=enable;
SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;
DEST_ID ERROR
---------- -----------------------------------------------------------------
STATUS LOG_SEQUENCE APPLIED_SCN
--------- ------------ -----------
2
VALID 215 5447429
--------1 备库重启后，在主库上归档出现ORA-03113错误
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
------------------------------ --------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 ERROR ORA-03113: end-of-file on communication channel
解决办法：在主库执行
SQL> alter system set log_archive_dest_state_2= enable;
这个命令式手动触发主库区尝试连接备库。
其实这种情况下，只要保证主备库之间的网络和配置是正确的。dataguard会自动恢复这个错误。这个周期默认是300秒，也可以在log_archive_dest_2的参数中添加reopen 参数
指定这个主备库之间失败后继续尝试的周期。
-------2 ORA-01031: insufficient privileges错误 or
主庫ORA-01033: ORACLE initialization or shutdown in progress
備庫Error 1017 received logging on to the standby
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
---------------------- -----------------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 ERROR ORA-01031: insufficient Privileges
SQL> select * from gv$pwfile_users;
INST_ID USERNAME SYSDB SYSOP SYSAS
---------- ------------------------------ ----- ----- -----
1 SYS TRUE TRUE FALSE
解决办法：统一主备库的数据库密码文件，或者重建密码文件，sys密码设置成一样。 然后在主库执行
SQL> alter system set log_archive_dest_state_2= enable;
-------3 ORA-16191: Primary log shipping client not logged on standby
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
------------------------------ -----------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 ERROR ORA-16191: Primary log shipping client not logged on standby
解决办法：统一主备库的数据库密码文件，或者重建密码文件，sys密码设置成一样。 然后在主库执行
SQL> alter system set log_archive_dest_state_2= enable;
-------4 发现备库一直无法应用日志，MRP0进程显示
WAIT_FOR_GAP的问题
发现从主库传来的日志无法应用
在备库检查,
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
———- —
930 NO
931 NO
932 NO
933 NO
然后开始查看有没有mrp
[oracle@HJITBACKUP bdump]$ ps -ef | grep mrp
oracle 31896 1 0 14:37 ? 00:00:00 ora_mrp0_flow
oracle 32001 31820 0 15:17 pts/1 00:00:00 grep mrp
看来有，接着查gap，发现备库上有此进程，
SQL> select * from v$archive_gap;
no rows selected
查询视图没有发现，
在接着检查V$MANAGED_STANDBY
SQL> select process,status from v$managed_standby;
PROCESS STATUS
——— ————
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_GAP
RFS IDLE
RFS IDLE
发现MRP0在等待GAP,进一步查看此视图
select process,status,group#,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS ——— ———— ———- ———- ———- ———- ———-
ARCH CONNECTED N/A 0 0 0 0 ARCH CONNECTED N/A 0 0 0 0 MRP0 WAIT_FOR_GAP N/A 1 928 0 0 RFS IDLE N/A 0 0 0 0 RFS IDLE N/A 0 0 0 0 发现日志928没有应用，
原来是由于主库删除了928，导致备库没法应用，所以只能从备份中恢复，restore archivelog
至此问题处理完毕。
***主庫有日誌，手動傳到備庫后，註冊日誌
scp 1_6097_965679726.dbf oracle@10.206.124.77:/orap1/arch
註冊日誌
alter database register logfile '/orap1/arch/1_6097_965679726.dbf';
日誌比較多的時候
rman target /
catalog start with '/orap1/arch/';
查询备库状态
SQL> select process,status from v$managed_standby;
PROCESS STATUS
——— ————
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
所以当standby装完后，在主库切换日志后，这里状态应该是
MRP0 WAIT_FOR_LOG 才是正常的状态
***主庫沒有日誌，從備份片段中恢復
RMAN> list backup of archivelog all;
找到缺失的archIvelog sequence所在的備份
RMAN> catalog start with '/orap1/app/flash_recovery_area/FINHH/backupset/2020_11_06';
RMAN> restore archivelog sequence between 1664 and 1665;
恢复全部归档日志文件
RMAN> restore archivelog all
恢复7天内的归档日志
RMAN> restore archivelog from time 'sysdate-7' ;
sequence between 写法
RMAN> restore archivelog sequence between 1 and 3;
恢复从第5个归档日志起
RMAN> restore archivelog from logseq 5;
或者
RMAN> restore archivelog low logseq 5;
只恢复 5到8这四个归档日志文件
RMAN> restore archivelog from logseq 5 until logseq 8;
还原到seqence 为5的日志文件为止
RMAN> restore archivelog until logseq 5;
或者
RMAN> restore archivelog high logseq 5;
如果想改变恢复到另外路径下 则可用下面语句
RMAN> run{
set archivelog destination to 'd:\backup'
allocate channel ci type disk;
set archivelog destination to 'd:\backup' ;
restore archivelog all ;
release channel ci;
}
---------5.ora-16009
从alert日志文件中,发现:"PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16009",于是考虑是不是log_archive_dest_2的设置有问题,目前主库服务器的数据库角色已经转换为standby database,不需要设置归档日志的远程路径,所以考虑将这个参数置空。
SQL>ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
---------6.ORA-16047: DGID mismatch between destination setting and target database
首先檢查監聽是否ping通
##主库：
select dest_id,status,destination,error from v$archive_dest where dest_id<=5;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=TWECARD LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TWECARD' scope=both;
再用命令检查，其中dest_id=2对应的status变成DEFERRED - Manually disabled by the user,意思是用户手工修改成不可用
select dest_id,status,destination,error from v$archive_dest where dest_id<=5;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
##查看lns進程
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
SQL> alter system set log_archive_dest_state_2=defer ;
SQL> alter system set log_archive_dest_state_2=enable ;
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
LNS WRITING
可以看到lns进程已经起来了。查看主库日志传送情况和备库应用情况。
---------7.監聽端口為1526，tnsping IP 不成功，tnsping到了oracle默認端口1521
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.134.32.16)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
[oracle@EHRMMDB ~]$ tnsping 10.134.21.16
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JUL-2021 14:43:36
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.4.0/db_1/network/admin/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.134.21.16)(PORT=1521)))
修改默认端口1521
修改listener.ora 文件里面的port设置 和tnsnames.ora
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
修改初始化参数local_listener
SQL> alter system set local_listener="(address = (protocol = tcp)(host = 10.134.32.16)(port = 1526))";
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69978530/viewspace-2849448/ ，如需转载，请注明出处，否则将追究法律责任。
