Home » RDBMS Server » Backup & Recovery » Converting Standby database to normal database
Converting Standby database to normal database [message #265031] Wed, 05 September 2007 05:15 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi,
We got a standby database, which was previously created for disaster recovery purposes.Now we dont want to use it as a standby database any more instead we want to use it as a normal production database.But when we try to open it as such, it says that the control files are written for a standby database.Is it possible to change the control files?If so how?and if not, how this could be done?
Re: Converting Standby database to normal database [message #265049 is a reply to message #265031] Wed, 05 September 2007 05:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

In mount state ,
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

What error comes. Specify detail with error number.
Re: Converting Standby database to normal database [message #265163 is a reply to message #265049] Wed, 05 September 2007 10:11 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Whats Oracle version.


there can be possibility of SWITCH OVER..
Re: Converting Standby database to normal database [message #265443 is a reply to message #265163] Thu, 06 September 2007 05:56 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I have no clue about the DBA tasks.Anyhow I had to do this as the DBA is away now unfortunately.I have no other help.
I have got a few batch files(.bat) and sql files which were already written for this but these are failing now.
I guess I need to connect as sysdba to do this?
connect / as sysdba fails now - It says insufficient privileges.What should I do to get connected as sysdba?

I am trying to solve issues one by one now so any help is appreciated.
To DreamzZ - Oracle version -Release 9.2.0.1.0
I dont know what is switch over -Pls explain?

[Updated on: Thu, 06 September 2007 06:00]

Report message to a moderator

Re: Converting Standby database to normal database [message #265444 is a reply to message #265031] Thu, 06 September 2007 05:58 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

At first search in this forum whenever you raise one issue. If you don't get then post new thread.
Re: Converting Standby database to normal database [message #265557 is a reply to message #265444] Thu, 06 September 2007 11:33 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
To DreamzZ - Oracle version -Release 9.2.0.1.0
I dont know what is switch over -Pls explain?


http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_9iSwitchoveFailoverBestPractices.pdf

Quote:
I have no clue about the DBA tasks.Anyhow I had to do this as the DBA is away now unfortunately.I have no other help.
I have got a few batch files(.bat) and sql files which were already written for this but these are failing now.
I guess I need to connect as sysdba to do this?
connect / as sysdba fails now - It says insufficient privileges.What should I do to get connected as sysdba?


post the sqlplus screen
Re: Converting Standby database to normal database [message #265629 is a reply to message #265557] Thu, 06 September 2007 14:47 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello aviana,

As a new user in this forum... you should read this forum guidelines and try to use/apply its contents/directions, even you being in a hurry because of your problem. I would say... for being in a hurry, you should read and follow the forum guidelines to get your questions answered as soon as possible.

Regarding your last doubt/question:
1) You are trying to connect locally on the server or remote?
2) Server... OS? Windows?
3) Oracle version: 9.2.0.1
4) Considering you are on Windows server... which is the current user? Does this current user belong to "ora_dba" group?
5) How about your environment variables? Specially ORACLE_SID...?

If you are a user member of "ora_dba" group... probably you will not need the password. Just:
C:> set o
ORACLE_SID=XYZ

C:> sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Qui Set 6 16:44:43 2007

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

SQL> conn sys as sysdba
Informe a senha:                 (just hit <Enter>)
Conectado.
SQL>

Regards,

mson77

[Updated on: Thu, 06 September 2007 14:49]

Report message to a moderator

Re: Converting Standby database to normal database [message #267497 is a reply to message #265031] Thu, 13 September 2007 09:11 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi,
I am able to connect now as sysdba and completed other steps before opening databse as a production one - Created new hot backup,also copied new archive files
Now i am running the script below
connect / as sysdba
shutdown immediate;
startup nomount;
alter database mount standby database;
Alter database activate standby database skip; 
shutdown immediate; 
startup; 



I am getting the error below:-


G:\Admin>g:\oracle\ora92\bin\sqlplus /"nolog" 

@Open_CAD_DB.sql

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Sep 13 14:06:59 2

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserve

Connected.
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area  839983304 bytes
Fixed Size                   454856 bytes
Variable Size             629145600 bytes
Database Buffers          209715200 bytes
Redo Buffers                 667648 bytes

Database altered.

Alter database activate standby database skip
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'G:\ORACLE\ORADATA\GEOP\SYSTEM01.DBF'


ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area  839983304 bytes
Fixed Size                   454856 bytes
Variable Size             629145600 bytes
Database Buffers          209715200 bytes
Redo Buffers                 667648 bytes
ORA-01666: controlfile is for a standby database


Pls help- the files in the standby server(data files and control files) are in line with the LIVE ones...I serached the error messages sections but cant figure out why.system01.dbf is as of todays date

[Updated on: Thu, 13 September 2007 09:13]

Report message to a moderator

Re: Converting Standby database to normal database [message #267508 is a reply to message #267497] Thu, 13 September 2007 10:04 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Copy all Archive logs create another STANDBY CONTROL FILE and perform recovery.
Re: Converting Standby database to normal database [message #267524 is a reply to message #265031] Thu, 13 September 2007 10:57 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

1) If redo logs are still being shipped from primary to stand by , then disable the logshipping. Basically disable the log_archive_dest_n that points to the standby dest.

On Standby
1) alter database backup controlfile to trace.
Find the tracefile in udump, use it to create a SQL staement to create a new controlfile with restlogs.

Say the created script is called foo.sql.

2) On standby, shutdown abort;
3)Edit init.ora to change any standby related parms.
startup nomount; ( if pfile is used , change standby parms and start nomount again )
4) @foo.sql;
5) recover database until time 'yyyy-mm-dd hh24:mi:ss' => Give a suitable time.

6) If necessary transfer archived logs from the promary required for recovery and name them appropriately.

7) alter datbase open resetlogs.

Srini


Re: Converting Standby database to normal database [message #268340 is a reply to message #267524] Tue, 18 September 2007 04:36 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Thanks Srini for your reply
But i need a little more clarification on these points as I havent done any DBA duties before.
1)How do i know if redo logs are being shipped from primary to standby?Where to check this?In which file should i disable log_archive_dest_n and how?

Quote:
On Standby
2) alter database backup controlfile to trace.
Find the tracefile in udump, use it to create a SQL staement to create a new controlfile with restlogs.


2)There are 3 tracefiles with today's date - Which should i take and how to create an SQL statement from it?Please give me the script

Quote:
3)Edit init.ora to change any standby related parms.
startup nomount; ( if pfile is used , change standby parms and start nomount again )

how do i know which one is used?

I am trying to resolve this error first..Pls pls help, i am not able to create new control files.Someone pls give the steps
Quote:
SQL> startup mount;
ORACLE instance started.

Total System Global Area 839983304 bytes
Fixed Size 454856 bytes
Variable Size 629145600 bytes
Database Buffers 209715200 bytes
Redo Buffers 667648 bytes
ORA-01666: controlfile is for a standby database


Thanks a lot

[Updated on: Tue, 18 September 2007 05:01]

Report message to a moderator

Re: Converting Standby database to normal database [message #268428 is a reply to message #265031] Tue, 18 September 2007 10:02 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

I guess you will have to do a little bit of reading of the Oracle docos for the relevant topics.

Read up on 1) how to recreate controlfile 2) How to do an incomplete recovery using a backup controlfile.

Srini
Re: Converting Standby database to normal database [message #268877 is a reply to message #268428] Wed, 19 September 2007 23:58 Go to previous messageGo to next message
d.c.b.a
Messages: 44
Registered: March 2005
Location: China
Member

1, You need to do alter system switch logfile on primary, and copy the last log to standby.

2, ALTER DATABASE RECOVER STANDBY DATABASE FINISH

3, alter database commit to switchover to primary;



Good tools make work easy and improve life quality.
-- http://www.dbatools.net
Re: Converting Standby database to normal database [message #269769 is a reply to message #268877] Mon, 24 September 2007 08:18 Go to previous message
aviana
Messages: 101
Registered: July 2007
Senior Member
I have done all
The error below is still not rectified
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  839983304 bytes
Fixed Size                   454856 bytes
Variable Size             629145600 bytes
Database Buffers          209715200 bytes
Redo Buffers                 667648 bytes
ORA-01666: controlfile is for a standby database


I think i am going to give up now..have tried everything several times...maybe i am missing something in between??!!
Previous Topic: TSPITR
Next Topic: Rman not open
Goto Forum:
  


Current Time: Mon Dec 05 10:59:46 CST 2016

Total time taken to generate the page: 0.07233 seconds