adgceshi

测试1：
测试命令
ALTER SYSTEM FLUSH REDO TO 'orcldg';
测试过程：
1 首先关闭备库的监听和数据库，让主备关系断开
2 主库新建部分表插入数据模仿业务
3 主库关闭，然后启动到mount状态
4 主库flush redo 到备库
5 成功后备库测试数据是否正常
6 测试发现数据和表能够传输到备库

测试结果：测试成功，日志输出如下

alter system flush redo to 'target_db_name';


主库日志
Fri Apr 24 07:00:15 2020
ALTER SYSTEM FLUSH REDO TO 'orcldg' CONFIRM APPLY
ALTER SYSTEM FLUSH REDO TO orcldg CONFIRM APPLY [Process Id: 1882] (orcl)
Flush redo: No wait for non-current ORLs to be archived
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for dest_id 2 to become synchronized...
Active, synchronized flush redo target has been identified
Managed Real Time Apply recovery running at physical standby 'LOG_ARCHIVE_DEST_2'
Flush End-Of-Redo Log thread 1 sequence 30 has been fixed
Flush Redo: Primary highest seen SCN set to 0x0.0x10baf4
ARCH: Noswitch archival of thread 1, sequence 30
ARCH: End-Of-Redo Branch archival of thread 1 sequence 30
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 30 for destination LOG_ARCHIVE_DEST_2
Flush End-Of-Redo Log thread 1 sequence 30
Archived Log entry 41 added for thread 1 sequence 30 ID 0x5d5a5ad6 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will wait for orcldg standby to have applied all redo
Final check for a target standby that has recovered all redo. Check will be made a few times.
LOG_ARCHIVE_DEST_2 is a potential flush redo target
LOG_ARCHIVE_DEST_2 is a potential flush redo target
LOG_ARCHIVE_DEST_2 has also applied all redo from primary
Active, synchronized target has been identified that has applied all the redo from the primary.
Flush Redo: Primary redo moved to standby


备库日志
Fri Apr 24 07:00:16 2020
RFS[3]: Assigned to RFS process 1975
RFS[3]: Selected log 21 for thread 1 sequence 30 dbid 1566263766 branch 1038483033
Fri Apr 24 07:00:16 2020
Archived Log entry 20 added for thread 1 sequence 30 ID 0x5d5a5ad6 dest 1:
Fri Apr 24 07:00:16 2020
Standby switchover readiness check: Checking whether recoveryapplied all redo..
Database not available for switchover
End-Of-REDO archived log file has not been recovered
Incomplete recovery SCN:0:1076072 archive SCN:0:1096436
Physical Standby did not apply all the redo from the primary.
Fri Apr 24 07:00:16 2020
Media Recovery Log /u01/app/oracle/arch/1_30_1038483033.dbf
Identified End-Of-Redo (move redo) for thread 1 sequence 30 at SCN 0x0.10baf4
Resetting standby activation ID 1566202582 (0x5d5a5ad6)
Media Recovery Waiting for thread 1 sequence 31
Fri Apr 24 07:00:17 2020
Standby switchover readiness check: Checking whether recoveryapplied all redo..
Physical Standby applied all the redo from the primary.




测试2：
主库redo 拷贝到备库，recover测试能够成功吗

测试命令

测试过程：
1 首先关闭备库的监听和数据库，让主备关系断开
2 主库新建部分表插入数据模仿业务
3 主库关闭
4 拷贝主库 redo 到备库，重新识别redo
备库停掉日志应用进程：alter database recover managed standby database cancel;
备库进行recover ：recover standby database until cancel;
拷贝过来的日志依次进行测试
日志1
SQL> SQL> recover standby database until cancel;
ORA-00279: change 1096816 generated at 04/24/2020 07:09:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_32_1038483033.dbf
ORA-00280: change 1096816 for thread 1 is in sequence #32


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/backup/o1_mf_1_hb22yshv_.log
ORA-00310: archived log contains sequence 31; sequence 32 required
ORA-00334: archived log: '/u01/app/oracle/backup/o1_mf_1_hb22yshv_.log'

日志2
SQL> recover standby database until cancel;
ORA-00279: change 1096816 generated at 04/24/2020 07:09:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_32_1038483033.dbf
ORA-00280: change 1096816 for thread 1 is in sequence #32


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/backup/o1_mf_2_hb22ytqt_.log
Log applied.
Media recovery complete.

日志3
SQL> recover standby database until cancel;
ORA-00279: change 1097102 generated at 04/24/2020 07:11:23 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_32_1038483033.dbf
ORA-00280: change 1097102 for thread 1 is in sequence #32


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/backup/o1_mf_3_hb22yw11_.log
ORA-00310: archived log contains sequence 30; sequence 32 required
ORA-00334: archived log: '/u01/app/oracle/backup/o1_mf_3_hb22yw11_.log'
可以看到应用日志2的时候数据库恢复成功


5 成功后备库测试数据是否正常
6 测试发现数据和表能够传输到备库

日志如下：
ALTER DATABASE RECOVER standby database until cancel
Media Recovery Start
started logmerger process
Fri Apr 24 08:06:23 2020
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER standby database until cancel ...
Fri Apr 24 08:06:34 2020
ALTER DATABASE RECOVER LOGFILE '/u01/app/oracle/backup/o1_mf_2_hb22ytqt_.log'
Media Recovery Log /u01/app/oracle/backup/o1_mf_2_hb22ytqt_.log
Fri Apr 24 08:06:34 2020
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1097102 time 04/24/2020 07:11:23
Media Recovery Complete (orcldg)
Completed: ALTER DATABASE RECOVER LOGFILE '/u01/app/oracle/backup/o1_mf_2_hb22ytqt_.log'
ALTER DATABASE RECOVER standby database until cancel
Media Recovery Start
started logmerger process
Fri Apr 24 08:06:40 2020
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER standby database until cancel ...
Fri Apr 24 08:06:46 2020
ALTER DATABASE RECOVER LOGFILE '/u01/app/oracle/backup/o1_mf_3_hb22yw11_.log'
Media Recovery Log /u01/app/oracle/backup/o1_mf_3_hb22yw11_.log
Errors with log /u01/app/oracle/backup/o1_mf_3_hb22yw11_.log
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/orcldg_pr00_2448.trc:
ORA-00310: archived log contains sequence 30; sequence 32 required
ORA-00334: archived log: '/u01/app/oracle/backup/o1_mf_3_hb22yw11_.log'
ORA-310 signalled during: ALTER DATABASE RECOVER LOGFILE '/u01/app/oracle/backup/o1_mf_3_hb22yw11_.log' ...
ALTER DATABASE RECOVER CANCEL



测试3：

数据库查询转换的scn号
SQL> select to_char(STANDBY_BECAME_PRIMARY_SCN) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
1097100
日志情况：
Standby became primary SCN: 1097100 ---------》》》这里记录了备库转换为主库时候的scn


alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select scn_to_timestamp(12128073) from dual;

Click to add to favorites
Learn More document

21-Oct-2019
Relevancy: 99%

11.2 Data Guard Physical Standby Switchover Best Practices

备库failover测试，查询转换为主库时候的scn号，日志如下：

Fri Apr 24 08:14:04 2020
ALTER DATABASE ACTIVATE STANDBY DATABASE
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (orcldg)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 2462 user 'oracle' program 'oracle@beiku (TNS V1-V3)'
CLOSE: all sessions shutdown successfully.
Fri Apr 24 08:14:07 2020
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1097102
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch
Archived Log entry 22 added for thread 1 sequence 32 ID 0x5d5a5ad6 dest 1:
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1566202582 (0x5d5a5ad6)
Online log /u01/app/oracle/oradata/ORCLDG/onlinelog/o1_mf_1_hb2myj69_.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ORCLDG/onlinelog/o1_mf_2_hb2myktf_.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ORCLDG/onlinelog/o1_mf_3_hb2mymgj_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1097100 ---------》》》这里记录了备库转换为主库时候的scn
Fri Apr 24 08:14:07 2020
Setting recovery target incarnation to 3
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE STANDBY DATABASE
Fri Apr 24 08:14:32 2020
Shutting down instance (abort)
License high water mark = 4
USER (ospid: 2620): terminating the instance
Instance terminated by USER, pid = 2620
Fri Apr 24 08:14:33 2020
Instance shutdown complete
Fri Apr 24 08:14:33 2020
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 64 KB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total System Global Area size is 602 MB. For optimal performance,
prior to the next instance restart:
1. Increase the number of unused large pages by
at least 301 (page size 2048 KB, total size 602 MB) system wide to
get 100% of the System Global Area allocated with large pages
2. Large pages are automatically locked into physical memory.
Increase the per process memlock (soft) limit to at least 610 MB to lock
100% System Global Area's large pages into physical memory
********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
NUMA status: non-NUMA system
cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
Grp 0:
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: beiku
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcldg.ora
System parameters with non-default values:
processes = 150
sga_target = 600M
control_files = "/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_hb22ypy0_.ctl"
db_block_size = 8192
compatible = "11.2.0.4.0"
log_archive_dest_1 = "LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg"
log_archive_dest_2 = "SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl"
log_archive_dest_state_2 = "ENABLE"
fal_client = "ORCL"
fal_server = "ORCLDG"
log_archive_config = "DG_CONFIG=(orcl,orcldg)"
log_archive_format = "%t_%s_%r.dbf"
db_create_file_dest = "/u01/app/oracle/oradata"
standby_file_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
db_unique_name = "orcldg"
open_cursors = 300
pga_aggregate_target = 200M
diagnostic_dest = "/u01/app/oracle"
Fri Apr 24 08:14:33 2020
PMON started with pid=2, OS id=2632
Fri Apr 24 08:14:33 2020
PSP0 started with pid=3, OS id=2634
Fri Apr 24 08:14:34 2020
VKTM started with pid=4, OS id=2636 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Fri Apr 24 08:14:34 2020
GEN0 started with pid=5, OS id=2640
Fri Apr 24 08:14:34 2020
DIAG started with pid=6, OS id=2642
Fri Apr 24 08:14:34 2020
DBRM started with pid=7, OS id=2644
Fri Apr 24 08:14:34 2020
DIA0 started with pid=8, OS id=2646
Fri Apr 24 08:14:34 2020
MMAN started with pid=9, OS id=2648
Fri Apr 24 08:14:34 2020
DBW0 started with pid=10, OS id=2650
Fri Apr 24 08:14:34 2020
LGWR started with pid=11, OS id=2652
Fri Apr 24 08:14:34 2020
CKPT started with pid=12, OS id=2654
Fri Apr 24 08:14:34 2020
SMON started with pid=13, OS id=2656
Fri Apr 24 08:14:34 2020
RECO started with pid=14, OS id=2658
Fri Apr 24 08:14:34 2020
MMON started with pid=15, OS id=2660
Fri Apr 24 08:14:34 2020
MMNL started with pid=16, OS id=2662
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Fri Apr 24 08:14:34 2020
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1566379114
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Apr 24 08:14:47 2020
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
ALTER DATABASE OPEN
Fri Apr 24 08:14:57 2020
Assigning activation ID 1566379114 (0x5d5d0c6a)
LGWR: STARTING ARCH PROCESSES
Fri Apr 24 08:14:57 2020
ARC0 started with pid=20, OS id=2674
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Apr 24 08:14:58 2020
ARC1 started with pid=21, OS id=2676
Fri Apr 24 08:14:58 2020
ARC2 started with pid=22, OS id=2678
Thread 1 advanced to log sequence 2 (thread open)
Fri Apr 24 08:14:58 2020
ARC3 started with pid=23, OS id=2680
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ORCLDG/onlinelog/o1_mf_2_hb2myktf_.log
Successful open of redo thread 1
Fri Apr 24 08:14:58 2020
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Apr 24 08:14:58 2020
SMON: enabling cache recovery
ARC1: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Fri Apr 24 08:14:58 2020
NSA2 started with pid=24, OS id=2682


***********************************************************************

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhuku)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CID=(PROGRAM=oracle@beiku)(HOST=beiku)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 24-APR-2020 08:14:58
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhuku)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CID=(PROGRAM=oracle)(HOST=beiku)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 24-APR-2020 08:14:58
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhuku)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CID=(PROGRAM=oracle)(HOST=beiku)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 24-APR-2020 08:14:58
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zhuku)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CID=(PROGRAM=oracle)(HOST=beiku)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 24-APR-2020 08:14:58
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12514 received logging on to the standby
ARC1: Error 12514 Creating archive log file to 'orcl'
[2672] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4291614 end:4291784 diff:170 (1 seconds)
Dictionary check beginning
Archived Log entry 23 added for thread 1 sequence 1 ID 0x5d5d0c6a dest 1:
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Apr 24 08:14:59 2020
QMNC started with pid=25, OS id=2684
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Thread 1 cannot allocate new log, sequence 3
Private strand flush not complete
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ORCLDG/onlinelog/o1_mf_2_hb2myktf_.log
Completed: ALTER DATABASE OPEN
Fri Apr 24 08:15:02 2020
Starting background process CJQ0
Fri Apr 24 08:15:02 2020
CJQ0 started with pid=29, OS id=2696
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ORCLDG/onlinelog/o1_mf_3_hb2mymgj_.log
ARC2: STARTING ARCH PROCESSES
Fri Apr 24 08:15:04 2020
ARC4 started with pid=26, OS id=2698
ARC4: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
Archived Log entry 24 added for thread 1 sequence 2 ID 0x5d5d0c6a dest 1:
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped