How to convert snapshot standby to primary

In our test environment we are using flashback technology. This technology afford to us to keep our test servers in the actual state regarding to production server, and the same time give the opportunity for testers make their tests for the new features for our system. We can roll back all changes which has made our testers, and roll forward all new changes which has made on primary server without restore whole database. This is very convenient. Couple days ago my colleague asked me if I can convert snapshot standby to primary without roll back changes which has made on this standby. This is important point, because in a normal way if you want to convert snapshot standby  you have to flashback your database, and roll back all changes. But we can escape this.

Shutdown database. Turn off flashback, and prepare script for new control file.

SQL> shu abort;
SQL> startup mount;
SQL> alter database flashback off;
SQL> alter database backup controlfile to trace noresetlogs;

Figure out where is your file with instructions for the new control file.

SQL> select value from v$diag_info where name ='Default Trace File'

SQL> shu abort;

Now, you need to wipe all information strings which start from star(*). And put this lines after TEMP creation strings.

SQL> startup mount force;
SQL> alter database noarchivelog;
SQL> alter database open;

Now, just run this script.

SQL> @/path/SID/trace/SID_ora_29508.trc

After that your database will become to primary role in noarchivelog mode, and without rolled back your changes.

Advertisements

2 thoughts on “How to convert snapshot standby to primary

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s