Standby and Primary Databases on the same host + ORA-1033

As part of 10g upgrade to all our production 9i databases, Next week we planned to upgrade one of the business critical 9i database to 10g. This database is very business critical and 24x7. It hard to get longer downtime. We are lucky enough to get a hour downtime to upgrade this database. The database is size is around 100gb

We thought of minimizing the upgrade process to time by looking deeply into the following options as well thinking to rollback the upgrade in case of application not acceptable of 10g:

Method1:

Installing 10g on a new machine, and import the data. Using streams, replicate the 9i database to 10g and vice versa. This method gives us the rolling back option in case end-users find strange behavior with 9i.

We need to drop this method as we need to initially use export and import whereas we preferred the RMAN backup options. Oracle supports says, this is available with 10g database.

Method2:

Transportable Tablespaces. Installing 10g software on a new machine, export production with transportable options, copy datafiles to new machine, import the transportable tablespace.

This method involves, a database creation and schema creations.

Method3:

Create a standby database, break the standby, cover to primary and do the manual upgrade.

We decided to go with method 3 as its looks more controlled method and easy method.

This database already has a standby database in place, on different machine, DRC location.

However, we thought of creating another standby of this database on the primary database machine and do the upgrade.

In this process, we have initially, stopped the previously existing standby database, and created a new standby database on the same machine where primary database exists and make standby sync with the primary. We wanted to observe for a week and do the upgrade during the morning hours of week-end during non-peak hours.

Once the process finishes, we went home. After sometime, we have got a call from the operations team that they are not able to run the batches due to ORA-1033 error. We have connected through the VPN and investigated about the problem.

When we shutdown the standby, the ORA-1033 error was not occurring, and when standby mounts or keep in managed recovery mode, the ORA-1033 error occurs whenever trying to connect to the primary database.

After lot of investigations, we found out that we have missed to set the SERVICE_NAME parameter in the standby initialization parameter.

Upon setting SERVICE_NAME parameter in the standby, the ORA-1033 error disappears.

We couldn’t believe the this parameter can cause the error.

However, now everything is perfect and just ready for the next week upgrade.

Happy Reading,

Jaffar

Submit an article/test case to Metalink and you can Win Up to $5,000 in prizes!

ere is your opportunity to showcase your expertise and knowledge of Oracle products and technologies with your peers in the industry। Contribute to Oracle's Customer Knowledge Exchange, the online customer channel that enables you to showcase your expertise, build your own customer network, and share news and information with your peers starting April 1 through May 15, 2007 and become eligible to win a $5,000.00 Oracle University Scholarship and much more.

First to Contribute, First to Win - Gift certificates of $100 to the first 20 contributors to have their article published in Oracle MetaLink – Customer Knowledge Exchange।

Best of Luck,

Jaffar

Fully Automated TSPITR

Couple of days ago, I have tested Automatic TSPITR feature which is introduced with version 10g. Maybe, many experts might have already discussed about this and might have written articles about this feature। However, I just thought of sharing my experience which could help to the DBA who is not yet aware of this feature or never tested this feature.

Starting with version 10g, Tablespace Point-in-Time Recovery(TSPITR) can also be done automatically, which is referred as fully Automated TSPITR.

This process requires the following two manual steps:

1. Specify the auxiliary location for RMAN to restore the auxiliary datafile/datasets
2. Configure the required channels on Target Instance.

Manual TSPITR is required the following procedure:

The basic procedure for performing user-managed TSPITR is as follows:

Take the tablespaces requiring TSPITR offline.

• Plan the setup of the auxiliary database.
• Create the auxiliary database and recover it to the desired point in time.
• Drop the tablespaces requiring TSPITR from the primary database.
• Use the transportable tablespace*** feature to transport the set of tablespaces from the auxiliary database to the primary database.

*** Probably in versions 8i and 9i, because transportable tablespace were first introduced with 8i (v. 8.1.5). Version which doesn’t have TTS, can do export and import of desired table/s.

To perform full automated TSPITR, you just need to add AUXILIARY DESTINATION in the RMAN along with recover tablespace.

Assuming that a table has been dropped from tablespace DATATS:

Example:

run
{ RECOVER TABLESPACE datats
UNTIL logseq thread 1 -- thread 1 indicates single instance
AUXILIARY DESTINATION '/u10/temp'; --Auxiliary Location
}

When the above script runs, RMAN then carries out the following steps:

If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
Takes the tablespaces to be recovered offline in the target database.
Restores a backup control file from a point in time before the target time to the auxiliary instance.
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance.
Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE).
Recovers the restored datafiles in the auxiliary instance to the specified time.
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database.
Shuts down the auxiliary instance.
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.

What I like about this feature is that, given the enough space to the auxiliary location, RMAN will do everything which required for TSPITR and once the process is done it will automatically obliterate the auxiliary instance, including files which restored during this process.

Its really worth to have a test about this feature.

Please note that I have not discussed about the prerequisites of performing TSPITR.

References:

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit003.htm#sthref778
Metalink Note : Getting Started with Transportable Tablespaces - Note:100693.1

Happy reading,
Jaffar

ALTER DATABASE BEGIN BACKUP

Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.

I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.

Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:

ALTER DATABASE BEGIN BACKUP;

ALTER DATABASE END BACKUP; -- to exit from the backup mode.

In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.

-- The following has done with Oracle 10gR1

SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

-- All the datafiles are in backup mode now

SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

-- All the datafiles are out of backup mode now.

Happy reading,

One more new Oracle Informative Site

Lately I have learnt hat my boss is maintaining a Oracle Informative Site. Upon visiting to his Oracle site, I feel good as he put a lot of stuff about Oracle (trying to put more), and also what we have done lately at our organization to resolve some of production issues, and couple of interesting articles for downloads.

I must appreciate for his work as he took some quality of time from his busiest schedule to put good stuff at his site and made it feel good site.
Although, the site is not fully done whith he wanted to have, still good to visit. He also promised me that he gonna put more stuff about RAC which we are doing on Stretched Clusters.

I am also going to contribute and participate for few activities on his site and we will put good articles written by us which could be useful in day-to-day business of a DBA and could help to resolve some of issues of production databases.

Following is the link for his site:

http://www.pinnacleoracle.com

Happy reading,

Jaffar