| Converting Standby database to normal database [message #265031] |
Wed, 05 September 2007 05:15  |
aviana
Messages: 106
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 #265443 is a reply to message #265163] |
Thu, 06 September 2007 05:56  |
aviana
Messages: 106
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 #265629 is a reply to message #265557] |
Thu, 06 September 2007 14:47 |
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 |
aviana
Messages: 106
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 #267524 is a reply to message #265031] |
Thu, 13 September 2007 10:57 |
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 |
aviana
Messages: 106
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 #269769 is a reply to message #268877] |
Mon, 24 September 2007 08:18 |
aviana
Messages: 106
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??!!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
