Modern Oracle DBA

UTL_FILE UTL_FILE is a PL/SQL package that allows programs to read and write operating system text files. It is commonly used to write custom log files. Examples General debug and temporary files Log and output files generated by Concurrent Processing In Oracle Database 11g R2 and 12c Release 1, the UTL_FILE_DIR database initialization parameter must be configured to define the directory or directories where these files are located. In Oracle Database 18c and later, the UTL_FILE_DIR database initialization parameter is desupported. Instead, Database Directory Objects are used to specify the locations used for PL/SQL file I/O. Advantage There is no need for a database bounce, and directory changes are reflected immediately. Steps to Create the UTL_FILE Directory Step 1: Query to Find the Existing UTL Directory SELECT name, value FROM apps.v$parameter WHERE name LIKE 'utl%'; Step 2: Create a New Directory at OS Level Example: mkdir /utlfile/common/sanjeev Step 3: Export PERL and ...

  SQL> select count(*),status from wf_notifications group by status;   COUNT(*) STATUS ---------- --------------       2018   CLOSED      35607  CANCELED       7390   OPEN   SQL> select count(*),status, MAIL_STATUS from wf_notifications group by status, MAIL_STATUS order by status;     COUNT(*)  STATUS   MAIL_STA ------------------------------------------------          3       CANCELED  ERROR         35604  CANCELED         12      CLOSED    ERROR       1921   CLOSED    SENT         86      CLOSED        444...

Let us first understand the logical structure of the database before we look at the tablespace. Tablespace Datafiles Segments Extents Data blocks Tablespaces : Oracle Database consists of one or more logical storage units called tablespace Its collectively store all the database data. Each tablespace consists one or more files called datafiles. Data is collectively stored in the datafiles that constitute each tablespace of the database Image Source: Oracle Doumentations    Types of tablespaces There are 2-types of tablespaces there System tablespace Non-System tablespace   System tablespace System tablespace created along with database It’s must require in all database Should not contain user data’s   Non-System tablespace Enable more flexibility in database administration Control the amount of space to user objects Separate data by backup requirement   Creating tablespace Create tablespace <Tablespace_Name> Da...

Before the start the BCT let’s take quick recap for incremental backup first Incremental backup The goal of an incremental backup is to back up only those data blocks that have changed since a previous backup. During incremental backup, RMAN checks every data block in all datafiles of the database and compares its SCN number with the SCN value that is at the incremental 0 backup. If the first value is greater than the second, it means that the data block has been changed after the last backup and needs to be backed up, so RMAN writes it to the backup file. This procedure is done for every data block in the database So as RMAN checks all data blocks and writes down only changed ones during incremental backup It takes more time for the checking process so BTC introduced in 10G Block Change Tracking (BCT) Oracle 10gR2 database introduced Block change tracking (BTC) It is used to improve the incremental backup performance by recording changed blocks in each datafile in ...

  Step 1: The following environment variables must be set in order to run adbldxml.pl. ORACLE_HOME export ORACLE_HOME=<Physical Path, where the Oracle DB is installed> ORACLE_SID export ORACLE_SID=<name of the SID selected when you installed the DB> PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH ORA_NLS10 export ORA_NLS10=$ORACLE_HOME/nls/data/9idata Ensure that the LD_LIBRARY_PATH environment variable is set correctly. PERL5LIB needs to be set export PERL5LIB=/u02/app/oracle/product/12.1.0/dbhome_3/perl/lib/5.16.3:/u02/app/oracle/product/12.1.0/dbhome_3/perl/lib/site_perl/5.16.3:/u02/app/oracle/product/12.1.0/dbhome_3/appsutil/perl Finally set the TNS_ADMIN variable to point to $ORACLE_HOME/network/admin/<SID>_<hostname> : export TNS_ADMIN=$ORACLE_HOME/network/admin/<SID>_<hostname> Generating DB Context file [oracle@vtebs1-lqhjl3 bin]$ perl adbldxml.pl jtop=/u02/app/oracle/product/12.1.0/dbhome_3/j...

  $INST_TOP/admin/install directory and run adgendbc.sh script. [applmgr@vtebs1 secure]$ cd $INST_TOP/admin/install [applmgr@vtebs1 install]$ sh adgendbc.sh adgendbc.sh started at Sat Jul 10 03:31:35 EDT 2021 Enter the APPS username: apps Enter the APPS password: SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 10 03:31:41 2021 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Enter value for 1: Enter value for 2: Enter value for 3: Connected. [ APPS_DATABASE_ID ] Application Id : 0 Profile Value  : test Level Name: SITE INFO           : Updated/created profile option value. PL/SQL procedure successfully completed. Commit complete. Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production ============================== * * * * DBC PARAMETERS * * * * ============================== fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5 fnd_jdbc_buffer_dec...