分类目录归档:Oracle 23ai

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 23ai, Oracle备份恢复 | 标签为 , , , | 评论关闭

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 Service1(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 Infrastructure1(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 Customer1(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 1 2-May-2024 N/A 17-Sep-2019 N/A N/A N/A N/A
Autonomous Database on Dedicated Exadata Infrastructure 2  1H CY2024 N/A 26-Jun-2019 N/A N/A N/A N/A
Autonomous Database on Exadata Cloud at Customer 2 1H CY2024 N/A 08-Jul-2020 N/A N/A N/A N/A
Autonomous Database on Shared Exadata Infrastructure 2 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 | 标签为 | 评论关闭

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.
发表在 Oracle 23ai | 标签为 , | 评论关闭