Modern Oracle DBA

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

  Solution: Check that the instance is not running using ps –ef oacore_server2 If it is, use kill -9 <pid> to stop the running instance. Remove the state file: If the managed server name is “ oacore_server2 ”, then go to the managed server machine, delete the following: cd $DOMAIN_HOME/servers/<Server-Name>/data/nodemanage/<Server-Name>.state <Server-Name>.state is the node manager file containing instance information and status. After deleting this file, the managed server can be remotely started from the Admin Console

This steps are ONLY for deletion of secondary application tier nodes. The primary application tier node cannot be deleted using these steps Before planning to delete the secondary node please ensure patching cycle enabled/not If patching cycle enabled we are not able to perform this activity. If example some scenarios we did some wrong configuration in this scenario we need to delete the node and add it back will see the steps below Sample Error: ERROR: vtebs1 entry is there in AD_NODES_CONFIG_STATUS ERROR: vtebs1 entry is there in fnd_oam_context_files ERROR: vtebs1 entry is there in ADOP_VALID_NODES ERROR: vtebs1 entry is there in fnd_nodes ERROR: vtebs1 already registered in AutoConfig tables ERROR: Some old configuration footprints still exists Run ebs-delete-node before re-running add node Solution: Execute below query and check node status SQL> select node_name, node_mode, support_cp, support_web, support_admin,support_forms from FND_NODES; Syntax: ...

  Finding my database is Multitenant or not? SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE; NAME Multitenant Option ? OPEN_MODE CON_ID --------- -------------------------- -------------------- ---------- CTEST1 Multitenant Option enabled READ WRITE 0 Connecting the CDB to PDB SQL> show con_name CON_NAME ------------------------------ CDB$ROOT Display the list of available services for the root and the PDBs. SQL> Show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 appspdb READ WRITE NO Verifying the status select pdb_name, status from cdb_pdbs; select name, open_mode from v$pdbs; select name, con_id from v$active_services order by 1; Connecting to PDBS SQL> alter session set container="appsdb"; Session a...