Saturday 27 May 2023

Startup Modes in Oracle Database

 In this blog post we will learn Startup Modes in Oracle Database

There are 3 states in which Oracle instance can be started i.e nomount, mount and open. Remember you can go directory to open from scratch i.e when database is shutdown and you can also go directly to mount and as always you can go to mount from nomount or you can go to open from mount as well. Its like climbing stairs in order.

1) First mode is nomount, in this mode only spfile/parameter is read and Oracle instance SGA memory is allocated :- Lets see :


Verify database instance is down :

[oracle@node2 ~]$ ps -ef  | grep smon

oracle     3597   2134  0 11:49 pts/0    00:00:00 grep --color=auto smon

Now lets start instance in nomount mode first as it is the first stage,

[oracle@node2 ~]$ sqlplus / as sysdba


SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 28 11:49:10 2023

Version 21.3.0.0.0


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


Connected to an idle instance.


SQL> startup nomount

ORACLE instance started.


Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             469762048 bytes

Database Buffers          591396864 bytes

Redo Buffers                2887680 bytes

SQL> select status from v$instance;

STATUS
------------
STARTED

#Here  started means nomount mode

2) Now lets go to mount mode :

In this mode controlfile of Oracle is read but physical existence of datafiles is not checked.

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

3) In open mode datafiles existence is also verified and if they need recovery these things are checked during mount to open phase.

SQL>  alter database open;


Database altered.

SQL> SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE


Now note this is read write mode, means application can do writes and reads a typical mode of Oracle primady database, you can also open in read only mode the instance instead of read write. Lets see how we can do but note you need to shutdown first the database in order to come back to read only. Lets see if we can try and do it :

SQL> SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

SQL>  alter database open read only;
 alter database open read only
*
ERROR at line 1:
ORA-01531: a database already open by the instance

# Lets shutdown now :

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

# Startup now to mount mode , because you cannot alter database from nomount to open mode, lets see :

ORACLE instance shut down.
SQL> SQL>
SQL>
SQL> startup nomount

ORACLE instance started.

Total System Global Area 1073740680 bytes
Fixed Size                  9694088 bytes
Variable Size             469762048 bytes
Database Buffers          591396864 bytes
Redo Buffers                2887680 bytes

SQL>  alter database open read only;
 alter database open read only
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>  select status from v$instance;

STATUS
------------
OPEN

SQL>  select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ ONLY


# Also with a single short cut startup force you can shutdown abort and startup in read write mode in a single shot , whichever mode you are currently in , lets see we can do it :

SQL> startup force

ORACLE instance started.

Total System Global Area 1073740680 bytes
Fixed Size                  9694088 bytes
Variable Size             469762048 bytes
Database Buffers          591396864 bytes
Redo Buffers                2887680 bytes
Database mounted.
Database opened.
SQL>  select status from v$instance;

STATUS
------------
OPEN

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

Alert log contents :

SQL> select value from v$diag_info;

VALUE
--------------------------------------------------------------------------------
TRUE
/u01/app/oracle
/u01/app/oracle/diag/rdbms/orcl/orcl
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4693.trc
0
0

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/21.3.0
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/attention_orcl.log

13 rows selected.

SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@node2 ~]$ tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
2023-05-28T12:10:24.990363+05:30
CJQ0 started with pid=66, OS id=4991
Completed: ALTER DATABASE OPEN
2023-05-28T12:10:28.316040+05:30
Setting Resource Manager plan SCHEDULER[0x52C4]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
PDB1(3):Setting Resource Manager plan SCHEDULER[0x52C1]:DEFAULT_MAINTENANCE_PLAN via scheduler window
PDB1(3):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2023-05-28T12:10:43.793248+05:30
Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c4] because plan is already set.
^C
[oracle@node2 ~]$ tail -200f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
2023-05-28T12:09:01.079236+05:30
ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'
Starting background process PMON
2023-05-28T12:09:01.087835+05:30
PMON started with pid=2, OS id=4579
Starting background process CLMN
2023-05-28T12:09:01.096427+05:30
CLMN started with pid=3, OS id=4583
Starting background process PSP0
2023-05-28T12:09:01.105050+05:30
PSP0 started with pid=4, OS id=4587
Starting background process VKTM
2023-05-28T12:09:01.114083+05:30
VKTM started with pid=5, OS id=4591 at elevated (RT) priority
2023-05-28T12:09:01.114836+05:30
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
2023-05-28T12:09:01.122772+05:30
GEN0 started with pid=6, OS id=4597
Starting background process MMAN
2023-05-28T12:09:01.131288+05:30
MMAN started with pid=7, OS id=4601
Starting background process GEN1
Starting background process GEN2
Starting background process VOSD
Starting background process DIAG
Starting background process OFSD
Starting background process DBRM
Starting background process VKRM
Starting background process SVCB
Starting background process PMAN
Starting background process DIA0
Starting background process DBW0
Starting background process LGWR
Starting background process CKPT
Starting background process SMON
Starting background process SMCO
Starting background process RECO
Starting background process LREG
Starting background process PXMN
Starting background process FENC
Starting background process MMON
Starting background process MMNL
2023-05-28T12:09:01.189362+05:30
GEN1 started with pid=9, OS id=4607_4609
2023-05-28T12:09:01.234103+05:30
GEN2 started with pid=11, OS id=4610
2023-05-28T12:09:01.260151+05:30
DIAG started with pid=13, OS id=4617
2023-05-28T12:09:01.262894+05:30
VOSD started with pid=12, OS id=4613
2023-05-28T12:09:01.304813+05:30
OFSD started with pid=14, OS id=4620_4622
2023-05-28T12:09:01.306459+05:30
Oracle running with ofslib:'Oracle File Server Library' version=2 inc=1
2023-05-28T12:09:01.349021+05:30
VKRM started with pid=17, OS id=4626
2023-05-28T12:09:01.350636+05:30
DBRM started with pid=16, OS id=4623
2023-05-28T12:09:01.377093+05:30
SVCB started with pid=18, OS id=4629
2023-05-28T12:09:01.435357+05:30
DIA0 started with pid=20, OS id=4634
2023-05-28T12:09:01.439370+05:30
PMAN started with pid=19, OS id=4631
2023-05-28T12:09:01.464040+05:30
DBW0 started with pid=21, OS id=4637
2023-05-28T12:09:01.479582+05:30
CKPT started with pid=23, OS id=4644
2023-05-28T12:09:01.481077+05:30
LGWR started with pid=22, OS id=4640 at elevated (RT) priority
2023-05-28T12:09:01.489370+05:30
SMCO started with pid=25, OS id=4654
2023-05-28T12:09:01.489751+05:30
SMON started with pid=24, OS id=4649
2023-05-28T12:09:01.497269+05:30
RECO started with pid=26, OS id=4657
2023-05-28T12:09:01.510576+05:30
PXMN started with pid=28, OS id=4662
2023-05-28T12:09:01.512162+05:30
LREG started with pid=27, OS id=4659
2023-05-28T12:09:01.526890+05:30
MMON started with pid=30, OS id=4668
2023-05-28T12:09:01.528532+05:30
FENC started with pid=29, OS id=4665
2023-05-28T12:09:01.542807+05:30
MMNL started with pid=31, OS id=4671
2023-05-28T12:09:01.543530+05:30
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Starting background process TMON
2023-05-28T12:09:01.605236+05:30
TMON started with pid=42, OS id=4691
RACD notifier running level=0
ORACLE_BASE from environment = /u01/app/oracle
2023-05-28T12:09:01.652008+05:30
ALTER DATABASE   MOUNT
2023-05-28T12:09:01.666269+05:30
Successful mount of redo thread 1, with mount id 1665802461
2023-05-28T12:09:01.666479+05:30
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
2023-05-28T12:09:01.725771+05:30
ALTER DATABASE OPEN
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.
2023-05-28T12:09:01.729878+05:30
Crash Recovery excluding pdb 2 which was cleanly closed.
2023-05-28T12:09:01.729920+05:30
Crash Recovery excluding pdb 3 which was cleanly closed.
Endian type of dictionary set to little
Starting background process ARC0
2023-05-28T12:09:01.740515+05:30
ARC0 started with pid=44, OS id=4695
2023-05-28T12:09:01.752033+05:30
Redo log for group 3, sequence 21 is not located on DAX storage
Thread 1 opened at log sequence 21
  Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ORCL/redo03.log
Successful open of redo thread 1
2023-05-28T12:09:01.755723+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Starting background process ARC1
2023-05-28T12:09:01.838258+05:30
ARC1 started with pid=48, OS id=4703
Starting background process ARC2
Undo initialization recovery: Parallel FPTR complete: start:2097012 end:2097013 diff:1 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 2097012 end: 2097013 diff: 1 ms (0.0 seconds)
2023-05-28T12:09:01.873465+05:30
ARC2 started with pid=49, OS id=4705
Starting background process ARC3
2023-05-28T12:09:01.917247+05:30
ARC3 started with pid=50, OS id=4707
[4693] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 2097013 end: 2097094 diff: 81 ms (0.1 seconds)
Undo initialization finished serial:0 start:2097012 end:2097099 diff:87 ms (0.1 seconds)
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process RCBG
2023-05-28T12:09:02.307179+05:30
RCBG started with pid=52, OS id=4711
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
2023-05-28T12:09:02.522697+05:30
AQPC started with pid=53, OS id=4713
PDB$SEED(2):Pluggable database PDB$SEED opening in read only
PDB$SEED(2):Autotune of undo retention is turned on.
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:2097868 end:2097868 diff:0 ms (0.0 seconds)
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2023-05-28T12:09:03.056673+05:30
PDB$SEED(2):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)
PDB$SEED(2):Opening pdb with no Resource Manager plan active
PDB1(3):Pluggable database PDB1 opening in read write
PDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):Undo initialization recovery: Parallel FPTR complete: start:2098719 end:2098722 diff:3 ms (0.0 seconds)
PDB1(3):Undo initialization recovery: err:0 start: 2098719 end: 2098723 diff: 4 ms (0.0 seconds)
PDB1(3):[4693] Successfully onlined Undo Tablespace 2.
PDB1(3):Undo initialization online undo segments: err:0 start: 2098723 end: 2098868 diff: 145 ms (0.1 seconds)
PDB1(3):Undo initialization finished serial:0 start:2098719 end:2098874 diff:155 ms (0.2 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2023-05-28T12:09:04.092265+05:30
PDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2023-05-28T12:09:04.107703+05:30
 :
QPI: opatch file present, opatch
 :
QPI: qopiprep.bat file present
2023-05-28T12:09:04.357949+05:30
Using default pga_aggregate_limit of 2048 MB
2023-05-28T12:09:04.601138+05:30
PDB1(3):Opening pdb with no Resource Manager plan active
Pluggable database PDB1 opened read write
2023-05-28T12:09:06.842646+05:30
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2023-05-28T12:09:07.447556+05:30
db_recovery_file_dest_size of 13896 MB is 34.33% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2023-05-28T12:10:24.982897+05:30
Starting background process CJQ0
2023-05-28T12:10:24.990363+05:30
CJQ0 started with pid=66, OS id=4991
Completed: ALTER DATABASE OPEN
2023-05-28T12:10:28.316040+05:30
Setting Resource Manager plan SCHEDULER[0x52C4]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
PDB1(3):Setting Resource Manager plan SCHEDULER[0x52C1]:DEFAULT_MAINTENANCE_PLAN via scheduler window
PDB1(3):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2023-05-28T12:10:43.793248+05:30
Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c4] because plan is already set.




# You learnt the 3 modes of Oracle in this post.
Posted by at No comments:

Starting Oracle processes via shell script

 In this post we will learn how to start Oracle processes via shell script


Lets write a script to start listener and then the database :


[oracle@node2 ~]$ cat start.sh

ORACLE_SID=orcl

ORAENV_ASK=NO

. oraenv

lsnrctl stop

lsnrctl start

sqlplus -s /nolog  <<EOF

connect / as sysdba

startup

exit;

EOF

>> Now lets execute the script :

[oracle@node2 ~]$ ./start.sh
The Oracle base remains unchanged with value /u01/app/oracle

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 28-MAY-2023 11:41:41

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2.example.com)(PORT=1521)))
The command completed successfully

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 28-MAY-2023 11:41:41

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/21.3.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 21.0.0.0.0 - Production
System parameter file is /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/node2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node2.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                28-MAY-2023 11:41:41
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/node2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node2.example.com)(PORT=1521)))
The listener supports no services
The command completed successfully
ORACLE instance started.

Total System Global Area 1073740680 bytes
Fixed Size                  9694088 bytes
Variable Size             448790528 bytes
Database Buffers          612368384 bytes
Redo Buffers                2887680 bytes
Database mounted.
Database opened.

>> Job done. 

Posted by at No comments:
Labels:

Monday 22 May 2023

Enabling encryption in RMAN Utility Oracle :

 Enabling encryption in RMAN Oracle :

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/dbs/snapcf_orcl.f'; # default


RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;


new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters are successfully stored



You can explicitly override the persistent encryption configuration for an RMAN session with the following command:


SET ENCRYPTION ON;


The encryption setting remains in effect until you issue the SET ENCRYPTION OFF command during an RMAN session, or change the persistent setting again with the following command:


CONFIGURE ENCRYPTION FOR DATABASE OFF;



# Lets try this command in RMAN prompt :



RMAN> CONFIGURE ENCRYPTION FOR DATABASE OFF;


old RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE OFF;

new RMAN configuration parameters are successfully stored



# Lets configure now ENCRYPTION ALGORITHM 'AES256' 


RMAN> CONFIGURE ENCRYPTION FOR DATABASE OFF;


old RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE OFF;

new RMAN configuration parameters are successfully stored


RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256' ;


old RMAN configuration parameters:

CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters:

CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters are successfully stored


RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;


old RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE OFF;

new RMAN configuration parameters:

CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters are successfully stored


Posted by at No comments:
Labels:

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

 In Oracle when you see ORA-00205: error in identifying control file, check alert log for more info

# It means your control file is missing either 1 or all as per control_files initialization parameter.

In this case we need to restore and recover the database using control file backup, lets see how we can do it via RMAN utility :

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:20:11 2023
Version 21.3.0.0.0

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

connected to target database: ORCL (not mounted)

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

Starting restore at 22-MAY-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 22-MAY-23

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> recover database ;

Starting recover at 22-MAY-23
Starting implicit crosscheck backup at 22-MAY-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=430 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 22-MAY-23

Starting implicit crosscheck copy at 22-MAY-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-MAY-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/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc
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/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-MAY-23

RMAN> exit


Recovery Manager complete.
[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:20:51 2023
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter database open resetlogs;

Database altered.

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

# Thats it recovery is now completed and your database is available now.
Posted by at No comments:
Labels:

How to take a full backup in Oracle

Lets learn today how to take a full backup in Oracle.


We will use recovery manager RMAN utility to take full backup :


[oracle@node2 ~]$ rman target/


Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:31:15 2023

Version 21.3.0.0.0


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


connected to target database: ORCL (DBID=1644501746)



 RMAN> backup database;


Starting backup at 22-MAY-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 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T232846_l6qcbpf5_.bkp tag=TAG20230522T232846 comment=NONE

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

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 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T232846_l6qccstr_.bkp tag=TAG20230522T232846 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 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T232846_l6qcd93q_.bkp tag=TAG20230522T232846 comment=NONE

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

Finished backup at 22-MAY-23


Starting Control File and SPFILE Autobackup at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137540592_l6qcdrcf_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 22-MAY-23


Posted by at No comments:

Recovering from a lost control file in Oracle

 In this post we learn how to recover from a lost control file.


# First lets take a database backup :

# Lets verify our version of database and environment

[oracle@node2 ~]$ sqlplus / as sysdba


SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:10:11 2023

Version 21.3.0.0.0


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



Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0


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 ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.0 (Maipo)

# Now lets take a backup

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:11:42 2023
Version 21.3.0.0.0

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

connected to target database: ORCL (DBID=1644501746)

RMAN> backup database;

Starting backup at 22-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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 22-MAY-23
channel ORA_DISK_1: finished piece 1 at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbbz9n_.bkp tag=TAG20230522T231150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
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 22-MAY-23
channel ORA_DISK_1: finished piece 1 at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbglpr_.bkp tag=TAG20230522T231150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
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 22-MAY-23
channel ORA_DISK_1: finished piece 1 at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbhp0y_.bkp tag=TAG20230522T231150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 22-MAY-23

Starting Control File and SPFILE Autobackup at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-23


# Now after deleting any of your control file, try to restart the database :

SQL> startup force
ORACLE instance started.

Total System Global Area 1073740680 bytes
Fixed Size                  9694088 bytes
Variable Size             377487360 bytes
Database Buffers          683671552 bytes
Redo Buffers                2887680 bytes
ORA-00205: error in identifying control file, check alert log for more info

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:20:11 2023
Version 21.3.0.0.0

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

connected to target database: ORCL (not mounted)

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

Starting restore at 22-MAY-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 22-MAY-23

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> recover database ;

Starting recover at 22-MAY-23
Starting implicit crosscheck backup at 22-MAY-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=430 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 22-MAY-23

Starting implicit crosscheck copy at 22-MAY-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-MAY-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/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc
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/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-MAY-23

RMAN> exit


Recovery Manager complete.
[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:20:51 2023
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter database open resetlogs;

Database altered.

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


Posted by at No comments:
Labels:

Creating a resource manager plan directive in Oracle

 Creating a resource manager plan directive in Oracle


In Oracle, Resource plan directives specify how resources are allocated to resource consumer groups or subplans. Each directive can specify several different methods for allocating resources to its consumer group or subplan.


[oracle@node2 ~]$ sqlplus / as sysdba


SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:03:48 2023

Version 21.3.0.0.0


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


Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0



SQL> alter session set container=PDB1


Session altered.


SQL> BEGIN

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

   PLAN             => 'DAYTIME',

   GROUP_OR_SUBPLAN => 'OLTP',

   COMMENT          => 'OLTP group',

   MGMT_P1          => 75,

   SWITCH_GROUP     => 'LOW_GROUP',

   SWITCH_TIME      => 5);

END;

/  2    3    4    5    6    7    8    9   10

BEGIN

*

ERROR at line 1:

ORA-29371: pending area is not active

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 2152

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 847

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1867

ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 345

ORA-06512: at line 2




SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA()


PL/SQL procedure successfully completed.


SQL> ed

Wrote file afiedt.buf


  1  BEGIN

  2    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

  3     PLAN             => 'DAYTIME',

  4     GROUP_OR_SUBPLAN => 'OLTP',

  5     COMMENT          => 'OLTP group',

  6     MGMT_P1          => 75,

  7     SWITCH_GROUP     => 'LOW_GROUP',

  8     SWITCH_TIME      => 5);

  9* END;

SQL> /


PL/SQL procedure successfully completed.



SQL>  exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()


PL/SQL procedure successfully completed.


SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()


PL/SQL procedure successfully completed.










Posted by at No comments:

Friday 19 May 2023

Creating and managing Oracle services in single instance Non RAC Systems.

 Creating and managing Oracle services in single instance Non RAC Systems.


#At first lets try to create a service with srvctl :


SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO


SQL> show parameter uniq


NAME                                 TYPE        VALUE

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

db_unique_name                       string      orcl


[oracle@node2 ~]$ srvctl status database -d orcl

****Unable to retrieve Oracle Clusterware home.

Start Oracle Clusterware stack and try again.



[oracle@node2 ~]$ srvctl add service -s s1 -d orcl

****Unable to retrieve Oracle Clusterware home.

Start Oracle Clusterware stack and try again.



# This clearly means you cannot manage services in non rac single instance systems with srvctl. The srvctl works with Grid Infrastructure and is the recommended way in Grid Infrastructure. However if you are in non RAC systems then use dbms_service to create and manage services.


Lets create a service now, the version we are using is 21c :


[oracle@node2 dk]$ sqlplus / as sysdba


SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 19 23:31:37 2023

Version 21.3.0.0.0


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



Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0


SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO



    BEGIN

      DBMS_SERVICE.create_service(

        service_name => 'service1',

        network_name => 'service1'

     );

   END;

SQL> /


PL/SQL procedure successfully completed.


# Lets see how to start a service 



SQL> exec dbms_service.start_service('service1');


PL/SQL procedure successfully completed.


# Lets see how to stop a service 



SQL> exec dbms_service.stop_service('service1');


PL/SQL procedure successfully completed.


# Lets see how to delete a service 



SQL> exec dbms_service.delete_service('service1');

BEGIN dbms_service.delete_service('service1'); END;


*

ERROR at line 1:

ORA-44305: service service1 is running

ORA-06512: at "SYS.DBMS_SERVICE", line 68

ORA-06512: at "SYS.DBMS_SERVICE", line 458

ORA-06512: at line 1



# You cannot delete a running service in Oracle.

# So you must stop it first, lets try it


SQL>  exec dbms_service.stop_service('service1');


PL/SQL procedure successfully completed.


SQL> exec dbms_service.delete_service('service1');


PL/SQL procedure successfully completed.


# It works now.


# Lets try to connect now :


SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO


# I am connected in the CDB$ROOT 


SQL> show con_name


CON_NAME

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

CDB$ROOT



# Create a user 


SQL> create user user1 identified by Oracle123mine ;

create user user1 identified by Oracle123mine

            *

ERROR at line 1:

ORA-65096: invalid common user or role name



SQL> create user c##user1 identified by Oracle123mine ;


User created.


SQL> grant connect to c##user1;


Grant succeeded.



[oracle@node2 dk]$ sqlplus c##user1@service1


SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 19 23:36:57 2023

Version 21.3.0.0.0


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


Enter password:


Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0






Posted by at No comments:

Wednesday 10 May 2023

Automatic Transaction Rollback feature in 23C

 Automatic Transaction Rollback feature in 23C

The Row lock contention has been a major pain area for developers specially when it comes to releasing locks in case there is an exception thrown by a transaction from an application. The developers in most cases take help from the DBAs to kill their blocking parent session or in essense kill the final blocking session if there is a chain of row locks under the lock tree.


The Automatic Transaction Rollback feature automatically rolls back low-priority transactions that are blocking higher priority transactions from obtaining row locks.


This parameter is one of four initialization parameters that enable you to configure Automatic Transaction Rollback. The other parameters are:


TXN_PRIORITY - Specifies a priority (HIGH, MEDIUM, or LOW) for all transactions in a user session


TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET - Specifies the maximum number of seconds that a MEDIUM priority transaction will wait for a row lock


TXN_AUTO_ROLLBACK_MODE - Specifies whether to enable Automatic Transaction Rollback or test Automatic Transaction Rollback by running in tracking mode


Note: This parameter is available starting with Oracle Database 23c.


Lets see what wait events have been introduced with the 23c version :


SQL> select event#, name, WAIT_CLASS from v$event_name where name like '%TX - row%';


    EVENT# NAME                                                             WAIT_CLASS

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

       340 enq: TX - row lock contention                                    Application

       341 enq: TX - row lock (HIGH priority)                               Application

       342 enq: TX - row lock (MEDIUM priority)                             Application

       343 enq: TX - row lock (LOW priority)                                Application


The parameter txn_auto_rollback_medium_priority_wait_target default value is 2147483647, The TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET specifies the maximum number of seconds that a HIGH priority transaction will wait for a row lock before the Automatic Transaction Rollback feature rolls back a lower priority transaction holding the lock.



SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET


NAME                                 TYPE        VALUE

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

txn_auto_rollback_medium_priority_wa integer     2147483647

it_target

SQL> col NAME For a100

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET


NAME                                 TYPE        VALUE

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

txn_auto_rollback_medium_priority_wa integer     2147483647

it_target

SQL>


# Now since this is a static parameter, it needs a database restart.


SQL> alter system set txn_auto_rollback_medium_priority_wait_target=50 scope=spfile;


System altered.


SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.


Total System Global Area 1608409424 bytes

Fixed Size                 10043728 bytes

Variable Size             402653184 bytes

Database Buffers         1191182336 bytes

Redo Buffers                4530176 bytes

Database mounted.

Database opened.


Lets confirm the value :


SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET


NAME                                 TYPE        VALUE

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

txn_auto_rollback_medium_priority_wa integer     50


Session 1 with Medium priority: 


SQL> select * from t1;


         I         I2

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

         1          1

         2          2

         3          3

         4          4

         5          5


SQL>  alter session set TXN_PRIORITY=MEDIUM;


Session altered.


SQL> update t1 set i=11 where i=1;


1 row updated.



Session 2 with High Priority: 


SQL> alter session set TXN_PRIORITY=HIGH;


Session altered.


SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET


NAME                                 TYPE        VALUE

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

txn_auto_rollback_medium_priority_wa integer     50


SQL> select * from t1;


         I         I2

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

         1          1

         2          2

         3          3

         4          4

         5          5


SQL> update t1 set i=1001 where i=1;


>>> Hangs here >>>>>





Session 3 DBA session Monitors the wait :


SQL> select sid,EVENT,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like '%lock%';


       SID EVENT                                    WAIT_CLASS            WAIT_TIME SECONDS_IN_WAIT STATE

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

       112 enq: TX - row lock (HIGH priority)       Application                   0              22 WAITING



SQL> select sid,EVENT,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like '%lock%';


       SID EVENT                                    WAIT_CLASS            WAIT_TIME SECONDS_IN_WAIT STATE

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

       112 enq: TX - row lock (HIGH priority)       Application                   0             49 WAITING



Now go to session 1, the one with MEDIUM priority :


SQL> select * from t1;

select * from t1

       *

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 1043

Session ID: 31 Serial number: 10449



Now go to session 2 >> High Priority session :


SQL> update t1 set i=1001 where i=1;





1 row updated.


SQL> 

SQL> select * from t1;


         I         I2

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

      1001          1

         2          2

         3          3

         4          4

         5          5


# This clearly explains how high priority session takes priority over low priority sessions. Happy 23C #Oracle.





Posted by at No comments:

How to create a database in 23C

 Creating a database in 23C :


Let us see how we can create a database in Oracle 23c free version for developers :


login as: root

root@192.9.201.121's password:

Activate the web console with: systemctl enable --now cockpit.socket


Register this system with Red Hat Insights: insights-client --register

Create an account or view all your systems at https://red.ht/insights-dashboard

Last login: Wed May 10 22:03:56 2023

[root@node1-example-com ~]# export DB_PASSWORD=Oracle123mine

[root@node1-example-com ~]# (echo "${DB_PASSWORD}"; echo "${DB_PASSWORD}";) | /etc/init.d/oracle-free-23c configure

Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:

Confirm the password:

Configuring Oracle Listener.

Listener configuration succeeded.

Configuring Oracle Database FREE.

Enter SYS user password:

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

Enter SYSTEM user password:

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

Enter PDBADMIN User Password:

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

Prepare for db operation

7% complete

Copying database files

29% complete

Creating and starting Oracle instance

30% complete

33% complete

36% complete

39% complete

43% complete

Completing Database Creation

47% complete

49% complete

50% complete

Creating Pluggable Databases

54% complete

71% complete

Executing Post Configuration Actions

93% complete

Running Custom Scripts

100% complete

Database creation complete. For details check the logfiles at:

 /opt/oracle/cfgtoollogs/dbca/FREE.

Database Information:

Global Database Name:FREE

System Identifier(SID):FREE

Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.


Connect to Oracle Database using one of the connect strings:

     Pluggable database: node1-example-com/FREEPDB1

     Multitenant container database: node1-example-com


So how do we login to the Oracle 23c Database :


[root@node1-example-com ~]# su - oracle

[oracle@node1-example-com ~]$ export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree

[oracle@node1-example-com ~]$ sqlplus sys/Oracle123mine@//localhost:1521/free as sysdba

bash: sqlplus: command not found...

[oracle@node1-example-com ~]$ ps -ef | grep smon

oracle     11268       1  0 22:49 ?        00:00:00 db_smon_FREE

oracle     11805   11758  0 22:55 pts/2    00:00:00 grep --color=auto smon

[oracle@node1-example-com ~]$  . oraenv

ORACLE_SID = [oracle] ? FREE

The Oracle base has been set to /opt/oracle

[oracle@node1-example-com ~]$ sqlplus / as sysdba


SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed May 10 22:56:01 2023

Version 23.2.0.0.0


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


Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0


SQL> SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 FREEPDB1                       READ WRITE NO


Thats it, Enjoy 23c  :) :)



Posted by at No comments:

Install Oracle 23c database free for Developers

 In this article we will learn how to Install Oracle 23c database free for Developers

First, get your downloaded files i.e :

oracle-database-free-23c-1.0-1.el8.x86_64.rpm

oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm


The above files can be downloaded from Oracle's web site : 


https://www.oracle.com/database/free/


[root@node1-example-com Documents]# ls -ltr

total 1717556

-rwxrw-rw-. 1 root root 1758776440 May 10 22:03 oracle-database-free-23c-1.0-1.el8.x86_64.rpm



[root@node1-example-com Documents]# rpm -ivh oracle-database-free-23c-1.0-1.el8.x86_64.rpm 

warning: oracle-database-free-23c-1.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

error: Failed dependencies:

oracle-database-preinstall-23c is needed by oracle-database-free-23c-1.0-1.x86_64


Here if you see i dont have the oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm package.


Lets install this misssing package to ensure the preinstall rpm work is done.


[root@node1-example-com Documents]# ls -ltr

total 1717588

-rwxrw-rw-. 1 root root 1758776440 May 10 22:03 oracle-database-free-23c-1.0-1.el8.x86_64.rpm

-rw-------. 1 root root      30688 May 10 22:07 oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

[root@node1-example-com Documents]# rpm -ivh oracle-database-

oracle-database-free-23c-1.0-1.el8.x86_64.rpm          oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm  

[root@node1-example-com Documents]# rpm -ivh oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm 

warning: oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

error: Failed dependencies:

compat-openssl10 is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

glibc-devel is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

ksh is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

libnsl is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

make is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

sysstat is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

[root@node1-example-com Documents]# rpm --nodeps -ivh oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm --force

warning: oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

Verifying...                          ################################# [100%]

Preparing...                          ################################# [100%]

Updating / installing...

   1:oracle-database-preinstall-23c-1.################################# [100%]

[root@node1-example-com Documents]# rpm -ivh oracle-database-free-23c-1.0-1.el8.x86_64.rpm 

warning: oracle-database-free-23c-1.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

Verifying...                          ################################# [100%]

Preparing...                          ################################# [100%]

Updating / installing...

   1:oracle-database-free-23c-1.0-1   ################################# [100%]

root

[INFO] Executing post installation scripts...

[INFO] Oracle home installed successfully and ready to be configured.

To configure Oracle Database Free, optionally modify the parameters in '/etc/sysconfig/oracle-free-23c.conf' and then run '/etc/init.d/oracle-free-23c configure' as root.


Posted by at No comments:

Friday 28 April 2023

High CPU Usage in Oracle Active Data Guard 19c

 Recently i noticed in an Oracle 19.16 database, the standby database server CPU usage went extremely high, we noticed it was the BEQ LOCAL CONNECTION PROCESS is using high cpu, and it was not any application which was running any workload, not even a single application connection was connected to this standby database. Please note this bug was hit even when PDB was mounted while CDB was read only mode. Hence make sure you have the patch 33359773 available for you for the bug id 33359773. You must prove to the Oracle Development that you have hit thig bug. 


Kindly provide the following to the Oracle support for your issue to get the above bug fix and prove that the symptoms match the problem : 


1) alertlog and trace files after you applied the second diag patch.

2)a. Turn on the diagnostics fix

alter system set events '10315 trace name context forever, level 3';

b. Run the test which caused the issue

c. Collect the trace generated

d. Collect AWR repository dump covering the this test period.

e. Collect OSWatcher log covering the this test period.

f. Collect TFA:

$tfactl diagcollect -srdc dbrman

g. Collect the pstack log

watch -n 2 'pstack <target process ospid> >> pstack.txt; echo -e "\n" >> pstack.txt'

h. Turn off the diagnostics fix

alter system set events '10315 trace name context off';


When you run the top command in linux you would see below processes using the high cpu :

So when you ps -ef   grep pid you would see :

 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))


Posted by at No comments:

Types of Deadlocks in Oracle Database

 


In a curious case recently, we faced a deadlock in Oracle RAC, so in this post i thought to share the different types of deadlock that can occur in Oracle database.


Deadlock types in Oracle :


Here are some common deadlock types:


1. TX deadlock in Exclusive(X) mode

trace shows:


Global Wait-For-Graph(WFG) at ddTS[0.170] :

BLOCKED 0x8aafb0ec 5 wq 2 cvtops x1 TX 0x320001.0x121c97 [99000-0001-00000002]0

BLOCKER 0x8aafafec 5 wq 1 cvtops x8 TX 0x320001.0x121c97 [9A000-0001-00000002]0

BLOCKED 0x8acb55e4 5 wq 2 cvtops x1 TX 0x430003.0x3843f [9A000-0001-00000002] 0

BLOCKER 0x8acb54e4 5 wq 1 cvtops x8 TX 0x430003.0x3843f [99000-0001-00000002] 0


These values are:

<BLOCKED|BLOCKER> <lockp> <cvt|held mode> <which q> <convert options> <res name - type id1.id2> [deadlock-level-trace] <node> 


Above graph shows the deadlock is related with 2 TX lock in mode 5 (exclusive) which happens on instance 1 (instance# starts from 0)


Above deadlock means two sessions involved in TX-0x320001-0x121c97 and TX-0x430003-0x3843f forms a deadlock, both sessions are from instance 1.


This is a typical application transaction TX enqueue lock, usually caused by SQL commit sequence and high concurrency. To avoid such deadlock, application code and logic need to be modified.


The application and SQL involved in the deadlock can be found in lmd0 or foreground trace (check all instances). Search for: "user session for deadlock lock" section to find out the SQL involved in the deadlock. For example:


user session for deadlock lock 0x8aafb0ec:

...

  current SQL:

  update test set OWNER='APPS' where rownum < 2

 


2. TX deadlock in Share(S) mode

trace shows:


Global Wait-For-Graph(WFG) at ddTS[0.b7] :

BLOCKED 0x2310e8918 3 [0x731000e][0x56268],[TX] [F1000-0001-0000000F] 0

BLOCKER 0x2310e87c8 3 [0x731000e][0x56268],[TX] [19F000-0001-00000011] 0

BLOCKED 0x2310e3c50 3 [0x72a0023][0x530d7],[TX] [19F000-0001-00000011] 0

BLOCKER 0x2310e7e80 3 [0x72a0023][0x530d7],[TX] [F1000-0001-0000000F] 0 


Corresponding to:

<BLOCKED|BLOCKER> <lockp> <cvt|held mode> <res name> <pid|did|txn_id> <node> 

mode 3 is shared lock

The causes for TX deadlock in S mode wait can be:


a. ITL contention, eg: INITRANS setting for the object is too small, it can not handle the number of concurrent transactions.


The solution is to increase INITRANS setting for the object involved in the deadlock using "alter table" or "alter index" command

The SQL involved in the deadlock can be found in lmd0 or client trace. The object involved in the SQL should be checked including table and its associated index.


b. If the object involved is an unique key index, the wait could be caused by uniqueness validation. Application needs to be checked to avoid unique key violation.


c. If the object involved has a bitmap index, then the bitmap index should be dropped to accommodate concurrent DML. Please refer to Document 1496403.1 ORA-60 DEADLOCK DUE TO BITMAP INDEX IN RAC.



3. TM deadlock


trace shows:


Global Wait-For-Graph(WFG) at ddTS[0.1] :

BLOCKED 0x7000003ccbf4798 3 wq 2 cvtops x1 TM 0x1cbde.0x0 [1004-004D-00000003] 0

BLOCKER 0x7000003d0bf9ad8 3 wq 1 cvtops x1 TM 0x1cbde.0x0 [200A-00AC-00000019] 1

BLOCKED 0x7000003d0bfcf88 2 wq 2 cvtops x1 TM 0x1cc77.0x0 [200A-00AC-00000019] 1

BLOCKER 0x7000003cc338e88 2 wq 2 cvtops x1 TM 0x1cc77.0x0 [2006-0063-00000055] 1

BLOCKED 0x7000003cc338e88 3 wq 2 cvtops x1 TM 0x1cc77.0x0 [2006-0063-00000055] 1

BLOCKER 0x7000003c879f9c0 3 wq 1 cvtops x1 TM 0x1cc77.0x0 [2006-0063-00000020] 1

BLOCKED 0x7000003c87978a8 2 wq 2 cvtops x1 TM 0x1cbde.0x0 [2006-0063-00000020] 1

BLOCKER 0x7000003ccbf4798 2 wq 2 cvtops x1 TM 0x1cbde.0x0 [1004-004D-00000003] 0

The object involved here are 0x1cbde and 0x1cc77, convert the hex number to decimal, they are the object_id for the tables involved in above deadlock


The deadlock is usually caused by missing index for foreign key constraint, refer to Document 473124.1 - "Frequent GES: Potential Blocker (Pid=nnnn) On Resource TM-<id1>-<id2>" for more information. Check dba_constraints and dba_index to verify if foreign key index is missing. Also refer to Document 1019527.6 Script to Check for Foreign Key Locking Issues for a Specific User which will generate a report for all problem objects.


The solution is to create index for every foreign key constraint.


4. Single resource deadlock for TX , TM, IV or LB

trace shows:


Single resource deadlock: blocking enqueue which blocks itself, f 1

Granted global enqueue 0xd078cec0

...

resname :[0x2001f][0x1a96c3],[TX]

or

resname :[0x00001432][0x0],[TM]

or

resname : [0xbb7cc5db][0x82d0d4b5],[IV]

or

resname : [0x5e582fb9][0xa216c7af],[LB]


a. For single resource deadlock on TX enqueue, often it is caused by using autonomous transaction in stored procedure or PL/SQL. It is a known issue that the use of autonomous transactions is vulnerable to deadlocks. Please check out Oracle® Database Concepts  Overview of Autonomous Transactions for detail explanation. Since AUTONOMOUS transaction has been used in the stored procedure, the system would consider any DML statement under this transaction as a separate one (commit/rollback won't affect the parent), and this would cause conflict if the same row is involved in the parent transaction (INSERT, UPDATE or DELETE), and hence deadlock is reported rightly. Usually the SQL involved in the deadlock is called from a stored procedure or PL/SQL with the following line:


PRAGMA AUTONOMOUS_TRANSACTION;

To avoid such deadlock, please remove the autonomous transaction in the application code.


b. If there is no autonomous_transaction involved, please check out Document 6145177.8, it can also be caused by Bug 6145177 - Single resource deadlock with a zero DID


c. For single resource deadlock on TM enqueue, missing foreign key index is often the cause, please check case 3 for the solution.


d. For single resource deadlock type IV (Instance Validation), refer to Document 973178.1, as mentioned in Bug 8843816, this message can be ignored. Bug 8843816 has been fixed in 11.1.


e. For single resource deadlock on LB (Library Cache Lock) with DID 0, if it is on external table disable stats on external table as below:  alter session set "_px_external_table_default_stats" = false; Reference Bug 21204478


5. LB deadlock

Global Wait-For-Graph(WFG) at ddTS[0.390] :

BLOCKED 0x3bc3e1b48 5 wq 2 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [7B000-0002-00006C9D] 1

BLOCKER 0x3bc2efad0 5 wq 1 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [48000-0001-000069FE] 0

BLOCKED 0x3bc2ed1c0 3 wq 2 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [48000-0001-000069FE] 0

BLOCKER 0x3bc2dc648 3 wq 2 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [34000-0001-0000095E] 0

BLOCKED 0x3bc2dc648 5 wq 2 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [34000-0001-0000095E] 0

BLOCKER 0x3bc2dbbb0 5 wq 1 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [76000-0002-000074D4] 1

BLOCKED 0x3bc2ef830 3 wq 2 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [76000-0002-000074D4] 1

BLOCKER 0x3bc3e1b48 3 wq 2 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [7B000-0002-00006C9D] 1

LB lock type refers to library cache lock. This type of deadlock is usually caused by a bug. 


For example: Bug 6475688  Concurrent rewrite and on-commit refresh can deadlock (library cache pin <--> lock) Document 6475688.8

The bug has been fixed in 11.1.0.7 and 11.2. Please apply patch accordingly.


6. Known Issues

For other deadlock type or known issues related to dead lock, refer to Document 554567.1 Summary Of Bugs Which Could Cause Deadlock In RAC Environment


7. Further Diagnosis

Please collect the following information for further diagnosis:


a. alert log lmd0, and trace mentioned in the alert log from all instances.

b. set the following event to collect systemstate dump ONLY if the information in trace files are insufficient:


alter system set events '60 trace name systemstate level 258';

It will cause a systemstate dump to be generated whenever a deadlock is reported. If there are constant deadlocks, it could cause a lot of trace files being generated, monitor the system carefully.


To turn off the trace:


alter system set events '60 trace name context off';


8. Deadlock Parser Tool (Enterprise Manager)

EM 12c, EM 13c: Using The Deadlock Parser Tool For Gathering EM Repository Deadlock Information Document:2222769.1

Posted by at No comments:

How to open a database from read write mode to mount status

 SQL> select name,open_mode from v$database;


NAME      OPEN_MODE

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

ORCL      READ WRITE


SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01100: database already mounted


# So here we get the error database is already mounted, in this case we need to ensure the database is shutdown first and then only we can mount the database.



SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


# So lets see how we can do it

SQL> startup mount

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
Database mounted.
SQL> SQL>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED

# With this we know how we can mount the database from open state.
Posted by at No comments:

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


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



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

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

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

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


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
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7



SQL>
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

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

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

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

sel
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQ
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

Posted by at No comments:
Subscribe to: Posts (Atom)