Thinking Out Loud

June 27, 2007

Flashback Database and Data Guard

Filed under: oracle — mdinh @ 7:17 pm

How many people out there actually use Flashback database?

My curiosity led me to discover that flashback database works great with data guard broker because there is to need to rebuild the standby database following a failover.

The following is a very simple scenario demonstrating flashback and data guard.

Here is the current configuration:

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Primary database
kite_o10gr2 – Physical standby database

Current status for “dg_o10gr2”:
SUCCESS

Perform graceful switchover to standby database – kite_o10gr2.

DGMGRL> switchover to kite_o10gr2;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “O10GR2” on database “hawk_o10gr2”
Shutting down instance “O10GR2″…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “O10GR2” on database “kite_o10gr2”
Shutting down instance “O10GR2″…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “O10GR2” on database “hawk_o10gr2”
Starting instance “O10GR2″…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “O10GR2” on database “kite_o10gr2”
Starting instance “O10GR2″…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “kite_o10gr2”

Since the operation is a graceful swithover, the standby database does not need to be rebuilt.

The standby database becomes primary and the primary becomes standby.

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Physical standby database
kite_o10gr2 – Primary database

Current status for “dg_o10gr2”:
SUCCESS

Perform failover to standby database – hawk_o10gr2.

Note: this should be executed at the current standby server.

DGMGRL> failover to hawk_o10gr2;
Performing failover NOW, please wait…
Failover succeeded, new primary is “hawk_o10gr2”

The standby database is now disabled.

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Primary database
kite_o10gr2 – Physical standby database (disabled)

Current status for “dg_o10gr2”:
SUCCESS

At this point, the standby database would need to be recreated.

Using flashback database saves the day as the standby database can be enable with a simple “reinstate” command.

DGMGRL> reinstate database kite_o10gr2;
Reinstating database “kite_o10gr2”, please wait…
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database “kite_o10gr2” failed

Startup former primary database in mount mode.

The former primary database – kite_o10gr2 is still online.

[oracle@kite]
O10GR2:/home/oracle$
sys

SQL*Plus: Release 10.2.0.2.0 – Production on Wed Jun 27 18:02:06 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

SYS@O10GR2:PRIMARY> startup force mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264968 bytes
Variable Size 964690616 bytes
Database Buffers 104857600 bytes
Redo Buffers 2928640 bytes
Database mounted.
SYS@O10GR2:PRIMARY>

Now go back and reinstate the standby database.

DGMGRL> reinstate database kite_o10gr2;
Reinstating database “kite_o10gr2”, please wait…
Operation requires shutdown of instance “O10GR2” on database “kite_o10gr2”
Shutting down instance “O10GR2″…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “O10GR2” on database “kite_o10gr2”
Starting instance “O10GR2″…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “kite_o10gr2” …
Reinstatement of database “kite_o10gr2” succeeded

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Primary database
kite_o10gr2 – Physical standby database

Current status for “dg_o10gr2”:
SUCCESS

Whoila!

LOG_ARCHIVE_FORMAT 10g

Filed under: oracle — mdinh @ 4:41 pm

From MetaLink Note:274302.1

LOG_ARCHIVE_START parameter is deprecated in Oracle 10g.
Also, LOG_ARCHIVE_FORMAT must be in the format: %s,%t,%r.

June 19, 2007

Cannot start DB using spfile

Filed under: oracle — mdinh @ 6:26 pm

I am currently running Oracle 9.2.0.8.0 SE on  RHEL4  (Nahant Update 3).

Trying to start the database using $ORACLE_HOME/bin/dbstart resulted in the following error:

Can’t find init file for Database “TIGGER”.

Database “TIGGER” NOT started.

No parameter file exists in the directory $ORACLE_HOME/dbs, i.e. only orapwTIGGER and  spfileTIGGER.ora exist.

Manually, start the database and create pfile from spfile. 

Shutdown the database.

Executing ORACLE_HOME/bin/dbstart now works. 

dbstart requires pfile (initTIGGER.ora) even though spfile (spfileTIGGER.ora) already exists.

Blog at WordPress.com.