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

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

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

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

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

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

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

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

TMON started with pid=42, OS id=35153 Setting CPU count to 336 ORACLE_BASE from environment = /u01/app/oracle WARNING: ASMB exiting with error ALTER DATABASE MOUNT /* db agent *//* {0:0:468} */ ORA-00210: cannot open the specified control file ORA-00202: control file: '+FRA' ORA-17503: ksfdopn:2 Failed to open file +FRA ORA-15045: ASM file name '+FRA' is not in reference form ORA-00210: cannot open the specified control file ORA-00202: control file: '+DATA' ORA-17503: ksfdopn:2 Failed to open file +DATA ORA-15045: ASM file name '+DATA' is not in reference form ORA-205 signalled during: ALTER ..read more

This document will demonstrate the steps required to configure SSL/TLS on RAC or SIHA. Instruction is by example and also shows various methods to check the state of the configuration. https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-secure-sockets-layer-authentication.html#GUID-85027CCD-62A0-4E95-8B02-020745D50900 1. Configure TCPS protocol endpoints. -bash-5.0# /u01/app/19.0/grid/bin/srvctl config listener Name: LISTENER Type: Database Listener Network: 1, Owner: grid Home: <CRS home> End points: TCP:1521 Listener is enabled. Listener is individually en ..read more