cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-00205: error in identifying control file

former_member182034
Active Contributor
0 Kudos

Dear all,

I copied the SAP Datafile 1,2,3,4 from source system (window server 2003 R2 and oracle 10.0.2.0.4) to Target system (Window server 2008 and 10.0.2.0.5) and took the trace file of source system. I changed the drive letters of Orig,Mirror and SAP Datafiles w.r.t. target system while the first line after edit the trace file is:

CREATE CONTROLFILE SET DATABASE "PRD" RESETLOGS ARCHIVELOG

SQL> startup
ORACLE instance started.

Total System Global Area 5251268608 bytes
Fixed Size                  2084872 bytes
Variable Size            2650804216 bytes
Database Buffers         2583691264 bytes
Redo Buffers               14688256 bytes
ORA-00205: error in identifying control file, check alert log for more info 

fyi...

please tell me. can we make data copy from different OS and oracle patch, if yes then how can i sort out the Error ORA-00205,

Regards,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

I think you are trying to do a DB refresh and have copied the datafiles to target. Ideally you should be generating the controlfile by editing the trace of the controlfile from source. I am assuming that your source DB SID is PRD. So you need to change the first line of controlfile to :

CREATE CONTROLFILE SET DATABASE "<Target SID>" RESETLOGS ARCHIVELOG

Also you should start the database on target in no mount to run the controlfile rebuild script. This will create new controlfiles in the location specified in your initSID.ora of your target system.

Once the controlfile is created, you can then open the target database with resetlogs option.

Hope this helps!

Regards,

Anup

former_member182034
Active Contributor
0 Kudos

dear anup,

when i execute the command for create control file then getting this strange error.

SQL> @E:\PRD.txt
SP2-0734: unknown command beginning "u2229u2557u2510CREATE CO..." - rest of line ignored
SP2-0734: unknown command beginning "MAXLOGFILE..." - rest of line ignored.
SP2-0734: unknown command beginning "MAXLOGMEMB..." - rest of line ignored.
SP2-0734: unknown command beginning "MAXDATAFIL..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "MAXINSTANC..." - rest of line ignored.
SP2-0734: unknown command beginning "MAXLOGHIST..." - rest of line ignored.
SP2-0042: unknown command "LOGFILE" - rest of line ignored.
SP2-0042: unknown command "GROUP 1 (" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "'F:\ORACLE..." - rest of line ignored.
SP2-0734: unknown command beginning "'F:\ORACLE..." - rest of line ignored.
SP2-0734: unknown command beginning ") SIZE 50M..." - rest of line ignored.
SP2-0042: unknown command "GROUP 2 (" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

regards,

Former Member
0 Kudos

Seems something is fuzzy with your file... Can you paste the contents..

Regards,

Anup

former_member188883
Active Contributor
0 Kudos

Hi,

I see lot of unwanted characters in your control.sql file. Ensure that they are removed and your control.sql file is a readable format on the target system.

Regards,

Deepak Kori

former_member182034
Active Contributor
0 Kudos

thx anup nd deep,

dear, i recreate the trace file and made a copy after editing. Control file successfully created and archives successfully applied on target system, i just canceled the recovery and when i tried to open the database then got below error

ORA-01092: ORACLE instance terminated. Disconnection forced on

when i tried to startup sql then got following error.

SQL> startup
ORACLE instance started.

Total System Global Area 5251268608 bytes
Fixed Size                  2084872 bytes
Variable Size            2650804216 bytes
Database Buffers         2583691264 bytes
Redo Buffers               14688256 bytes
Database mounted.
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: 'F:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

while alert log is

ALTER DATABASE   MOUNT
Setting recovery target incarnation to 2
Successful mount of redo thread 1, with mount id 1837327357
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...

and trace file is

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.0 Service Pack 2
CPU                 : 32 - type 8664, 16 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:27452M/32744M, Ph+PgF:80967M/87504M
Instance name: prd

Redo thread mounted by this instance: 0 <none>

Oracle process number: 0

Windows thread id: 6536, image: ORACLE.EXE (SHAD)


Parsing user specified table space list to be ignored
Dynamic strand is set to TRUE
Running with 2 shared and 0 private strand(s). Zero-copy redo is FALSE

Regards,

Former Member
0 Kudos

Hi,

So you have restored the online backup.. Your database is in mount mode now. Can you check the following on target DB?

select distinct status from v$backup;

Regards,

Anup

former_member182034
Active Contributor
0 Kudos

hi anup,

out of mentioned command is

SQL> select distinct status from v$backup;

STATUS
------------------
NOT ACTIVE

SQL> startup mount
ORACLE instance started.

Total System Global Area 5251268608 bytes
Fixed Size                  2084872 bytes
Variable Size            2650804216 bytes
Database Buffers         2583691264 bytes
Redo Buffers               14688256 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

while alert log is :

ALTER DATABASE   MOUNT
Setting recovery target incarnation to 2
Successful mount of redo thread 1, with mount id 1837374969
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
ALTER DATABASE RECOVER  database  
Media Recovery Start
 parallel recovery started with 16 processes
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: F:\ORACLE\PRD\ORIGLOGB\LOG_G12M1.DBF
  Mem# 1: F:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF
Completed: ALTER DATABASE RECOVER  database  
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 16 processes
Started redo scan
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 2, block 2, scn 1196144162
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: F:\ORACLE\PRD\ORIGLOGB\LOG_G12M1.DBF
  Mem# 1: F:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF
Completed redo application
Completed crash recovery at
 Thread 1: logseq 2, block 3, scn 1196164164
 0 data blocks read, 0 data blocks written, 1 redo blocks read
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=33, OS id=7124
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=34, OS id=3840
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: F:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF
  Current log# 3 seq# 3 mem# 1: F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
SMON: enabling cache recovery
Errors in file f:\oracle\prd\saptrace\usertrace\prd_ora_6912.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6912
ORA-1092 signalled during: alter database open...

Regards,

former_member188883
Active Contributor
0 Kudos

Hi,

You need to recover your database before you open it.

SQL> recover database using backup controlfile until cancel;

Supply the redo log files which is needed for recovery.

Post this recovery use the command

SQL>alter database open;

Regards,

Deepak Kori

former_member182034
Active Contributor
0 Kudos

hi Deep,

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP#=F.GROUP# AND G.STATU
S='CURRENT';

MEMBER
--------------------------------------------------------------------------------

F:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF
F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1196164167 generated at 01/27/2012 12:27:11 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC00003_0773666065.001
ORA-00280: change 1196164167 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF
Log applied.
Media recovery complete.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1196164169 generated at 01/27/2012 12:27:11 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC00003_0773666065.001
ORA-00280: change 1196164169 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Regards,

former_member188883
Active Contributor
0 Kudos

Hi,

Since your oracle version is on higher side it resembles a scenario of upgrade. In such a case you need to start your database using the command

SQL> startup upgrade;

Then run the script @catupgrd.sql

Post this you need to run the following commands

SQL> shutdown immediate;

SQL>startup

Regards,

Deepak Kori

former_member182034
Active Contributor
0 Kudos

hi deepkorri,

now i m getting previous issue when I start the database with upgrade

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 5251268608 bytes
Fixed Size                  2084872 bytes
Variable Size            2650804216 bytes
Database Buffers         2583691264 bytes
Redo Buffers               14688256 bytes
Database mounted.
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: 'F:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

Regard,

former_member188883
Active Contributor
0 Kudos

Hi,

It seems your recovery is not complete. you need to recover with help from offline redo logs which you might need to copy from your soruce system.

Also check alert_SID.log for more details about the error.

Regards,

Deepak Kori

former_member182034
Active Contributor
0 Kudos

Dear Deepokkori,

i remembered that i did not oracle upgrade when i install Patch set 10.2.0.5.

now first going to recover database and then will upgrade the oracle with mentioned upgrade and utlrp scripts.

I will update you.

for the meanwhile, please tell me about my starting query of this post mean, i did copy the data from Source(window server 2003, 10.2.0.4) to Target(window 2008, 10.2.0.5). it is ok and we can do it or not and which issues can i face after this?

Waiting your reply.

Regards,

former_member182034
Active Contributor
0 Kudos

hi deepkorri,

bundle of thx for your expert guidance,

dear, now sql is successfully startup buttttt when i try to start sap form sapmmc then getting following error.


trc file: "dev_w0", trc level: 1, release: "700"
---------------------------------------------------
*
*  ACTIVE TRACE LEVEL           1
*  ACTIVE TRACE COMPONENTS      all, MJ
*
M sysno      00
M sid        PRD
M systemid   562 (PC with Windows NT)
M relno      7000
M patchlevel 0
M patchno    236
M intno      20050900
M make:      multithreaded, Unicode, 64 bit, optimized
M pid        6004
M 
M  
M Fri Jan 27 16:05:13 2012
M  kernel runs with dp version 243000(ext=110000) (@(#) DPLIB-INT-VERSION-243000-UC)
M  length of sys_adm_ext is 576 bytes
M  ***LOG Q0Q=> tskh_init, WPStart (Workproc 0 6004) [dpxxdisp.c   1349]

Fri Jan 27 16:05:14 2012
C  Oracle Client Version: '10.2.0.2.0'
C  application info callback registered
OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:
C     ORA-01017: invalid username/password; logon denied 
C  *** ERROR => CONNECT failed with sql error '1017'
 [dbsloci.c    12273]
C  Try to connect with default password
***LOG BY2=> sql error 1017   performing CON [dbsh#3 @ 1208] [dbsh    1208 ]
B  ***LOG BY0=> ORA-01017: invalid username/password; logon denied [dbsh#3 @ 1208] [dbsh    1208 ]
B  ***LOG BY2=> sql error 1017   performing CON [dblink#8 @ 433] [dblink  0433 ]
B  ***LOG BY0=> ORA-01017: invalid username/password; logon denied [dblink#8 @ 433] [dblink  0433 ]
M  ***LOG R19=> ThInit, db_connect ( DB-Connect 000256) [thxxhead.c   1502]
M  in_ThErrHandle: 1
M  *** ERROR => ThInit: db_connect (step 1, th_errno 13, action 3, level 1) [thxxhead.c   10631]

please tell me solution of ORA-01017

how can i update this table OPS$ table?

Regards,

Answers (4)

Answers (4)

Former Member
0 Kudos

hi , u create a new control file using create controlfile or check both alert log files and pfile also , see the chage u have done to database are in effort with the pfile . Best solution is to create a new control file and then create pfile from spfile and see the pfile and details of the control file in pfile. You also make a trace file of control and check the control file also .u can create control file at any state (nomount ,mount and open state)

former_member204746
Active Contributor
0 Kudos

Hi,

Your Oracle instance is crashing because the DB binaries do not match the DB instance.

downgrade your Oracle binaries to 10.2.0.4 as in your original system, then try to startup your instance and recover it. it should now work.

when this is completes, upgrade it to 10.2.0.5.

this will make your life much simpler.

former_member182034
Active Contributor
0 Kudos

hi Deepkorri and Eric,

the oracle version on Source and Target System:

Source System

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Target System

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

I am getting ORA-01017: invalid username/password; logon denied when work process is being to start, Please help me to resolve this issue.

while

Eric is saying about dowanload Oracle binaries to 10.2.0.4 from source system.

please tell me how can i download Oracle binaries from source system and upload on Target system.

Regards,

Former Member
0 Kudos

Hi,

ORA-01017: invalid username/password; logon denied

Is "R3trans -d" working fine ?

I assume, your OPS$ mechanism is correct. If not, refer the below note.

[Note 400241 - Problems with ops$ or sapr3 connect to Oracle|https://service.sap.com/sap/support/notes/400241]

You need to reset the password of your schema user.

Use BRCONNECT to do so...

brconnect -u system/<password> -f chpass -o <schema_user> -p <password>
or
brconnect -u / -f chpass -o <schema_user> -p <password>

You can also try default password "pass" (without quote) for your schema user. (this will ignore OPS$ mechanism).

Regards.

Rajesh Narkhede

former_member182034
Active Contributor
0 Kudos

hi Rajesh,

the status of R3trans is

C:\Users\prdadm.PRDSAPX5>R3trans -d
This is R3trans version 6.14 (release 700 - 09.04.10 - 11:26:00).
unicode enabled version
2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_o
ra_tnsname = 'PRD'"
R3trans finished (0012).

SQL> ALTER USER OPS$PRDADM IDENTIFIED BY pass;
SQL> alter user SAPSR3 account unlock;
SQL> alter user OPS$PRDADM account unlock;
User altered.
SQL> commit;
Commit complete.
SQL> select username,account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ ------------------------
OPS$PRDSAP\PRDADM              OPEN
OPS$PRDSAP\SAPSERVICEPRD       OPEN
SYS                            OPEN
SYSTEM                         OPEN
OPS$PRDADM                     OPEN
SAPSR3                         OPEN
OUTLN                          LOCKED
TSMSYS                         EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
DBSNMP                         EXPIRED & LOCKED

11 rows selected.


Version of 'F:\usr\sap\PRD\DVEBMGS00\exe\dboraslib.dll' is "700.08", patchlevel (0.244)
B  New connection 0 created
C  Prepending F:\usr\sap\PRD\DVEBMGS00\exe to Path.
C  Oracle Client Version: '10.2.0.2.0'
C  application info callback registered
C  Client NLS setting (OCINlsGetInfo): connection handle 0 -> 'AMERICAN_AMERICA.UTF8'
C  Logon as OPS$-user to get SAPSR3's password
C  Connecting as /@PRD on connection 0 (nls_hdl 0) ... (dbsl 700 280110)
C  Nls CharacterSet                 NationalCharSet                   EnvHp      ErrHp ErrHpBatch
C    0 UTF8                                                      0000000012F9A8F0 0000000012FA23F0 0000000012FB2788
C  Attaching to DB Server PRD (con_hdl=0,svchp=0000000012FB2648,srvhp=0000000012FB5428)
C  Starting user session: OCISessionBegin(con_hdl=0, usr='/',svchp=0000000012FB2648, srvhp=0000000012FB5428, usrhp=0000000012FA2C08)
C     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:
C     ORA-01017: invalid username/password; logon denied                                                                        
C  *** ERROR => CONNECT failed with sql error '1017'
 [dbsloci.c    11370]
C  Info: 99=DBSL_ERR_DB, oerr=1017, try to connect with default password.
C  Connecting as SAPSR3/<pwd>@PRD on connection 0 (nls_hdl 0) ... (dbsl 700 280110)
C  Nls CharacterSet                 NationalCharSet                   EnvHp      ErrHp ErrHpBatch
C    0 UTF8                                                      0000000012F9A8F0 0000000012FA23F0 0000000012FB2788
C  Starting user session: OCISessionBegin(con_hdl=0, usr=SAPSR3/<pwd>, svchp=0000000012FB2648, srvhp=0000000012FB5428, usrhp=0000000012FA2C08)
C     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:
C     ORA-01017: invalid username/password; logon denied                                                                        
C  *** ERROR => CONNECT failed with sql error '1017'
 [dbsloci.c    11370]
B  ***LOG BY2=> sql error 1017   performing CON [dbsh#3 @ 1208] [dbsh    1208 ]
B  ***LOG BY0=> ORA-01017: invalid username/password; logon denied [dbsh#3 @ 1208] [dbsh    1208 ]
B  ***LOG BY2=> sql error 1017   performing CON [dblink#8 @ 433] [dblink  0433 ]
B  ***LOG BY0=> ORA-01017: invalid username/password; logon denied [dblink#8 @ 433] [dblink  0433 ]
M  ***LOG R19=> ThInit, db_connect ( DB-Connect 000256) [thxxhead.c   1503]
M  in_ThErrHandle: 1
M  *** ERROR => ThInit: db_connect (step 1, th_errno 13, action 3, level 1) [thxxhead.c   10632]
M

Regards,

nicholas_chang
Active Contributor
0 Kudos

Hi,

Ensure you've performed as suggested by Rajesh:

brconnect -u system/<password> -f chpass -o <schema_user> -p <password>

and executed sapdba_role_sql and sap_conn_role.sql after system refresh. Refer to SAP Note 134592 & 834917 for more info.

Hope it helps,

Nicholas Chang

Former Member
0 Kudos

Hello,

SQL> ALTER USER OPS$PRDADM IDENTIFIED BY pass;

This is wrong... OPS$ users should not have database level passwords.

Correct it as:

SQL> ALTER USER OPS$PRDADM IDENTIFIED EXTERNALLY;

Do the same for other OPS$ users.

Check the same with following sql command

SQL> select username,password,account_status from dba_users;

Regards.

Rajesh Narkhede

former_member182034
Active Contributor
0 Kudos

Dear,

please find output of mentioned commands,

SQL> alter user OPS$PRDADM IDENTIFIED EXTERNALLY;

User altered.

SQL> SELECT USERNAME,PASSWORD,ACCOUNT_STATUS FROM DBA_USERS;

USERNAME                       PASSWORD
------------------------------ ------------------------------
ACCOUNT_STATUS
--------------------------------
OPS$PRDSAP\PRDADM              EXTERNAL
OPEN

OPS$PRDSAP\SAPSERVICEPRD       EXTERNAL
OPEN

SYS                            36C67A2F5C05DCC8
OPEN


USERNAME                       PASSWORD
------------------------------ ------------------------------
ACCOUNT_STATUS
--------------------------------
SYSTEM                         92A2B934ADF85461
OPEN

OPS$PRDADM                     EXTERNAL
OPEN

SAPSR3                         A53A56F36703DF44
OPEN


USERNAME                       PASSWORD
------------------------------ ------------------------------
ACCOUNT_STATUS
--------------------------------
OUTLN                          4A3BA55E08595C81
LOCKED

TSMSYS                         3DF26A8B17D0F29F
EXPIRED & LOCKED

DIP                            CE4A36B8E06CA59C
EXPIRED & LOCKED


USERNAME                       PASSWORD
------------------------------ ------------------------------
ACCOUNT_STATUS
--------------------------------
ORACLE_OCM                     6D17CF1EB1611F94
EXPIRED & LOCKED

DBSNMP                         E066D214D5421CCC
EXPIRED & LOCKED


11 rows selected.

one more then which i want to tell u that is , the name of source and target system are:

Source System: PRDSAP

Target System: PRDSAPX5

I am getting issue on Target system while above source user(OPS$PRDSAP\PRDADM) are exist on target system(PRDSAPX5)

rEGARDS,

Former Member
0 Kudos

Hi,

The correct OPS$ user in target system should look like as:

OPS$PRDSAPX5\PRDADM

OPS$PRDSAPX5\SAPSERVICEPRD

I again suggest you to refer the Note 400241 and section ORA-01017: invalid username/password; logon denied

Create the OPS$ user as mentioned in the note.

For example:

CREATE USER "OPS$PRDSAPX5\PRDADM" DEFAULT TABLESPACE <tablespace_name>  TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

Same for OPS$PRDSAPX5\SAPSERVICEPRD also.

One thing I noticed that you marked the thread as "Answered". Please open another thread if the issue is different...

Regards.

Rajesh Narkhede

former_member182034
Active Contributor
0 Kudos

hi Rajesh,

last question of this post.

SQL> drop user OPS$PRDADM;

User dropped.

OPS$PRDSAP\PRDADM
OPS$PRDSAP\SAPSERVICEPRD

how can i delete above users because I am getting issue when i try to delete with above systax.

Regards,

Former Member
0 Kudos

Hi,

Use this command to drop users...

SQL> drop user "OPS$PRDSAP\PRDADM";
SQL> drop user "OPS$PRDSAP\SAPSERVICEPRD";

Regards.

Rajesh Narkhede

Former Member
0 Kudos

hi , u create a new control file using create controlfile or check both alert log files and pfile also , see the chage u have done to database are in effort with the pfile . Best solution is to create a new control file and then create pfile from spfile and see the pfile and details of the control file in pfile. You also make a trace file of control and check the control file also .u can create control file at any state (mount and open state)

former_member188883
Active Contributor
0 Kudos

Hi Majamil,

Check what is the path of control files mentioned in init<SID>.ora.

ensure that you have control files present in those locations. If not copy it from one of the locations where you have updated controlfile to rest other locations. Later start your database again.

Note: In case you are making a copy of control file from one location to remaining locations, ensure that ORacle database is down.

Regards,

Deepak Kori

former_member182034
Active Contributor
0 Kudos

hi Dear,

Check what is the path of control files mentioned in init<SID>.ora.

path exist w.r.t controlfile location.

ensure that you have control files present in those locations. If not copy it from one of the locations 
where you have updated controlfile to rest other locations. Later start your database again

Dear, I just cut and past the control file from their actual location on target and trying to create new controlfile with the help of following file.

CREATE CONTROLFILE SET DATABASE "PRD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 11469
LOGFILE
  GROUP 1 (
    'F:\ORACLE\PRD\ORIGLOGA\LOG_G11M1.DBF',
    'F:\ORACLE\PRD\MIRRLOGA\LOG_G11M2.DBF'
  ) SIZE 50M,
  GROUP 2 (
    'F:\ORACLE\PRD\ORIGLOGB\LOG_G12M1.DBF',
    'F:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF'
  ) SIZE 50M,
  GROUP 3 (
    'F:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF',
    'F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF'
  ) SIZE 50M,
  GROUP 4 (
    'F:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF',
    'F:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'F:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',
  'F:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1',
  'F:\ORACLE\PRD\SAPDATA1\SYSAUX_1\SYSAUX.DATA1',
  'F:\ORACLE\PRD\SAPDATA2\SR3_1\SR3.DATA1',
  'F:\ORACLE\PRD\SAPDATA2\SR3_2\SR3.DATA2',
  'F:\ORACLE\PRD\SAPDATA2\SR3_3\SR3.DATA3',
  'F:\ORACLE\PRD\SAPDATA2\SR3_4\SR3.DATA4',
  'F:\ORACLE\PRD\SAPDATA2\SR3_5\SR3.DATA5',
  'F:\ORACLE\PRD\SAPDATA2\SR3_6\SR3.DATA6',
  'F:\ORACLE\PRD\SAPDATA2\SR3_7\SR3.DATA7',
  'F:\ORACLE\PRD\SAPDATA2\SR3_8\SR3.DATA8',
  'F:\ORACLE\PRD\SAPDATA2\SR3_9\SR3.DATA9',
  'F:\ORACLE\PRD\SAPDATA2\SR3_10\SR3.DATA10',
  'F:\ORACLE\PRD\SAPDATA2\SR3_11\SR3.DATA11',
  'F:\ORACLE\PRD\SAPDATA2\SR3_12\SR3.DATA12',
  'F:\ORACLE\PRD\SAPDATA2\SR3_13\SR3.DATA13',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_1\SR3700.DATA1',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_2\SR3700.DATA2',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_3\SR3700.DATA3',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_4\SR3700.DATA4',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_5\SR3700.DATA5',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_6\SR3700.DATA6',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_7\SR3700.DATA7',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_8\SR3700.DATA8',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_9\SR3700.DATA9',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_10\SR3700.DATA10',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_11\SR3700.DATA11',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_12\SR3700.DATA12',
  'F:\ORACLE\PRD\SAPDATA3\SR3700_13\SR3700.DATA13',
  'F:\ORACLE\PRD\SAPDATA4\SR3USR_1\SR3USR.DATA1'
CHARACTER SET UTF8
;

i just change the drive letter because all above files are exist in G drive of source system while in F drive on Target system.

and when i am trying to start the sql then getting

ORA-00205: error in identifying control file, check alert log for more info

while the alert log is

Adjusting the default value of parameter parallel_max_servers
from 640 to 65 due to the value of parameter processes (80)
Fri Jan 27 10:21:30 2012
Starting ORACLE instance (normal)
Fri Jan 27 10:21:30 2012
Specified value of sga_max_size is too small, bumping to 5251268608
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.5.0.
 
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'F:\ORACLE\PRD\ORIGLOGA\CNTRL\CNTRLPRD.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

ORA-205 signalled during: ALTER DATABASE   MOUNT...

the database was down when i copy only datafile from source server.

Regards,

Edited by: majamil on Jan 27, 2012 10:29 AM

former_member204746
Active Contributor
0 Kudos

read the error message, it clearly states:

check alert log for more info

so, do that, tit will tell you what is going wrong! then, you will be able to find out what is missing or mis-configured.