Friday, 28 April 2023

Recovering control file from Oracle database

RMAN> backup database;

Starting backup at 28-APR-23

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf

channel ORA_DISK_1: starting piece 1 at 28-APR-23

channel ORA_DISK_1: finished piece 1 at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_04_28/o1_mf_nnndf_TAG20230428T221502_l4qy0gcj_.bkp tag=TAG20230428T221502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 28-APR-23

channel ORA_DISK_1: finished piece 1 at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_04_28/o1_mf_nnndf_TAG20230428T221502_l4qy2sjf_.bkp tag=TAG20230428T221502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 28-APR-23

channel ORA_DISK_1: finished piece 1 at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_04_28/o1_mf_nnndf_TAG20230428T221502_l4qy3ll3_.bkp tag=TAG20230428T221502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 28-APR-23

Starting Control File and SPFILE Autobackup at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_04_28/o1_mf_s_1135376217_l4qy41rz_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 28-APR-23

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/ORCL/c

ontrol01.ctl, /u01/app/oracle/

fast_recovery_area/ORCL/contro

l02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

diagnostics_control string IGNORE

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rm /u01/app/oracle/oradata/ORCL/control01.ctl

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:19:23 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> shu abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size 9694088 bytes

Variable Size 306184192 bytes

Database Buffers 754974720 bytes

Redo Buffers 2887680 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Apr 28 22:22:28 2023

Version 21.3.0.0.0

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_04_28/o1_mf_s_1135376217_l4qy41rz_.bkp';

Starting restore at 28-APR-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=422 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ORCL/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

Finished restore at 28-APR-23

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 04/28/2023 22:23:00

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 04/28/2023 22:23:08

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:23:12 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using BACKUP CONTROLFILE ;

ORA-00279: change 4671729 generated at 04/28/2023 22:15:26 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_7_l4qy8174

_.arc

ORA-00280: change 4671729 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 4671801 generated at 04/28/2023 22:19:05 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_8_l4qy8477

_.arc

ORA-00280: change 4671801 for thread 1 is in sequence #8

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_7_l4qy817

4_.arc' no longer needed for this recovery

ORA-00279: change 4671815 generated at 04/28/2023 22:19:08 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_9_l4qy877k

_.arc

ORA-00280: change 4671815 for thread 1 is in sequence #9

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_8_l4qy847

7_.arc' no longer needed for this recovery

ORA-00279: change 4671823 generated at 04/28/2023 22:19:11 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_10_l4qy8b5

x_.arc

ORA-00280: change 4671823 for thread 1 is in sequence #10

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_9_l4qy877

k_.arc' no longer needed for this recovery

ORA-00279: change 4671831 generated at 04/28/2023 22:19:14 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_11_%u_.arc

ORA-00280: change 4671831 for thread 1 is in sequence #11

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_10_l4qy8b

5x_.arc' no longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_11_%u_.ar

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

SQL>

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Apr 28 22:23:52 2023

Version 21.3.0.0.0

connected to target database: ORCL (DBID=1644501746, not open)

RMAN> recover database;

Starting recover at 28-APR-23

Starting implicit crosscheck backup at 28-APR-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=430 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 28-APR-23

Starting implicit crosscheck copy at 28-APR-23

using channel ORA_DISK_1

Finished implicit crosscheck copy at 28-APR-23

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_04_28/o1_mf_s_1135376217_l4qy41rz_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/oradata/ORCL/redo02.log

archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=11

media recovery complete, elapsed time: 00:00:00

Finished recover at 28-APR-23

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:24:12 2023

Version 21.3.0.0.0

sel

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

ORCL READ WRITE

Optimizer settings in 21C in Oracle

SQL> show parameter optimizer

NAME TYPE VALUE

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

optimizer_adaptive_plans boolean TRUE

optimizer_adaptive_reporting_only boolean FALSE

optimizer_adaptive_statistics boolean FALSE

optimizer_capture_sql_plan_baselines boolean FALSE

optimizer_capture_sql_quarantine boolean FALSE

optimizer_cross_shard_resiliency boolean FALSE

optimizer_dynamic_sampling integer 2

optimizer_features_enable string 21.1.0

optimizer_ignore_hints boolean FALSE

optimizer_ignore_parallel_hints boolean FALSE

optimizer_index_caching integer 0

optimizer_index_cost_adj integer 100

optimizer_inmemory_aware boolean TRUE

optimizer_mode string ALL_ROWS

optimizer_real_time_statistics boolean FALSE

optimizer_secure_view_merging boolean TRUE

optimizer_session_type string NORMAL

optimizer_use_invisible_indexes boolean FALSE

optimizer_use_pending_statistics boolean FALSE

optimizer_use_sql_plan_baselines boolean TRUE

optimizer_use_sql_quarantine boolean TRUE

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:17:53 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> select * from v$version;

BANNER

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

BANNER_FULL

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

BANNER_LEGACY

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

CON_ID

----------

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

BANNER

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

BANNER_FULL

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

BANNER_LEGACY

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

CON_ID

----------

Tuesday, 11 October 2022

Causes of ORA-6502 Errors (PL/SQL: numeric or value error)

1) A common reason for this error can be when there is a mismatch between the data type of the variable and the value that is being assigned to it. For Example: Assigning character value to a numeric data type or assigning more value to a variable than it can hold.

2) However its interesting that this issue can be caused by the mismatch between the client side NLS_LANG and NLS_CHARACTERSET of the database.

So the thing is when there is a mismatch between client side character set NLS_LANG and the database character set NLS_CHARACTERSET this can occur. When working with UNICODE database (UTF8/AL32UTF8), make sure that:

The NLS_LANG is not set at the client side. When NLS_LANG is not set, it will use database character set.

If NLS_LANG is set, then make sure PL/SQL bind variables is declared with enough buffer size so that the data conversion does not fail.

Some common code related issues are shown below:

SET SERVEROUTPUT ON

DECLARE

V_VAR1 VARCHAR2(10);

V_VAR2 NUMBER;

BEGIN

V_VAR2 := 'MIKE'; /* VALID ORA-6502 */

V_VAR1 := 'ABCDEFGHIKL'; /* VALID ORA-6502 */

END;

SET SERVEROUTPUT ON

DECLARE

V_VAR1 VARCHAR2(3);

BEGIN

SELECT 'ABCD' INTO V_VAR1 FROM DUAL;

END;

CREATE OR REPLACE FUNCTION

TEST_6502_ERROR

RETURN VARCHAR2

BEGIN

RETURN 'ASDF';

END;

SET SERVEROUTPUT ON

DECLARE

V_GET_VALUE VARCHAR2(3);

BEGIN

V_GET_VALUE := TEST_6502_ERROR;

END;

Exadata Storage : Grid disks based on the OFFSETs.

The Cells storage servers in Exadata offer the capability to create grid disks based on their offsets, Create the new grid disks in the order BASED ON THE OFFSET, using the sizes shown from the command list griddisk attributes name,size,offset.

Lets see how we can do it :

An example :

CellCLI> create griddisk DATA_CD_07_<Host Name> celldisk=CD_07_<Host Name>,size=220G

CellCLI> create griddisk RECO_CD_07_<Host Name> celldisk=CD_07_<Host Name>,size=308.6875G

CellCLI> create griddisk SYSTEMDG_CD_07_<Host Name> celldisk=CD_07_<Host Name>,size=29.125G

Run the previous query on the new grid disks and make sure all the offsets are matching (in the third column):

CellCLI> list griddisk where celldisk=CD_07_<Host Name> attributes name,size,offset

DATA_CD_07_<Host Name> 220G 32M

RECO_CD_07_<Host Name> 308.6875G 220.046875G

SYSTEMDG_CD_07_<Host Name> 29.125G 528.734375G

At the ASM level, the old disk was likely dropped because the disk drop timer has expired. Log into +ASM1 instance and add the new disk. Set the rebalance power higher (11) to perform faster and specify the failgroup if the default (name of Storage Server) is not used.

sql> alter diskgroup data drop disk DATA_CD_07_<Host Name> force;

sql> alter diskgroup data add {failgroup <FAILGROUP>} disk 'o/*/DATA_CD_07_<Host Name>' rebalance power 11;

Do the same for RECO and SYSTEMDG from a different ASM instance (to balance the work) or wait until DATA finishes on +ASM1.

If after disk replacement old celldisk and griddidsk are in state proactive failure or any other that prevents them from being deleted, try the following:

celcli> ALTER CELL RESTART SERVICES cellsrv

celcli> alter lun {lun number} reenable force

for example :

celcli> alter lun 0_0 reenable force

After this, delete cell and griddisks that corresponds to the physical disk

(it may be even done automatically).

Next, create cell disk and griddisk.

Finally after griddisks are created they should be presented in ASM but marked as candidates, but will need to be enabled.

Thursday, 29 September 2022

Resolving error RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

Error RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode:

This error comes when database is not in archivelog mode, in order to resolve this lets see how it can be done :

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Sep 30 01:17:37 2022

Version 21.3.0.0.0

connected to target database: ORCL:PDB1 (DBID=3054857248)

RMAN> backup database;

Starting backup at 30-SEP-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=467 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 09/30/2022 01:17:42

RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 30 01:17:49 2022

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> shu immediate

Pluggable Database closed.

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ unset ORACLE_PDB_SID

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 30 01:18:04 2022

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size 9694088 bytes

Variable Size 301989888 bytes

Database Buffers 759169024 bytes

Redo Buffers 2887680 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Sep 30 01:20:14 2022

Version 21.3.0.0.0

connected to target database: ORCL (DBID=1644501746)

RMAN> backup database;

Starting backup at 30-SEP-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=456 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf

channel ORA_DISK_1: starting piece 1 at 30-SEP-22

channel ORA_DISK_1: finished piece 1 at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_09_30/o1_mf_nnndf_TAG20220930T012018_kmcxqtjt_.bkp tag=TAG20220930T012018 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 30-SEP-22

channel ORA_DISK_1: finished piece 1 at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2022_09_30/o1_mf_nnndf_TAG20220930T012018_kmcxs7p8_.bkp tag=TAG20220930T012018 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 30-SEP-22

channel ORA_DISK_1: finished piece 1 at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2022_09_30/o1_mf_nnndf_TAG20220930T012018_kmcxsprj_.bkp tag=TAG20220930T012018 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 30-SEP-22

Starting Control File and SPFILE Autobackup at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2022_09_30/o1_mf_s_1116724881_kmcxssyx_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 30-SEP-22

>> Backup completed successfully now.

How REGEXP_LIKE Condition works in Oracle database

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

SQL> select * from scott.emp;

EMPNO FIRST_NAME LAST_NAME SALARY COMM HIREDATE

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

101 Scott Tiger 2000 10 30-SEP-22

102 John Smith 2000 10 30-SEP-22

103 Ram Hari 2000 10 30-SEP-22

104 Rakesh Sharma 4000 10 30-SEP-22

Lets say if we want find all employees whose first_name starts with R. Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'^R');

EMPNO FIRST_NAME LAST_NAME SALARY COMM HIREDATE

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

103 Ram Hari 2000 10 30-SEP-22

104 Rakesh Sharma 4000 10 30-SEP-22

Lets say if we want find all employees whose first_name starts with R but 'a' in the middle and m at the end, note here the use of paranthesis and $ sign for middle and last alphabet. Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'^R(a)m$');

EMPNO FIRST_NAME LAST_NAME SALARY COMM HIREDATE

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

103 Ram Hari 2000 10 30-SEP-22

Lets say if we want find all employees whose first_name end with 'h' , note the use of $ symbol Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'h$');

EMPNO FIRST_NAME LAST_NAME SALARY COMM HIREDATE

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

104 Rakesh Sharma 4000 10 30-SEP-22

Lets say if we want find all employees whose first_name starts with 'Ra' but it can be Ram or Rakesh so in the middle with paranthesis and pipe we use (m|k) and note it can be anything in the end so here we did not give any last alphabet condition so both Ram and Rakesh are printed. Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'^Ra(m|k)');

EMPNO FIRST_NAME LAST_NAME SALARY COMM HIREDATE

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

103 Ram Hari 2000 10 30-SEP-22

104 Rakesh Sharma 4000 10 30-SEP-22

Tuesday, 20 September 2022

How Indentation works in Python

Lets say we want to write a python program to check whether a variable has a value 10 or not.

Code :

var = 10

if (var == 10):

print(' A is 10')

Error :

File "<ipython-input-53-61659dc01be1>", line 3

    print(' A is 10')
    ^
IndentationError: expected an indented block

>> As we can see above we get the above error. This means the print statement is not as per expected indentation. Lets see if put it in the right way like below :

var = 10
if (var == 10):
  print(' A is 10')

In above code i try to shift/move the print statement a bit towards right side with a couple of spaces it could also be possible with tab and it works fine now.

 A is 10
In [ ]:

It prints the expected statement now as above we can see the actual cell is displayed .

Oracle Checkpoint

Friday, 28 April 2023

Recovering control file from Oracle database

Optimizer settings in 21C in Oracle

Tuesday, 11 October 2022

Causes of ORA-6502 Errors (PL/SQL: numeric or value error)

Exadata Storage : Grid disks based on the OFFSETs.

Thursday, 29 September 2022

Resolving error RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

How REGEXP_LIKE Condition works in Oracle database

Tuesday, 20 September 2022

How Indentation works in Python

Blog Archive

About Me