分类目录归档：Oracle 23ai

Oracle23ai新特性—SCHEMA级授权( grant select any table on schema)

发表于 2024 年 7 月 29 日由惜分飞

创建测试用户a并在a中创建t1,t2两个测试表,另外创建用户b(只授权登录权限)

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:14:11 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 XIFENFEI                       READ WRITE NO

SQL> create user b identified by oracle;

User created.

SQL> grant create session  to b;

Grant succeeded.

SQL> grant unlimited tablespace to a;

Grant succeeded.

SQL> create table a.t1 as select * from dba_objects;

Table created.

SQL> c/t1/t2;
  1* create table a.t2 as select * from dba_objects
SQL> /

Table created.

SQL> select count(1) from a.t1;

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

SQL> select count(1) from a.t2;

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

SQL> create user b identified by oracle;

User created.

SQL> grant create session  to b;

Grant succeeded.

直接使用b用户登录并尝试查询a用户数据,结果是无法查询数据报ora-00942(意料之中因为b现在无权限访问a.t1表)

SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t1;
select count(1) from a.t1
                       *
ERROR at line 1:
ORA-00942: table or view "A"."T1" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

直接schema级别授权a用户下面的表给b用户查询权限

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:07:14 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL>  grant select any table on schema a to b;

Grant succeeded.

SQL>  conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t1;

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

SQL> select count(1) from a.t2;

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

在a用户中新增加表,在b用户中可以直接查询(无需再次授权)

SQL> conn a/oracle@127.0.0.1/xifenfei
Connected.
SQL>  create table t3 as select * from tab;

Table created.

SQL> select count(1) from t3;

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

SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t3;

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

Oracle 23ai中，Oracle引入了架构级（SCHEMA级别）授权。这允许你以更简单、更直观的方式对整个Schema进行授权。以前的版本中如果需要类似授权操作,需要对schema下面所有表进行grant select on user.table to user2形式授权工作量比较大而且user1中如果新增加表还需要额外授权

发表在 Oracle 23ai | 标签为 grant select any table on schema, schema级别授权 | 评论关闭

Oracle 23ai rm redo*.log恢复

发表于 2024 年 5 月 4 日由惜分飞

在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备份恢复 | 标签为 kcbzib_kcrsds_1, ORA-600 kcbzib_kcrsds_1, rm redo恢复, 删除日志恢复 | 评论关闭

根据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 | 标签为 Oracle 23ai发布时间 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle 23ai

Oracle23ai新特性—SCHEMA级授权( grant select any table on schema)

Oracle 23ai rm redo*.log恢复

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

文章分类

最近发表

分类目录归档：Oracle 23ai

Oracle23ai新特性—SCHEMA级授权( grant select any table on schema)

Oracle 23ai rm redo*.log恢复

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

标签云

文章分类

最近发表