Monday, August 15, 2022

ORA-15017: diskgroup "VOTE2" cannot be mounted on cluster nodes

Reuse disk and Create Diskgrup using old disk and mount diskgroup:

While creating disk group got error message"ORA-15017: diskgroup "VOTE2" cannot be mounted on cluster nodes" but diskgruoup created and mounted on only 1 node

Issue:

Unbale to mount diskgroup on node2

Cause:

ASM disk is not visible on node2

Error message:

ORA-15017: diskgroup "VOTE2" cannot be mounted on cluster nodes

Troubleshooting logs:

Check alert log andassociate trace file (/u01/app/oracle/diag/crs/oraclelab2/crs/trace/crsd_oraagent_oracle.trc)

Solution:

after asm disk scan, ASM disks are visible on node2.

Mount the Diskgroup after ASM disks are visible

[root@oraclelab1 ~]# ps -ef|grep smon

root 7423 7375 0 21:01 pts/1 00:00:00 grep --color=auto smon

root 23851 1 1 09:11 ? 00:10:18 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 24512 1 0 09:12 ? 00:00:00 asm_smon_+ASM1

oracle 25502 1 0 09:12 ? 00:00:00 ora_smon_DEVDB1

[root@oraclelab1 ~]# su - oracle

Last login: Sat Aug 13 20:46:30 IST 2022

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ asmcmd -p lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 20476 17524 0 17524 0 N DATA/

MOUNTED EXTERN N 512 512 4096 4194304 3068 2744 0 2744 0 N OCR/

MOUNTED EXTERN N 512 512 4096 4194304 10236 9252 0 9252 0 N RECO/

MOUNTED EXTERN N 512 512 4096 4194304 1020 856 0 856 0 Y VOTE1/

[oracle@oraclelab1 ~]$ oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASM_VOTEDISK1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ cd /dev/

[oracle@oraclelab1 dev]$ ll sd*

brw-rw----. 1 root disk 8, 0 Aug 13 08:05 sda

brw-rw----. 1 root disk 8, 1 Aug 13 08:05 sda1

brw-rw----. 1 root disk 8, 2 Aug 13 08:05 sda2

brw-rw----. 1 root disk 8, 16 Aug 13 08:05 sdb

brw-rw----. 1 root disk 8, 17 Aug 13 08:05 sdb1

brw-rw----. 1 root disk 8, 32 Aug 13 08:05 sdc

brw-rw----. 1 root disk 8, 33 Aug 13 08:05 sdc1

brw-rw----. 1 root disk 8, 48 Aug 13 08:05 sdd

brw-rw----. 1 root disk 8, 49 Aug 13 08:05 sdd1

brw-rw----. 1 root disk 8, 64 Aug 13 08:05 sde

brw-rw----. 1 root disk 8, 65 Aug 13 08:05 sde1

brw-rw----. 1 root disk 8, 80 Aug 13 08:05 sdf

brw-rw----. 1 root disk 8, 81 Aug 13 08:05 sdf1

[oracle@oraclelab1 dev]$

[oracle@oraclelab1 dev]$ oracleasm createdisk ASM_VOTEDISK2 /dev/sdf1

Unable to open device "/dev/sdf1": Permission denied

[oracle@oraclelab1 dev]$

[oracle@oraclelab1 dev]$ exit

logout

[root@oraclelab1 ~]# oracleasm createdisk ASM_VOTEDISK2 /dev/sdf1

Device "/dev/sdf1" is already labeled for ASM disk ""

[root@oraclelab1 ~]#

[root@oraclelab1 ~]# dd if=/dev/zero of=/dev/sdf1 bs=4096 count=100

100+0 records in

100+0 records out

409600 bytes (410 kB) copied, 0.00242461 s, 169 MB/s

[root@oraclelab1 ~]# oracleasm createdisk ASM_VOTEDISK2 /dev/sdf1

Writing disk header: done

Instantiating disk: done

[root@oraclelab1 ~]# oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASM_VOTEDISK1

ASM_VOTEDISK2

[root@oraclelab1 ~]#

Connect to +ASM1 and create disksgroupn in sql command prompt or use asmca to create diskgroup:

CREATE DISKGROUP VOTE2 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/ASM_VOTEDISK2' SIZE 1023M

ATTRIBUTE 'compatible.asm'='19.0.0.0','au_size'='4M'

[oracle@oraclelab1 asmca]$ grep "CREATE DISKGROUP VOTE2" /u01/app/oracle/cfgtoollogs/asmca/asmca-220813PM090427.log

[Thread-83] [ 2022-08-13 21:08:40.050 IST ] [UsmcaLogger.logInfo:156] SQL: CREATE DISKGROUP VOTE2 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/ASM_VOTEDISK2' SIZE 1023M

[oracle@oraclelab1 asmca]$

While creating VOTE2 diskgroup get beloe error on asmca:

[DBT-30028] Generic failure interacting with CRS. Details PRCR-1079 : Failed to start resource ora.VOTE2.dg

CRS-5017: The resource action "ora.VOTE2.dg start" encountered the following error:

ORA-15032: not all alterations performed

ORA-15017: diskgroup "VOTE2" cannot be mounted

ORA-15040: diskgroup is incomplete

. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/oraclelab2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.VOTE2.dg' on 'oraclelab2' failed

[root@oraclelab2 ~]#tail -f /u01/app/oracle/diag/crs/oraclelab2/crs/trace/crsd_oraagent_oracle.trc

2022-08-13 21:08:45.424 : USRTHRD:916879104: [ INFO] {1:56316:8567} DgpAgent::fetchDgStatus(VOTE2) query:SELECT a.state, b.startup_time FROM v$asm_diskgroup_stat a, v$instance b WHERE a.name = :1 /* asm agent *//* {1:56316:8567} */ OCI error:1403 what:no data found

2022-08-13 21:08:45.424 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::queryDgStatus 122 updateDGSCache

2022-08-13 21:08:45.424 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::queryDgStatus 300 OCI error 1403 no data found

2022-08-13 21:08:45.424 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::queryDgStatus 300 cmdId:259 ckType:65535 dgs.m_dgName:VOTE2 dgs.m_dgpAgent:0x7f34501c43a0 dgs.m_isStatusCached:1

2022-08-13 21:08:45.424 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::queryDgStatus 310 no data found in v$asm_diskgroup_stat

2022-08-13 21:08:45.425 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::stopSingle 100 diskgroup VOTE2 already stopped clsagfw_res_status 1 exit }

2022-08-13 21:08:45.425 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::stop 900 s_DGStatusThread:0x7f344831ef60 m_pConnxn:0x7f3440056140

2022-08-13 21:08:45.425 : USRTHRD:916879104: [ INFO] {1:56316:8567} DgpAgent::fetchDgStatus(VOTE2) query:SELECT a.state, b.startup_time FROM v$asm_diskgroup_stat a, v$instance b WHERE a.name = :1 /* asm agent *//* {1:56316:8567} */ OCI error:1403 what:no data found

2022-08-13 21:08:45.426 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] ConnectionPool::signalEvent entry { this:0x7f3470091af0 s_ohSidEventMapLock:0x563118de55f0 action:3

2022-08-13 21:08:45.426 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] DgpAgent::stopSingle 999 status:2 }

2022-08-13 21:08:45.426 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] clean }

2022-08-13 21:08:45.426 :CLSDYNAM:916879104: [ora.VOTE2.dg]{1:56316:8567} [clean] (:CLSN00106:) clsn_agent::clean }

2022-08-13 21:08:45.426 : AGFW:916879104: [ INFO] {1:56316:8567} Command: clean for resource: ora.VOTE2.dg 2 1 completed with status: SUCCESS

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ asmcmd -p lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 20476 17524 0 17524 0 N DATA/

MOUNTED EXTERN N 512 512 4096 4194304 3068 2744 0 2744 0 N OCR/

MOUNTED EXTERN N 512 512 4096 4194304 10236 9252 0 9252 0 N RECO/

MOUNTED EXTERN N 512 512 4096 4194304 1020 856 0 856 0 Y VOTE1/

MOUNTED EXTERN N 512 512 4096 4194304 1020 932 0 932 0 N VOTE2/

[oracle@oraclelab1 ~]$

[root@oraclelab2 ~]# ps -ef|grep smon

root 24149 1 1 09:11 ? 00:09:23 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 25416 1 0 09:13 ? 00:00:00 asm_smon_+ASM2

oracle 25987 1 0 09:13 ? 00:00:00 ora_smon_DEVDB2

root 27062 23163 0 21:13 pts/2 00:00:00 grep --color=auto smon

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASM_VOTEDISK1

[root@oraclelab2 ~]# su - oracle

Last login: Sat Aug 13 20:10:43 IST 2022

[oracle@oraclelab2 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM2

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab2 ~]$ asmcmd -p lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 20476 17524 0 17524 0 N DATA/

MOUNTED EXTERN N 512 512 4096 4194304 3068 2744 0 2744 0 N OCR/

MOUNTED EXTERN N 512 512 4096 4194304 10236 9252 0 9252 0 N RECO/

MOUNTED EXTERN N 512 512 4096 4194304 1020 856 0 856 0 Y VOTE1/

[oracle@oraclelab2 ~]$

[root@oraclelab2 ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

Instantiating disk "ASM_VOTEDISK2"

[root@oraclelab2 ~]# oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASM_VOTEDISK1

ASM_VOTEDISK2

[root@oraclelab2 ~]#

[oracle@oraclelab2 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM2

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 13 21:24:30 2022

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> ALTER DISKGROUP VOTE2 mount;

ALTER DISKGROUP VOTE2 mount

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15260: permission denied on ASM disk group

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 13 21:25:31 2022

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> ALTER DISKGROUP VOTE2 mount;

Diskgroup altered.

SQL>

[oracle@oraclelab2 ~]$ asmcmd -p lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 20476 17524 0 17524 0 N DATA/

MOUNTED EXTERN N 512 512 4096 4194304 3068 2744 0 2744 0 N OCR/

MOUNTED EXTERN N 512 512 4096 4194304 10236 9252 0 9252 0 N RECO/

MOUNTED EXTERN N 512 512 4096 4194304 1020 856 0 856 0 Y VOTE1/

MOUNTED EXTERN N 512 512 4096 4194304 1020 888 0 888 0 N VOTE2/

[oracle@oraclelab2 ~]$

[oracle@oraclelab1 asmca]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

-- ----- ----------------- --------- ---------

1. ONLINE 834b0ae647534ff3bf237d71ebf0e33e (/dev/oracleasm/disks/ASM_VOTEDISK1) [VOTE1]

Located 1 voting disk(s).

[oracle@oraclelab1 asmca]$ crsctl replace votedisk +VOTE2

Successful addition of voting disk d5d34d250c634f14bf3dacc987d84bb7.

Successful deletion of voting disk 834b0ae647534ff3bf237d71ebf0e33e.

Successfully replaced voting disk group with +VOTE2.

CRS-4266: Voting file(s) successfully replaced

[oracle@oraclelab1 asmca]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

-- ----- ----------------- --------- ---------

1. ONLINE d5d34d250c634f14bf3dacc987d84bb7 (/dev/oracleasm/disks/ASM_VOTEDISK2) [VOTE2]

Located 1 voting disk(s).

[oracle@oraclelab1 asmca]$

Regards,

Mallik

Mallikarjun Ramadurg

THE ORACLE SME – ORACLE ACE, EXADATA, OCI, OCP, OCA, RAC EXPERT. Mallikarjun Ramadurg is the one of the active member of Oracle Community. Mallikarjun is an Oracle DBA and EXADATA administration Expert and has a deep knowledge in Oracle EBS APPS. He is having more than 11 Years of experience. He has got In-depth knowledge of database design, build, cloning and administration and he has got good exposure to environments like database HA, RAC, Oracle Cloud and Datawarehouse. He has worked on Oracle ERP, having sound knowledge on EBS application administration, apps cloning and upgradation, patching, security and performance turning. He has worked on informatica administration, implementation, design, mappings, workflows. He has good knowledge on data modeling, Erwin data modeling. He has good hands-on experience in Oracle Cloud and infrastructure level. He is currently working as Oracle SME & Staff Customer Success Engineer @ Rubrik Inc, Bangalore India

Saturday, August 6, 2022

Convert Non-CDB database as PDB inside CDB database

Converting a non-CDB to a PDB

Source: DEVDB (Normal 19c database)

Target: DEVCDB (CDB database)

Task:

Convert this DEVDB(Normal database) as PDB inside DEVCDB database.

High level Steps:

1. Create xml file from Non-CDB database

2. Check plug in compatibility check for created xml file from CDB database

3. Create or plugin Non-CDB database as PDB inside CDB database

4. Run noncdb_to_pdb.sql sql script inside newly create PDB

1. Create xml file from Non-CDB database

[oracle@oraclelab1 patches]$ ps -ef|grep smon

oracle 2827 1 0 19:59 ? 00:00:00 ora_smon_TESTCDB

oracle 6556 2545 0 20:52 pts/1 00:00:00 grep --color=auto smon

oracle 12924 1 0 Aug02 ? 00:00:08 ora_smon_DEVDB

oracle 15033 1 0 Aug02 ? 00:00:03 ora_smon_DEVCDB

[oracle@oraclelab1 patches]$

[oracle@oraclelab1 patches]$ . oraenv

ORACLE_SID = [TESTCDB] ? DEVDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab1 patches]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 5 20:52:59 2022

Version 19.14.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 268434272 bytes

Fixed Size 8895328 bytes

Variable Size 218103808 bytes

Database Buffers 33554432 bytes

Redo Buffers 7880704 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> begin

DBMS_PDB.DESCRIBE(pdb_descr_file => '/u01/patches/DEVDB.xml');

end;

/ 2 3 4

PL/SQL procedure successfully completed.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

[oracle@oraclelab1 patches]$

begin

DBMS_PDB.DESCRIBE(pdb_descr_file => '/u01/patches/DEVDB.xml');

end;

2. Check plug in compatibility check for created xml file from CDB database

[oracle@oraclelab1 patches]$ . oraenv

ORACLE_SID = [DEVDB] ? DEVCDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab1 patches]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 5 20:56:34 2022

Version 19.14.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/u01/patches/DEVDB.xml',

pdb_name => 'DEVDB');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible');

END IF;

END;

SQL>

incompatible >>>>>>>>>>>>>>>>>>>>>>>>> Issue here

PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/u01/patches/DEVDB.xml',

pdb_name => 'DEVDB');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible');

END IF;

END;

Issue1:

For alert log we got to know the database patch level miss match between source DB and target CDB

[root@oraclelab1 ~]# locate alert_DEVCDB.log

/u01/app/oracle/diag/rdbms/devcdb/DEVCDB/trace/alert_DEVCDB.log

[root@oraclelab1 ~]# tail -f /u01/app/oracle/diag/rdbms/devcdb/DEVCDB/trace/alert_DEVCDB.log

DEVPDB(3):Clearing Resource Manager plan via parameter

PDB2(5):Closing scheduler window

PDB2(5):Closing Resource Manager plan via scheduler window

PDB2(5):Clearing Resource Manager plan via parameter

2022-08-05T16:30:02.138175+05:30

Thread 1 advanced to log sequence 31 (LGWR switch), current SCN: 4303253

Current log# 1 seq# 31 mem# 0: /u01/app/oracle/oradata/DEVCDB/onlinelog/o1_mf_1_kfypzdy8_.log

Current log# 1 seq# 31 mem# 1: /u01/app/oracle/fast_recovery_area/DEVCDB/onlinelog/o1_mf_1_kfypzf45_.log

2022-08-05T20:56:50.597761+05:30

Opatch validation is skipped for PDB DEVDB (con_id=0)

DEVDB:

======

QL> set pagesize 1000;

set linesize 1000;

col STATUS for a10;

col ACTION_TIME format a30;

col DESCRIPTION format a55;

select PATCH_ID,status,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;

SQL> SQL> SQL> SQL> SQL>

PATCH_ID STATUS ACTION_TIME DESCRIPTION

---------- ----------- ------------------------------ -------------------------------------------------------

29517242 SUCCESS 11-JUL-22 09.09.12.789728 AM Database Release Update : 19.3.0.0.190416 (29517242)

33515361 WITH ERRORS 02-AUG-22 09.59.05.092960 AM Database Release Update : 19.14.0.0.220118 (33515361)

SQL>

SQL> column comp_name format a40

column version format a12

column status format a15

select comp_name,version,status from dba_registry;

SQL> SQL> SQL>

COMP_NAME VERSION STATUS

---------------------------------------- ------------ ---------------

Oracle Database Catalog Views 19.0.0.0.0 VALID

Oracle Database Packages and Types 19.0.0.0.0 INVALID

Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF

JServer JAVA Virtual Machine 19.0.0.0.0 VALID

Oracle XDK 19.0.0.0.0 VALID

Oracle Database Java Packages 19.0.0.0.0 VALID

OLAP Analytic Workspace 19.0.0.0.0 VALID

Oracle XML Database 19.0.0.0.0 INVALID

Oracle Workspace Manager 19.0.0.0.0 INVALID

Oracle Text 19.0.0.0.0 VALID

Oracle Multimedia 19.0.0.0.0 VALID

COMP_NAME VERSION STATUS

---------------------------------------- ------------ ---------------

Spatial 19.0.0.0.0 INVALID

Oracle OLAP API 19.0.0.0.0 VALID

Oracle Label Security 19.0.0.0.0 VALID

Oracle Database Vault 19.0.0.0.0 VALID

15 rows selected.

SQL>

DEVCDB:

=======

SQL> set pagesize 1000;

set linesize 1000;

col STATUS for a10;

col ACTION_TIME format a30;

col DESCRIPTION format a55;

select PATCH_ID,status,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;

SQL> SQL> SQL> SQL> SQL>

PATCH_ID STATUS ACTION_TIME DESCRIPTION

---------- ---------- ------------------------------ -------------------------------------------------------

29517242 SUCCESS 26-JUL-22 08.46.22.698373 AM Database Release Update : 19.3.0.0.190416 (29517242)

33515361 SUCCESS 02-AUG-22 10.21.30.647761 AM Database Release Update : 19.14.0.0.220118 (33515361)

SQL>

Solution 1:

Lots of Registry componets were invalid due to that datapatch was failed with error inside DEVDB.

I have fixed the registry componets re-ran the datapatch which went fine.

SQL> column comp_name format a40

column version format a12

column status format a15

select comp_name,version,status from dba_registry;SQL> SQL> SQL>

COMP_NAME VERSION STATUS

---------------------------------------- ------------ ---------------

Oracle Database Catalog Views 19.0.0.0.0 VALID

Oracle Database Packages and Types 19.0.0.0.0 VALID

Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF

JServer JAVA Virtual Machine 19.0.0.0.0 VALID

Oracle XDK 19.0.0.0.0 VALID

Oracle Database Java Packages 19.0.0.0.0 VALID

OLAP Analytic Workspace 19.0.0.0.0 VALID

Oracle XML Database 19.0.0.0.0 VALID

Oracle Workspace Manager 19.0.0.0.0 VALID

Oracle Text 19.0.0.0.0 VALID

Oracle Multimedia 19.0.0.0.0 VALID

COMP_NAME VERSION STATUS

---------------------------------------- ------------ ---------------

Spatial 19.0.0.0.0 VALID

Oracle OLAP API 19.0.0.0.0 VALID

Oracle Label Security 19.0.0.0.0 VALID

Oracle Database Vault 19.0.0.0.0 VALID

15 rows selected.

SQL>

SQL> set pagesize 1000;

set linesize 1000;

col STATUS for a10;

col ACTION_TIME format a30;

col DESCRIPTION format a55;

select PATCH_ID,status,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;SQL> SQL> SQL> SQL> SQL>

PATCH_ID STATUS ACTION_TIME DESCRIPTION

---------- ---------- ------------------------------ -------------------------------------------------------

29517242 SUCCESS 11-JUL-22 09.09.12.789728 AM Database Release Update : 19.3.0.0.190416 (29517242)

33515361 SUCCESS 06-AUG-22 07.58.22.273849 AM Database Release Update : 19.14.0.0.220118 (33515361)

SQL>

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVCDB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 6 20:34:57 2022

Version 19.14.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/u01/patches/DEVDB.xml',

pdb_name => 'DEVDB');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible');

END IF;

END;

/SQL>

compatible >>>>>>>>>>>>>>>>>>>>>>>>>>> Its succeeded now which failed earlier

PL/SQL procedure successfully completed.

SQL>

3. Create or plugin Non-CDB database as PDB inside CDB database

Issue 2:

While creating DEVDB as PDB inside CDB which failed with error message temp file exists.

SQL> create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy;

create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy

ERROR at line 1:

ORA-27038: created file already exists

ORA-01119: error in creating database file

'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_temp_kgvmybcv_.tmp'

SQL>

Alert log error message:

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

Undo Create of Pluggable Database DEVDB with pdb id - 4.

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

ORA-27038 signalled during: create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy...

2022-08-06T20:55:18.071973+05:30

create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy

2022-08-06T20:55:18.084888+05:30

Opatch validation is skipped for PDB DEVDB (con_id=6)

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

Undo Create of Pluggable Database DEVDB with pdb id - 6.

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

ORA-27038 signalled during: create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy...

2022-08-06T20:55:51.372436+05:30

create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy TEMPFILE REUSE

2022-08-06T20:55:51.385142+05:30

Opatch validation is skipped for PDB DEVDB (con_id=7)

DEVDB(7):Endian type of dictionary set to little

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

Pluggable Database DEVDB with pdb id - 7 is created as UNUSABLE.

If any errors are encountered before the pdb is marked as NEW,

then the pdb must be dropped

local undo-1, localundoscn-0x0000000000000009

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

Solution 2:

We I have used temp file reuse command to create DEVDB as PDB inside CDB.

SQL> create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy TEMPFILE REUSE;

Pluggable database created.

SQL>

Alert log message:

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

DEVDB(7):Pluggable database DEVDB pseudo opening

DEVDB(7):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18

DEVDB(7):Autotune of undo retention is turned on.

DEVDB(7):Undo initialization recovery: Parallel FPTR complete: start:1597465044 end:1597465045 diff:1 ms (0.0 seconds)

DEVDB(7):Undo initialization recovery: err:0 start: 1597465044 end: 1597465045 diff: 1 ms (0.0 seconds)

DEVDB(7):[4463] Successfully onlined Undo Tablespace 2.

DEVDB(7):Undo initialization online undo segments: err:0 start: 1597465046 end: 1597465070 diff: 24 ms (0.0 seconds)

DEVDB(7):Undo initialization finished serial:0 start:1597465044 end:1597465071 diff:27 ms (0.0 seconds)

DEVDB(7):Database Characterset for DEVDB is AL32UTF8

DEVDB(7):Pluggable database DEVDB pseudo closing

DEVDB(7):JIT: pid 4463 requesting stop

DEVDB(7):Closing sequence subsystem (1597465245122).

2022-08-06T20:55:52.429393+05:30

DEVDB(7):Buffer Cache flush started: 7

DEVDB(7):Buffer Cache flush finished: 7

Completed: create pluggable database DEVDB using '/u01/patches/DEVDB.xml' nocopy TEMPFILE REUSE

SQL> ALTER PLUGGABLE DATABASE DEVDB open;

Warning: PDB altered with errors.

SQL>

Alert log message:

DEVDB(7):Deleting old file#1 from file$

DEVDB(7):Deleting old file#2 from file$

DEVDB(7):Deleting old file#3 from file$

DEVDB(7):Deleting old file#4 from file$

DEVDB(7):Deleting old file#5 from file$

DEVDB(7):Deleting old file#7 from file$

DEVDB(7):Adding new file#25 to file$(old file#1). fopr-0, newblks-128000, oldblks-64000

DEVDB(7):Adding new file#26 to file$(old file#3). fopr-0, newblks-98560, oldblks-51200

DEVDB(7):Adding new file#27 to file$(old file#4). fopr-0, newblks-78720, oldblks-3200

DEVDB(7):Adding new file#28 to file$(old file#7). fopr-0, newblks-640, oldblks-640

DEVDB(7):Successfully created internal service DEVDB at open

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

Post plug operations are now complete.

Pluggable database DEVDB with pdb id - 7 is now marked as NEW.

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

DEVDB(7):Database Characterset for DEVDB is AL32UTF8

Violations: Type: 1, Count: 1

DEVDB(7):***************************************************************

DEVDB(7):WARNING: Pluggable Database DEVDB with pdb id - 7 is

DEVDB(7): altered with errors or warnings. Please look into

DEVDB(7): PDB_PLUG_IN_VIOLATIONS view for more details.

DEVDB(7):***************************************************************

2022-08-06T20:57:05.441349+05:30

DEVDB(7):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)

DEVDB(7):Opening pdb with no Resource Manager plan active

DEVDB(7):joxcsys_required_dirobj_exists: directory object does not exist, pid 4463 cid 7

DEVDB(7):joxcsys_ensure_directory_object: created directory object with path /u01/app/oracle/product/19.0.0.0/dbhome_1/javavm/admin/, pid 4463 cid 7

Pluggable database DEVDB opened read write

Completed: ALTER PLUGGABLE DATABASE DEVDB open

4. Run noncdb_to_pdb.sql sql script inside newly create PDB

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY YES

3 DEVPDB READ WRITE YES

5 PDB2 READ WRITE YES

7 DEVDB READ WRITE YES

SQL> alter session set container=DEVDB;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;

SQL> SET FEEDBACK 1

SQL> SET NUMWIDTH 10

SQL> SET LINESIZE 80

SQL> SET TRIMSPOOL ON

SQL> SET TAB OFF

SQL> SET PAGESIZE 100

SQL> SET VERIFY OFF

SQL>

SQL> WHENEVER SQLERROR EXIT;

SQL>

SQL> DOC

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01403: no data found"

DOC> error if we're not in a PDB.

DOC> This script is intended to be run right after plugin of a PDB,

DOC> while inside the PDB.

DOC>#######################################################################

DOC>#

SQL>

SQL> VARIABLE cdbname VARCHAR2(128)

SQL> VARIABLE pdbname VARCHAR2(128)

SQL> BEGIN

2 SELECT sys_context('USERENV', 'CDB_NAME')

3 INTO :cdbname

4 FROM dual

5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;

6 SELECT sys_context('USERENV', 'CON_NAME')

7 INTO :pdbname

8 FROM dual

9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';

10 END;

11 /

PL/SQL procedure successfully completed.

SQL>

SQL> @@?/rdbms/admin/loc_to_common0.sql

SQL> Rem

...........................

SQL> alter session set "_enable_view_pdb"=false;

Session altered.

SQL>

SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2022-08-06 21:00:37

1 row selected.

SQL>

SQL> DOC

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

SQL>

SQL> DECLARE

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

6 /

...........................

SQL> alter session set "_enable_view_pdb"=false;

Session altered.

SQL>

SQL> -- leave the PDB in the same state it was when we started

SQL> BEGIN

2 execute immediate '&open_sql &restricted_state';

3 EXCEPTION

4 WHEN OTHERS THEN

5 BEGIN

6 IF (sqlcode <> -900) THEN

7 RAISE;

8 END IF;

9 END;

10 END;

11 /

PL/SQL procedure successfully completed.

SQL>

SQL> WHENEVER SQLERROR CONTINUE;

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY YES

3 DEVPDB READ WRITE YES

5 PDB2 READ WRITE YES

7 DEVDB MOUNTED

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 6 21:09:05 2022

Version 19.14.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY YES

3 DEVPDB READ WRITE YES

5 PDB2 READ WRITE YES

7 DEVDB MOUNTED

SQL>

SQL> alter pluggable database DEVDB open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY YES

3 DEVPDB READ WRITE YES

5 PDB2 READ WRITE YES

7 DEVDB READ WRITE NO

SQL>

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kgvmtpcn_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_kgvmw3hs_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_kgvmwwmm_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_kgvmwxo3_.dbf

SQL>

Note:

We have already plugged or create DEVDB as PDB inside DEVCDB, If we try to open DEVDB as normal database which will not open.

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DEVDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 6 21:11:21 2022

Version 19.14.0.0.0

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690985848 bytes

Fixed Size 8903032 bytes

Variable Size 721420288 bytes

Database Buffers 2952790016 bytes

Redo Buffers 7872512 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1:

'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kgvmtpcn_.dbf'

SQL>

Try to shuldwon PDB (DEVDB) from DEVCDB and try to open normal DEVDB database which will fail again

Once you create or plugin node database DEVDB as PDB inside DEVCDB, you dont have any option to revet that back.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

7 DEVDB READ WRITE NO

SQL>

SQL> shut immediate;

Pluggable Database closed.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

7 DEVDB MOUNTED

SQL> conn / as sysdba

Connected.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY YES

3 DEVPDB READ WRITE YES

5 PDB2 READ WRITE YES

7 DEVDB MOUNTED

SQL>

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 6 21:14:36 2022

Version 19.14.0.0.0

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690985848 bytes

Fixed Size 8903032 bytes

Variable Size 721420288 bytes

Database Buffers 2952790016 bytes

Redo Buffers 7872512 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1:

'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kgvmtpcn_.dbf'

ORA-01204: file number is 25 rather than 1 - wrong file

SQL>

Regards,

Mallik

Mallikarjun Ramadurg

Thursday, August 4, 2022

RMAN Commands - Hands On!!!

RMAN Commands, All you want to know about RMAN!!!

Backup Commands:

Backup database and archivelog into FRA location

RMAN> backup database;

RMAN> backup database plus archivelog;

RMAN> backup archivelog all;

Backup database and archivelog into backup location

RMAN> backup database format '/u01/backup/DB_Full_%d_%T_%U';

RMAN> backup database format '/u01/backup/DB_Full_%d_%T_%U' plus archivelog format '/u01/backup/Archive_%d_%T_%U';

RMAN> backup archivelog all format '/u01/backup/Archivelog_%d_%T_%U';

Backup spfile and controlfile into FRA location

RMAN> backup spfile;

RMAN> backup current controlfile;

Backup spfile and controlfile into backup location

RMAN> backup spfile format '/u01/backup/spfile_%d_%T_%U';

RMAN> backup current controlfile format '/u01/backup/controlfile_%d_%T_%U';

Backup standby controlfile

RMAN> backup current controlfile for standby;

RMAN> backup current controlfile for standby format '/u01/backup/standby_controlfile_%d_%T_%U';

RMAN> backup database plus archivelog delete input;

RMAN> backup database plus archivelog delete all input;

List Commands:

List all datafiles and all archivelogs of a database

RMAN> REPORT SCHEMA;

RMAN> list archivelog all;

List everything, list of backupsets of everything

RMAN> LIST BACKUP SUMMARY;

RMAN> list backup;

List of backupset of spfile and controlfile

RMAN> list backup of spfile;

RMAN> list backup of controlfile;

List of copy of controlfile

RMAN> list copy of controlfile;

RMAN> LIST CONTROLFILECOPY <key>;

RMAN> LIST CONTROLFILECOPY 3333;

List of backupset of databse

RMAN> list backup of database summary;

RMAN> list backup of database;

List of copy of databse

RMAN> list copy of database;

List of backupset of archivelog all

RMAN> list backup of archivelog all summary;

RMAN> list backup of archivelog all;

List of copy of archivelog all

RMAN> list copy of archivelog all;

List specific datafile backup as backupset

RMAN> LIST BACKUP OF DATAFILE 4;

RMAN> LIST BACKUP OF DATAFILE '/u01/app/oradata/TEST/users01.dbf';

List a specific copy of datafile or all datafile or specific backup key of a datafile

RMAN> LIST DATAFILECOPY ALL;

RMAN> list copy of datafile 4;

RMAN> LIST DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

RMAN> LIST DATAFILECOPY <Key>;

RMAN> LIST DATAFILECOPY 2222;

List specific backupset

RMAN> LIST BACKUPSET <key>;

RMAN> LIST BACKUPSET 1111;

List backupset or copy of a specific tablespace

RMAN> list backup of tablespace users;

RMAN> list copy of tablespace users;

List expired backupset and copy of everything

RMAN> LIST EXPIRED BACKUP;

RMAN> list expired backup of database;

RMAN> list EXPIRED archivelog all;

RMAN> list EXPIRED backup of archivelog all;

RMAN> list EXPIRED copy of archivelog all;

RMAN> LIST EXPIRED DATAFILECOPY ALL;

RMAN> LIST EXPIRED copy of datafile 4;

RMAN> LIST EXPIRED DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

RMAN> list expired BACKUP OF DATAFILE 4;

RMAN> list expired BACKUP OF DATAFILE '/u01/app/oradata/TEST/users01.dbf';

RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;

RMAN> LIST EXPIRED copy OF TABLESPACE USERS;

Expired Backups

Handling expired backups

RMAN> LIST EXPIRED BACKUP;

RMAN> list expired backup of database;

RMAN> crosscheck BACKUP;

RMAN> crosscheck backup of database;

RMAN> delete noprompt EXPIRED BACKUP;

RMAN> delete noprompt expired backup of database;

Handling expired Archivelogs

RMAN> list EXPIRED archivelog all;

RMAN> list EXPIRED backup of archivelog all;

RMAN> list EXPIRED copy of archivelog all;

RMAN> crosscheck archivelog all;

RMAN> crosscheck backup of archivelog all;

RMAN> crosscheck copy of archivelog all;

RMAN> delete noprompt EXPIRED archivelog all;

RMAN> delete noprompt EXPIRED backup of archivelog all;

RMAN> delete noprompt EXPIRED copy of archivelog all;

Handling expired datafiles

RMAN> LIST DATAFILECOPY ALL;

RMAN> LIST copy of datafile 4;

RMAN> LIST DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

RMAN> list BACKUP OF DATAFILE 4;

RMAN> list BACKUP OF DATAFILE '/u01/app/oradata/TEST/users01.dbf';

RMAN> LIST EXPIRED DATAFILECOPY ALL;

RMAN> LIST EXPIRED copy of datafile 4;

RMAN> LIST EXPIRED DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

RMAN> list expired BACKUP OF DATAFILE 4;

RMAN> list expired BACKUP OF DATAFILE '/u01/app/oradata/TEST/users01.dbf';

RMAN> crosscheck DATAFILECOPY ALL;

RMAN> crosscheck copy of datafile 4;

RMAN> crosscheck DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

RMAN> crosscheck BACKUP OF DATAFILE 4;

RMAN> crosscheck BACKUP OF DATAFILE '/u01/app/oradata/TEST/users01.dbf';

Handling expired TABLESPACE

RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;

RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;

RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;

-----------------

To query details about past and current RMAN jobs:

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

SELECT SESSION_KEY, INPUT_TYPE, STATUS,

TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

ELAPSED_SECONDS/3600 hrs

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;

The following sample output shows the backup job history:

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

----------- ------------- --------- -------------- -------------- -------

9 DATAFILE FULL COMPLETED 04/18/07 18:14 04/18/07 18:15 .02

16 DB FULL COMPLETED 04/18/07 18:20 04/18/07 18:22 .03

113 ARCHIVELOG COMPLETED 04/23/07 16:04 04/23/07 16:05 .01

COL in_sec FORMAT a10

COL out_sec FORMAT a10

COL TIME_TAKEN_DISPLAY FORMAT a10

SELECT SESSION_KEY,

OPTIMIZED,

COMPRESSION_RATIO,

INPUT_BYTES_PER_SEC_DISPLAY in_sec,

OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,

TIME_TAKEN_DISPLAY

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;

The following sample output shows the speed of the backup jobs:

SESSION_KEY OPT COMPRESSION_RATIO IN_SEC OUT_SEC TIME_TAKEN

----------- --- ----------------- ---------- ---------- ----------

9 NO 1 8.24M 8.24M 00:01:14

16 NO 1.32732239 6.77M 5.10M 00:01:45

113 NO 1 2.99M 2.99M 00:00:44

https://docs.oracle.com/cd/E18283_01/backup.112/e10642/rcmreprt.htm

http://www.juliandyke.com/Research/RMAN/ListCommand.php

Regards,

Mallik

Mallikarjun Ramadurg

Vismo Technologies — Oracle DBA Knowledge Hub

Monday, August 15, 2022

ORA-15017: diskgroup "VOTE2" cannot be mounted on cluster nodes

Reuse disk and Create Diskgrup using old disk and mount diskgroup:

Saturday, August 6, 2022

Convert Non-CDB database as PDB inside CDB database

Converting a non-CDB to a PDB

Thursday, August 4, 2022

RMAN Commands - Hands On!!!

RMAN Commands, All you want to know about RMAN!!!

Backup Commands:

Backup database and archivelog into FRA location

Backup database and archivelog into backup location

Backup spfile and controlfile into FRA location

Backup spfile and controlfile into backup location

Backup standby controlfile

List Commands:

List all datafiles and all archivelogs of a database

List everything, list of backupsets of everything

List of backupset of spfile and controlfile

List of copy of controlfile

List of backupset of databse

List of copy of databse

List of backupset of archivelog all

List of copy of archivelog all

List specific datafile backup as backupset

List a specific copy of datafile or all datafile or specific backup key of a datafile

List specific backupset

List backupset or copy of a specific tablespace

List expired backupset and copy of everything

Expired Backups

Handling expired backups

Handling expired Archivelogs

Handling expired datafiles

Handling expired TABLESPACE

🌟 Proud to be an Oracle ACE Pro! 🚀

Social Plugin

Blog Archive