The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 248 other followers

Oracle Database 11g Interactive Quick Reference

Posted by Harald van Breederode on November 24, 2010

Just a quick note about the Oracle Database 11g Interactive Quick Reference That was released today to the public. This Interactive Quick Reference, created by Oracle University and the Server Technology Curriculum group, is your Essential Guide to Oracle Database 11g Release 2. This interactive quick reference includes an architecture diagram, a comprehensive list of background processes and an extensive list of DBA views organized by product feature areas.
-Harald

Posted in Oracle | 12 Comments »

Recovering a lost Data Guard broker configuration file (ORA-16572)

Posted by Harald van Breederode on October 26, 2010

While teaching the Oracle11g Release 2 Data Guard course a while back I got a question about how to recover from the loss of a Data Guard broker configuration file. I didn’t know the answer right away so I did a bit of research and this is what I found out.

Basically there are 4 different recovery scenarios possible, listed by level of difficulty starting with the easiest one:

  1. Recovering a lost broker configuration file on a standby database while it is still running
  2. Recovering a lost broker configuration file on a standby database while it is not running
  3. Recovering a lost broker configuration file on the primary database while it is still running
  4. Recovering a lost broker configuration file on the primary database while it is not running

Before explaining how to recover a lost Data Guard broker configuration file, let’s explore my Data Guard configuration and take a look at the size and location of the actual broker configuration files itself.

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2peppi.dat

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2kokki.dat

As shown in the output above, my Data Guard configuration is called “PeppiEnKokki”, named after a famous Dutch children TV series. The primary database is named “peppi” and is hosted on a system with a hostname “el4:, and there is one physical standby database named “kokki” which is running on a system with the hostname “el5”. Furthermore the output shows that each database in the Data Guard configuration has two broker configuration files which are store in their $ORACLE_HOME/dbs directory. The reason behind two configuration files is that one of them stores the current configuration while the other stores the previous version of the configuration. When the configuration gets updated the new configuration gets written to the “oldest” configuration file and once this update is complete the broker switches from the current file to the updated file.

Now that my Data Guard setup is clear we can proceed with the 4 different recovery scenario’s.

Scenario #1: Recovering a lost broker configuration file on a standby database while it is still running

The first scenario deals with the loss of either one or both broker configuration files on a standby database while the standby database is still running. Before explaining how to recover this we first need to create the situation where there is actually something to recover ;-) This is easily achieved by brutally deleting both broker configuration files on host “el5”.

el5$ rm /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
ls: /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat: No such file or directory

The question is: Does the broker realize that its configuration files are missing?

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The broker clearly does not recognize the fact that the configuration files are missing. This is more or less true because the broker keeps the active configuration loaded in memory. However, if the standby database needs to be restarted a problem will show up as we will see in the next scenario. The question for now is: How do we recover the lost broker configuration files?

The answer is we don’t have to because the broker will recover from this error automatically without user intervention! Every time the broker configuration gets updated the new configuration will be written from memory to the inactive configuration file on disk. Thus the lost file will be recovered automatically upon the next change in the broker’s configuration.

If we want to trigger this process by hand we can simply re-enable the standby database in question as shown below:

DGMGRL> enable database kokki 

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1kokki.dat

We see that one broker configuration file has been re-created because we re-enabled the standby database. By re-enable the standby database another time the other broker configuration file will be re-created as well.

DGMGRL> enable database kokki 

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2kokki.dat

Now they are both back and we can proceed to the next scenario.

Scenario #2: Recovering a lost broker configuration file on a standby database while it is not running

The second scenario deals with the loss of the active broker configuration file while the standby database is not running. Again we start our journey by first creating the actual problem by shutting down the standby database instance followed by removing both broker configuration files as shown below:

SQL> connect sys/oracle@kokki as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.

el5$ rm /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
ls: /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat: No such file or directory

The first step in recovering from this situation is to startup the standby database instance:

SQL> connect sys/oracle@kokki as sysdba
Connected.
SQL> startup mount quiet
ORACLE instance started.
Database mounted.

The standby database instance started normally and didn’t complain about the loss of the broker configuration files. Does the broker itself realize that they are gone?

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database
      Error: ORA-16532: Data Guard broker configuration does not exist

Fast-Start Failover: DISABLED

Configuration Status:
ERROR
 

YES! The broker knows that its configuration files are indeed missing and it starts recovery without user intervention. Behind the scenes it asks the primary database for the current configuration and writes it to disk. If we wait a while we can see that this is indeed the case:

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1kokki.dat

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

As shown above the broker recovered one of the configuration files automatically and it reports that the overall status of the configuration is fine. Upon the next change in the configuration the other file will be recovered as well. If we want to trigger this process by hand we can simply re-enable the standby database just as we did in scenario #1.

DGMGRL> enable database kokki 

el5$ ls -1hs $ORACLE_HOME/dbs/dr?kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1kokki.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2kokki.dat

Now that both files are recovered we can proceed to the next scenario.

Scenario #3: Recovering a lost broker configuration file on the primary database while it is still running

This scenario deals with the loss of either one or both broker configuration files on the primary database while the primary database is still running. This scenario is pretty much the same as the first scenario. As before we begin setting up the problem by removing the broker configuration files from disk.

el4$ rm /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
ls: /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat: No such file or directory

Now that they are gone we will ask the broker for an overview of the configuration in order to determine if it realizes that its configuration files are missing.

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The broker is not aware of the missing files and reports a successful overall status. Just like in the first scenario the broker will re-create the missing configuration files upon a configuration change by writing the new configuration to disk thereby in effect performing recovery. Again this recovery can be triggered by re-enable the primary database if we have the desire to do so.

DGMGRL> enable database peppi 

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2peppi.dat

As shown above one configuration file is recovered and if we re-enable the primary database another time the other file will be recovered as well.

DGMGRL> enable database peppi 

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2peppi.dat

They are now both recovered and we will move on to our final scenario.

Scenario #4: Recovering a lost broker configuration file on the primary database while it is not running

The final scenario deals with the loss of the active broker configuration file on the primary database while the primary database is not running. Again we start by knocking down the primary database instance followed by removing the broker configuration files from disk.

SQL> connect sys/oracle@peppi as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.

el4$ rm /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
ls: /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat: No such file or directory

Now that we removed the broker configuration files, let’s see what happens if we startup the primary database instance.

SQL> connect sys/oracle@peppi as sysdba
Connected.
SQL> startup quiet
ORACLE instance started.
Database mounted.
Database opened.
 

The primary database instance starts up normally and doesn’t complain about the loss of the broker configuration files. One might expect that the broker will recover them automatically, just as it did on the standby database, but this is simply not possible on the primary database. Even if we wait a long time, the broker configuration files will not show up automatically.

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
ls: /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat: No such file or directory

It is clear that the broker didn’t recover the lost broker configuration files automatically, and the question arises: Is the broker actually aware that its configuration files are indeed gone? Let’s see what the broker has to say:

DGMGRL> show configuration;
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

Hmmm, the broker reports that there is no configuration at all! Is this really true or is the broker just incapable to distinguish between a non-existing configuration or the lost configuration? Examining the broker’s log file seems like a good thing to do in order to determine what is really going on here.

$ tail $ORACLE_BASE/diag/rdbms/peppi/v1120/trace/drc*.log
………
2010-09-12 19:11:55.034                            status = ORA-16572
………

What is an ORA-16672 ?

$ oerr ora 16572 
16572, 00000, "Data Guard configuration file not found"
// *Cause:  The Data Guard broker configuration file was either unavailable or
//          did not exist.
// *Action: Verify that the configuration file was successfully created.
//          If the DG_BROKER_CONFIG_FILE[1|2] initialization parameters were
//          changed, ensure the file name on disk and the parameter value
//          match, there is space on the device, and the proper permissions
//          are granted. For a RAC database, ensure that these initialization
//          parameters are set to file locations that are shared by all
//          instances of the RAC database.

The above makes clear that the broker configuration files are either not there or they can’t be accessed. We already know that they are gone, because we removed them ourselves, so there is no need to verify this. The question is of course: How do we recover the lost broker configuration files?

The solution is either recreating the whole broker configuration from scratch or to copy a broker configuration file from one of its standby databases to the primary database. I’ll go for the second solution as shown below. It’s important to perform this action while the broker itself is not running.

SQL> alter system set dg_broker_start=false;

System altered.

el4$ scp -q el5:$ORACLE_HOME/dbs/dr1kokki.dat $ORACLE_HOME/dbs/dr1peppi.dat

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1peppi.dat

SQL> alter system set dg_broker_start=true;

System altered.

After this step we need to give the broker a bit of time so it can do whatever it needs to do. After a while we can take a look at the broker configuration files and verify the overall configuration status.

el4$ ls -1hs $ORACLE_HOME/dbs/dr?peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr1peppi.dat
16K /u01/app/oracle/product/11.2.0/dbs/dr2peppi.dat

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The broker reports an overall status of SUCCESS despite the fact that the standby database is disabled. We can easily correct this by re-enabling the standby database in question:

DGMGRL> enable database kokki 

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Everything is fine now and it is about time to wrap up and summarize our findings.

In summary

In most situation recovering lost Data Guard broker configuration files is handled automatically by the broker itself, but can be manually triggered by forcing an update to the broker configuration. Only in case of the loss of all broker configuration files on the primary database user intervention is required. All we need to do is copy a broker configuration file from one of its standby databases to the primary database followed by re-enabling any disabled databases.
-Harald

Posted in Oracle | 4 Comments »

Dropping interval partitions – Revisited

Posted by Harald van Breederode on July 26, 2010

In a previous posting about dropping interval partitions I explained how the transition point in an interval partitioned table can be moved up to be able to drop partitions. I explained that by temporarily converting the table from interval to range partitioning, all interval partitions are converted into range partitions.

Last week I discovered, suggested by Heijne Makkreel, that this conversion is unnecessary and that just setting the interval granularity to what it is already set to, all interval partitions are converted to range partitions.

The query below shows the partitions of an interval partitioned table (the same table as used in my original posting) with one range partition and four adjacent interval partitions. The partitions are 1 month in size.

SQL> select partition_name,interval,high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       YES TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       YES TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       YES TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

SQL> select interval from dba_part_tables where table_name = 'IPART';

INTERVAL
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(1,'MONTH')

By issuing an ALTER TABLE command that sets the interval to what it is already set to, all interval partitions are converted into range partitions as demonstrated below:

SQL> alter table ipart set interval (NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name,interval,high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       NO  TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       NO  TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

The query output above shows that all partitions are now of type range, and we can simply drop them if we wish to do so.
-Harald

Posted in Oracle | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 248 other followers