Partitioning using CREATE TABLE AS (CTAS) and Column Default Values

This is about the CTAS (Create Table As...) operations during the creation of partitioned tables from normal tables in an Oracle database.

The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns. This might lead to a nasty surprise if you are doing RANGE PARTITIONING and the partition key DATE column has a DEFAULT VALUE of SYSDATE in the normal table. This DEFAULT VALUE setting is NOT copied to the partitioned table!

What will happen is, if you do not specify a date explicitly in your INSERT statements you will get an 'ORA-14400: inserted partition key does not map to any partition' error. As the partition key value passed in will be NULL and the partitioned table will NOT know about the DEFAULT value.

Things can be worse if you are using a combination of CTAS to create partitioned tables from normal tables, and then you use an ALTER TABLE ... RENAME TO .. to replace your normal production tables with the new partitioned tables. Let me tell you how. If your application's INSERT statements into these new partitioned tables, do not explicitly specify the date value of the partition key, you will pass in NULLs and hit ORA-14400. Watch out you will suffer production outage!

To fix this problem, you will have to either explicitly change your code to pass in a date value for the partition key column, or alter the partitioned table after CTAS and modify the column to have a default value.

Here is a demonstration on Oracle 10g R2 of how easily this thing can happen.

Create the normal table

conn scott/tiger
Connected.

drop table big_table;

create table big_table
(id number primary key,
subject varchar2(500),
created_date date default sysdate
)
/

insert into big_table (id, subject) values (4,'tset3')
/

1 row created.

commit;

Commit complete.


Create the partitioned table with CTAS from the normal table above, consider using NOLOGGING table creation option to avoid trashing the logs if you think this data is recoverable from elsewhere. This will also create the table faster.

drop table par_big_table
/
-- change dates below appropriately to include the SYSDATE
-- at the time you run this example.

create table par_big_table
partition by range (created_date)
(
partition p200809 values less than (to_date('01-10-2008', 'DD-MM-YYYY')),
partition p200810 values less than (to_date('01-11-2008', 'DD-MM-YYYY'))
)
as
select * from big_table
/

Now try to insert into the new partitioned table without passing the CREATED_DATE value, as you will wrongly assume the new partitioned table will have a DEFAULT VALUE. See how you get the error.

insert into par_big_table (id, subject) values (5,'test4')
/

insert into par_big_table (id, subject) values (5,'test4')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This happens because the value the INSERT statement is passing for the CREATED_DATE column is NULL and the partitioned table doesn't have a DEFAULT VALUE for this column.

To fix this error and stop the production outage you might have caused :-) you have two choices:

* ALTER the new partitioned table and make the column to have a DEFAULT VALUE
* Change the application code to always include a value for the CREATED_DATE

Let's ALTER the table.


alter table par_big_table modify ( created_date date default sysdate);

insert into par_big_table (id, subject) values (5,'test4');

1 row created.

commit;

Commit complete.


Again watch for those DEFAULT VALUES in columns when you are creating partitioned tables with CTAS.

Capture bind variables in SQL with Oracle FGA

In Oracle 10g with fine-grained auditing (FGA) it is possible to track the bind variables of your application's SQL statements. I know, Oracle 11g is out but how amazing, I am still discovering new things in Oracle 10g!

Oracle FGA is good for bind variables, if that is what you want after all. It is lightweight and more easy to use than its alternatives, for example the full SQL TRACE with the option '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'.

Another benefit of FGA is that you can choose which object you want to audit and what kind of statements, SELECT, INSERT...etc you want to audit. Whereas with the SQL TRACE you just have to accept the trace dump and the performance implications.

More good news is that in Oracle 10g you don't have to bounce the database anymore to set the initialization parameter AUDIT_TRAIL and enable auditing for your system. FGA does not require a database shutdown/restart.

Object or user auditing can be done ad-hoc and on the spot, thanks to FGA. You can audit INSERT, DELETE or just SELECT statements. Moreover, you can be object specific or user specific. For example if you wanted to audit the DML happening on a table all you have to do is to create an Audit Policy for that table. You can use the DBMS_FGA.ADD_POLICY procedure to create the policy. The modifications on the table, together with the bind variables, are captured and logged in a SYS owned log table called FGA_LOG$. How amazing!

Here is an example:

We set up an object auditing policy, which will monitor all INSERT, UPDATE and DELETE operations on the table EMP on Scott's schema.


SQL> begin
dbms_fga.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_DETECTIVES',
audit_column => 'ENAME',
statement_types => 'INSERT, UPDATE, DELETE',
audit_trail => DBMS_FGA.DB_EXTENDED
);
end;

PL/SQL procedure successfully completed.


Then connect as SCOTT the owner of the object you are auditing and do some changes on the object.


SQL> conn scott/tiger

Connected.

SQL> variable myname varchar2(50);
SQL> exec :myname :='ROBIN';


PL/SQL procedure successfully completed.

SQL> insert into emp values (9999, :myname, null, null, null, null, null,null);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> delete emp where ename = :myname;

1 row deleted.

SQL> commit;


Now look at the FGA_LOG$ to see the audit entries.

SQL> conn sys/***** as sysdba

Connected.

SQL> column ntimestamp# format a30
SQL> column lsqltext format a15
SQL> column dbuid format a15
SQL> column obj$name format a10
SQL> column lsqlbind format a15
SQL> column lsqltext format a20

SQL> select ntimestamp#, dbuid, obj$name, lsqlbind, lsqltext  from sys.fga_log$;


NTIMESTAMP#                    DBUID           OBJ$NAME   LSQLBIND        LSQLTEXT
------------------------------ --------------- ---------- --------------- --------------------
14-AUG-08 19.39.48.427696 PM   SCOTT           EMP         #1(5):ROBIN    insert into emp valu
                                                                          es (9999, :myname, n
                                                                          ull, null, null, nul
                                                                          l, null,null)

14-AUG-08 19.39.56.577619 PM   SCOTT           EMP         #1(5):ROBIN    delete emp where ena
                                                                          me = :myname

WARNING : Don't forget to drop the policy once you are done, cause the auditing will go for infinite time on the object. The FGA_LOG$ table will fill up and you will waste space. You can drop the policy once you have done with it like this:


SQL> conn sys/***** as sysdba
SQL>
begin
dbms_fga.drop_policy ( 'SCOTT', 'EMP','EMP_DETECTIVES');
end;



Oracle OBIEE Install on Unbreakable Linux

After visiting a seminar given by Oracle on Data Analysis and Oracle BI, I have decided to explore the Oracle OBIEE product and see it first hand. In this post I would like to share my experience of installing Oracle OBIEE on Oracle's Unbreakable Linux.


I am in the opinion that all things should be in the database and I was reluctant and skeptical about Oracle BI. Why on earth do we need another app server kind of server, a server which is half webserver and half app server and all it does is things like daily office automation tasks. After reading some marketing text about the tool I found it to claim to do things like:

• Mail merge
• Cheque printing
• PDF reports
• Flash graphs
• Any format Reports
• Interactive dashboards
• Ad-hoc analysis
• Market analysis

A true mix of Siebel, JD Edwards and Peoplesoft one can say. Especially what is the point of having a tool like OBIEE, when you can have all this (maybe not all, but a significant portion) directly from the database with Oracle APEX in Oracle 11g served to the user via a web browser and for FREE. Having Google offering MS Word like applications on the web what is the point really of OBIEE. Is OBIEE already old technology?

Installing Oracle OBIEE on Unbreakable Linux

So, I have downloaded Unbreakable Linux from the Oracle Store here. Installed the OS on a Desktop Machine and then, downloaded Oracle Business Intelligence Enterprise Edition OBIEE and particularly the Linux version file biee_linux_x86_redhat_101333_disk1.cpio from Oracle Downloads Website.

One thing I noticed when I was reading about OBIEE is that it is "Database Agnostic", that is it doesn't need a database to operate, although you can put it in XMLDB. But this is not necessary as OBIEE can live on a file system.

That is what I did and I run the command on the downloaded file like this from my Linux prompt

$ cpio -idmv < biee_linux_x86_redhat_101333_disk1.cpiowhen the file extracted gave me following directory structure

/home/oracle/RH_Linux/Server/Oracle_Business_Intelligence/

then all you need to do to start installilng the OBIEE is run the setup.sh script like this

/home/oracle/RH_Linux/Server/Oracle_Business_Intelligence/setup.sh

During the installation the trickiest part was to provide your JDK software's home. That is you have to have Java installed on the box where you will run OBIEE as it runs on OC4J (Oracle Containers 4 Java) platform. Basically a J2EE platform.

Once I have correctly indicated the directory where my Java JDK 1.5 (or greater) was installed the installation was smooth and finished quickly. I have chosen the full complete installation of OBIEE from the install options and it put software in the designated Oracle BI homes.

/home/oracle/OracleBI
/home/oracle/OracleBIData

As this is not MS Windows I didn't get lots of menu items at the end of the installation. Instead I had to go to a directory and start certain scripts in order to get the whole of my OBIEE infrastructure up and running. If you want to know which servers and services to start, you will need to read stuff on the Installation manuals and other BI blogs. But as a starter page after the installation you will get the welcome page: file:///home/oracle/OracleBI/index_bi_ee.html

This page tells it all. You get links to the 3 main components of your OBIEE installation on the right top corner of this page and these are:

• Application Server Control
• Oracle BI Interactive Dashboards
  
• Oracle BI Publisher

Theoretically all 3 should work when you click them. In my case I found only the 1st link to be working. After some Google whacking and troubleshooting search I have realised that some services or some '.sh' scripts required didn't automatically run and required services weren't started properly. Tsk, tsk tsk... Remember? This is a linux platform. Probably OBIEE is best run on Windows, then!?

Anyway I found the /home/oracle/OracleBI/setup directory to be full of cool xxxx.sh scripts which you can use to start stuff in OBIEE.

After successfully starting Oracle BI Server, Oracle BI Presentation Services (SAW server) I have finally managed the get all 3 links to work and started exploring the Oracle BI Infrastructure.

In another post I hope to write about my thoughts on OBIEE.

NOTES:

OBIEE Defaut User is : Administrator
OBIEE Default User password is : Administrator


Resources I used for the OBIEE installation.

• http://www.biblogs.com/?p=5093
• http://www.oracle.com/technology/documentation/bi_ee.html

Data Analysis: A Quick & Cost Effective Approach by Oracle Cornerstone Briefings

I have attended yesterday the Data Analysis: A Quick & Cost Effective Approach by Oracle Cornerstone Briefings for customers in TVP, Reading, Oracle UK Headquarters.



I must say working in UK for the last decade I have never visited this part of the country which is considered the "Silicon Valley" of UK. I was impressed by the buildings Oracle's training rooms and facilities. Woow!


The topic was about Data Analysis and the OBIEE Server. The presentation was in two parts and was Given by Aidan and Stuart of Oracle UK.

Part one focused in the current situation and challenges in Data Management today. The reference architecture was a 3 component chart described as "process data" , "manage data" and then "access data".

The highlights of the first part can be summarized as:

• The data management process is still difficult, there are still a questions about the "single version of truth" in data management still people chop and spread data all over the place and analyse it in tools such as Excel and etc.
• There is still complexities even the best of the tools out there can not address.
• The sources, where we gather data from are still evolving, and new sources appear from which we are not able to harvest the data quickly and efficiently.

The second part of the briefing was, if you haven't guessed already, about how Oracle with its Business Intelligence Server Enterprise Edition (OBIEE), ex Siebel a company Oracle has purchased recently, is able to meet these challenges and complexities on data management.

It seems to me like OBIEE, sweet name for a product, they call it just "obiee", is an application which is "Database Agnostic" that is, it doesn't need to belong or be installed on or work with a particular database. It is a system which is installed on a file system, windows, linux whatever and then it runs all your reporting, report publishing, dashboarding and metadata in one place. So it is not a database! It is a kind of application which manages your reporting and dashboarding metadata storage and publication processes. A kind of webserver and appserver together which schedules and runs queries on any database, the extract of reports via email, pdf, Excel, XML, Flash Charts, dashboards you name it, and it is possible kind of tool.

When I was listening and seeing demonstrations about all this I was thinking how different is OBIEE from Oracle APEX. Then I asked them.

Their answer was that they were not similar at all. APEX is more like a rapid application development environment on the web and is all stored in the database in XMLDB. Whereas although OBIEE can be stored in XMLDB like Apex, Obiee is not an application development tool but a data analysis environment. They are different. Are they?

Oracle Data Guard and High Availability Physical Standby Database configuration using Oracle 10g R2 and Ubuntu 7.10

In this post I would like to share my experience of setting up an Oracle Data Guard (DG) Fast-Start Failover High Availability (HA) environment with a Physical Standby Database and Data Guard Broker enabled, using 2 PCs with Ubuntu 7.10 Desktop and Oracle 10g R2.


Data Guard and High Availability is not much of DBA work really, there is no tuning, data modelling or core DBA skills like SQL involved.

This is all about the chit-chat between two servers, lots of networking magic-do, and one server informing the other when it is down. Is like SysAdmin disguised as a DBA. You can also call it a "poor man's RAC", I suppose.

It took me about 2-3 hours, depending on the size of your database, to set up and successfully implement the following.

Summary of steps

• Configure the primary database


• Create a physical standby database from your primary database using RMAN.


• Oracle Data Guard and Data Broker (DGMGRL) configuration


• Do the failover


• Reinstate the failed primary database as a physical standby database after the failover

My instructions will be as brief and as neat as possible. Here we go:


1. Configure the primary database

1.1 Enable Forced Logging on the primary database

SQL> conn / as sysdba
SQL> alter database force logging

1,2 Create a password file with orapwd on then primary database

$ orapwd file=/usr/local/oracle/product/10.2.0.1/dbs/orapwtest01 password=kubi entries=2

1.3 Configure standby redo Logs on the primary database, these will be needed when the primary changes role and becomes standby

SQL>ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo01.log' SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo02.log' SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo03.log' SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo04.log' SIZE 50M;

1.4 Get a better than the default standby_archive_dest location

SQL> alter system set standby_archive_dest='/u01/oradata/test01/standby_archive_dest';

1.5 Create pfile from the existing database spfile, if you have used dbca to create your db you probably have an spfile

SQL> create pfile='/usr/local/oracle/product/10.2.0.1/dbs/inittest01.ora' from spfile;

1.6 Then Edit the pfile and add those Data Guard specific parameters

On the primary database box edit the pfile for the primary database like this, add the following at the end of the file, change your host names and database SID accordingly.

# DG Config PRIMARY ROLE initialization parameters
*.db_unique_name=host_istanbul
*.db_domain='mediterranean'
*.log_archive_config='DG_CONFIG=(host_istanbul,host_london)'
*.log_archive_dest_1='LOCATION=/u01/oradata/test01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=host_istanbul'
*.log_archive_dest_2='SERVICE=TO_HOST_london LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=host_london'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_max_processes=4
*.db_flashback_retention_target=4320
*.undo_retention=3600
#
# DG Config STANDBY ROLE initialization parameters
*.fal_server=host_london
*.fal_client=host_istanbul
*.standby_file_management=auto
#
# Flashback
*.db_recovery_file_dest=/u01/oradata/test01/fra
*.db_recovery_file_dest_size=524288000

1.7 Then startup the primary database instance using the pfile with the new Data Guard parameters

SQL> startup force pfile='/usr/local/oracle/product/10.2.0.1/dbs/inittest01.ora';

1.8 After the instance startup, recreate the spfile to include the new added Data Guard parameters

SQ> create spfile from pfile='/usr/local/oracle/product/10.2.0.1/dbs/inittest01.ora';

1.9 Bounce the primary database so that it starts up using the spfile and the new Data Guard parameters

SQL> shutdown immediate;

SQL> startup mount;

1.10 Put the primary database in archivelog mode

SQL> alter database archivelog;


1.11 Enable flashback on the primary database, flashback will be necessary for fast-start failovers

SQL> alter database flashback on;
SQL> alter database open;

Now you have a database ready to be used as primary database with all Data Guard configuration parameters in place. It is now time to create the physical standby database, lets move on.



2. Create a physical standby database from your primary database using RMAN


2.1 Create a backup copy of the primary database data files with RMAN (Oracle 10g R2) ready to be used for standby duplication.

# !/bin/bash

# Unix controls
#
trap cleanup 1 2 3 15
cleanup()
{
echo "Caught CTRL-C Signal ... exiting script."
exit 1
}


#!/bin/bash
# Oracle Environemt Variables
#
export ORACLE_SID=test01
export ORACLE_BASE=/usr/local/oracle
export ORACLE_HOME=/usr/local/oracle/product/10.2.0.1
export PATH=$PATH:${ORACLE_HOME}/bin
#
rman target=/ <<
 EOF
configure controlfile autobackup on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/test01/standby_backups/autobkpcontrolfile_%F';
run {
change archivelog all crosscheck;
allocate channel rman_back_ch01 type disk;
allocate channel rman_back_ch02 type disk;
backup as compressed backupset incremental level 0 database 
format '/u01/backup/test01/standby_backups/sbybk_inc0_%s_%p' include current controlfile for standby;
sql "alter system archive log current";
backup as compressed backupset archivelog all format '/u01/backup/test01/standby_backups/archlog_%s_%p';
release channel rman_back_ch01;
release channel rman_back_ch02;
}
EOF

2.2 Prepare an Initialization Parameter File for the standby database

oracle@istanbul:~$ scp /usr/local/oracle/product/10.2.0.1/dbs/inittest01.ora
                         oracle@london:/usr/local/oracle/product/10.2.0.1/dbs/

On the standby box after you copy a pfile from the primary database default location edit like this:

# DG Config PRIMARY ROLE initialization parameters
*.db_unique_name=host_london
*.db_domain='mediterranean'
*.log_archive_config='DG_CONFIG=(host_istanbul,host_london)'
*.log_archive_dest_1='LOCATION=/u01/oradata/test01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=host_london'
*.log_archive_dest_2='SERVICE=TO_HOST_istanbul LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=host_istanbul'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_max_processes=4
*.db_flashback_retention_target=4320
*.undo_retention=3600
#
# DG Config STANDBY ROLE initialization parameters
*.fal_server=host_istanbul
*.fal_client=host_london
*.standby_file_management=auto

# Flashback
*.db_recovery_file_dest=/u01/oradata/test01/fra
*.db_recovery_file_dest_size=524288000
#

2.3 Startup the standby database in NOMOUNT state and create it

SQL> startup pfile='/usr/local/oracle/product/10.2.0.1/dbs/inittest01.ora' nomount;

Before you duplicate the database for standby with RMAN you have to configure connectivity between the 2 boxes with tnsnames.ora.

On the Primary System your tnsnames.ora file should look like this:

TO_HOST_london =
( DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = london)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = test01))
)

TO_HOST_istanbul =
( DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = test01))
)

On the Standby System your tnsnames.ora file should look like this:

TO_HOST_istanbul =
( DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = test01))
)

TO_HOST_london =
( DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = london)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = test01))
)

This is so that the boxes can communicate with each other via those service names. Then you can duplicate the primary database on the standby box using RMAN like this

Make sure orapwd is run on london as well

orapwd file=/usr/local/oracle/product/10.2.0.1/dbs/orapwtest01 password=kubi entries=2

Then connect to RMAN on the primary database and start the creation of the standby database via RMAN.

oracle@istanbul$ rman target /
RMAN>
connect auxiliary sys/kubi@to_host_london
run
{
allocate auxiliary channel ch1 type disk;
duplicate target database for standby dorecover nofilenamecheck;
release channel ch1;
}

2.4 Add the standby log files on the standby database

If you watch the backup in the alert.log file RMAN gives the recommendation of adding the standby log files anyway.

ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo03.log' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo02.log' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo01.log' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/oradata/test01/sbyredo04.log' SIZE 52428800;

2.5 Create SPFILE for the standby database

SQL> create spfile from pfile='/usr/local/oracle/product/10.2.0.1/dbs/inittest01.ora';

2.6 Bounce the standby database to pickup the changes and start with an spfile.

SQL> startup force mount;

2.7 Put the standby database in ARCHIVELOG mode.

SQL> alter database archivelog;

2.8 Put the standby database inf FLASHBACK mode

SQL> alter database flashback on;

2.9 Put the standby database in constant recovery mode receiving archived logs from the primary database

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

3. Oracle Data Guard and Data Broker (DGMGRL) configuration

3.1. On both Primary and Standby Database set the parameter DG_BROKER_START to TRUE

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

3.2. Add global_db_name parameters and other parameters to the listener.ora file on both primary and standby as follows, on Primary Database your listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0.1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = test01)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0.1)
)
(SID_DESC =
(SID_NAME=pdb)
(GLOBAL_DBNAME=host_istanbul_DGMGRL.mediterranean)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0.1)
)
)
LISTENER = (DESCRIPTION =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=istanbul)
(PORT=1521))))
SQLNET.EXPIRE_TIME=2

on Standby Database your listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0.1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = test01)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0.1)
)
(SID_DESC =
(SID_NAME=pdb)
(GLOBAL_DBNAME=host_london_DGMGRL.mediterranean)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0.1)
)
)
LISTENER = (DESCRIPTION =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=london)
(PORT=1521))))
SQLNET.EXPIRE_TIME=2

3.3 Create the DGMGRL broker Configuration

$dgmgrl
DGMGRL> connect sys/kubi@to_host_istanbul;

DGMGRL> create configuration mediterranean_dg as primary database is host_istanbul connect identifier is to_host_istanbul;

DGMGRL> add database host_london as connect identifier is to_host_london maintained as physical;

DGMGRL> enable configuration;

3.4. Enabling Fast-Start Failover and the Observer

DGMGRL> EDIT DATABASE 'host_istanbul' SET PROPERTY 'LogXptMode'='SYNC';
DGMGRL> EDIT DATABASE 'host_london' SET PROPERTY 'LogXptMode'='SYNC';

3.5 Specify the FastStartFailoverTarget property

DGMGRL> EDIT DATABASE 'host_istanbul' SET PROPERTY FastStartFailoverTarget='host_london';
DGMGRL> EDIT DATABASE 'host_london' SET PROPERTY FastStartFailoverTarget='host_istanbul';

3.6. Upgrade the protection mode to MAXAVAILABILITY, if necessary.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

3.7. Enable fast start failover

DGMGRL> ENABLE FAST_START FAILOVER;

3.8. Start the observer.

DGMGRL> CONNECT sys/kubi@to_host_istanbul;
DGMGRL> START OBSERVER;
Observer started

The above command will just hang, will not return you back to the prompt, this is how the observer is started, it is normal. Start anothter dgmgrl prompt for the rest of the operations.

3.8 Check the configuration so far

DGMGRL> connect sys/kubi@to_host_istanbul
Connected.
DGMGRL> show configuration;

Configuration
Name: mediterranean_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
host_istanbul - Primary database
host_london - Physical standby database
- Fast-Start Failover target

Current status for "mediterranean_dg":
SUCCESS

Also see the verbose output where you can see how long it will take for a box to failover to the other.

DGMGRL> SHOW CONFIGURATION VERBOSE;

Configuration
Name: mediterranean_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
host_istanbul - Primary database
host_london - Physical standby database
- Fast-Start Failover target

Fast-Start Failover
Threshold: 30 seconds
Observer: istanbul

Current status for "mediterranean_dg":
SUCCESS

4. Do the failover

Test by killing PMON of database instance test01 on the primary database box istanbul, this automatically will trigger a failover within 30 seconds to the standby database as configured. You can watch this exciting event happening by looking at the alert_tst01.log on both boxes simultaneously.Once you crashed test01 on host_istanbul, then on host_london go to dgmgrl and check out what it says.

oracle@london$ dgmgrl

DGMGRL> connnect sys/kubi@to_host_london

DGMGRL> show configuration;

Configuration
Name: mediterranean_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
host_istanbul - Physical standby database (disabled)
- Fast-Start Failover target
host_london - Primary database

Current status for "mediterranean_dg":
Warning: ORA-16608: one or more databases have warnings

That is, host_istanbul database is down!

Be careful, the database is down and NOT the box. As I am running observer on host_istanbul as well, if you unplug the box, probably nothing nice will happen as the observer will be incapable of detecting anything. In a real life situation I suppose the observer runs on a 3rd piece of hardware. I haven't tested the 'unplugging' of the box, I don't know what it will do. I just 'kill -9' the PMON backgroud process for the primary database instance test01.

Anyway, your primary database now is host_london. You have failed over to it successfully in 30 seconds.

If you followed the configuration steps above you will be able to REINSTATE a disabled standby database as you have flashback enabled and put it back in the High Availability environment after you mount it. This is what we will do next.



5. Reinstate the failed primary database as a physical standby database after the failover

Fast-Start failover is a very good configuration for High Availability (HA) as it requires no intervention from the DBA, almost, you wish!

Say the primary database fails, say at 04:00 am, when you are sleeping, you get no phonecall from your boss, and the standby database becomes primary. Oleey! business continues. The next day when you realise what happened, you can with Data Guard Observer and using flash back logs reinstate the failed primary to be a standby to the new primary.

Basically Data Guard 'rolls forward' the SCN of the old Primary to match that of the now new primary (ex Standby). That is what Flashback Database does and it is all about. Moving the SCN to catch up with the primary.

In all situations, you will have to manually mount the failed ex-Primary database, probably the next day, if it has no media failure and can be restarted without any problems.

Oracle Data Guard Broker can then reinstate the failed primary database as the new standby database. Changing of roles. But, before you reinstate the failed primary database host_istanbul, first see the state of both databases by logging in to the DGMGRL from the new primary database host_london.


5.1 Login to DGMGML from the new primary database host and checkout your configuration

oracle@london:~$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/kubi@to_host_london;
DGMGRL> show configuration verbose;

Configuration
Name: mediterranean_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
host_istanbul - Physical standby database (disabled)
- Fast-Start Failover target
host_london - Primary database

Fast-Start Failover
Threshold: 30 seconds
Observer: istanbul

Current status for "mediterranean_dg":
Warning: ORA-16608: one or more databases have warnings

5.2 After this you are sure you have failed over and the new primary database is working properly go to the failed ex-primary database box and mount the instance test01.

oracle@istanbul:~$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 24 23:35:58 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 167776020 bytes
Database Buffers 436207616 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL>

5.3 Go back to DGMGRL on new primary database host_london and run the reinstate database command

DGMGRL> reinstate database 'host_istanbul';

You have to give it some time, the configuration will not immediately show the reinstated status, but after a few minutes you will get the following.

DGMGRL> show configuration;

Configuration
Name: mediterranean_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
host_istanbul - Physical standby database
- Fast-Start Failover target
host_london - Primary database

Current status for "mediterranean_dg":
SUCCESS

Congratulations! You just had a successfully failed over and now your primary database is host_london and the standby database is host_istanbul and the obsesrver is watching them.

NOTES

Be patient when you are working with this configuration use a test system, you can use VMWare to have multiple nodes in one box and make sure you have:

• password files with the same password on both machines.
• you have tnsnames.ora set up on both machines and you can communicate between both machines via service names
  
• you have flashback enabled on both databases
  
• you have configured a db_domain and is the same in both init.ora files

References:

1. Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-01 link: http://youngcow.net/doc/oracle10g/server.102/b14239/rcmbackp.htm
   



2. Using Data Guard to do fail-over and other cool features here:
   http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#i1005573