Vismo Technologies — Oracle DBA Knowledge Hub: 2021

Vismo Technologies ☰ Menu

Enroll Now →

Wednesday, December 8, 2021

19c Oracle DBA Training || 16-Dec-2021 New Batch || Course Details & Demo Classes

Don't forget to attend Demo classes on 16-Dec-2021 & 17-Dec-2021.

Ping me on my WhatsApp Number +91 9880616848 for registration & more details.

Attend Demo Classes using below zoom Meeting:

https://us02web.zoom.us/j/83073563937?pwd=Zk5RRHJVYnRqQng0UXRsckNWdXNEUT09

Timing:

Dec 16, 2021 09:00 AM IST to 11:00 AM IST

Dec 17, 2021 09:00 AM IST to 11:00 AM IST

Check out this video for more details on course content:

https://youtu.be/xji-4sZ0o74

Check the below link for Course Curriculum:

https://mallik034.blogspot.com/p/database-training.html

Check the below link for Couse registration and Timing and fees details:

https://mallik034.blogspot.com/p/upcoming-batch-schedule.html

#19c #DBA #oracle #database #training #course #announcement #CareerOpportunities

Regards,

Mallik

Mallikarjun Ramadurg

THE ORACLE SME – ORACLE ACE, EXADATA, OCI, OCP, OCA, RAC EXPERT. Mallikarjun Ramadurg is the one of the active member of Oracle Community. Mallikarjun is an Oracle DBA and EXADATA administration Expert and has a deep knowledge in Oracle EBS APPS. He is having more than 11 Years of experience. He has got In-depth knowledge of database design, build, cloning and administration and he has got good exposure to environments like database HA, RAC, Oracle Cloud and Datawarehouse. He has worked on Oracle ERP, having sound knowledge on EBS application administration, apps cloning and upgradation, patching, security and performance turning. He has worked on informatica administration, implementation, design, mappings, workflows. He has good knowledge on data modeling, Erwin data modeling. He has good hands-on experience in Oracle Cloud and infrastructure level. He is currently working as Oracle SME & Staff Customer Success Engineer @ Rubrik Inc, Bangalore India

Wednesday, November 24, 2021

Automatic SQL Tuning Adviser

Automatic SQL Tuning Adviser:

=============================

Optimizer --- Will generate and pick execution plan

Suppose Stale Table or Wrong statistics of a table

1. Statistical Analysis
2. Accessing Path (Using Index or not)

High Level steps for SQL Tuning Adviser:

How to find the SQL ID:

=======================

select sql_id from v$sql where sql_text like 'select * from student';

Create Tuning Task:

===================

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

Execute Tuning Task:

====================

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

Status of Tuning Task:

=====================

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

Display the Recommendation:

==========================

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

Drop the Tuning Task:

======================

execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

Find out state Tables:

======================

set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('MALLIK')

AND stale_stats='YES'

ORDER BY owner;

Gather table state:

===================

execute dbms_stats.gather_table_stats(ownname =>'MALLIK',tabname =>'STUDENT',estimate_percent =>100);

Execution logs from SQL Tuning Adviser:

=======================================

1. Run SQL statement and capture the SQL ID:

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle 4941 1 0 Nov12 ? 00:00:16 ora_smon_TESTDB

oracle 8631 5466 0 00:55 pts/1 00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus mallik/mallik

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:55:21 2021

Version 19.3.0.0.0

Last Successful login time: Wed Nov 24 2021 00:16:14 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select * from student;

STNO STNAME

---------- ---------------

1 Mallik

2 John

10 AAA

9 rows selected.

2. Validate the table and make sure there table is not in stale state:

SQL> set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('MALLIK')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL> SQL> SQL> 2 3 4 5

no rows selected

SQL> select sql_id from v$sql where sql_text like 'select * from student';

SQL_ID

-------------

cdq26qbcmz1hc

SQL>

3. Create Tuning Task for the SQL ID:

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle 4941 1 0 Nov12 ? 00:00:16 ora_smon_TESTDB

oracle 8049 5048 0 00:48 pts/0 00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:58:04 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> DECLARE

2 l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_1',

3 4 5 6 7 8 9 description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/ 10 11 12

PL/SQL procedure successfully completed.

SQL>

4. Run the Tuning Task and check the status:

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_1');

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_1';

TASK_NAME

--------------------------------------------------------------------------------

STATUS

-----------

my_tuning_task_1

COMPLETED

SQL>

5. Review the recommendation provided by Tuning Task:

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_1') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : my_tuning_task_1

Tuning Task Owner : SYS

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status : COMPLETED

Started at : 11/24/2021 00:58:28

Completed at : 11/24/2021 00:58:28

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: MALLIK

SQL ID : cdq26qbcmz1hc

SQL Text : select * from student

-------------------------------------------------------------------------------

There are no recommendations to improve the statement.

-------------------------------------------------------------------------------

SQL>

6. Delete Some rows from table and table will become in stale state:

SQL> select * from student;

STNO STNAME

---------- ---------------

1 Mallik

2 John

10 AAA

9 rows selected.

SQL>

SQL> delete from student where STNO=10;

7 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from student;

STNO STNAME

---------- ---------------

1 Mallik

2 John

SQL> set lines 160 pages 2000

col owner format a15

SQL> SQL> col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('MALLIK')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL> 2 3 4 5

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED

--------------- ----------------------------------- ------------- -----------------------------------

MALLIK STUDENT 9 24-NOV-2021 00:28:55

SQL>

7. Now you can generate the new Tuning Task and see the recommendation from it:

SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_2',

description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/ 2 3 4 5 6 7 8 9 10 11 12

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_2');

PL/SQL procedure successfully completed.

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_2';

TASK_NAME

----------------------------------------------------------------------------------------------------

STATUS

-----------

my_tuning_task_2

COMPLETED

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_2') from dual;SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : my_tuning_task_2

Tuning Task Owner : SYS

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status : COMPLETED

Started at : 11/24/2021 01:02:18

Completed at : 11/24/2021 01:02:18

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: MALLIK

SQL ID : cdq26qbcmz1hc

SQL Text : select * from student

-------------------------------------------------------------------------------

FINDINGS SECTION (1 finding)

-------------------------------------------------------------------------------

1- Statistics Finding

---------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

Optimizer statistics for table "MALLIK"."STUDENT" are stale.

Recommendation

--------------

- Consider collecting optimizer statistics for this table.

execute dbms_stats.gather_table_stats(ownname => 'MALLIK', tabname =>

'STUDENT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale

---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

The optimizer requires up-to-date statistics for the table in order to

select a good execution plan.

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original

-----------

Plan hash value: 2356778634

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9 | 63 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| STUDENT | 9 | 63 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

-------------------------------------------------------------------------------

SQL>

8. In this above Tuning task has given recommendation that table is in state stat and gather stats on the table

SQL> execute dbms_stats.gather_table_stats(ownname => 'MALLIK', tabname =>'STUDENT', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>

9. Now the table is not in stale state then you can generate the new tuning task which will not give any recommendation since table is upto date.

SQL> DECLARE

2 l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

3 4 5 sql_id => 'cdq26qbcmz1hc',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement cdq26qbcmz1hc');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

6 END;

/ 7 8 9 10 11 12

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

PL/SQL procedure successfully completed.

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

TASK_NAME

----------------------------------------------------------------------------------------------------

STATUS

-----------

my_tuning_task_3

COMPLETED

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : my_tuning_task_3

Tuning Task Owner : SYS

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status : COMPLETED

Started at : 11/24/2021 01:06:05

Completed at : 11/24/2021 01:06:05

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: MALLIK

SQL ID : cdq26qbcmz1hc

SQL Text : select * from student

-------------------------------------------------------------------------------

There are no recommendations to improve the statement.

-------------------------------------------------------------------------------

SQL>

10. Drop those tuning tasks:

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

PL/SQL procedure successfully completed.

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_2');

PL/SQL procedure successfully completed.

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_3');

PL/SQL procedure successfully completed.

SQL>

Regards,

Mallik

Mallikarjun Ramadurg

Monday, November 22, 2021

Database Mode - Restrict Vs Exclusive & Drop database - Standalone Vs RAC

High-level steps to drop RAC Database:

==============================

$ORACLE_HOME/bin/srvctl status database -d <DBNAME>

$ORACLE_HOME/bin/srvctl stop database -d <DBNAME>

sqlplus / as sysdba

startup; (open mode) --- Unbale drop DB

startup mount; --- Unbale drop DB

startup mount exclusive --- Unbale drop DB

startup mount restrict --- Unbale drop DB

startup mount restrict exclusive; --- Unbale drop DB

show parameter cluster_database;

alter system set cluster_database=false scope=spfile;

shutdown;

startup mount exclusive restrict; --- Able to drop my database

drop database;

High-level steps to drop Standalone Database:

=================================

shutdown;

startup; (open mode) --- Unbale drop DB

startup mount; --- Unbale drop DB

startup mount exclusive --- Unbale drop DB

startup mount restrict --- Able to drop my database

startup mount exclusive restrict; --- Able to drop my database

drop database;

Actual logs of Standalone database drop:

===================================

[oracle@node1 ~]$ ps -ef|grep smon

oracle 14678 1 0 14:54 ? 00:00:00 ora_smon_ORA19C

root 16277 1 1 Nov02 ? 06:37:35 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 17721 1 0 Nov02 ? 00:01:18 ora_smon_RAC12C1

oracle 17735 1 0 Nov02 ? 00:01:18 ora_smon_CDBDB1

oraprod 18146 1 0 Nov02 ? 00:01:22 ora_smon_ORAPROD1

oracle 20401 13581 0 15:08 pts/3 00:00:00 grep --color=auto smon

oraprod 25407 1 0 Nov15 ? 00:00:10 ora_smon_SINGLE

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [ORA19C] ? ORA19C

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@node1 ~]$ srvctl status database -d ORA19C

Instance ORA19C is running on node node1

[oracle@node1 ~]$ srvctl config database -d ORA19C

Database unique name: ORA19C

Database name: ORA19C

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/rbrkspfileORA19C.ora

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: SINGLE

OSDBA group: oinstall

OSOPER group: oinstall

Database instance: ORA19C

Configured nodes: node1

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 22 15:10:42 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

ORA19C READ WRITE

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

ORA19C OPEN

SQL> drop database;

drop database

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$ srvctl stop database -d ORA19C

[oracle@node1 ~]$ srvctl status database -d ORA19C

Instance ORA19C is not running on node node1

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 22 15:13:39 2021

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup mount exclusive;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 3070227808 bytes

Fixed Size 9139552 bytes

Variable Size 1358954496 bytes

Database Buffers 1694498816 bytes

Redo Buffers 7634944 bytes

Database mounted.

SQL> drop database;

drop database

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount restrict;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 3070227808 bytes

Fixed Size 9139552 bytes

Variable Size 1358954496 bytes

Database Buffers 1694498816 bytes

Redo Buffers 7634944 bytes

Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> exit

[oracle@node1 ~]$

Actual logs of RAC database drop:

============================

[root@node1 ~]# su - oracle

Last login: Mon Nov 22 14:45:05 IST 2021 on pts/1

[oracle@node1 ~]$ clear

[oracle@node1 ~]$ ps -ef|grep smon

oracle 14678 1 0 14:54 ? 00:00:00 ora_smon_ORA19C

root 16277 1 1 Nov02 ? 06:37:34 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 17721 1 0 Nov02 ? 00:01:18 ora_smon_RAC12C1

oracle 17735 1 0 Nov02 ? 00:01:18 ora_smon_CDBDB1

oraprod 18146 1 0 Nov02 ? 00:01:22 ora_smon_ORAPROD1

oracle 20248 13548 0 15:07 pts/1 00:00:00 grep --color=auto smon

oraprod 25407 1 0 Nov15 ? 00:00:10 ora_smon_SINGLE

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [oracle] ? CDBDB1

The Oracle base has been set to /u01/app/oracle

[oracle@node1 ~]$ srvctl status database -d CDBDB

Instance CDBDB1 is running on node node1

Instance CDBDB2 is running on node node2

[oracle@node1 ~]$ srvctl config database -d CDBDB

Database unique name: CDBDB

Database name: CDBDB

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile: +DATA/CDBDB/PARAMETERFILE/spfile.334.1067561253

Password file: +DATA/CDBDB/PASSWORD/pwdcdbdb.289.1067560793

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: CDBDB1,CDBDB2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 22 15:11:19 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

CDBDB READ WRITE

SQL> select name, open_mode from gv$database;

NAME OPEN_MODE

--------- --------------------

CDBDB READ WRITE

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

CDBDB1 OPEN

SQL> select instance_name, status from gv$instance;

INSTANCE_NAME STATUS

---------------- ------------

CDBDB1 OPEN

CDBDB2 OPEN

SQL> drop database;

drop database

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> startup mount restrict;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount restrict;

ORACLE instance started.

Total System Global Area 3070230528 bytes

Fixed Size 8797488 bytes

Variable Size 838861520 bytes

Database Buffers 2214592512 bytes

Redo Buffers 7979008 bytes

Database mounted.

SQL> drop database;

drop database

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> SQL>

SQL>

SQL> startup mount restrict exclusive;

ORACLE instance started.

Total System Global Area 3070230528 bytes

Fixed Size 8797488 bytes

Variable Size 838861520 bytes

Database Buffers 2214592512 bytes

Redo Buffers 7979008 bytes

Database mounted.

SQL> drop database;

drop database

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> show parameter cluster_database;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean TRUE

cluster_database_instances integer 2

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount restrict exclusive;

ORACLE instance started.

Total System Global Area 3070230528 bytes

Fixed Size 8797488 bytes

Variable Size 771752656 bytes

Database Buffers 2281701376 bytes

Redo Buffers 7979008 bytes

Database mounted.

SQL> show parameter cluster_database;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean FALSE

cluster_database_instances integer 1

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Regards,

Mallik

Mallikarjun Ramadurg

Saturday, October 9, 2021

How to enable or disable BCT in Oracle Database?

Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.

Enabling the BCT will improves the RMAN incremental performance

To determine if block change tracking is enabled or not

[oracle@oracledb ~]$ . oraenv

ORACLE_SID = [ORACDB] ?

The Oracle base remains unchanged with value /u01/app/oradev

[oracle@oracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 8 21:18:19 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME

---------- ------------

DISABLED

SQL>

Enable:

Enable the block change tracing using below command and verify the same:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME

---------- --------------------------------------------------------------------------------

ENABLED /u01/app/oradev/oradata/ORACDB/ORACDB/changetracking/o1_mf_jp0t5bt0_.chg

Disable:

Disable the block change tracing using below command and verify the same:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME

---------- --------------------------------------------------------------------------------

DISABLED

Regards,

Mallik

Mallikarjun Ramadurg

Monday, September 27, 2021

sqlplus and rman and other binary executable are not working with error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

Issue:

sqlplus and rman and other binary executable are not working with error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

Error Message:

/orasw/oracle/product/12.1.0.2/db/bin/sqlplus: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

/orasw/oracle/product/12.1.0.2/db/bin/rman: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

[oracle@oracledb lib]$ sqlplus / as sysdba

sqlplus: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

[oracle@oracledb lib]$ rman target /

rman: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

[oracle@oracledb lib]$

Cause:

After Inverstigating found that the shared object file libclntshcore.so.12.1 was missing from the lib(/orasw/oracle/product/12.1.0.2/db/lib) location.

Solution:

[oracle@oracledb lib]$ pwd

/u01/app/oracle/product/12.2.0.1/dbhome_1/lib

[oracle@oracledb lib]$ ls -lrtr libclntshcore.so.12.1

ls: cannot access libclntshcore.so.12.1: No such file or directory

[oracle@oracledb lib]$

Copy from the existing Oracle Home and verify the file exist and has proper permission

[oracle@oracledb lib]$ ls -lrtr libclntshcore.so.12.1

-rwxr-xr-x. 1 oracle oinstall 8032696 Sep 27 19:28 libclntshcore.so.12.1

[oracle@oracledb lib]$

[oracle@oracledb lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 27 19:29:18 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oracledb lib]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Sep 27 19:29:24 2021

connected to target database: ORA12C (DBID=457334020)

RMAN>

Recovery Manager complete.

[oracle@oracledb lib]$

Regards,

Mallik

Mallikarjun Ramadurg

Friday, September 24, 2021

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

Recently I have installed Oracle 19c Database binary to windows machine. And after that I started DBCA to create database.

In “Configuration options” steps I got memory error. i.e. [DBT-50000] Unable to check for available memory

Earlier I selected 8GB for my SGA but after this error I had selected 2GB only. But still was facing this issue.

Here is the screenshot for the same.

After searching oracle docs for known issue, I found, it’s a bug. One can find details about this bug on following oracle notes.

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory in “Specify configuration option” (Doc ID 2631718.1)

Workaround for this issue is,

Run dbca with “-J-Doracle.assistants.dbca.validate.ConfigurationParams=false” like a bellow command,

Open CMD (Run as Administrator)

Run database configuration assistant with the following command

dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

Regards,

Mallik

Mallikarjun Ramadurg

Monday, September 20, 2021

19c Oracle DBA Training || 30-Sep-2021 New Batch || Course Details & Demo Classes

Oracle Database Training!!! - 19c Oracle Database Training!!!

New DBA batch is starting on 30-Sep-2021. Don't forget to attend Demo classes on 30-Sep-2021 & 01-Oct-2021.

Ping me on my WhatsApp Number +91 9880616848 for registration & more details.

Attend Demo Classes using below zoom Meeting:

https://us02web.zoom.us/j/86254664197?pwd=QVVCdEdVVlRoREQ2RGdYQUZyVVNFUT09

Timing:

Sep 30, 2021 09:00 AM IST to 11:00 AM IST

Oct 01, 2021 09:00 AM IST to 11:00 AM IST

Check out this video for more details on course content:

https://youtu.be/BANrqNLiZqo

Check the below link for Course details:

https://mallik034.blogspot.com/p/database-training.html

Check the below link for Couse registration and Timing and fees details:

https://mallik034.blogspot.com/p/upcoming-batch-schedule.html

#19c #DBA #oracle #database #training #course #announcement #CareerOpportunities

Regards,

Mallik

Mallikarjun Ramadurg

Wednesday, September 1, 2021

Manually Drop RAC Database and Remove From Clusterware

1. Verify RAC database status:

[oracle@oraclenode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? RACSB1

The Oracle base has been set to /u01/app/oracle

[oracle@oraclenode1 ~]$ srvctl status database -d RACSB

Instance RACSB1 is running on node oraclenode1

Instance RACSB2 is running on node oraclenode2

[oracle@oraclenode1 ~]$

2. Stop RAC database and start in exclusive restrict mode

[oracle@oraclenode1 ~]$ srvctl stop database -d RACSB

[oracle@oraclenode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 1 01:33:54 2021

Connected to an idle instance.

SQL> startup mount exclusive restrict and try to drop DB which will fail;

ORACLE instance started.

Total System Global Area 3070230528 bytes

Fixed Size 8625416 bytes

Variable Size 838861560 bytes

Database Buffers 2214592512 bytes

Redo Buffers 8151040 bytes

Database mounted.

SQL> drop database;

drop database

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3070230528 bytes

Fixed Size 8625416 bytes

Variable Size 838861560 bytes

Database Buffers 2214592512 bytes

Redo Buffers 8151040 bytes

### only way to open your RAC database is EXCLUSIVE session is to set cluster_database=FALSE ###

3. Set cluster_database=FALSE and start DB in exclusive restrict mode

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 3070230528 bytes

Fixed Size 8625416 bytes

Variable Size 771752696 bytes

Database Buffers 2281701376 bytes

Redo Buffers 8151040 bytes

Database mounted.

SQL> select instance_name,status,logins from v$Instance;

INSTANCE_NAME STATUS LOGINS

---------------- ------------ ----------

RACSB1 MOUNTED RESTRICTED

4. Drop Database

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit

5. Remove database from cluster

[oracle@oraclenode1 ~]$ srvctl remove database -d RACSB

Remove the database RACSB? (y/[n]) y

[oracle@oraclenode1 ~]$

Regards,

Mallik

Mallikarjun Ramadurg

Tuesday, August 17, 2021

21c Installation and Database Creation Using dbca - 21.0.0.0

21c Oracle Home Installation and Database Creation Using dbca || 21.0.0.0 OH & Database Creation

Download Location:

1. From OTN:

https://www.oracle.com/in/database/technologies/oracle-database-software-downloads.html

https://www.oracle.com/database/technologies/oracle21c-linux-downloads.html

2. From edelivery:

https://edelivery.oracle.com/

MOS notes:

MOS Note: 742060.1 – Release Schedule of Current Database Releases

21c Installation and Database Creation Using dbca - 21.0.0.0:

1. Download the Software

2. Prerequisites (https://mallik034.blogspot.com/2021/08/oracle-home-installation-prerequisite.html)

- create user

- yum install oracle* (which will install the all the prerequisites)

- create OH directory /u01/app/oracle/product/21.0.0.0/dbhome_1

3. Install the 21c Oracle Home

4. Create the database usig dbca

Regards,

Mallik

Mallikarjun Ramadurg

Monday, August 16, 2021

Oracle Home Installation Prerequisite Package Installation

Oracle Home Installation Prerequisite Package Installation:

1. Setup all the Oracle home installation pre-requisites and rpms

- yum install oracle* --skip-broken

2. Reset the oracle user passwrord

- passwd oracle

[root@oracletest ~]# yum install oracle*

Loaded plugins: langpacks, ulninfo

ol7_UEKR5 | 2.5 kB 00:00:00

ol7_latest | 3.6 kB 00:00:00

(1/3): ol7_latest/x86_64/group_gz | 136 kB 00:00:01

(2/3): ol7_latest/x86_64/updateinfo | 3.3 MB 00:00:02

(3/3): ol7_latest/x86_64/primary_db | 38 MB 00:00:02

(1/2): ol7_UEKR5/x86_64/updateinfo | 115 kB 00:00:01

(2/2): ol7_UEKR5/x86_64/primary_db | 25 MB 00:00:03

Package oracle-spacewalk-client-release-el7 is obsoleted by oracle-linux-manager-client-release-el7, trying to install oracle-linux-manager-client-release-el7-1.0-2.el7.x86_64 instead

Package oracle-spacewalk-server-release-el7 is obsoleted by oracle-linux-manager-server-release-el7, trying to install oracle-linux-manager-server-release-el7-1.0-4.el7.x86_64 instead

Resolving Dependencies

--> Running transaction check

---> Package oracle-ceph-release-el7.x86_64 0:1.0-2.el7 will be installed

---> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed

--> Processing Dependency: ksh for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64

--> Processing Dependency: libaio-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64

---> Package oracle-database-preinstall-19c.x86_64 0:1.0-3.el7 will be installed

---> Package oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7 will be installed

---> Package oracle-epel-release-el7.x86_64 0:1.0-4.el7 will be installed

---> Package oracle-gluster-release-el7.x86_64 0:1.0-7.el7 will be installed

---> Package oracle-golang-release-el7.x86_64 0:1.0-9.el7 will be installed

---> Package oracle-graalvm-ce-release-el7.x86_64 0:1.0-2.el7 will be installed

---> Package oracle-instantclient-release-el7.x86_64 0:1.0-3.el7 will be installed

---> Package oracle-linux-manager-client-release-el7.x86_64 0:1.0-2.el7 will be installed

---> Package oracle-linux-manager-server-release-el7.x86_64 0:1.0-4.el7 will be installed

---> Package oracle-logos.noarch 0:70.7.0-1.0.5.el7 will be updated

---> Package oracle-logos.noarch 0:70.7.0-1.0.7.el7 will be an update

---> Package oracle-nodejs-release-el7.x86_64 0:1.0-7.el7 will be installed

---> Package oracle-olcne-release-el7.x86_64 0:1.0-9.el7 will be installed

---> Package oracle-openstack-release-el7.x86_64 0:1.0-2.el7 will be installed

---> Package oracle-ovirt-release-el7.x86_64 0:1.0-3.el7 will be installed

---> Package oracle-php-release-el7.x86_64 0:1.0-5.el7 will be installed

---> Package oracle-rdbms-server-11gR2-preinstall.x86_64 0:1.0-6.el7 will be installed

---> Package oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-7.el7 will be installed

---> Package oracle-release-el7.x86_64 0:1.0-4.el7 will be installed

---> Package oracle-softwarecollection-release-el7.x86_64 0:1.0-4.el7 will be installed

---> Package oracleasm-support.x86_64 0:2.1.11-2.el7 will be installed

---> Package oraclelinux-developer-release-el7.x86_64 0:1.0-6.el7 will be installed

---> Package oraclelinux-release.x86_64 7:7.8-1.0.7.el7 will be updated

---> Package oraclelinux-release.x86_64 7:7.9-1.0.9.el7 will be an update

---> Package oraclelinux-release-el7.x86_64 0:1.0-11.el7 will be updated

---> Package oraclelinux-release-el7.x86_64 0:1.0-17.el7 will be an update

--> Running transaction check

---> Package ksh.x86_64 0:20120801-142.0.1.el7 will be installed

---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed

--> Processing Conflict: oracle-linux-manager-client-release-el7-1.0-2.el7.x86_64 conflicts oracle-linux-manager-server-release-el7

--> Finished Dependency Resolution

Error: oracle-linux-manager-client-release-el7 conflicts with oracle-linux-manager-server-release-el7-1.0-4.el7.x86_64

You could try using --skip-broken to work around the problem

You could try running: rpm -Va --nofiles --nodigest

[root@oracletest ~]# yum install oracle* --skip-broken