default response file location:
export ORACLE_HOME=/oracle/app/oracle/product/19.9.0.0/dbhome_1
cd $ORACLE_HOME/assistants/dbca

By using the default response files, you can create a new response file, as per your requirement.

Below is the response file i have created.

cat /export/home/oracle/db_create.rsp



responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=DBACLASS9
sid=DBACLASS9
databaseConfigType=SI
policyManaged=false
createServerPool=false
createAsContainerDatabase=false
templateName=/oracle/app/oracle/product/19.9.0.0/dbhome_1/assistants/dbca/templates/New_Database.dbt
sysPassword=oracle#123	
systemPassword=oracle#123
runCVUChecks=FALSE
dvConfiguration=false
olsConfiguration=false
datafileJarLocation=
datafileDestination=/oradata/{DB_UNIQUE_NAME}/
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=UTF8
listeners=LISTENER_B2COM
variables=ORACLE_BASE_HOME=/oracle/app/oracle/product/19.9.0.0/dbhome_1,DB_UNIQUE_NAME=DBACLASS9,ORACLE_BASE=/oracle/app/oracle,PDB_NAME=,DB_NAME=DBACLASS9,ORACLE_HOME=/oracle/app/oracle/product/19.9.0.0/dbhome_1,SID=DBACLASS9
initParams=undo_tablespace=UNDOTBS1,sga_target=10093MB,db_block_size=8192BYTES,log_archive_dest_1='LOCATION=/archive/DBACLASS9',nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP) (SERVICE=DBACLASS9XDB),diagnostic_dest={ORACLE_BASE},control_files=("/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/oradata/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=1400,pga_aggregate_target=3365MB,nls_territory=AMERICA,local_listener=LISTENER_DBACLASS9,open_cursors=300,log_archive_format=%t_%s_%r.dbf,compatible=19.0.0,db_name=DBACLASS9,audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0  

Before creating the database, make sure

1. Oracle binary/home is already installed
2. Requirement directory structure is already created

Now run DBCA in silent mode:

oracle@b2bdev:~$ dbca -silent -createDatabase -responseFile /export/home/oracle/db_create.rsp
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
5% complete
Creating and starting Oracle instance
6% complete
9% complete
Creating database files
10% complete
14% complete
Creating data dictionary views
15% complete
18% complete
19% complete
22% complete
23% complete
25% complete
27% complete
Oracle JVM
34% complete
41% complete
48% complete
50% complete
Oracle Text
51% complete
54% complete
55% complete
Oracle Multimedia
68% complete
Oracle OLAP
69% complete
70% complete
71% complete
72% complete
73% complete
Oracle Spatial
74% complete
82% complete
Completing Database Creation
85% complete
86% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /oracle/app/oracle/cfgtoollogs/dbca/DBACLASS9.
Database Information:
Global Database Name:DBACLASS9
System Identifier(SID):DBACLASS9
Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/DBACLASS9/DBACLASS9.log" for further details.

DB has been created successfully

post check:

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DBACLASS9    READ WRITE

SQL> select comp_id,status from dba_registry;

COMP_ID                        STATUS
------------------------------ --------------------------------------------
CATALOG                        VALID
CATPROC                        VALID
RAC                            OPTION OFF
JAVAVM                         VALID
XML                            VALID
CATJAVA                        VALID
APS                            VALID
XDB                            VALID
OWM                            VALID
CONTEXT                        VALID
ORDIM                          VALID

COMP_ID                        STATUS
------------------------------ --------------------------------------------
SDO                            VALID
XOQ                            VALID

The post How to create database using dbca in silent mode – 19C appeared first on DBACLASS.

In this below example we will show how to apply the jdk patch for standalone and grid database

1. Find the latest jdk patch available.

JDK and PERL Patches for Oracle Database Home and Grid Home (Doc ID 2584628.1)

Download the jdk patch for your respective database version and copy the zip file to your db server.

 Steps for applying jdk patch on  standalone database:

For standalone database, we need to apply only to the database_home i.e is the rdbms home.

1. Check the existing jdk version:

[oracle@dbhost-1 bin]$ /u01/app/oracle/product/12.2/dbhome_1/jdk/bin/java -version
java version "1.8.0_271"
Java(TM) SE Runtime Environment (build 1.8.0_271-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.271-b09, mixed mode)

2. Shutdown database

SQL> SHUTDOWN IMMEDIATE;

3. unzip the patch:

cd /backup/software/
unzip 32162748_patch.zip
cd 32162748

4. Apply the patch:

Note –  > Please ensure that latest opatch utility installed.

export ORACLE_HOME=/u01/app/oracle/product/12.2/dbhome_1
cd /backup/software/32162748


[oracle@dbhost-1 32162748]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2/dbhome_1//oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2/dbhome_1/cfgtoollogs/opatch/opatch2021-03-25_12-57-53PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32162748

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '32162748' to OH '/u01/app/oracle/product/12.2/dbhome_1'

Patching component oracle.jdk, 1.8.0.91.0...
Patch 32162748 successfully applied.
Log file location: /u01/app/oracle/product/12.2/dbhome_1/cfgtoollogs/opatch/opatch2021-03-25_12-57-53PM_1.log

OPatch succeeded.

5. Start the database:

SQL> startup
ORACLE instance started.

Total System Global Area 1.6267E+11 bytes
Fixed Size                 29864856 bytes
Variable Size            3.4897E+10 bytes
Database Buffers         1.2724E+11 bytes
Redo Buffers              507002880 bytes
Database mounted.
Database opened.
SQL>

6. Now check the jdk version again:

[oracle@dbhost-1 32162748]$  /u01/app/oracle/product/12.2/dbhome_1/jdk/bin/java -version
java version "1.8.0_281"
Java(TM) SE Runtime Environment (build 1.8.0_281-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.281-b09, mixed mode)

We can see JDK has version has been change from 1.8.0_271 to 1.8.0_281

Steps for Applying JDK patch on GRID /RAC database:

In case of RAC database, we need to apply jdk patch on both RDBMS home and GRID home.

First we will start with GRID_HOME on node 1:

1. Unlock the crs: [ from root]

[root@dbhost-1]# cd $GRID_HOME/crs/install
[root@dbhost-1 install]# ./rootcrs.sh -prepatch
Using configuration parameter file: /u01/app/12.2/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/dbhost-1/crsconfig/crspatch_2021-03-25_01-02-53PM.log
2021/03/25 13:03:14 CLSRSC-347: Successfully unlock /u01/app/12.2/grid

NOTE –  > In case of standalone grid , use roothas.sh – prepatch command

2. Apply the patch on grid_home:

[oracle@dbhost-1 32162748]$ cd /backup/software/32162748
[oracle@dbhost-1 32162748]$
[oracle@dbhost-1 32162748]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/12.2/grid/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.2.0.1.4
Log file location : /u01/app/12.2/grid/cfgtoollogs/opatch/opatch2021-03-25_13-04-13PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32162748

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '32162748' to OH '/u01/app/12.2/grid'

Patching component oracle.jdk, 1.8.0.91.0...
Patch 32162748 successfully applied.
Log file location: /u01/app/12.2/grid/cfgtoollogs/opatch/opatch2021-03-25_13-04-13PM_1.log

OPatch succeeded.

3. Now lock the CRS: [ from root ]

[root@dbhost-1]# cd $GRID_HOME/crs/install
[root@dbhost-1 install]# ./rootcrs.sh -postpatch
Using configuration parameter file: /u01/app/12.2/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/dbhost-1/crsconfig/crspatch_2021-03-25_01-04-55PM.log
2021/03/25 13:04:57 CLSRSC-329: Replacing Clusterware entries in file '.....'

NOTE –  > In case of standalone grid , use roothas.sh – postpatch command

4. Now on RDBMS HOME on node 1:

SQL>shutdown immediate
SQL> exit


export ORACLE_HOME=/u01/app/oracle/product/12.2/dbhome_1
cd /backup/software/32162748


[oracle@dbhost-1 32162748]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2/dbhome_1//oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2/dbhome_1/cfgtoollogs/opatch/opatch2021-03-25_12-57-53PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32162748

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '32162748' to OH '/u01/app/oracle/product/12.2/dbhome_1'

Patching component oracle.jdk, 1.8.0.91.0...
Patch 32162748 successfully applied.
Log file location: /u01/app/oracle/product/12.2/dbhome_1/cfgtoollogs/opatch/opatch2021-03-25_12-57-53PM_1.log

OPatch succeeded.


SQL> startup
ORACLE instance started.

Total System Global Area 1.6267E+11 bytes
Fixed Size                 29864856 bytes
Variable Size            3.4897E+10 bytes
Database Buffers         1.2724E+11 bytes
Redo Buffers              507002880 bytes
Database mounted.
Database opened.
SQL>

Once both grid_home and rdbms_home patching is done, we need to do the same steps on other node also.

The post How to apply JDK patch in oracle database appeared first on DBACLASS.

$ORACLE_HOME/OPatch/opatch lsinventory

2. list patchsets applied :

$ORACLE_HOME/OPatch/opatch lspatches

3. Find opatch version:

$ORACLE_HOME/OPatch/opatch version

4. Find details of a particular patch(before applying):

$ORACLE_HOME/OPatch/opatch query -all {PATCH_PATH}

$ORACLE_HOME/OPatch/opatch query -all /software/PSUPATCH/30089984

5. Apply a patch to RDBMS HOME:

-- You may need to shutdown the database and listener services:

cd /SOFTWARE/PSUPATCH/30089984 -- Go to the patch path:
$ORACLE_HOME/OPatch/opatch apply

6. Rollback an patch from RDBMS HOME:

$ORACLE_HOME/OPatch/opatch rollback -id [patch_id]
$ORACLE_HOME/OPatch/opatch rollback -id 30089984

7. Apply one off patch in grid_home:

root # $GI_HOME/crs/install/rootcrs.sh -prepatch. 


oracle$ cd /SOFTWARE/PSUPATCH/30089984 -- Go to the patch path:
oracle$ $GRID_HOME/OPatch/opatch apply


root# $GI_HOME/crs/install/rootcrs.sh -postpatch

8. Check conflict against ORACLE_HOME

- go to patch folder.
cd 27734982

[27734982]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

9.Check whether active executables are running:

- go to patch folder.
cd 27734982


[27734982]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

10.Opatch command using different inventory location:

$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /etc/orainv/orainventory

The post Useful OPATCH commands appeared first on DBACLASS.

when sec_case_sensitive_logon is TRUE – -> t he database use login passwords are case sensitive.

when sec_case_sensitive_logon is FALSE – -> means database use login passwords are case insensitive.

DEFAULT VALUE IS TRUE .

Lets see below DEMO:

DEMO:

1. When sec_case_sensitive_logon is TRUE.

SQL> show parameter sec_case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE



SQL> alter user DBACLASS identified by DBAclass;

User altered.



SQL> connect DBACLASS/dbaclass
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.



SQL> connect  DBACLASS/DBAclass
Connected.

We can see the the password is case sensitive.

2. When sec_case_sensitive_logon is FALSE.

Lets make the parameter to false:

SQL> ALTER SYSTEM SET sec_case_sensitive_logon=FALSE scope=both;

database altered.


SQL> show parameter sec_case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> alter user DBACLASS identified by DBAclass;

User altered.


Now try to connect with any case(small or caps)  

SQL>
SQL> connect DBACLASS/dbaclass
Connected.
SQL>  connect DBACLASS/DBACLASS
Connected.

We can see password is case insensitive.

The post sec_case_sensitive_logon parameter in oracle appeared first on DBACLASS.

NOTE – > This activity needs downtime.

         FOR MULTIPLEXING IN RAC – > STEPS FOR MULTIPLEXING IN ORACLE RAC

In this below DEMO, we will explain the steps for multiplexing control files.

DEMO:

1. Check current control files:

SQL> select name from gv$controlfile
  2  ;

NAME
--------------------------------------------------------------------------------
/dmdata02/oradata/BSDMSIT2/control01.ctl
/dmdata02/oradata/BSDMSIT2/control02.ctl
/dmdata07/oradata/control03.ctl

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /dmdata02/oradata/BSDMSIT2/con
                                                 trol01.ctl, /dmdata02/oradata/
                                                 BSDMSIT2/control02.ctl

Create a directory in the new mount point, where we will keep the new control file.

mkdir -p /dmdata07/oradata

2. update the control_files parameter by adding the new path also

SQL> alter system set control_files='/dmdata02/oradata/BSDMSIT2/control01.ctl','/dmdata02/oradata/BSDMSIT2/control02.ctl','/dmdata07/oradata/control03.ctl' scope=spfile;

System altered.

3. Shutdown the database and start with nomount:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 8388608000 bytes
Fixed Size                  5303664 bytes
Variable Size            3623879312 bytes
Database Buffers         4747952128 bytes
Redo Buffers               11472896 bytes
SQL> exit

4. restore the controlfile from existing control file .

$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 24 08:50:19 2020

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

connected to target database: BSDMSIT2 (not mounted)

RMAN> restore controlfile from '/dmdata02/oradata/BSDMSIT2/control01.ctl';

Starting restore at 24-FEB-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/dmdata02/oradata/BSDMSIT2/control01.ctl
output file name=/dmdata02/oradata/BSDMSIT2/control02.ctl
output file name=/dmdata07/oradata/control03.ctl
Finished restore at 24-FEB-20

RMAN> exit

5. Restart the database:

SQL> shutdown immediate;
startup
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 8388608000 bytes
Fixed Size                  5303664 bytes
Variable Size            3623879312 bytes
Database Buffers         4747952128 bytes
Redo Buffers               11472896 bytes
Database mounted.
Database opened.

6. Check whether new control file is available or not :

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /dmdata02/oradata/BSDMSIT2/con
                                                 trol01.ctl, /dmdata02/oradata/
                                                 BSDMSIT2/control02.ctl, /dmdat
                                                 a07/oradata/control03.ctl
                                             NUMBER

SQL> select name from gv$controlfile;

NAME
--------------------------------------------------------------------------------
/dmdata02/oradata/BSDMSIT2/control01.ctl
/dmdata02/oradata/BSDMSIT2/control02.ctl
/dmdata07/oradata/control03.ctl

We can observe controlfile is available now on all 3 locations.

The post How to multiplex control file in standalone database appeared first on DBACLASS.

1. Gather dictionary stats:

-- It gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and other internal schemas.

EXEC DBMS_STATS.gather_dictionary_stats;

2. Gather fixed object stats:

--- Fixed object means gv$ or v$views

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

3. Gather full database stats:

EXEC DBMS_STATS.gather_database_stats;

-- With estimate_percent to 15 percent or any other value , if the db size very huge. 

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

-- With auto sample size and parallel degree 

EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

4. Gather schema statistics:

EXEC DBMS_STATS.gather_schema_stats('DBACLSS');

EXEC DBMS_STATS.gather_schema_stats('DBACLASS', estimate_percent => 25);

EXEC DBMS_STATS.gather_schema_stats('DBACLASS', estimate_percent => 100, cascade => TRUE);

-- STATS WITH AUTO ESTIMATION and degree 8 

exec dbms_stats.gather_schema_stats( ownname => 'DBACLASS',method_opt => 'FOR ALL COLUMNS SIZE 1', 
granularity => 'ALL', degree => 8, cascade => TRUE, 
estimate_percent=>dbms_stats.auto_sample_size);

5. Gather table statistics:

EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP');
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15, cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS  (ownname => 'DBACLASS' , tabname => 'EMP',cascade => true, 
method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 8);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DBACLASS' , tabname => 'EMP',
cascade => true, method_opt=>'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', degree => 8);

6. Gather stats for single partition of a table:

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/

7. Lock/unlock statistics:

-- Lock stats of a schema:
EXEC DBMS_STATS.lock_schema_stats('DBACLASS');

-- Lock stats of a table:
EXEC DBMS_STATS.lock_table_stats('DBACLASS', 'EMP');

-- Lock stats of a partition:
EXEC DBMS_STATS.lock_partition_stats('DBACLASS', 'EMP', 'EMP');

-- unlock stats of a schema:

EXEC DBMS_STATS.unlock_schema_stats('DBACLASS');
-- unlock stats of a table:

EXEC DBMS_STATS.unlock_table_stats('DBACLASS', 'DBACLASS');
--unlock stats of a partition:

EXEC DBMS_STATS.unlock_partition_stats('DBACLASS', 'EMP', 'TEST_JAN2016');

--- check stats status:

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

8 . Delete statistics:

-- Delete complete db statistics:
EXEC DBMS_STATS.delete_database_stats;

-- Delete schema statistics:
EXEC DBMS_STATS.delete_schema_stats('DBACLASS');

-- Delete table statistics:
EXEC DBMS_STATS.delete_table_stats('DBACLASS', 'EMP');

-- Delete column statistics:
EXEC DBMS_STATS.delete_column_stats('DBACLASS', 'EMP', 'EMPNO');

-- Delete index statistics:

EXEC DBMS_STATS.delete_index_stats('DBACLASS', 'EMP_PK');

-- Delete dictionary statistics:
EXEC DBMS_STATS.delete_dictionary_stats;


-- Delete fixed object statistics:

exec dbms_stats.delete_fixed_objects_stats;

-- Delete system statistics:

exec dbms_stats.delete_system_stats('STAT_TAB');

8. Setting statistics preference:

-- View preference details for the database:

SELECT dbms_stats.get_prefs('PUBLISH') EST_PCT FROM dual;

-- View Publish preference for table 


-- View Publish preference for schema:

select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual

-- View preference details for table

select dbms_stats.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'PUBLISH') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'INCREMENTAL') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'GRANULARITY') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'STALE_PERCENT')  FROM DUAL;
select   DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'ESTIMATE_PERCENT')  FROM DUAL;
select   DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'DEGREE')  FROM DUAL;


-- Set table  preferences

exec dbms_stats.set_table_prefs('DBACLASS','EMP','PUBLISH','FALSE');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','ESTIMATE_PERCENT','20');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','DEGREE','8');




-- Set schema preferences:

exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','ESTIMATE_PERCENT','20');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','CASCADE','TRUE');


-- Set database preference:

exec dbms_stats.set_database_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_database_prefs('DEGREE', '16');


-- Set global preference:

exec dbms_stats.set_global_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_global_prefs('DEGREE', '16');

9 . Deleting preferences :

-- Deleting schema preference:

exec dbms_stats.delete_schema_prefs('DBACLASS', 'DEGREE');
exec dbms_stats.delete_schema_prefs('DBACLASS', 'CASCADE');


-- Delete database preference:
exec dbms_stats.delete_database_prefs('ESTIMATE_PERCENT', FALSE);
exec dbms_stats.delete_database_prefs('DEGREE', FALSE);

10 . Publish pending statistics:

-- For schema DBACLASS
exec dbms_stats.publish_pending_stats('DBACLASS',null);

-- For table DBACLASS.EMP
EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('DBACLASS','EMP');

11. Delete pending statistics:

-- for table DBACLASS.EMP
exec dbms_stats.delete_pending_stats('DBACLASS', 'EMP');

-- For schema DBACLASS
exec dbms_stats.delete_pending_stats('DBACLASS', null);

12. Upgrade stats table:

----- If we are importing stats table from higher version to lower version,
then before importing in the database, we need to upgrade the stats table.


EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>'RAJ',STATTAB =>'STAT_TEST');

13. View/modify statistics retention period:

-- View current stats retention

select dbms_stats.get_stats_history_retention from dual;

-- Modify the stats retention

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);

14. create stats table:

--- Create staging table to store the statistics data

exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');

15. Export stats data:

-- Export full database stats to a table SCOTT.STAT_BACKUP

exec dbms_stats.export_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for table DBACLASS.EMP to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Export stats for schema DBACLASS to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Export fixed object stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export dictionary stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for index DBACLAS.EMP_UK1 to SCOTT.STAT_BACKUP table

exec dbms_stats.export_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');

16. Import stats table data:

-- Import full database stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for table DBACLASS.EMP from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Import stats for schema DBACLASS from  stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Import fixed object stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import dictionary stats from  table SCOTT.STAT_BACKUP

exec dbms_stats.import_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for index DBACLAS.EMP_UK1 from  SCOTT.STAT_BACKUP table

exec dbms_stats.import_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');

17 . Few stats related sql queries:

-- Check stale stats for table:

select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME';

--Check stale stats for index:

select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME';


-- For getting history of TABLE statistics
setlines 200
col owner for a12
col table_name for a21
select owner,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&TABLE_NAME';



-- Space used to store statistic data in SYSAUX tablespace:


SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';


-- Check whether table stats locked or not:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

The post Useful gather statistics commands in oracle appeared first on DBACLASS.

NOTE – > It can be done ,when the database up are running. No downtime required. 

FOR STANDALONE DATABASE:

1. Find the current archivelog location:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dbaclass05/ORCLSIT2/Arch
Oldest online log sequence     25626
Next log sequence to archive   25628
Current log sequence           25628


SQL>  select destination,STATUS from v$archive_dest where statuS='VALID';

DESTINATION                    			    STATUS
----------------------------------                ---------
/dbaclass05/ORCLSIT2/Arch                             VALID

create the new archive location:

mkdir -p /dbaclass06/arch

change the destination:

SQL> alter system set log_archive_dest_1='LOCATION=/dbaclass06/arch' scope=both;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dbaclass06/arch
Oldest online log sequence     25626
Next log sequence to archive   25628
Current log sequence           25628

SQL>  select destination,STATUS from v$archive_dest where statuS='VALID';

DESTINATION                    			    STATUS
----------------------------------                ---------
/dbaclass06/arch                                    VALID

Check whether archives are getting generated at new location:

SQL> alter system switch logfile;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

$ cd /dbaclass06/arch
$ ls -ltr
total 11768
-rw-r----- 1 oracle oinstall 12049920 Mar 22 15:17 1_25628_929882536.dbf

For Real application cluster(RAC):

In RAC, the archive destination is pointed to an ASM diskgroup.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=+CRMARCH

Change the archive destination:

Before pointing the path to new ASM DG, make sure that Diskgroup exists.

-- Use sid='*', so that parameter will be changed across all the instance pfiles.

alter system set log_archive_dest_1='LOCATION=+FRA' scope=both sid='*'

System altered.


SQL> select destination,STATUS from v$archive_dest where statuS='VALID';

DESTINATION        STATUS
------------------ ---------
+CRMARCH           VALID

The post how to change archivelog destination in oracle appeared first on DBACLASS.

EXAMPLE:

—  CREATE AN INDEX ( BY DEFAULT THE INDEX WILL BE A VISIBLE ONE)

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER

SQL> select count(*) from emp;

  COUNT(*)
----------
     90323


SQL>  create index EMP_ID1 on DBACLASS.EMP(OBJECT_ID);

Index created.

-- Check the visibility of the index 

SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';

INDEX_NAME          VISIBILIT
------------------- ---------
EMP_ID1             VISIBLE

Check the explain plan:

SQL> explain plan for select count(*) from dbaclass.emp where object_id=1;

Explained.

SQL> select * from table(dbms_xplan.display)
  2  ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Plan hash value: 853747123

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_ID1 |     1 |     5 |     1   (0)| 00:00:01 | -->>> INDEX USED 
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1)

14 rows selected.

As expected optimizer is using the index for the query.

When index is INVISIBLE:

Now make the INDEX invisible and check the explain plan for the same query.

SQL> alter index EMP_ID1 invisible;

Index altered.

SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';

INDEX_NAME          VISIBILIT
------------------- ---------
EMP_ID1             INVISIBLE

SQL> explain plan for select count(*) from dbaclass.emp where object_id=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   149   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     5 |   149   (2)| 00:00:01 | -->> FULL TABLE SCAN  
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=1)

14 rows selected.

We can see, it is bypassing the index scan and doing full table scan, Despite the index is present.
Because the index is invisible.

Create an invisible index directly:

 SQL> create index EMP_IDINV on DBACLASS.EMP(OBJECT_ID) invisble;

SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_IDINV';

INDEX_NAME          VISIBILIT
------------------- ---------
EMP_IDINV           INVISIBLE

optimizer_use_invisible_indexes paramter and invisible index There is an parameter

optimizer_user_invisible_indexes, which is by default set to FALSE, means, optimizer will ignore all the invisible indexes in the database.

SQL> show parameter invi

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
optimizer_use_invisible_indexes      boolean     FALSE

To force the optimizer to use all invisible indexes at database level. then set it to TRUE.

SQL> alter system set optimizer_use_invisible_indexes=TRUE scope=both;

System altered.

SQL>  show parameter  optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL>


SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';

INDEX_NAME          VISIBILIT
------------------- ---------
EMP_ID1             INVISIBLE

SQL> explain plan for select count(*) from dbaclass.emp where object_id=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Plan hash value: 853747123

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_ID1 |     1 |     5 |     1   (0)| 00:00:01 | --- >>>>>> INDEX USED
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1)

14 rows selected.

We can see the optimizer is using the INDEX, despite it is being invisible.

What is its use?

Most common use is while dropping the INDEX . If we are planning to drop index, then before dropping , keep the index in invisible mode and monitor for sometime. once things are good, we can drop it later.( Because post dropping, creating a index will take lot of time, if table size is huge).

The post Invisible index in oracle database appeared first on DBACLASS.

Sometime, MMON process crashes or get suspended .

Follow below steps to restart the MMON process:

1. Grant restricted session to public:

This grant is required to avoid any impact on the sessions connecting to database.

grant restricted session to public;

2. Enable and disable restricted sessions

Enabling and disabling will start the MMON process.

alter system enable restricted session;

alter system disable restricted session;

3. Revoke the restricted session:

revoke restricted session from  public;

4. Check MMON process;

ps -ef | grep mmon

The post Restart MMON process in oracle database appeared first on DBACLASS.

In ORACLE RAC:(Move from +ORACLDG diskgroup to +DATA diskgroup)

1. Get the current control_file location

SQL>  show parameter control_files

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           +ORACLDG/DBACLASS/CONTROLFILE/control01.ctl

2. Set the new location of controlfile:

SQL> alter system set control_files='+DATA' scope=spfile; System altered.

3. start the database in nomount stage:

srvctl stop database -d DBACLASS
srvctl start database -d DBACLASS -o nomount

4. Restore controlfile to new location:

RMAN>  restore controlfile from '+ORACLDG/DBACLASS/CONTROLFILE/control01.ctl';

Starting restore at 13-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2201 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/DBACLASS/CONTROLFILE/current.349.997455411
Finished restore at 13-JAN-19

5. restart the database:

srvctl stop database -d DBACLASS
srvctl start database -d DBACLASS

6. Check the control_file again:

SQL>  show parameter control_files


NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           +DATA/DBACLASS/CONTROLFILE/curren
                                                                      t.349.997455411

FOR STANDALONE DB(SINGLE INSTANCE DB):

Follow below steps if controlfile resides inside file system(Move from /u01 mount point to /u03 )

1. Get the current control_file location

SQL>  show parameter control_files

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           /u01/oracle/dbaclass/control01.ctl

2. Set the new location of controfile:

SQL> alter system set control_files='/u03/oracle/dbaclass/control01.ctl' scope=spfile;

System altered.

3. start the database in nomount stage:

shutdown immediate;
startup nomount

4. Restore controlfile to new location:

RMAN>  restore controlfile  from '/u01/oracle/dbaclass/control01.ctl';

Starting restore at 13-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2201 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u03/oracle/dbaclass/control01.ctl
Finished restore at 13-JAN-19

5. restart the database:

alter database mount;
alter database open;

6. Check the control_file again:

SQL>  show parameter control_files


NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           /u03/oracle/dbaclass/control01.ctl

The post How to move controlfile to a new location in oracle appeared first on DBACLASS.