
Oracle DBA Resource
20 FOLLOWERS
Oracle Database Administrator with nearly 16 years of rich experience in Core Oracle Database Administration, Oracle Administration for Core Banking, Telecom Billing Applications. Proficient at conducting Oracle installation, database creation, Performance Tuning, consolidation & configuration and up-gradation of Oracle Server Software & Fusion Middleware.
Oracle DBA Resource
3w ago
Tablespace Creation Procedure to move Table across tablespace Move Table Partition and Sub-Partitions Move LOB and store them as SECUREFILE Rebuild Index, Partition Index and Sub-Partition Index Important Point ..read more
Oracle DBA Resource
4M ago
You're currently a free subscriber. Upgrade your subscription to get access to the rest of this post and other paid-subscriber only content.
Upgrade subscription ..read more
Oracle DBA Resource
1y ago
Following script can be used to extract the DDL of INVALID or ALL objects to a single spool file per database listed.
Modify the ORACLE_HOME path and update the database instance name running in the database.
# Set the Oracle environment variables
export ORACLE_HOME=/path/to/oracle/home
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
# Define the SQL command to run
sql_command="
select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
select
owner,
object_name,
decode(object_type,
'DATABASE LINK ..read more
Oracle DBA Resource
1y ago
Following shell script can be used to capture the Database name and Running Database Instance and run the srvctl command
#!/bin/ksh
# get the current list of oracle instances running on the node
#
# for sidnow in `ps -fea|grep pmon|grep -v -e grep -e ASM|awk {' print $NF '}|sort|sed s/ora_pmon_//`
for sidnow in `ps -fea|grep pmon|grep -v -e grep -e ASM -e MGMT|awk {' print $NF '}|sort|sed 's/ora_pmon_//'|grep -v '^s\/\/\/'`
do
#echo "Stopping database instance:$sidnow ..."
echo "srvctl status database -d ${sidnow%[1-9]} #pre stop "
#echo "srvctl stop instance -d ${sidnow%[1-9]} -i $sidn ..read more
Oracle DBA Resource
1y ago
In Oracle Database, for a sql-id there are possible different plan hash values, and often times during execution of the SQL, the execution plan picked sub-optimal plan.
There are one way to create the profile.
declare
ar_profile_xml clob;
cl_sql_text clob;
begin
select regexp_replace(other_xml,'.(.).*','\1')
into ar_profile_xml
from dba_hist_sql_plan -- this can be queried from gv$sql_plan too
where SQL_ID = 'awup6ytqb2p5c'
AND PLAN_HASH_VALUE = 3061475182 -- better execution plan identified from step 2
and other_xml is not null
and rownum =1;
select sql_text into cl_sql_text
from dba_h ..read more
Oracle DBA Resource
1y ago
select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid.')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and s.end_interval_time >= to_date(tr ..read more
Oracle DBA Resource
1y ago
Invalid Objects from OLAP Schema
To check if OLAP being used by Oracle Database.
select VALUE from v$OPTION where PARAMETER = ‘OLAP’;
If the above value is TRUE, the instance does support OLAP.
query the feature usage table DBA_FEATURE_USAGE_STATISTICS to by using the following:
SQL> select name “FEATURE”, first_usage_date “FROM”, last_usage_date “TO”
from DBA_FEATURE_USAGE_STATISTICS
where name like ‘%OLAP%’
query the DBA registry to determine the current state of the OLAP option:
SQL> col comp_id format a10
SQL> col comp_name format a30
SQL> col version format a15
SQL> col sta ..read more
Oracle DBA Resource
3y ago
The Oracle 19c ASM clusterware can be installed in non-clustered way in a standalone machine with Raw disks presented to the host.
In this example, with Oracle Solaris SPARC we going to install 19.6 ASM and RDBMS software in standalone manner. As a result we configured the RDBMS software and Disks on ASM, so Database can be created on 2 diskgroups and we can extend this setup to add another node to make 2 node cluster
Step 1:
Get following binary files from Oracle
SOLARIS.SPARC64_193000_grid_home.zip
SOLARIS.SPARC64_193000_db_home.zip
Step 2:
Unzip the file in folder /u01/app/19.0/grid using g ..read more
Oracle DBA Resource
3y ago
Chef Inspec Test can perform on database server which validate the resources availability and consistency as per desired stage.
Inspec required to configured as bundle, on Windows Desktop systems using Powershell running as Administrator
gem install bundler
gem install chef-utils -v ‘16.6.14’
gem install chef-config -v 16.6.14
gem install chef-telemetry -v 1.0.14
gem install inspec-bin -v 4.18.51
gem install rubocop-rake
gem install yamllint -v 0.0.9
gem install inspec -v 4.18.51
gem install rake
gem install rubocop
gem install rubocop-rake
gem install rubocop-rspec
Sample code can be used to ..read more
Oracle DBA Resource
3y ago
Configuring Kernel Parameters
Verify that the kernel parameters shown in the following table are set to values greater than or equal to the minimum value shown. The procedure following the table describes how to verify and set the values.
Note:
The kernel parameter and shell limit values in this section are minimum values only. For production database systems, Oracle recommends that you tune these values to optimize the performance of the system. Refer to your operating system documentation for more information about tuning kernel parameters.
Parameter
Minimum Value
File
semmsl
sem ..read more