What is Oracle 18c


What does it contain or we expect from this release?

  1. Tagged as a fully autonomous database if your database in Oracle Public Cloud
  2. If you are not in Oracle Public cloud, you will not be getting fully autonomous features
  3. Self-patching sounds to me like Oracle Configuration Management feature enhancements

Hope this helps…

Nimai Karmakar


Hi Guys,


Last day I just installed and was thinking to let start with some basics on oracle 12c like what is it? why we need it?

what are the new features available?


So after some research on that (although it was not so hard.. :)) I came to know with some below points.


New Multitenant Architecture
Designed for the cloud, Oracle Multitenant delivers a new architecture that simplifies consolidation and delivers the high density of schema based consolidation, but without requiring changes to existing applications. It’s an option of Oracle Database 12c Enterprise Edition that offers all the benefits of managing many databases as one, yet retains the isolation and resource control of separate databases. In this new architecture, a single multitenant container database can host many ‘pluggable’ databases. Each database consolidated or ‘plugged in’ to a multitenant container looks and feels to applications the same as for existing Oracle databases. Accessing pluggable databases is the same as for existing Oracle databases, and administrators can control the prioritization of available resources between consolidated databases.


Manage Many Databases As One
Consolidating multiple databases means that administrators have fewer databases to manage and the benefits of managing many databases as one can permeate throughout the data center. It is fairly common for IT organizations to manage hundreds, if not thousands of databases, and each of those databases -be they for production, test or development -requires maintenance.


Fewer Patches and Upgrades
Applying patches, patch set updates and product updates to multiple (non-consolidated) databases in order to maintain currency of database releases can be a challenge that only gets more difficult with the number of databases managed. All databases in the data center, regardless of their production, test or development status, are subject to patching and upgrading. Consolidating multiple pluggable databases into a multitenant container can dramatically reduce the amount of patching and upgrade activities required. With Oracle Databases 12c, patches and upgrades are applied at the container level, and not applied to individual pluggable databases. Customers also have the flexibility of creating new patched and/or upgraded container databases and selectively unplugging databases from older container databases and plugging into new upgraded container databases.


Fewer Backups
Every good database administrator regularly takes backup copies of all databases managed in the data center. However, instead of having to backup each separate database in the data center, the new pluggable architecture only requires executing backups at the multitenant container level. This means that database administrators have fewer backups to manage, as each pluggable database in a container database is automatically backed up. However, in the event of any problem encountered with an individual pluggable database, database administrators have the flexibility to perform data recovery operations at the pluggable database level within a container database.

Fewer Standby Databases
Another good example of the benefits of managing many pluggable databases as one is maintaining standby databases. Many Oracle customers use replication technologies such as Oracle Data Guard or Oracle Active Data Guard to maintain complete working copies of production databases by shipping and applying redo logs to a standby system usually in a separate location. With Oracle Database 12c, shipping and applying of redo logs occurs at the container level, therefore every pluggable database consolidated into a container is fully protected from site outages. In addition, customers using Oracle Active Data Guard can make active use of their standby pluggable databases for reporting and other purposes.


Database Resource Management (This one is good..)
While there are undoubtedly many benefits from consolidating multiple databases, customers should consider all aspects of database consolidation. An obvious question to ask is “how can I guarantee the prioritization of resources in this new multitenant architecture?” After all, many of the benefits of managing many database as one will be quickly lost if database applications are continually vying for available system resources, and user performance service levels start to drop off.
Using Oracle Database 12c’s resource management features, database administrators can easily define resource utilization priorities at the pluggable database level. The database server pro-actively monitors database usage to ensure that each pluggable database in a container stays within pre-defined minimum and maximum resource thresholds. For example, at quarter-end, a high priority ERP application can automatically pull system resources from low priority applications in the container to ensure users performance service level remain consistent even at peak demand.


I got all the points from an oracle documentation or you can say oracle white paper, I only have noted which seems important for DBAs to begin with.Click on the link to open it.


and then after installing oracle 12c and it was not a big deal, I thought from where should I start… what is multitenant/container/plugable.

A container is either a PDB or the root. The root container is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.

Every CDB has the following containers:

  • Exactly one root

    The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. The root container is named CDB$ROOT.

  • Exactly one seed PDB

    The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

  • Zero or more user-created PDBs

    A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.


Oracle 12c cloud architecture


You can use the same administration tools for both CDBs and non-CDBs.

For example, you can use the following tools in a multitenant environment:

  • SQL*Plus for command-line access
  • Oracle Enterprise Manager Cloud Control (Cloud Control)

    Cloud Control is an Oracle Database administration tool that provides a graphical user interface (GUI). Cloud Control supports Oracle Database 12targets, including PDBs, CDBs, and non-CDBs.

  • Oracle Enterprise Manager Database Express (EM Express)

    EM Express is a web-based management product built into the Oracle database. EM Express enables you to provision and manage PDBs, including the following operations:

    • Creating and dropping PDBs
    • Plugging in and unplugging and PDBs
    • Cloning PDBs
    • Setting resource limits for PDBs
  • Oracle Database Configuration Assistant (DBCA)

    DBCA enables you to create CDBs or non-CDBs, and create, plug, and unplug PDBs.

  • Oracle Multitenant Self-Service Provisioning application

    This application enables the self-service provisioning of PDBs. CDB administrators control access to this self-service application and manage quotas on PDBs.


Okay this is as quick post to begin with, soon I will post some more points to begin with. Please feel free to post your comments or questions and experts are always welcome with there valuable points.


Thanks & Regards

Nimai Karmakar

Hi friends,


Last one year i have work a lot on export and import for small to huge database, from specific objects to schema to and query based backup.


here I am listed some of my syntax which you can note down for your reference.

  1. export objects from specific schema which match the specific criteria.

expdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_%u.dmp logfile=BG_objects.log

2. Import objects to the schema which were own by other schema but exclude some specific objects.

impdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_01.dmp logfile=BG_objects_imp.log EXCLUDE=TABLE:\”LIKE \’%POSTED%\’\” table_exists_action=REPLACE

3. Import objects from one schema to other schema where tablespace for both the schema were different and from different version(like from 10g to 11g).

impdp system/oracle directory=DUMP_BACKUP dumpfile=dumpfile_17032015.dmp logfile=logfile_imp.log remap_schema=<from_schema>:<to_schema>
remap_tablespace=<from_tablespace>:<to_tablespace> table_exists_action=replace EXCLUDE=STATISTICS;

4. Export specific objects from the schema.

expdp username directory=BCKUP_DUMP dumpfile=schema_pk_objects_%U.dmp logfile=schema_pk_objects.log include=TABLE:\”LIKE \’%PK_%\’\”;

5. Export only one table.

expdp oracle directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=<Table_name>;

6.Export a big database and break the dumpfile to matching criteria to fit to the space available in the disk.

expdp SYSTEM/oracle dumpfile=DUMP1:SCHEMA_%u.dmp dumpfile=DUMP2:SCHEMA_%u.dmp filesize=96G logfile=DUMP1:schema.log full=y exclude=statistics;

7. Import a full dump which contains multiple user in different tablespace to a single common user in a single common tablespace to same database or in a different database.

impdp system/oracle directory=dump dumpfile=EPIXDB_01.dmp,EPIXDB_02.dmp full=y logfile=epixdb_imp.log remap_schema=<Schema1>:<schema_common>
remap_schema=<Schema2>:<schema_common> remap_schema=<Schema3>:<schema_common> remap_schema=<Schema4>:<schema_common> remap_schema=<Schema5>:<schema_common>
remap_schema=<Schema6>:<schema_common> remap_schema=<Schema7>:<schema_common> remap_schema=<Schema8>:<schema_common> remap_schema=<Schema9>:<schema_common> remap_schema=<Schema10>:<schema_common>
remap_schema=<Schema11>:<schema_common> remap_schema=<Schema12>:<schema_common> remap_tablespace=<tablespace1>:<common_tablespace> remap_tablespace=<tablespace2>:<common_tablespace>
remap_tablespace=<tablespace3>:<common_tablespace> table_exists_action=replace;

8.export of a object to a specific time or from a specific time this is also known as incremental backup (note:flashback log must be present)

expdp username directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=TABLE_NAME
FLASHBACK_TIME=\”to_timestamp\(to_char\(sysdate,\’yyyy-mm-dd hh24:mi:ss\’\),\’yyyy-mm-dd hh24:mi:ss\’\)\”;

9. Traditional export or exp utility to take backup of specific data.

exp user/pass file=exp.dmp log=exp.log TABLES=test query=”””where rownum< 101″””;

exp uwclass/uwclass owner=SCOTT tables=emp query=\” WHERE job=\’MANAGER\’ AND sal \>50000\” STATISTICS=NONE;

10.Export of multiple table from multiple schemas.

expdp system/password directory=BACKUP dumpfile=schema_old_table%u.dmp logfile=schema_old_tables.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′

11.Export from multiple schema but table name can be like matching criteria.

expdp system/U6dba#15@projdb directory=backup dumpfile=projdbdmp_%u.dmp logfile=projdbdmp_full.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′ INCLUDE=TABLE:\”IN\(\SELECT table_name FROM dba_tables where table_name like \’CC_%\’ \)\”;


Thats it..


Above are a snap-chat 🙂 of my huge collection of syntax which are most common in worst scenario.Still if you have any other critical situation which you are facing please let me know in the comment, It would be a honor to help.


Hope it helps.


With Regards

Nimai Karmakar



High Availability

Today Hemant Sir have posted a good link that represent some basic for oracle HIGH AVAILABILITY with demonstration.


Please click the link…


oracle High availability demos…


Hope this helps…

Hi friends,


This post is about lost writes in Oracle, on techniques for reproducing and investigating the effects of lost writes and on the use of Active Data Guard to protect against lost writes

Imagine this scenario: a production system has two standbys to protect against disaster and to load balance read-only load (with Active Data Guard (ADG)). A lost write happens in the primary and remains unnoticed for a few days. Finally the block that suffered a lost write is updated again. Both standbys stop applying redo throwing ORA-600 [3020] (also known as stuck recovery). The primary DB keeps working fine, although it is logically corrupted by the lost write.
You are the DBA in charge of fixing this, what would you do?

I hope that the example above illustrates that lost write can generate quite complex recovery scenarios and overall a few headaches to support DBAs. In this post I illustrate a few techniques and examples that can be used to test the effects of lost writes in Oracle and therefore prepare in case of a real-world issue strikes. Of particular interest will be to test the effect of lost writes in an environment with (Active) Data Guard.
We need to cover some ground first on techniques and how to setup the test. But first a definition.

Lost writes: “A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage” (from support note 1302539.1). Lost writes can be caused by faulty storage, but also by Oracle bugs, in general anything in between our data in RAM and storage can corrupt our data, including controllers and network.

Digression on techniques.
1. A useful technique that we will need in the following is the ability to read and write a single block from Oracle data files (in a test environment). For databases on filesystems (and also DBs on NFS) dd is the tool for this job (I am using the Linux platform as reference). Examples:

read one 8KB block from filesystem (block 134 in this example):
dd if=testlostwrite.dbf bs=8192 count=1 skip=134 of=blk134.dmp

write one 8KB block to filesystem (block 134 in this example)::
dd of=testlostwrite.dbf bs=8192 count=1 seek=134 if=blk134.dmp conv=notrunc

Note when writing to an Oracle datafile we must use conv=notrunc or else we will end up with an unusable (truncated) output file. Note also the syntax for specifying the block offset, skip is used for input files and seek for output files (see dd manual).

How to read and write single blocks on ASM data files. One possibility is to take a backup copy of the datafile with RMAN, edit it with dd (as detailed above), then (with rman again) restore the backup copy. With a little knowledge of ASM internals, more direct ways to access files in ASM are available: one can find the position of the block (and its mirror copies if relevant) and then use dd to read/write data directly. Currently my preferred way is sightly different and it exploits the dbms_diskgroup package. This is an undocumented package (see again the link above on ASM internals for some additional details) although it is extensively used by Oracle’s asmcmd utility. I have packaged the dbms_diskgroup.read and dbms_diskgroup.write calls into a small utility written in perl (that I called asmblk_edit, follow this link to download asmblk_edit). Similar ideas can be found also in support note 603962.1.
The following example illustrates using the utility asmblk_edit to read and write block number 134 from and to a data files stored in ASM:

read one 8KB block from an Oracle data file in ASM (block 134 in this example):
./asmblk_edit -r -s 134 -a +TESTDB_DATADG1/TESTDB/datafile/testlostwrite.353.810578639 -f blk134.dmp

write one 8KB block to and Oracle data file in ASM (block 134 in this example):
./asmblk_edit -w -s 134 -a +TESTDB_DATADG1/TESTDB/datafile/testlostwrite.353.810578639 -f blk134.dmp

2. Another technique that we need is quite straightforward and allows us to find the offset of the Oracle block that we want to read/write to for our tests.
The example here below shows how to find block number where data is stored, using rowid:
SQL> select rowid, dbms_rowid.ROWID_BLOCK_NUMBER(rowid), a.* from testlosttable a;

Incidentally finding the block number for an index leaf block can be done with the following (using the undocumented function sys_op_lbid):
SQL> select rowid rowid_table_from_index_leaf, sys_op_lbid(18297477,’L’,t.rowid) index_leaf_rowid from testlosttable t –note: 18297477 in this example is the data_object_id of the index I am examining, edit with the actual number as relevant

3. Last but not least, we need a way to modify data blocks ‘under the nose of Oracle’, in particular we want to make sure we flush/invalidate the relevant cached data and metadata. The method we will use is:

  • Offline the tablespace where data resides (this flushes dirty blocks and invalidates cache entries)
  • Perform the read/write modifications to the block, with dd or asmblk_edit, as needed
  • Online the tablespace again before the next usage

A basic recipe to reproduce the effects of a lost write. 

We can now put together the ideas and techniques described above into a working example aimed at reproducing the effects of a lost write in Oracle:

SQL> create bigfile tablespace testlostwrite datafile ‘{edit with datafile directory}/testlostwrite.dbf’ size 10m;
SQL> create table testlosttable (id number, payload varchar2(100)) tablespace testlostwrite ;
SQL> create index i_testlosttable on testlosttable (id) tablespace testlostwrite ;
SQL> insert into testlosttable values (10,’aaaaaaaaaaaaaaaaaaaaaaaaa’);
SQL> insert into testlosttable values (20,’bbbbbbbbbbbbbbbbbbbbbbbbb’);
SQL> commit;

SQL> select rowid, dbms_rowid.ROWID_BLOCK_NUMBER(rowid), a.* from testlosttable a;
–note: this will allow to find the block_id where data resides, let’s say it’s block 134
SQL> alter tablespace testlostwrite offline;

— read block either with dd or with asmblk_edit and create backup copy. Example:
— ./asmblk_edit -r -s 134 -a +TESTDB_DATADG1/TESTDB/datafile/testlostwrite.353.810578639 -f blk134.dmp
SQL> alter tablespace testlostwrite online;
SQL> insert into testlosttable values (30,’cccccccccccccccccccccccccc’);
SQL> commit;
SQL> alter tablespace testlostwrite offline;

— write block either with dd or with asmblk_edit and from previously created backup copy. Example:
— ./asmblk_edit -w -s 134 -a +TESTDB_DATADG1/TESTDB/datafile/testlostwrite.353.810578639 -f blk134.dmp
SQL> alter tablespace testlostwrite online;
SQL> — our database has now a lost write in the table testlosttable block 134

The effect of the lost write on the table is that the row with id=30 has disappeared from the table. However the entry with id=30 is still visible in the index i_testlosttable. This observation can be confirmed with  the 2 queries reported here below. Note that in case of normal operations (i.e. no lost writes) the 2 queries would both return three rows, this is not the case here because of our manual editing of the table block with the asmblk utility.
SQL> select /*+ INDEX_FFS(a)*/ id from testlosttable a where id is not null;
SQL> select  /*+ FULL(a)*/ id from testlosttable a;
SQL> — these 2 queries return different results in our test with a lost write.. and only one of them is correct!


We can now proceed with this additional test: insert a new row into the table, for example:

SQL> insert into testlosttable values (40,’ddddddddddddddddddddddd’);
SQL> commit;

What we should notice notice at this point is that Oracle keeps working fine and no errors are shown to the user. With the techniques discussed above we can easily show that this new row has  been inserted into block 134 (the block with a lost write). Let’s postpone further investigations for a later paragraph and for now just note that Oracle has not detected that we have suffered a lost write.

An example of the effects of lost writes with Standby (Data Guard)
A standby database, among others, provides a copy of the database that Oracle can use to detect lost writes (i.e. the standby provides a reference copy of ‘good data’). A simple test to see how this works in practice:

  • We start by going through the same steps described above and we create a lost write in the primary.
    • Note that at this point the standby has no knowledge that something has gone wrong in the primary.
  • Now we can go ahead and run DML against the block that has suffered the lost write
    • e. we insert the row with id=40 as detailed above.
  • At the moment when the standby database will try to apply the redo log entry (change vector) to the block with a lost write, it will compare SCN numbers and find that something is wrong. MRP will stop and throw ORA-600 [3020] (stuck recovery).

An example of the error stack for a stuck recovery:

ORA-00600: internal error code, arguments: [3020], [10], [134], [134], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 10, block# 134, file offset is 1097728 bytes)
ORA-10564: tablespace TESTLOSTWRITE
ORA-01110: data file 10: ‘+STDBY_DATADG1/TESTDB/datafile/testlostwrite.347.810578685’
ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 18299147

Additional information is dumped in the trace files of MRP and of the recovery slaves.
It’s worth noting that ORA-600 [3020] can be caused by a lost write (as in this example) but also by other issues, in particular with the redo consistency (outside the scope of this discussion).

Oracle 11g and lost write protection 
In the example above Oracle only throws an error alerting that something is wrong (in this case ORA-600 [3020], stuck recovery) when a DML operation is performed on the primary DB against a block that had suffered a lost write. This means that lost writes may also remain silent and unnoticed in the primary DB for a very long period of time. A new feature of 11g can be used to make Oracle more active in detecting lost write issues, in particular to check the blocks that are subject to physical reads into the buffer cache. This is how to activate these checks:

  • set the initialization parameter DB_LOST_WRITE_PROTECT= TYPICAL (or FULL if we prefer) on the primary database. This will cause the generation of additional redo entries when Oracle performs physical reads.
  • set DB_LOST_WRITE_PROTECT = TYPICAL also on the standby, this will make MRP and its recovery slaves to check for lost writes using the extra information in the redo log stream.
  • Outcome: if we hit a block suffering from lost write, MRP and its slaves will stop and throw ORA-752: recovery detected a lost write of a data block 

An example of the error stack for lost writes with db_lost_write_protect set to typical is pasted here below (note that additional information is dumped in the trace files of MRP and of the recovery slaves):

Hex dump of (file 10, block 134) in trace file {path..}/standby_pr09_26471.trc
Reading datafile ‘+STDBY_DATADG1/TESTDB/datafile/testlostwrite.347.810578685’ for corruption at rdba: 0x00000086 (file 10, block 134)
Read datafile mirror ‘STDBY_DATADG1_0000’ (file 10, block 134) found same corrupt data (logically corrupt)
Read datafile mirror ‘STDBY_DATADG1_0011’ (file 10, block 134) found same corrupt data (logically corrupt)


ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 10, block# 134, file offset is 1097728 bytes)
ORA-10564: tablespace TESTLOSTWRITE
ORA-01110: data file 10: ‘+STDBY_DATADG1/TESTDB/datafile/testlostwrite.347.810578685’
ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 18299538

Comparing this error message with the ORA-600 [3020] reported in the previous paragraph,the first thing we notice is that we have more information and more details. Moreover Oracle has checked that this is indeed a case of lost write and has even tried to resolve the ‘logical corruption’ trying to read the second mirror copy from the normal-redundancy ASM diskgroup used in this example (an attempt that did not help in this case as both mirror copies of the block have suffered a lost write, as generated in our example using the asmblk utility).

DB_LOST_WRITE_PROTECT and its impact on primary and standby DBs
From the example here above we see that the extra checks that oracle recovery process does when setting DB_LOST_WRITE_PROTECT with Oracle 11g are definitely an improvement from 10g behavior, although this mechanism does not provide a complete protectionagainst lost writes but only gives us a higher probability that a lost write can be found.

How about the impact of setting this parameter? Extra redo entries are generated on the primary: they are called block read redo (BRR). We can directly investigate BRR entries for example by dumping redo (BRR corresponds to redo at layer 23 and opcode 2), see this example on how to do this type of dump.
SQL> alter system dump logfile ‘…{path to storage}…/thread_2_seq_1816.510.810661967’ layer 23 opcode 2;

Another method is by querying v$mystat or v$sysstat. We will look for stats containing the text ‘lost write’ in their name:
SQL> select name, sum(value) from v$mystat se, v$statname n where n.statistic#=se.statistic# and (n.name like ‘%lost write%’ or name like ‘%physical read%’) group by name;

The size of BRR entries in the redo stream varies as Oracle’s optimizations can come into play, such as batching several BRR entries in one redo record. Based on a limited number of observations of a production system I estimate than on average we can expect 30 bytes of extra redo generated by BRR for each physical block read performed by the database, although mileage may vary and definitely the impact of the parameter should be tested before applying it to a busy production system! Another observation based on testing is thatdirect read operations do not generate BRR (for example reads for parallel query).

Example of BRR entry from a logfile dump:

CHANGE #5 TYP:0 CLS:4 AFN:10 DBA:0x00000086 OBJ:18299943 SCN:0x05ca.9b6e95f9 SEQ:1 OP:23.2 ENC:0 RBL:1
Block Read – afn: 10 rdba: 0x00000086 BFT:(1024,134) non-BFT:(0,134)
scn: 0x05ca.9b6e95f9 seq: 0x01
flags: 0x00000004 ( ckval )

After setting db_lost_write_protect to typical (or full) and rebooting the instance Oracle will set_log_committime_block_cleanout to true (based on observations on my test system The effect is that (on the primary instances) Oracle will write into the redo logs additional entries related to commit time block cleanout operations (layer 4 opcode 6). Logging block cleanout redo record (and subsequently applying them to the standby database) is a desirable feature especially in the case of an environment deploying Active Data Guard.

On a standby instance Oracle setting db_lost_write_protect to  TYPICAL or FULL will make MRP slaves do the extra work of checking for lost writes by comparing SCNs in BRRs with SCN in the block headers. Notably the extra work implies additional physical reads on the standby. This additional work on the standby can be monitored by querying v$sysstat (statistics name = ‘recovery blocks read for lost write detection’ and also the related stat ‘recovery blocks skipped lost write checks’).

Note: checks for lost write based on SCN from BRR entries are performed also when doing media recovery (i.e. a recover database command). Therefore even if Data Guard is not available one can use a simple restore of the database from backup to perform validation of BRR records to search for lost writes.


Analysis and troubleshooting.
From the steps details above we have a test environment with a lost write that we have generate. This will allow us to have some fun investigating the issue at low level and developing techniques for analysis and troubleshooting. All this with the great advantage, compared to a real-life case, that now we know for sure what the root cause of this incident is!
A good starting point for troubleshooting lost writes in a production system is support note 1265884.1 “Resolving ORA-752 or ORA-600 [3020] During Standby Recovery”. A few more pointers here below.


– One technique to investigate the affected blocks both on primary and standby is by dumping the block contents to a trace file. The idea is to compare the contents of the primary and standby. Of particular interest will be  the SCN of the last change to the block and also SCNs in the ITL list. Example:
SQL> alter system dump datafile 10 block 134;
SQL> –the following performs a dump of the cached version of the block
SQL> alter session set events ‘immediate trace name set_tsn_p1 level <ts#+1>’; — where ts# is the tablespace number
SQL> alter session set events ‘immediate trace name buffer level <decimal rdba>’; –rba is 134 in our example

– We can also investigate the contents of the block suffering a lost write using SQL. This has the advantage of allowing the use offlashback query. The current content of the block on the primary database  may not be what we want: we are interested in aconsistent image of the block at the SCN equal to the SCN reported in our ORA-752 message for example (this would also be current SCN of the standby as recovery is stuck there at that point in time).  Example:
SQL> set num 16
SQL> select ora_rowscn, rowid, dbms_rowid.rowid_row_number(a.rowid) row_number, a.* from testlosttable as of scn 6367748219413 a where rowid like ‘ABFzkJAAAAAAACG%’; — edit values for scn and rowid, use SQL below to find the values to use

– Digression: how to find the rowid of the block with lost write (block 134 of file 10 and object_id=18299145). Actually what we need is just the first 15 characters of the rowid (the last three characters are the row_number inside the block). Example:
SQL> select substr(DBMS_ROWID.ROWID_CREATE(rowid_type =>1, object_number =>18299145, relative_fno =>0, block_number =>134, row_number =>0),1,15) rowid_prefix from dual;

– How to find the current SCN (at the standby):
SQL> select current_scn from v$database;

– Another powerful technique is to dump redo log information from all relevant logfiles searching for entries related to the block with lost writes (in our example it’s block 134 of file 10). The dump will include transaction details and most notably redo marking the time when DBWR has written the give block (this info is sotre in block written redo, BWR). If  the parameter db_lost_write_protect is set to typical or full the redo dump will also show details of the block read redo (see BRR discussed above). For further info on logfile dumps see also Julian Dyke’s website. Example:
SQL> alter system dump logfile ‘…{path to storage}…/thread_2_seq_1816.510.810661967’ DBA MIN 10 134 DBA MAX 10 134; — edit file number and block number as needed


– Additional investigation on data affected by the lost write can be performed by log mining. The goal is to find the SQL of all the transactions for the affected block. Identify the relevant redo logs to mine first. Example:

SYS.DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>’…{path to storage}…/thread_2_seq_1816.510.810661967′,Options=>SYS.DBMS_LOGMNR.NEW);




SQL> SELECT scn,sql_redo FROM SYS.V_$LOGMNR_CONTENTS WHERE data_obj#=18299145 and row_id like ‘ABFzkJAAAAAAACG%’; — calculate rowid with dbms_rowid package as detailed above


– Other analysis that can be done in case we have indexes on the tables:

In case one or more indexes are present we can read data from the index and compare the results with what we have on the table. Example to read from the index:
SQL> select rowid rowid_table_from_index_leaf, id, sys_op_lbid(18299146,’L’,t.rowid) index_leaf_rowid from testlosttable t where rowid like ‘ABFzkJAAAAAAACG%’;
–note: update 18299146 with the the data_object_id of the index of interest
— in this example this is the data_object_id of I_TESTLOSTTABLE

– We can also check if there are inconsistencies (corruption) between the table and its indexes with ‘analyze table validate’, although in my experience this can be quite time consuming and not necessarily add more information to the analysis. Example:
SQL> analyze table testlosttable validate structure cascade online;


Actions that we can take to restore the services
Suppose that our analysis has confirmed that a lost write happened and also that we have the details of what ‘is lost’. We need now to fix the corrupted block on the primary and restore the service on the standby. In particular if we have an Active Data Guard with a SLA, the latter may be quite an urgent action. Hopefully we also have some ideas of what the root cause was and a way to fix it in the future.

One possible action is to failover to standby. This action plan however may provide to be unacceptable in many circumstances, given the potential for data loss it implies. A failover would likely not be acceptable if the primary database has continued working and accepting users transactions since the time (SCN) of the incident that has generated ORA-600 (or ORA-752).

Another possibility is to use our knowledge of the lost transactions gathered in the analysis phase to run SQL actions to ‘fix the primary’. This has to be evaluated case by case. In some circumstances we can also get away with just dropping and recreating the object with a lost write. In our simple example of a lost insert on table testlosttable, the action to perform on the primary is:

SQL> alter index i_testlosttable rebuild online;
SQL> insert into testlosttable values (30,’cccccccccccccccccccccccccc’);
SQL> commit;

What about fixing the standby? We can unstuck the recovery on the standby by allowing it to corrupt  the block with a lost write (and fix it later, as detailed below). Example:

SQL> alter system set db_lost_write_protect=none; –temporarily disable lost write check if needed
SQL> alter database recover automatic standby database allow 1 corruption;
SQL> –wait till the redo that cause the error message has been applied
SQL> alter database recover cancel;
SQL> — restart normal Data Guard operations. An example for ADG:
SQL> alter system set db_lost_write_protect=typical;
SQL> alter database open read only;
SQL> alter database recover managed standby database nodelay using current logfile disconnect;


At this point all is OK except for one corrupted block on the standby. How to restore the corrupted block on standby? This depends on the case, we may just rebuild the object on primary this will fix the problem on standby. Another option is to copy over the datafile from primary to standby
In our example the corrupted block is block 134 of file 10 and we have an active data guard in real time apply. We can use automatic block media recovery (ABMR) to fix it. In my tests AMBR is attempted but does not really work against the corrupted block 134, I can work around this by zeroing out the block. This is an example (intended to be used on test databases):

./asmblk_edit -w -s 134 -a +STDBY_DATADG1/TESTDB/datafile/testlostwrite.347.810578685 -f zeroblock

where zeroblock file is created with: dd if=/dev/zero bs=8192 count=1 of=zeroblock

if my standby was on filesystem I could have used:
dd if=/dev/zero of=testlostwrite.dbf bs=8192 count=1 seek=134 if=blk134.dmp conv=notrunc
If we now query the table (the table testlostwrite in our example) when Oracle reaches the zeroed block it will fetch a copy from production (see Oracle documentation for the details about configuring and using the ABMR feature). This will happen in a transparent way for the user issuing the query, the operation is logged in the alert log of the instance (the 2 lines here below appeared repeated twice in my test using

Automatic block media recovery requested for (file# 10, block# 134)

Automatic block media recovery successful for (file# 10, block# 134)

Oracle’s OTN demo video on lost write

Short videos with demos on testing lost writes and automatic block media recovery in a Data Guard environment can be found on OTN.
See also support document “Best Practices for Corruption Detection, Prevention, and Automatic Repair – in a Data Guard Configuration” [ID 1302539.1]

Conclusions and future work
This article illustrates a simple example on how to reproduce and investigate the effects lost write in a test environment, which is intended as a training exercise for DBAs.
I would like to end with four lessons learned on the topic of how lost writes can affect a high-availability Oracle deployment:

  • Finding the root causes of a lost write can prove to be very hard. Two possible sources of the root causes of these type of issues are the storage and Oracle bugs. How to find which one (if any of the two) is to blame for a particular incident? If the issue cannot be re-produced we can easily find ourselves in between a finger-pointing discussion between vendors.
  • Having a standby (Data Guard) is of great help for discovering lost writes. Without a standby there are high chances that we will not realize we had a lost write in the first place. However, once a lost write is found the redo apply stops:the recovery is stuck and throws error ORA-600 [3020] or ORA-752 if we try to restart it. A support DBA most likely will have to analyze the situation and decide what to do to fix the issue (for example perform a failover or rather going through a process similar to what described in this article). Moreover if one or more Active Data Guards are used for critical read-only activity, there is time pressure to restart the redo apply.
  • How can we check that our primary and standby databases are ‘in sync’, that is that there are nolost writes waiting to ‘explode’ as time bombs? I am not aware of a utility that could do such check. This is a very interesting topic, probably material for another post. A brief discussion of this problem and possible solutions can be found at this link and also here.
  • Database restores can be affected by lost writes too. For example the restore of a database backup can fail (get stuck on applying redo) because of a lost write that has happened in production after the latest data file backup. This has potential impacts on the disaster and recovery strategy.

Note: the examples reported in this article have been tested against Oracle 64 bit for Linux (RHEL5).

Hope it helps..

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 10,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 4 sold-out performances for that many people to see it.

Click here to see the complete report.

Oracle Enterprise Manager Cloud Control Installation on Solaris Spark 64bit


As stated earlier on my post about the installation of Oracle 12c enterprise manager cloud control installation. So , friends this post belong to the installation….

Please comment your suggestion or any feedback.


The minimal Hardware requirement for OMS (got the data from metalink docs) –


1 OMS, < =1000 targets, <100 agents 2 OMSes for < =10,000 targets and <1000 agents >2 OMSes, >=10,000 targets, >=1000 agents
CPU Cores/Host 2 4 8
RAM 4 GB 6 GB 8 GB
RAM with ADP, JVMD 6 GB 10 GB 14 GB
Oracle WebLogic Server JVM Heap Size 512 MB 1 GB 2 GB
Hard Disk Space 7 GB 7 GB 7 GB
Hard Disk Space with ADP, JVMD 10 GB 12 GB 14 GB


Note – If you have Enterprise Manager 10g Grid Control Release 4 ( or lower installed, then you must first upgrade to Enterprise Manager 10g Grid Control Release 5 ( or Enterprise Manager 11g Grid Control Release 1 ( before you can upgrade to the new release.


Before proceeding please drop the old enterprise manager repository database.

Follow the steps to drop the old EM (Single Instance) repository –

  1. $ORACLE_HOME/bin/emca <space>-deconfig <space> dbcontrol <space> db<space> -repos <space> drop <space> – SYS_PWD <SYS Password> <space> – SYSMAN_PWD <SYSMAN Password>
  2. Enter the following information:

Database SID: DBTEST11G

Listener port number: 1525

To de-configure Database Control for a Real Application Clusters (RAC) database, follow the following command on database host:

  1. Set the environment variable <ORACLE_HOME>.
  2. Now run the following command:

$ORACLE HOME/bin/emca <space> -deconfig <space> dbcontrol <space> db <space> -repos <space>drop <space> -cluster <space> -SYS_PWD <space> <sys password> <space> -SYSMAN_PWD <sysman password>



Oracle strongly suggest to make below changes on your database before start 12c installation Init.ora parameter dedicated to EM12.







MEMORY_TARGET => should not be used

Or if your using and using automatic memory management then no need to update SGA_TARGET or PGA_AGGREGATE_TARGET


Now that the environment and repository database is ready let’s start the installation.

First we need to update the parameters as per our environment.

SQL> alter system set shared_pool_size=600M scope=both sid=’*’;

alter system set PGA_AGGREGATE_TARGET=1G scope=both sid=’*’;

alter system set PROCESSES=300 scope=spfile sid=’*’;

alter system set sessions=335 scope=spfile sid=’*’;

(as the 10g rule applied for 1.1*process+5)

alter system set JOB_QUEUE_PROCESSES=20 scope=both sid=’*’;

alter system set SESSION_CACHED_CURSORS=300 scope=spfile sid=’*’;

Oracle recommended to use UNDO tablespace is at least 200M in size.


In 11g version We need to install Weblogic separately but in 12c its comes by default .(Default version is 10.3.5

Please be sure DO NOT SET ORACLE_HOME or ORACLE_SID before start installation

Oracle also recommended to set umask 022 before start installation

Now we can start 12.1 installation.


Go to 12.1 setup path and type below command to can start installation



  • ./runInstaller


  • Optional screen to provide oracle support credentials.


  • If you are skipping the support credentials page. Just press yes and proceed.



  • Next screen is the prerequisite check(here we got a failed prerequisite check ulimit)


To solve the ulimit error we need to add set ulimit = 4096 /etc/system file if available but in our scenario as it is a virtual server we have to add it to /etc/project file.


save the file and do source /etc/project and check it

bash-3.2$ projects -l


projid : 0

comment: “”

users : (none)

groups : (none)



projid : 1

comment: “”

users : (none)

groups : (none)



projid : 2

comment: “”

users : (none)

groups : (none)



projid : 3

comment: “”

users : (none)

groups : (none)



projid : 10

comment: “”

users : (none)

groups : (none)



projid : 100

comment: “”

users : (none)

groups : (none)

attribs: project.max-sem-ids=(privileged,100,deny)






  •  Once the prerequisite have passed we can proceed with Installation type.


Here we need to select the installation type depend upon our environment to monitor.

If you use Simple than Cloud Control will install by default plugins. (Exadata ,Oracle Database, Fusion Middleware, My Oracle Support)

  • Next we have to provide the location for Middleware and Agent Binaries.


  • Next we need to supply the oms admin credentials with the DB details where you want to install the management repository database.


While specifying Database Host name please be sure to use the fully qualified domain name like testDB.oracle.com or you can also supply the host IP.

  • Review the setup and click next.


  • In addition after proceeding with the setup you may get yes/no and ok/cancel screen just review and proceed further.


  • After the installation is successful. Just execute the root.sh

Now wait for around 45 minutes to complete the setup and open the URL at the browser…


  1. After everything is done successfully. Use the link to open the enterprise manager console.



Login with the username sysman and the password you have provided during installation.


  To check the status of enterprise manager agent.

cd $AGENT_HOME/bin

bash-3.2$ ./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.


Agent Version         :

OMS Version           :

Protocol Version       :

Agent Home             : /u01/oracle/enterprise_mang/agent/agent_inst

Agent Log Directory   : /u01/oracle/enterprise_mang/agent/agent_inst/sysman/log

Agent Binaries         : /u01/oracle/enterprise_mang/agent/core/

Agent Process ID      : 5969

Parent Process ID     : 5918

Agent URL             : https://testDB:3872/emd/main/

Local Agent URL in NAT : https://testDB:3872/emd/main/

Repository URL         : https://testDB:1159/empbs/upload

Started at             : 2014-09-15 21:24:42

Started by user       : oracle

Operating System       : SunOS version 5.10 (sparcv9)

Last Reload           : (none)

Last successful upload                       : 2014-09-16 12:16:28

Last attempted upload                       : 2014-09-16 12:16:28

Total Megabytes of XML files uploaded so far : 2.06

Number of XML files pending upload           : 0

Size of XML files pending upload(MB)         : 0

Available disk space on upload filesystem   : 44.19%

Collection Status                           : Collections enabled

Heartbeat Status                             : Ok

Last attempted heartbeat to OMS             : 2014-09-16 12:17:03

Last successful heartbeat to OMS             : 2014-09-16 12:17:03

Next scheduled heartbeat to OMS             : 2014-09-16 12:18:03



Agent is Running and Ready




Hope that will help a bit…


Thanks & Regards

Nimai Karmakar

Bitbach's Blog

Just another Oracle weblog @ WordPress

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

The Ayurveda

Site for Ayurveda and Yoga articles


Few adventures in my slow running life...


4 out of 5 dentists recommend this WordPress.com site


Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World