Database SOS – 第7页 – Professional Database Recovery Support@+8617813235971

ORA-600 2131故障处理

数据库启动报ORA-600 2131错误,查看alert日志发现是在mount过程报错

Fri May 17 20:58:28 2024
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 16
Number of processor cores in the system is 8
Number of processor sockets in the system is 1
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =249
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.
Windows NT Version V6.2  
CPU                 : 16 - type 8664, 8 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:93799M/97925M, Ph+PgF:78891M/112261M 
Using parameter settings in server-side spfile E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEXFF.ORA
System parameters with non-default values:
  processes                = 1500
  sessions                 = 2272
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  sga_target               = 29440M
  control_files            = "E:\ORADATA\xff\CONTROL01.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.4.0"
  log_archive_dest_1       = "LOCATION=e:\app\archivelog\"
  log_archive_format       = "ARC%S_%R.%T"
  undo_tablespace          = "UNDOTBS2"
  sec_case_sensitive_logon = FALSE
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=xffXDB)"
  audit_file_dest          = "E:\APP\ADMINISTRATOR\ADMIN\xff\ADUMP"
  audit_trail              = "NONE"
  db_name                  = "xff"
  open_cursors             = 300
  pga_aggregate_target     = 9792M
  diagnostic_dest          = "E:\APP\ADMINISTRATOR"
Fri May 17 20:58:29 2024
PMON started with pid=2, OS id=6696 
Fri May 17 20:58:29 2024
PSP0 started with pid=3, OS id=2424 
Fri May 17 20:58:30 2024
VKTM started with pid=4, OS id=5472 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri May 17 20:58:30 2024
GEN0 started with pid=5, OS id=5764 
Fri May 17 20:58:30 2024
DIAG started with pid=6, OS id=372 
Fri May 17 20:58:30 2024
DBRM started with pid=7, OS id=2992 
Fri May 17 20:58:30 2024
DIA0 started with pid=8, OS id=4960 
Fri May 17 20:58:30 2024
MMAN started with pid=9, OS id=6036 
Fri May 17 20:58:30 2024
DBW0 started with pid=10, OS id=4724 
Fri May 17 20:58:30 2024
DBW1 started with pid=11, OS id=2652 
Fri May 17 20:58:30 2024
LGWR started with pid=12, OS id=5320 
Fri May 17 20:58:30 2024
CKPT started with pid=13, OS id=5732 
Fri May 17 20:58:30 2024
SMON started with pid=14, OS id=936 
Fri May 17 20:58:30 2024
RECO started with pid=15, OS id=2192 
Fri May 17 20:58:30 2024
MMON started with pid=16, OS id=5576 
Fri May 17 20:58:30 2024
MMNL started with pid=17, OS id=5748 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = E:\app\Administrator
Fri May 17 20:58:31 2024
ALTER DATABASE   MOUNT
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5452.trc  (incident=403399):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_403399\xff_ora_5452_i403399.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: ALTER DATABASE   MOUNT...

这个错误是由于controlfile损坏导致,有这个库以前部署过rman备份，解决起来比较简单，使用rman还原控制文件,并尝试recover

RMAN> restore controlfile from 'E:\rmanback\rmanfile\CTL_20240517_A62R067K_1_1.RMAN';

启动 restore 于 17-5月 -24
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=E:\ORADATA\XFF\CONTROL01.CTL
完成 restore 于 17-5月 -24

RMAN>

RMAN>

RMAN> alter database mount;

数据库已装载
释放的通道: ORA_DISK_1

RMAN> recover database;

启动 recover 于 17-5月 -24
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=996 设备类型=DISK

正在开始介质的恢复

线程 1 序列 4100 的归档日志已作为文件 E:\ORADATA\XFF\REDO02.LOG 存在于磁盘上
线程 1 序列 4101 的归档日志已作为文件 E:\ORADATA\XFF\REDO03.LOG 存在于磁盘上
线程 1 序列 4102 的归档日志已作为文件 E:\ORADATA\XFF\REDO01.LOG 存在于磁盘上
归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004025_1165094245.0001 线程=1 序列=4025
归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004026_1165094245.0001 线程=1 序列=4026
…………
归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004099_1165094245.0001 线程=1 序列=4099
归档日志文件名=E:\ORADATA\XFF\REDO02.LOG 线程=1 序列=4100
归档日志文件名=E:\ORADATA\XFF\REDO03.LOG 线程=1 序列=4101
归档日志文件名=E:\ORADATA\XFF\REDO01.LOG 线程=1 序列=4102
介质恢复完成, 用时: 00:00:22
完成 recover 于 17-5月 -24

RMAN> exit


恢复管理器完成。

E:\oradata\XFF>

这种恢复情况下,如果现在要打开库,需要resetlogs方式,考虑通过创建ctl直接打开（不想用resetlogs）

SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 3.0732E+10 bytes
Fixed Size                  2296264 bytes
Variable Size            3825206840 bytes
Database Buffers         2.6844E+10 bytes
Redo Buffers               61206528 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 876
  7  LOGFILE
  8    GROUP 1 'E:\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'E:\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'E:\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'E:\ORADATA\XFF\SYSTEM01.DBF',
 14    'E:\ORADATA\XFF\SYSAUX01.DBF',
 15    'E:\ORADATA\XFF\USERS01.DBF',
 16    'E:\ORADATA\XFF\XFF_DATA01.DBF',
 17    'E:\ORADATA\XFF\XFF_INDEX01.DBF',
 18    'E:\ORADATA\XFF\UNDOTBS2.DBF'
 19  CHARACTER SET ZHS16GBK
 20  ;

控制文件已创建。

SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORADATA\XFF\TEMP01.DBF' REUSE;

表空间已更改。

至此本次恢复晚上,由于arch,redo和数据文件没有损坏,恢复非常完美，参考以前类似说明：ORA-600 2131故障说明

收集统计信息报ORA-00600 16515问题解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：收集统计信息报ORA-00600 16515问题解决

在一套win 11.2.0.1的数据库在收集统计信息报ORA-600 16515错误

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1


SQL> exec dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL');
BEGIN dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL'); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 25335
ORA-06512: ?? "SYS.DBMS_STATS", line 25511
ORA-06512: ?? "SYS.DBMS_STATS", line 25467
ORA-06512: ?? line 1

确认报错对象

SQL> select owner, object_name, object_type
  2  from sys.dba_objects
  3  where object_id =6375;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SYS
WRI$_SQLMON_USAGE
TABLE

对对象进行处理,确认对象本身没有问题,尝试收集统计信息依旧报错

SQL> alter table WRI$_SQLMON_USAGE move;

表已更改。

SQL> select index_name from dba_indexes where table_name='WRI$_SQLMON_USAGE';

未选定行

SQL> SELECT COUNT(1) FROM WRI$_SQLMON_USAGE;

  COUNT(1)
----------
         1

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

删除统计再尝试

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'WRI$_SQLMON_USAGE');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

使用ANALYZE收集统计信息正常

SQL> ANALYZE TABLE  WRI$_SQLMON_USAGE  COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

表已分析。

删除hist_head$中对应报错行记录,ORA-00600 16515问题解决

SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=6376 AND intcol#=1;

ROWID                    OBJ#    INTCOL# TIMESTAMP#
------------------ ---------- ---------- --------------
AAAAHAAABAAAIELABQ       6376          1 01-4?  -24

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

SQL>  DELETE FROM hist_head$ WHERE ROWID='AAAAHAAABAAAIELABQ';

已删除 1 行。

SQL> COMMIT;

提交完成。

SQL> begin
  2  dbms_stats.delete_table_stats ( ownname => user, tabname => 'WRI$_SQLMON_USAGE', cascade_parts => true, cascade_col
umns => true, cascade_indexes => true, force => true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();

PL/SQL 过程已成功完成。

参考文档:DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1)

rm -rf误删Oracle数据库恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：rm -rf误删Oracle数据库恢复

有客户把虚拟化环境中装有oracle数据库的linux操作系统,由于操作失误在/下面执行了rm -rf *，导致所有文件被删除,系统无法启动.客户希望要求恢复出其中的Oracle数据库.由于是虚拟化环境,然后客户直接从虚拟化平台下载下来磁盘文件,通过工具加载和分析确认是一个xfs的文件系统

使用工具进一步扫描分析,找到部分数据文件

这里可以获取到两个信息:
1. 尝试恢复oracle的control01.ctl文件,然后通过该文件尝试分析其他数据文件位置,运气不错该文件恢复出来是好的,直接加载到新库查询v$datafile,分析出来所有数据文件信息
2. 这里有一个非常不幸的信息,oracle最核心的system01.dbf文件大小明显异常,进一步分析该文件信息,结论是该文件无法通过反删除方式进行恢复

先把可以os层面可以恢复的数据恢复出来,并且检查坏块情况

对于异常的system文件,有两个处理方法:
1. 通过阅览被删除的文件，发现客户有5月14日1点左右的rman备份,通过恢复软件中完整度提示,大概率应该没有什么问题,但是分析发现部分归档日志损坏无法完整恢复
2. 通过对磁盘做碎片,恢复出来该数据文件,参考以往文章:
dbca删除库和rm删库恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
通过这个方法运气不错,恢复出来该库的system01.dbf文件非常完整0丢失

[oracle@localhost oradata]$ dbv file=system01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Thu May 15 23:26:57 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oradata/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 199680
Total Pages Processed (Data) : 113988
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 26869
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 40253
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18570
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658228557 (0.658228557)

完成上述恢复工作之后,目前确认只有sysaux01.dbf有8026个block损坏,但是该表空间不涉及业务数据,尝试在新的系统中直接修改路径并open库

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback off;

Database altered.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

运气不错,数据库直接open成功,现在处理sysaux01.dbf中的损坏文件:
1. 确认该文件具体坏块开始位置:

2. 由于坏块在文件中比较靠后,分析实际存储数据最后的位置

SQL> select max(block_id+blocks) from dba_extents where file_id=3;

MAX(BLOCK_ID+BLOCKS)
--------------------
             3493120

最后存储数据的位置小于坏块的位置,证明坏块部分是没有存储数据的,直接resize掉坏块部分

SQL> alter database datafile '/u01/oradata/sysaux01.dbf' resize 27290m;

Database altered.

然后dbv该数据文件,确认没有任何问题

[oracle@localhost trace]$ dbv file=/u01/oradata/sysaux01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Wed May 15 22:43:00 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oradata/sysaux01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 3493120
Total Pages Processed (Data) : 1516833
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1868832
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 56577
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 32107
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18771
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658223915 (0.658223915)

使用rman检测全库,也确定没有任何问题

[oracle@localhost trace]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 22:43:58 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XIFENFEI (DBID=2912535091)

RMAN> 

RMAN> 

RMAN> backup validate check logical database skip inaccessible;

Starting backup at 15-MAY-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=278 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
………………
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
32   OK     0              6273         6400            370625094 
  File Name: /u01/oradata/xff_com.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              127             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              163752       832000          627920639 
  File Name: /u01/oradata/XFF_DATA_202312231.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              374296          
  Index      0              285002          
  Other      0              8950            

Finished backup at 15-MAY-24

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 15 22:47:44 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> select * from v$database_block_corruption ;

no rows selected

SQL>

至此对于这次rm -rf /*的故障实现了Oracle数据库完美恢复,数据0丢失.

分布式存储故障导致数据库无法启动故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：分布式存储故障导致数据库无法启动故障处理

国内xx医院使用了国外医疗行业龙头的pacs系统,由于是一个历史库,存放在分布式存储中,由于存储同时多个节点故障,导致数据库多个文件异常,数据库无法启动,三方维护人员尝试通通过rman归档进行应用日志,结果发现日志有损坏报ORA-00354 ORA-00353,无法记录恢复,希望我们给予支持

Mon Apr 29 13:28:40 2024
Media Recovery failed with error 354
Mon Apr 29 13:28:40 2024
Errors in file F:\XXXXXX_DB\ORACLE\ADMIN\diag\rdbms\xxx\msxxx1\trace\xxx_pr00_4568.trc:
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 487424 change 8737273868 time 04/01/2024 01:38:25
ORA-00334: archived log: 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'
ORA-283 signalled during: alter database recover logfile 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'...

接手故障之后,通过尝试恢复发现除该错误之外,还有ORA-600 4552之类错误

跳过这些异常文件恢复,最终确认异常文件有如下部分

这些文件由于日志无法正常应用(有日志损坏无法应用，有日志和数据文件block不匹配导致无法应用),这样的情况直接通过自研的Oracle Recovery Tools小工具直接修改文件头信息

然后尝试OPEN数据库结果报ORA-1207

对于这个故障可以通过rectl或者using backup ctl方式处理,然后open数据库成功

由于该系统是历史库,不会有新业务写入,通过对异常表和索引进行处理之后,客户测试业务可以正常访问,完成本次恢复

read_me_recover_tn勒索恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：read_me_recover_tn勒索恢复

最近有客户被MySQL删库勒索,现象如下:
1. 删除掉以前的库,并创建一个同名库,并且会创建一个read_me_recover_tn库,类似下图:

2. 在read_me_recover_tn库中有一个readme表,每个被删除然后创建的库里面也有一个readme表

3. 每个readme表内容类似信息类似:

mysql> desc readme
    -> ;
+-----------------+------+------+-----+---------+-------+
| Field           | Type | Null | Key | Default | Extra |
+-----------------+------+------+-----+---------+-------+
| id              | int  | NO   | PRI | NULL    |       |
| Message         | text | YES  |     | NULL    |       |
| Bitcoin_Address | text | YES  |     | NULL    |       |
+-----------------+------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from readme\G;
*************************** 1. row ***************************
             id: 1
        Message: I have backed up all your databases. To recover them you must
 pay 0.008 BTC (Bitcoin) to this address: 15f9vdGBeT1NCMp6z9NxrQEEUxnYqRPvyC . 
Backup List: xxxx_db, xxxx_db_test. After your payment email me at 
dbrestore3195@onionmail.org with your server IP (xx.xx.xx.xx) and transaction 
ID and you will get a download link to your backup. Emails without transaction 
ID and server IP will be ignored.
Bitcoin_Address: 15f9vdGBeT1NCMp6z9NxrQEEUxnYqRPvyC
1 row in set (0.00 sec)

这类勒索和我以前介绍相关文章类似:
RECOVER_YOUR_DATA勒索恢复
 A____Z____RECOVER____DATA勒索恢复

处理办法也完全相同:
建议先对系统进行镜像或者快照,然后按照先os层面恢复,在block级别恢复的方法处理,如果无法自行解决,可以联系我们进行技术支持,最大限度抢救和数据,减少损失
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
另外建议加强系统和mysql安全加固,数据库尽量不要暴露在公网上

WINDOWS 下用dg broker搭建ADG（单机to单机）

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：WINDOWS 下用dg broker搭建ADG（单机to单机）

环境

#主备库 C:\Windows\System32\drivers\etc\hosts 文件
192.168.11.10  dg1
192.168.11.11  dg2
#环境
主库主机名：dg1 现有实例orcl
备库主机名：dg2 只安装软件

一，主库配置
–主库设置强制日志，保证所有的操作都记录到日志文件
–查看当前force\_logging的设置

#主库如果已开启归档，不需要停机
sqlplus / as sysdba
select force_logging from v$database;
select flashback_on from v$database;
alter database force logging; -- 开启强制日志模式
#######################################################
#如果没开归档
sqlplus / as sysdba
shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
#alter database flashback on;  -- 开启闪回，不是必须，推荐开启
#######################################################
#主库添加standby日志组
#查看日志文件大小 select bytes/1024/1024 from v$log;这里是50M
alter database add standby logfile group 10 ('D:\app\Administrator\oradata\orcl\standby_redo01.log') size 50m;
alter database add standby logfile group 11 ('D:\app\Administrator\oradata\orcl\standby_redo02.log') size 50m;
alter database add standby logfile group 12 ('D:\app\Administrator\oradata\orcl\standby_redo03.log') size 50m;
alter database add standby logfile group 13 ('D:\app\Administrator\oradata\orcl\standby_redo04.log') size 50m;
########################################################
#设置文件管理自动
alter system set standby_file_management=auto;

二、主备库网络设置

#主库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL)  #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#主库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
#备库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_stby_DGMGRL) #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#备库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
#主库监听reload
lsnrctl reload
#备库启动监听
lsnrctl start

三、备库配置

#创建一个临时参数文件如d:\pfile.txt内容如下
*.db_name='orcl'
#创建密码文件，或者从主库拷贝一个
orapwd file=D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\database\PWDorcl.ora password=oracle entries=10
#创建备库所需目录
mkdir D:\app\Administrator\oradata\orcl\
mkdir D:\app\Administrator\admin\orcl\adump\
mkdir D:\app\Administrator\fast_recovery_area\orcl\
#用ORADIM创建实例
oradim -new -sid orcl
#用临时参数文件启动
sqlplus / as sysdba
starup nomount pfile='d:\pfile.txt';

四、RMAN duplicate创建备库

#tnsping测试互通性
tnsping orcl
tnsping orcl_stby
#主库连接备库
sqlplus sys/oracle@stddb as sysdba
#备库连接主库
sqlplus sys/oracle@orcl as sysdba
#########################################################
#备库执行，连接主备库
rman target sys/oracle@orcl auxilary sys/oracle@orcl_stby
#创建dg备库，这里假设主备库路径相同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
  nofilenamecheck;
#########################################################
#如果主备库路径不同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
    set db_file_name_convert='orcl','orcl_stby'
    set log_file_name_convert='orcl','orcl_stby'
    set job_queue_processes='0'
  nofilenamecheck;
#开启ADG
sqlplus / as sysdba
alter database open read only;
alter database recover managed standby database disconnect from session;

五、配置DG BROKER

#主备库两边执行
alter system set dg_broker_start=true;
#主库连接dgmgrl
dgmgrl sys/oracle@orcl
#创建dg broker配置
create configuration dg_config as primary database is orcl connect identifier is orcl;
#添加备库到配置文件
add database orcl_stby as connect identifier is orcl_stby;
#启用配置
enable configuration;

############################################################################
#显示DG配置信息
show configuration
show configuration verbose
#显示主备库信息
show database orcl
show database orcl_stby
show database verbose orcl
show database verbose orcl_stby

六、一些测试

#测试Database Switchover
dgmgrl sys/oracle@orcl
switchover to orcl_stby;
show configuration
#切换回来
switchover to orcl;
show configuration
###########################################################################
#测试Database Failover，此时dg关系已经打破
dgmgrl sys/oracle@orcl
failover to orcl_stby;
#如果主库开启了flashback，执行以下语句自动重建主库
 reinstate database orcl;
#如果没有开启flashback，删除重建主库，重新建立dg关系
############################################################################
#测试快照备库
dgmgrl sys/oracle@orcl
convert database orcl_stby to snapshot standby;
show configuration;
#快照转成正常备库
convert database orcl_stby to physical standby;
show configuration;

七、总结
优点在于除监听设置外主备库都不需要做过多的设置，备库临时参数文件只需要一个dbname，其余dg有关的参数dg broker会自动设置。
八、参考资料
ORACLE-BASE – Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2

存储故障后oracle报—ORA-01122/ORA-01207故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：存储故障后oracle报—ORA-01122/ORA-01207故障处理

客户存储异常,通过硬件恢复解决存储故障之后,oracle数据库无法正常启动(存储cache丢失),尝试recover数据库报ORA-00283 ORA-01122 ORA-01110 ORA-01207错误
以前处理过比较类似的存储故障case:
又一起存储故障导致ORA-00333 ORA-00312恢复
 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理

SQL> recover database until cancel;
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 536 验证失败
ORA-01110: 数据文件 536: '+DATA/orcl/dt_img_dat511.ora'
ORA-01207: 文件比控制文件更新 - 旧的控制文件

Sun May 05 00:09:03 2024
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Sun May 05 00:09:10 2024
SUCCESS: diskgroup FRA was mounted
Sun May 05 00:09:10 2024
NOTE: dependency between database orcl and diskgroup resource ora.FRA.dg is established
Sun May 05 00:09:14 2024
WARNING! Recovering data file 1 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Media Recovery failed with error 1122
Slave exiting with ORA-283 exception
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_pr00_8048.trc:
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 536 验证失败
ORA-01110: 数据文件 536: '+DATA/orcl/dt_img_dat511.ora'
ORA-01207: 文件比控制文件更新 - 旧的控制文件
Sun May 05 00:09:16 2024
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database until cancel  ...

using backup controlfile进行恢复

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 18646239951 (在 04/25/2024 17:14:50 生成) 对于线程 1 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003886.199435.1167240505
ORA-00280: 更改 18646239951 (用于线程 1) 在序列 #1003886 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 18646239951 (在 04/25/2024 17:11:40 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677876.199531.1167239807
ORA-00280: 更改 18646239951 (用于线程 2) 在序列 #677876 中


ORA-00279: 更改 18646255791 (在 04/25/2024 17:16:46 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677877.199472.1167240099
ORA-00280: 更改 18646255791 (用于线程 2) 在序列 #677877 中
ORA-00278: 此恢复不再需要日志文件
'+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677876.199531.1167239807'


ORA-00279: 更改 18646295647 (在 04/25/2024 17:21:38 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677878.199379.1167240623
ORA-00280: 更改 18646295647 (用于线程 2) 在序列 #677878 中
ORA-00278: 此恢复不再需要日志文件
'+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677877.199472.1167240099'


ORA-00279: 更改 18646331784 (在 04/25/2024 17:28:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507
ORA-00280: 更改 18646331784 (用于线程 1) 在序列 #1003887 中
ORA-00278: 此恢复不再需要日志文件
'+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003886.199435.1167240505'


ORA-00308: 无法打开归档日志
'+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507'
ORA-17503: ksfdopn: 2 未能打开文件
+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507
ORA-15012: ASM file
'+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507' does not exist


ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: '+DATA/orcl/system01.dbf'

通过分析,确认由于cache丢失导致thread_1_seq_1003887这个日志丢失（而且redo已经被覆盖）

数据库无法通过正常recover的思路解决.通过屏蔽一致性,强制打开数据库,alert日志报ORA-600 2662错误

Sat May 04 17:23:00 2024
Redo thread 2 internally disabled at seq 1 (CKPT)
ARC1: Archiving disabled thread 2 sequence 1
Archived Log entry 2 added for thread 2 sequence 1 ID 0x0 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_3684.trc  (incident=47066):
ORA-00600: ??????, ??: [2662], [4], [1466533588], [4], [1466584862], [12583040], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_3684.trc:
ORA-00600: ??????, ??: [2662], [4], [1466533588], [4], [1466584862], [12583040], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_3684.trc:
ORA-00600: ??????, ??: [2662], [4], [1466533588], [4], [1466584862], [12583040], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3684): terminating the instance due to error 600
Instance terminated by USER, pid = 3684
ORA-1092 signalled during: alter database open resetlogs...

通过修改数据库scn,open数据库报ORA-600 4137

Sun May 05 00:12:41 2024
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Sun May 05 00:12:56 2024
Trace dumping is performing id=[cdmp_20240505001256]
Sun May 05 00:12:56 2024
ORACLE Instance orcl1 (pid = 22) - Error 600 encountered while recovering transaction (28, 21).
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_smon_5896.trc:
ORA-00600: ??????, ??: [4137], [28.21.42965783], [0], [0], [], [], [], [], [], [], [], []

这个错误比较明显,由于28号回滚段异常导致,对异常回滚段进行处理,重建undo,数据库恢复主要工作完成

Oracle 23ai rm redo*.log恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 23ai rm redo*.log恢复

在oracle 23ai的pdb中创建用户和表,并且插入数据(不提交),在另外一个会话中abort库,并从os层面rm删除掉redo文件,模拟数据库当前redo丢失,数据库恢复
创建用户和表并插入数据

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:40:55 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> 
SQL> 
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

SQL> alter session set container=FREEPDB1;

Session altered.

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff ;

Grant succeeded.

SQL> conn xff/oracle@FREEPDB1
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.


SQL> insert into t1 select *from t1;

75877 rows created.

SQL> /

151754 rows created.

另外一个会话中abort库

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:43:30 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> shutdown abort;
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

操作系统层面rm -rf 删除redo

[oracle@xifenfei ~]$ cd $ORACLE_BASE/oradata
[oracle@xifenfei oradata]$ ls
FREE
[oracle@xifenfei oradata]$ cd FREE/
[oracle@192 FREE]$ ls
control01.ctl  FREEPDB1  redo01.log  redo03.log    system01.dbf  undotbs2.dbf
control02.ctl  pdbseed   redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@xifenfei FREE]$ ls -ltr
total 2441036
drwxr-x---. 2 oracle     1000         85 May  1 16:49 pdbseed
-rw-r-----. 1 oracle oinstall   20979712 May  1 16:51 temp01.dbf
drwxr-x---. 2 oracle     1000        104 May  1 16:55 FREEPDB1
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:23 redo01.log
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:23 redo02.log
-rw-r-----. 1 oracle oinstall    7348224 May  3 15:23 users01.dbf
-rw-r-----. 1 oracle oinstall 1080041472 May  3 15:43 system01.dbf
-rw-r-----. 1 oracle oinstall  692068352 May  3 15:43 sysaux01.dbf
-rw-rw----. 1 oracle oinstall   52436992 May  3 15:43 undotbs2.dbf
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:43 redo03.log
-rw-r-----. 1 oracle oinstall   18759680 May  3 15:43 control01.ctl
-rw-r-----. 1 oracle oinstall   18759680 May  3 15:43 control02.ctl
[oracle@xifenfei FREE]$ rm -rf redo0*
[oracle@192 FREE]$ ls -l redo*
ls: cannot access 'redo*': No such file or directory
[oracle@xifenfei FREE]$

尝试启动数据库，报ora-00313，ora-00312，ora-27037等错误

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:44:17 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/FREE/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

由于所有redo均被删除(包含当前redo),因此只能强制resetlogs方式打开库,尝试打开数据库

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 4244588 generated at 05/03/2024 15:23:22 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/23ai/dbhomeFree/dbs/arch1_6_1167842962.dbf
ORA-00280: change 4244588 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/FREE/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by irrecoverable error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 5596
Session ID: 29 Serial number: 63204

数据库遇到常见的ORA-600 kcbzib_kcrsds_1错误,这类错误类似oracle在12c之前版本中的ORA-600 2662错误
ORA-600 kcbzib_kcrsds_1报错
 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
 redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
 ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1
这种一般就是scn问题,通过修改数据库scn,数据库启动报ORA-16433错误

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:49:00 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
SQL> alter database mount;

Database altered.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database or pluggable database must be opened in read/write mode.


SQL> shutdown abort;
ORACLE instance shut down.

处理ctl,open数据库成功

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "FREE" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/FREE/redo01.log'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '/opt/oracle/oradata/FREE/redo02.log'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '/opt/oracle/oradata/FREE/redo03.log'  SIZE 200M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/opt/oracle/oradata/FREE/system01.dbf',
 14    '/opt/oracle/oradata/FREE/pdbseed/system01.dbf',
 15    '/opt/oracle/oradata/FREE/sysaux01.dbf',
 16    '/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf',
 17    '/opt/oracle/oradata/FREE/users01.dbf',
 18    '/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf',
 19    '/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf',
 20    '/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf',
 21    '/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf',
 22    '/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf',
 23    '/opt/oracle/oradata/FREE/undotbs2.dbf'
 24  CHARACTER SET AL32UTF8
 25  ;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> show pdbs;

          CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------------- ------------------------------ ---------- ----------
               2 PDB$SEED                       READ ONLY  NO
               3 FREEPDB1                       READ WRITE NO

至此当前数据库redo故障模拟和恢复基本完成

Oracle 发布计划—包含Oracle 23ai版本

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 发布计划—包含Oracle 23ai版本

根据oracle官方公布的Oracle 23ai的发布计划（当然free版本，exadata,cloud版本已经发布）,可以确定在2024年上半年即将发布Linux x86-64版本，Oracle Solaris SPARC/Linux on Arm/Microsoft Windows x64 (64-bit)版本将在下版本发布,Oracle Solaris x86-64平台不在发布Oracle 23ai版本数据库，IBM AIX on POWER Systems/IBM Linux on System z/HP-UX Itanium在发布计划中具体时间没有公布。具体参考：Release Schedule of Current Database Releases (Doc ID 742060.1)

Platform	23aiLong Term Release	21cInnovation Release	19cLong Term Release	18c	12.2.0.1	12.1.0.2	12.1.0.1	11.2.0.4
*Oracle Public Cloud Releases*
Exadata Express Cloud Service	Not Planned	Not Planned	Not Planned	Not Planned	18-Sep-2016	N/A
Base Database Service¹(Previously known as: Database Cloud Service)	19-Sep-2023	08-Dec-2020available on VM (RAC or single-instance) and Bare Metal (single-instance)	OCI DB System – VM: Jul-2019OCI DB System – Bare Metal: April 2020	1-Mar-2018	4-Nov-2016	Sep-2014	N/A	Sep-2014
Exadata Database Service on Dedicated Infrastructure¹(Previously known as: Exadata Cloud Service)	2-May-2024	N/A	19-Jun-2019	Jun-2018	4-Nov-2016	Oct-2015	N/A	Oct-2015
Gen 1 Exadata Cloud at Customer¹(Previously known as: Exadata Cloud at Customer)	N/A	N/A	17-Sep-2019	May-2018	2-May-2017	Dec-2016	N/A
Exadata Database Service on Cloud at Customer ¹	2-May-2024	N/A	17-Sep-2019	N/A	N/A	N/A	N/A
Autonomous Database on Dedicated Exadata Infrastructure ²	1H CY2024	N/A	26-Jun-2019	N/A	N/A	N/A	N/A
Autonomous Database on Exadata Cloud at Customer ²	1H CY2024	N/A	08-Jul-2020	N/A	N/A	N/A	N/A
Autonomous Database on Shared Exadata Infrastructure ²	1H CY2024	N/A	15-Mar-2020	18-Mar-2018	N/A	N/A	N/A
*On-Premises Engineered Systems*(Same software as released for other platforms but tested on Engineered Systems)
Oracle Database Appliance	CY2024	16-Sep-2021	16-Oct-2019	7-Mar-2018	17-Nov-2017	Apr 2014	N/A	Oct 2013
Exadata	1H CY2024	13-Aug-2021	13-Feb-2019	16-Feb-2018	10-Feb-2017	Oct 2014	Mar 2013	Nov 2013
Supercluster	TBA	N/A	26-Apr-2019	12-Mar-2018	10-Feb-2017	Nov 2014	Apr 2014	Dec 2013
**On-Premises Server Releases (includes client)(Download here)**
Linux x86	Not Planned	Not Planned	Not planned	Not planned	Not planned	Not planned	Not planned	28-Aug-2013
Linux x86-64	1H CY2024	13-Aug-2021	25-Apr-2019	23-Jul-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	27-Aug-2013
Linux on Arm	2H CY2024	Not Planned	28-June-2023	Not Planned	Not Planned	Not Planned	Not Planned	Not Planned
Oracle Solaris SPARC (64-bit)	2H CY2024	See DOC ID 2853097.1	26-Apr-2019	30-Jul-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	29-Aug-2013
Oracle Solaris x86-64 (64-bit)	Platform de-supported	Platform de-supported	07-Nov-2019Terminal Release	6-Aug-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	29-Aug-2013
Microsoft Windows x64 (64-bit)	2H CY2024	08-Oct-2021	08-Jun-2019	21-Aug-2018	16-Mar-2017	25-Sep-2014	9-Jul-2013	25-Oct-2013
OpenVMS Itanium	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Terminal Release
HP-UX Itanium	TBA	28-Sept-2021	28-May-2019	20-Nov -2018	13-Apr-2017	14-Nov-2014	9-Jan-2014	10-Oct-2013
HP-UX PA-RISC (64-bit)	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	2-Jan-2014
IBM AIX on POWER Systems	TBA	See DOC ID 2766930.1	28-May-2019	20-Nov -2018	13-Apr-2017	14-Nov-2014	9-Jan-2014	10-Oct-2013
IBM Linux on System z	TBA	See DOC ID 2766930.1	06-June-2019	20-Nov -2018	6-Jun-2017	14-Nov-2014	9-Jan-2014	9-Jan-2014
Microsoft Windows (32-bit)	Not Planned	Client Only – Oct 2021	Client only – June 2019	Not planned	Not planned	Not planned	Not planned	25-Oct-2013
Platform	23ai	21c	19	18	12.2.0.1	12.1.0.2	12.1.0.1	11.2.0.4
*Instant Client-Only Releases*
Apple macOS (Intel) download			10-Sept-2019	Mar-2018	15-Jan-2018	2016	Not planned	20-Apr-2014 Download
IBM Linux on POWER (Big Endian)						18-Dec-2015 (download)		4-Dec-2014
IBM Linux on POWER (Little Endian)download			Jul-2019download	31-Oct-2018 (download)	7-Jun-2017 (download)	18-Dec-2015 (download)
Linux on Arm		Not Planned	May-2021download
¹Oracle Base Database Service (formerly Oracle Database Cloud Service) and Exadata Database Service (formerly Exadata Cloud Service and Exadata Cloud@Customer) follow the same support life and error correction schedule as on-premises dates unless otherwise noted in Table 1 above (for example, 11.2.0.4 MDS). Also, Extended Support is bundled with both the license-included and BYOL versions of these services and does not require additional fees. These services will not be covered under Sustaining Support and Oracle makes no commitment that any cloud service instances will continue to run after the end of their support life (Premier, extended, error correction, or MDS). We will not disable any installed databases upon the expiration of support, but the underlying infrastructure will continue to be updated. The infrastructure updates may render the unpatched databases inoperable. We make no commitment as to how long any unpatched databases will continue to run.²Oracle’s current plan for Oracle Autonomous Database – Shared Exadata Infrastructure, Oracle Autonomous Database – Dedicated Exadata Infrastructure, Autonomous Database on Exadata Cloud@Customer services is to support Long Term Release versions for a similar period of time found for those major releases when deployed on-premises. The intent of this plan is to provide stability of service experience for the thousands of critical database deployments found in Oracle Cloud. Additionally, we will at times make available Innovation Releases as part of the Autonomous Database Cloud Services. Innovation Releases, when provided, will be supported for a shorter period of time as compared to a Long Term Release, similar to what is found for those releases when deployed on-premises. Oracle reserves the right to change this plan with changing business requirements. Any change will take into account that stability is of utmost importance to Oracle’s many thousands of mission critical deployments. *References for Client-Only:* https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-windows-5539283.html *32-bit Linux & Windows Instant Clients, https://www.oracle.com/database/technologies/instant-client/linux-x86-32-downloads.html* *https://www.oracle.com/database/technologies/instant-client/microsoft-windows-32-downloads.html*

Oracle 23ai 变化之—-默认数据文件变为bigfile

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 23ai 变化之—-默认数据文件变为bigfile

oracle把23c变为23ai，引入了很多新特性,今天下载了free版本的虚拟机登录进去第一感觉就是system,sysaux,undo等文件以前版本默认的smallfile变为了bigfile

SQL> select BANNER_FULL from v$version;

BANNER_FULL
----------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

SQL> select con_id,ts#,name,bigfile from v$tablespace order by 1,2;

    CON_ID        TS# NAME                           BIG
---------- ---------- ------------------------------ ---
         1          0 SYSTEM                         YES
         1          1 SYSAUX                         YES
         1          3 TEMP                           NO
         1          4 USERS                          YES
         1          5 UNDOTBS2                       YES
         2          0 SYSTEM                         YES
         2          1 SYSAUX                         YES
         2          2 UNDOTBS1                       YES
         2          3 TEMP                           NO
         3          0 SYSTEM                         YES
         3          1 SYSAUX                         YES
         3          2 UNDOTBS1                       YES
         3          3 TEMP                           NO
         3          6 USERS                          NO

14 rows selected.

SQL> select con_id,file#,ts#,rfile#,name from v$datafile;

    CON_ID      FILE#        TS#     RFILE# NAME
---------- ---------- ---------- ---------- ------------------------------------------------------
         1          1          0       1024 /opt/oracle/oradata/FREE/system01.dbf
         2          2          0       1024 /opt/oracle/oradata/FREE/pdbseed/system01.dbf
         1          3          1       1024 /opt/oracle/oradata/FREE/sysaux01.dbf
         2          4          1       1024 /opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf
         1          7          4       1024 /opt/oracle/oradata/FREE/users01.dbf
         2          9          2       1024 /opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf
         3         12          0       1024 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
         3         13          1       1024 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
         3         14          2       1024 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
         3         15          6         15 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
         1         16          5       1024 /opt/oracle/oradata/FREE/undotbs2.dbf

11 rows selected.