Oracle 19C DBA Training - APR 2024 Course Index

 Dear All,

In this Post i am sharing ORACLE 19C DBA Online Training details .

Oracle 19C DBA Course : Duration 40 hours 
9th Apr 2024  -  7:30AM to 8:30 AM IST ..( Only on Weekdays)
Course Fee : 15K INR 

Contact us :  racsinfotech@gmail.com   / +91 9392634440     for more details ..

This training Prepared with Realtime Scenarios   , It will useful for jobseekers and students those who are looking for jobs in IT post studies .

Course Index : 

Oracle 19C DBA Training with Live Labs.

1. 12c and 19c Architecture.

2. DBA Roles and Responsibilities 

3. Required tools and Prerequisites for DBA Training.

4. VirtualBox , Linux OS and Database Installation .

5. How to connect to database and Network setup .

6. User Management ( user , role , profile creation etc .. )

7. Tablespace / Storage management

8. Backup and Recovery.

9. Database Patching.

10. Database Clone.

11. Performance Tuning Basics( AWR/ADDM/ASH reports ).

12. ASM and DG Installation / Configuration .

13. Database Upgrade steps (  12c to 19c and 19C to 21C  ) .

14. Frequently asked Interview questions and answers Discussions.

15. CV Preparation suggestions and support.

16. Real time Scenarios and excises.

Note : we will provide the Lab setup* on your PC/ Laptops ,

 This training designed completely with Realtime scenarios .

Thanks,

Srini

Oracle 19C RAC Node Addition steps

 Dear All,

In this post i am going to share the rac node addition steps ..

Present we are having 2 node Rac setup , we are going to add Node3 ..

Before node addition : 

Node addtion :: RAC 

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

1)  Configure Node3 will all pre-requisites and add IP's into remain nodes.

>> Add these IPs into resvers zone / forward zone .. /var/named/localdomain.zone ..   /var/named/4.38.10.in-addr.arpa

node3 : preparation steps 

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

cat /etc/resolv.conf

cd /var/named/

0.168.192.in-addr.arpa

localdomain.zone

[root@racs2 named]# cat localdomain.zone

$TTL 86400

@ IN SOA racs2.localdomain.com. root.localdomain.com. (

2014051001 ; serial

3600 ; refresh

1800 ; retry

604800 ; expire

86400 ; minimum

)

@ IN NS racs2.localdomain.

localhost IN A 127.0.0.1

racs1 IN A 192.168.0.71

racs2 IN A 192.168.0.72

racs1-priv IN A 192.168.10.1

racs2-priv IN A 192.168.10.2

racs1-vip IN A 192.168.0.81

racs2-vip IN A 192.168.0.82

racs-scan IN A 192.168.0.91

racs-scan IN A 192.168.0.92

racs-scan IN A 192.168.0.93

[root@racs2 named]# 

>>>create the grid and oracle users on Node3 

rpm -qa|grep oracle

create the directores for oracle home, grid home , create the groups and add those to grid and oracle users 

2) Install Oracle asm Librarys , Grid software , oracle software , create instance3 and attache the shared disks on node3.

oracleasm init

oracleasm configure -i  >> grid , oinstall , y , y

oracleasm scandisks

oracleasm listdisks

>> GI Installation -> GI_HOME/gridSetup.sh (GUI)

Add new cluster nodes -> GI_HOME/gridSetup.sh (GUI) or GI_HOME/addnode/addnode.sh (Command line)

>> OH Installation -> runInstaller (GUI)

Add OH on newly added nodes -> ORACLE_HOME/addnode/addnode.sh (GUI) or ORACLE_HOME/addnode/addnode.sh (Command line)

>> create database/create Instance -> ORACLE_HOME/bin/dbca (GUI)

add 3rd Insatnce -> ORACLE_HOME/bin/dbca (GUI) or srvctl add instance (Command line)

3) Enable the password less connectivity between all the nodes ..

./sshUserSetup.sh -user oracle -hosts "racs1 racs2 racs3" -noPromptPassphrase -confirm -advanced

./sshUserSetup.sh -user grid -hosts "racs1 racs2 racs3" -noPromptPassphrase -confirm -advanced

./sshUserSetup.sh -user root -hosts "racs1 racs2 racs3" -noPromptPassphrase -confirm -advanced

4) Verify the Cluvfy Utility & Node pre-requisites - From Node1 

/u02/app/19.0.0/grid_home/bin   ## node to node comparison check 

>> cd $G_H/bin/cluvfy comp peer -n racs3 -refnode racs1 -r 19

/u02/app/19.0.0/grid_home/bin   ## pre-requisites check 

cluvfy stage -pre nodeadd -n racs3 -fixup -verbose

if any fixup.sh .. run from node3 ... under root users 

export IGNORE_PREADDNODE_CHECKS=Y

/u02/app/19.0.0/grid_home/addnode

./addnode.sh -silent "CLUSTER_NEW_NODES={racs3.localdomain.com}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={racs3-vip.localdomain.com}"

orainstRoot.sh 

root.sh

5) Check the cluster status 

crsctl stat res -t 

crsctl check cluster -all 

6) set the oracle home for racs3 from racs1 ..

set the environment 

$ORACLE_HOME/addnode 

./addnode.sh -silent "CLUSTER_NEW_NODES={racs3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={racs3-vip}"

root.sh 

7) Update the inventory for Racs3 

cd $O_H/oui/bin/runInstaller -updateNodeList -noClusterEnabled ORACLE_HOME=homepath CLUSTER_NODES=racs1,racs2,racs3 CRS=false

"INVENTORY_LOCATION=/u02/app/oraInventory" LOCAL_NODE=3

cd $ORACLE_HOME/addnode

 ./addnode.sh "CLUSTER_NEW_NODES={racs3}"

cd $ORACLE_HOME/bin

./dbca 

>> run the root.sh

add instance to Cluster database for Racs3 node 

srvctl add instance -d RACS -i racs3 -n racs3  ## by using dbca also we can add the node3 instance to cluster .

After node addition : 

Thanks,

Srini

RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

 Dear All,

In this post i am going to show you RMAN Duplicate database clone errors fixup on Oracle database 19C .

while im doing RAC to Non RAC clone i faced this error on Target server .

[oracle@uatdb01 ~]$ rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 09:06:18 2024

Version 19.22.0.0.0

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

connected to auxiliary database: UATDB (not mounted)

RMAN> duplicate database to 'UATDB' backup location '/u01/Backup' nofilenamecheck;

Starting Duplicate Db at 06-MAR-24

searching for database ID

found backup of database ID 1143261986

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

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

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

RMAN-03002: failure of Duplicate Db command at 03/06/2024 09:09:00

RMAN-05501: aborting duplication of target database

RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

ORA-00349: failure obtaining block size for '+DATA'

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

Solution for the above error :

Add below values in parameter file on target server ..  update the correct path for your datafile and logfiles location in target side parameter .. that will resolve the below error .

*.cluster_database=FALSE

#*.control_files='/u01/app/oracle/oradata/UATDB/control01.ctl','/u01/app/oracle/fast_recovery_area/UATDB/control02.ctl'
*.db_file_name_convert='+DATA/RACSDB/DATAFILE','/u01/app/oracle/oradata/UATDB'
*.log_file_name_convert='+DATA/RACSDB/ONLINELOG','/u01/app/oracle/oradata/UATDB','+ARCH/RACSDB/ONLINELOG','/u01/app/oracle/oradata/UATDB'

After that restart the duplicate command 

RMAN> duplicate database to 'UATDB' backup location '/u01/Backup' nofilenamecheck;

Starting Duplicate Db at 06-MAR-24

searching for database ID

found backup of database ID 1143261986

contents of Memory Script:

{

   sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

contents of Memory Script:

{

   sql clone "alter system set  control_files =

  ''/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl'' comment=

 ''Set by RMAN'' scope=spfile";

   sql clone "alter system set  db_name =

 ''RACSDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''UATDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile from  '/u01/Backup/c-1143261986-20240306-00';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  control_files =   ''/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''RACSDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''UATDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

Starting restore at 06-MAR-24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

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

output file name=/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl

Finished restore at 06-MAR-24

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/RACSDB/TEMPFILE/temp.264.1159692461 conflicts with a file used by the target database

RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

   set until scn  7634718;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/UATDB/system.257.1159692337";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665";

   set newname for datafile  7 to

 "/u01/app/oracle/oradata/UATDB/users.260.1159692389";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 06-MAR-24

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/UATDB/system.257.1159692337

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/UATDB/users.260.1159692389

channel ORA_AUX_DISK_1: reading from backup piece /u01/Backup/1143261986-20240306-022l0f0a_2_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/Backup/1143261986-20240306-022l0f0a_2_1_1 tag=TAG20240306T075553

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 06-MAR-24

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/system.257.1159692337

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

datafile 7 switched to datafile copy

input datafile copy RECID=10 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389

contents of Memory Script:

{

   set until scn  7634718;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 06-MAR-24

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=46

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=2 sequence=36

channel ORA_AUX_DISK_1: reading from backup piece /u01/Backup/1143261986-20240306-032l0f2d_3_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/Backup/1143261986-20240306-032l0f2d_3_1_1 tag=TAG20240306T075701

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_1_46_lyhsxpdr_.arc thread=1 sequence=46

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_2_36_lyhsxpgb_.arc thread=2 sequence=36

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_1_46_lyhsxpdr_.arc RECID=2 STAMP=1162891151

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_2_36_lyhsxpgb_.arc RECID=1 STAMP=1162891150

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

Finished recover at 06-MAR-24

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''UATDB'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

}

executing Memory Script

sql statement: alter system set  db_name =  ''UATDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UATDB" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP     1 ( '/u01/app/oracle/oradata/UATDB/group_1.257.1159692457', '/u01/app/oracle/oradata/UATDB/group_1.262.1159692455' ) SIZE 200 M  REUSE,

  GROUP     2 ( '/u01/app/oracle/oradata/UATDB/group_2.258.1159692457', '/u01/app/oracle/oradata/UATDB/group_2.263.1159692455' ) SIZE 200 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/UATDB/system.257.1159692337'

 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'

  GROUP     3 ( '/u01/app/oracle/oradata/UATDB/group_3.266.1159692729', '/u01/app/oracle/oradata/UATDB/group_3.259.1159692729' ) SIZE 200 M  REUSE,

  GROUP     4 ( '/u01/app/oracle/oradata/UATDB/group_4.267.1159692729', '/u01/app/oracle/oradata/UATDB/group_4.260.1159692729' ) SIZE 200 M  REUSE

contents of Memory Script:

{

   set newname for tempfile  1 to

 "+DATA";

   switch clone tempfile all;

   catalog clone datafilecopy  "/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373",

 "/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387",

 "/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665",

 "/u01/app/oracle/oradata/UATDB/users.260.1159692389";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373 RECID=1 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387 RECID=2 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665 RECID=3 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389 RECID=4 STAMP=1162891171

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

datafile 5 switched to datafile copy

input datafile copy RECID=3 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

datafile 7 switched to datafile copy

input datafile copy RECID=4 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Cannot remove created server parameter file

Finished Duplicate Db at 06-MAR-24

RMAN> 

Do the post restore steps .. 

Thanks,
Srini

Oracle Linux Commands - part 2/4

 

In this post i am going to share few of the oracle Linux commands .. i have posted total 4 post for Complete Linux high level commands .

part 1 : https://anjaniappsdba.blogspot.com/2023/09/oracle-linux-commands-part-14.html

this is  part 2 .. in my next post will share part 3 commands 

In Linux Searching Commands

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

 locate filename>> to search entire file system for given file name

 find / -name filename>> to search entire / file system for given file

name

 find . –name filename>> it searches the file in current directory

 find . –iname filename>> it searches the file in current directory with

ignore the case sensitive

 find / -type f –name filename>> it searches the file in the file system of /

 find / -type d –name dirname >> it searches the directory in the file

system of /

 find / -type f –name filename –exec rm –rf {}\; >>to find the file and

remove that file

 find / -type f –empty >> it display the empty file

 find / -type d –empty >> it display the empty directories

 find / -mtime 50 >> to be find the last 50 days modified files

 find / -mtime +50 –mtime -100 >> to be find the last 50 days to 100days

modified files.

 find / -mmin -60>> it displayes the last one hour modified file

 find / -cmin -60>> it displayes the last one hour changed file

 find / -atime 50>> it displayes the last 50 days accessed file

 find / -size 50M >> it displayes the 50 mb files.

 find / -size +50M –size -100M >> it displayes the 50 mb to 100mb files.

 Find / -size +100M –exec rm –rf {} \; >> it finds the 100 mb files and

delete the files

Find the largest file in the current & sub
directories

find . -type f -exec ls -s {} \; | sort -n -r | head -1

Find the smallest file in the current directory and sub

directories

find . -type f -exec ls -s {} \; | sort -n -r | tail -1

or

find . -type f -exec ls -s {} \; | sort -n | head -1

Find the files which are created between two files

find . -cnewer f1 -and ! -cnewer f2

Find the permissions of the files which contain the name

"java"?

find -name "*java*"|xargs ls -l

Alternate method is

find -name "*java*" -exec ls -l {} \;

Find the files which have the name "java" in it and then display
only the files which have "class" word in them?

find -name "*java*" -exec grep -H class {} \;

Remove files which contain the name "java".

find -name "*java*" -exec rm -r {} \;

Find & Copy Commands :

find . -type f -name "*.mp3" -exec cp {} /tmp/MusicFiles \;

# cp *.mp3 files to /tmp/MusicFiles

How to Copy one file to Many directories 

find dir1 dir2 dir3 dir4 -type d -exec cp header.shtml {} \;

# copy the file header.shtml to those dirs

Find and Delete Files in the Linux Command Line

find . -name "*.bak" -delete

Find files with different file extensions

find . -type f \( -name "*.c" -o -name "*.sh" \)

find . -iname foo

If you're just interested in directories, search like this:

find . -iname foo -type d

And if you're just looking for files, search like this:

find . -iname foo -type f

Thanks,

Srini