DBA Blogs

Find out which SQL or PL/SQL caused row lock contention days ago

Tom Kyte - Tue, 2017-03-28 10:46
Hi Team, As we known 'enq: TX - row lock contention' often happened during a DBA lifetime, and Tom used to say that 'The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys. The number two cause is bitm...
Categories: DBA Blogs

How are dirty buffers written to the datafiles?

Tom Kyte - Tue, 2017-03-28 10:46
Hello, I have a question regarding how dirty buffers are written to the datafiles on disk. Most of the cases the db blocks are 8K in size, so suppose we execute a update which just modifies only few bytes in the entire 8K block. Ex will be a colum...
Categories: DBA Blogs

Physical writes for delete vs update statement.

Tom Kyte - Tue, 2017-03-28 10:46
Hello, The DBWR will write out the whole block even if only few bytes have changed following an DML statement. Considering the db block size of 8K and I have a table of just 7 columns with row size small enough to fit into a single block. If I exe...
Categories: DBA Blogs

golden gate

Tom Kyte - Tue, 2017-03-28 10:46
Hi , As we all aware that to implement golden gate replication ,minimal supplemental logging should be enabled at source db end. 1)Why we need to enable supplemental logging as redo contains both undo and redo vector details. 2)What is the u...
Categories: DBA Blogs

Desert Island DBA

Tom Kyte - Tue, 2017-03-28 10:46
Hi Guys, I've just re-read oracle insights (Tales of the oak table) and want to get your take on something in the book. I believe it's in the section on Compulsive Tuning Disorder (CTD) where the author says that DBA's are taken to a desert islan...
Categories: DBA Blogs

Links for 2017-03-27 [del.icio.us]

Categories: DBA Blogs

datafile, tablespace

Tom Kyte - Mon, 2017-03-27 16:46
Hi Tom, For lots of datafiles, tablespace there are different value of extend, and different values of max_size, how to find the max_size and how to make sure weather the value of extend is which one best??
Categories: DBA Blogs

XMLTYPE storage issue on oracle 11G

Tom Kyte - Mon, 2017-03-27 16:46
We recently upgraded from 10G to 11G and the existing query started running really slow....5 minutes to more than 3 hours. Check the attached live sql file for sample details about the issue. With SECUREFILE BINARY XML, EXTRACT method is runnin...
Categories: DBA Blogs

Implicit Materialized View Fast Refresh

Tom Kyte - Mon, 2017-03-27 16:46
Hi, Is there any way by which we can fast refresh a materialized view implicitly without using refresh explicitly. Thanks In Advance
Categories: DBA Blogs

Using Materialized views with Oracle Change Data Capture

Tom Kyte - Mon, 2017-03-27 16:46
Tom, I would value your opinion on my intended solution for replicating data aggregations to external applications. We have a Data Warehouse with a table containing 100 million rows with several tens of thousands of new rows added each day. Thi...
Categories: DBA Blogs

12cR1 RAC Posts -- 8d : Registering the two databases in DataGuard Broker

Hemant K Chitale - Mon, 2017-03-27 10:19
Continuing my series on setting up a non-RAC, FileSystem Standby for a RAC, ASM Database....

On the Standby:

[oracle@oem132 ~]$ . oraenv
ORACLE_SID = [oracle] ? STBY
The Oracle base has been set to /u01/app/oracle
[oracle@oem132 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:24:54

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oem132/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oem132.racattack)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 27-MAR-2017 22:24:58
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oem132/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "STBY" has 1 instance(s).
Instance "STBY", status UNKNOWN, has 2 handler(s) for this service...
The command completed successfully
[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 27 22:25:07 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 230689520 bytes
Database Buffers 599785472 bytes
Redo Buffers 5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL>
SQL> show parameter dg_broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr1STBY.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr2STBY.dat
dg_broker_start boolean FALSE
SQL>
SQL> alter system set dg_broker_Start=TRUE;

System altered.

SQL>


On the first instance of the Primary :

SQL> show parameter dg_broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr1RAC.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr2RAC.dat
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_config_file1='+DATA/RAC/dgbroker1.dat' sid='*' scope=BOTH;

System altered.

SQL> alter system set dg_broker_config_file2='+FRA/RAC/dgbroker2.dat' sid='*' scope=BOTH;

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

SQL>

[oracle@collabn1 ~]$ tnsping RAC1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:37:41

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus sys/racattack@STBY

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 27 22:37:51 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name: sys/racattack@STBY as SYSDBA

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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
[oracle@collabn1 ~]$ tnsping RAC1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:38:07

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ tnsping STBY

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:38:10

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oem132)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY)))
OK (0 msec)
[oracle@collabn1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/racattack@RAC1
Connected as SYSDBA.
DGMGRL> create configuration RAC as primary database is RAC connect identifier is RAC1;
Configuration "rac" created with primary database "rac"
DGMGRL> add database stby as connect identifier is stby maintained as physical;
Database "stby" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 7 seconds ago)

DGMGRL>


Errors in the STBY DataGuard Broker Logfile drcSTBY.log :

03/27/2017 22:45:12
Warning: Property 'StandbyFileManagement' has inconsistent values:METADATA='MANUAL', SPFILE='', DATABASE='MANUAL'
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='STBY'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='STBY'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site rac. Error code is ORA-16501.

Errors in the RAC1 DataGuard Broker Logfile drcRAC1.log :

Site stby returned ORA-16664.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration rac Warning ORA-16607
Primary Database rac Success ORA-00000
Physical Standby Database stby Error ORA-16664


Actions taken on the Standby :

SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> alter system set standby_file_management='AUTO' scope=BOTH;

System altered.

SQL> alter system set archive_lag_target=0 scope=BOTH;

System altered.

SQL> alte system set log_archive_max_processes=4 scope=BOTH;
SP2-0734: unknown command beginning "alte syste..." - rest of line ignored.
SQL> alter system set log_archive_max_processes=4 scope=BOTH;

System altered.

SQL> alter system set log_archive_min_succeed_dest=1 scope=BOTH;

System altered.

SQL> alter system set log_archive_trace=0 scope=BOTH;

System altered.

SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=BOTH;
alter system set log_archive_format='%t_%s_%r.dbf' scope=BOTH
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL>


I also had to edit the TNSNAMES.ORA on the STBY server for the correct *VIP* entry (not the physical hostname) for the RAC1 instance.

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.racattack)
)
)

RAC1_DGB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_DGMGRL)
)
)


Thereafter, it worked.  Note : You sometimes have to wait a while to enter the SHOW CONFIGURATION, the first result may show an error.  So I entered it twice.

DGMGRL> disable configuration;
Disabled.
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 125 seconds ago)

DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 14 seconds ago)

DGMGRL>


These are the drcSTBY.log entries when the TNSNAMES.ORA for RAC1 was incorrect on the Standby Server :

Failed to connect to remote database rac. Error is ORA-12541
Failed to send message to site rac. Error code is ORA-12541.


This is the drcSTBY.log entry on success :

03/27/2017 23:00:51
Apply Instance for Database stby set to STBY


And the entry in drcRAC1.log on success :

03/27/2017 23:00:10
DISABLE CONFIGURATION
03/27/2017 23:00:46
ENABLE CONFIGURATION
03/27/2017 23:00:57
EDIT DATABASE stby SET PROPERTY ActualApplyInstance = STBY
03/27/2017 23:01:00
Apply Instance for database stby is STBY


Setting Maximum Performance mode (command issued on the *Primary*) :

SQL> alter database set standby to maximize performance;

Database altered.

SQL>

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;
Succeeded.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 34 seconds ago)

DGMGRL>
DGMGRL> show configuration verbose

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


Will be testing Redo Transmission and Apply later.
.
.
.

Categories: DBA Blogs

What Oracle DBAs Need to Learn in Oracle Cloud Platform?

Pakistan's First Oracle Blog - Mon, 2017-03-27 01:28
The transition from Oracle on-premises DBA to Oracle Cloud DBA is imminent for many of us. In fact, IMHO; the existing Oracle DBAs would have to manage database both on-premises and in cloud for a long time.

So what Oracle DBA needs to learn in Oracle Cloud Platform? If you visit the Oracle Cloud website, it's a mouthful and more. Its very easy to get bogged down as there are lots of things to learn it seems at first.

The good news is that as an experienced Oracle DBA you know most of things already. So just brush up your basic concept of cloud computing and then start from the following cloud offerings from Oracle Cloud Platform:

  • Database Cloud Schema Service
  • Database Cloud Database as a Service
  • Database Backup Cloud Service

Read about above as much as possible, and if you get a chance play with them. You would be surprised to find out that you know almost everything about these things as they are built upon the existing Oracle technologies.

One thing which is a must for this brave new world of Oracle Cloud DBA (OCDBA) in Oracle Cloud Platform or in any other cloud platform is to know how to migrate an Oracle database to Oracle Cloud (or any other cloud for that matter.) For this purpose, make sure you understand the following concepts:

  • Oracle Goldengate
  • Oracle Datapump
  • Oracle Secure External Password Store
  • Oracle Connection Manager
  • RMAN
  • Oracle Cloud Control

If all of above is ready, you are all good to go! :)
Categories: DBA Blogs

SQL * Loader

Tom Kyte - Sun, 2017-03-26 22:26
Hi, I loaded data into a table referenced by other table(Pk, Fk Relation and No cascade delete) via sql loader. the data loaded successfully after using "replace into table" statement, before i tried truncate,append,insert statements but data not ...
Categories: DBA Blogs

Fast Refresh of Materialized view takes long time

Tom Kyte - Sun, 2017-03-26 22:26
Hi Tom, I have a materialized view that joins two tables. Both tables have materialized view logs and the view meets the criteria for a fast refresh. However, simply adding one new record to the ATTRIBUTE base table takes several minutes to commit...
Categories: DBA Blogs

PL/SQL procedure fails when it´s executed with shell script

Tom Kyte - Sun, 2017-03-26 22:26
Hello Tom, I have created the next procedure into the database: PROCEDURE DACQP00_PIVOT_DATES (p_schema in VARCHAR2, p_party out TIMESTAMP) IS BEGIN BEGIN EXECUTE IMMEDIATE ('SELECT TO_TIMESTAMP(FILE_CREATION_DATE,''DD/MM/YY HH24:M...
Categories: DBA Blogs

How to escape Reserved Words in CONTAINS function?

Tom Kyte - Sun, 2017-03-26 22:26
Hi, I need to escape reserved words in contains function when I search into blob column. Let's suppous we have this simple table: <code>CREATE TABLE MY_TABLE( MY_ID NOT NULL, MY_BLOB BLOB NOT NULL, CONSTRAINT MY_BLOB CHECK(...
Categories: DBA Blogs

Fetching the type of join, columns used in join and filter for a given select query

Tom Kyte - Sun, 2017-03-26 22:26
Dear Sir, I have a requirement to parse a given select query to get the columns used in joins, type of joins and filters used. Can you please suggest how to do this. If this issue was already discussed in this blog, my apologies for raising it o...
Categories: DBA Blogs

Question about undo management

Tom Kyte - Sun, 2017-03-26 22:26
Hi, I have two questions about automatic undo management. 1.Assume my undo tablespace has maximum size to 32GB with undo_retention=900 and currently it's used 10GB. While I querying a table more than 15 min and others users perform DML stateme...
Categories: DBA Blogs

Getting Compilation errors for PACKAGE BODY NAV_PKG_ST Error: PLS-00306: wrong number or types of arguments in call to '||' Line: 1728 Text: EXECUTE IMMEDIATE 'INSERT INTO '||TAB_COUNT.TABLE_NAME ||' VALUES '||V_CRS_IS_DART_COLL(I)

Tom Kyte - Sun, 2017-03-26 22:26
Thanks in Advance TOM I am Srinadh And i have an issue in my code. i am using dynamic sql to insert data into some of tables(Cursor C_TABLES ) from Views(Cursor DAIL_VIEWS ). i am getting the above error and also i'm not sure can we use Dyna...
Categories: DBA Blogs

regarding oracle database export

Tom Kyte - Sun, 2017-03-26 22:26
i am trying to export database.i am using arabic in my databse . my database Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 client charaterset oracle 6i on windows10 machine AMERICAN_AMERICA.WE8ISO8859P1 database charaterset ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs