Solved

Restoration of Oracle database fails with ORA-01460

Posted on 2007-12-05
9
1,746 Views
Last Modified: 2013-12-18
I am evaluating Backup Exec's ability to backup and restore Oracle 10g databases.  The backups seem to work without error, but when I try to restore from one of them, it bails with an RMAN script failure.  

Now my database is at a point where it will not start -- it says ORA-01610: recovery using the BACKUP CONTROLFILE must be done.

I've attached the RMAN results below.

Has anyone successfully recovered an Oracle database with Backup Exec 11d

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 6 01:30:46 2007
 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 

 Connected.

 ORA-01109: database not open
 
 

Database dismounted.

ORACLE instance shut down.

 ORACLE instance started.
 

Total System Global Area  612368384 bytes

Fixed Size    2056864 bytes

Variable Size  205524320 bytes

Database Buffers  398458880 bytes

Redo Buffers    6328320 bytes

 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options
 

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Dec 6 01:31:01 2007
 

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

 

 # -----------------------------------------------------------------

 # RMAN command section

 # -----------------------------------------------------------------

 

 RUN {

 ALLOCATE CHANNEL ch0

     TYPE 'SBT_TAPE';

 SEND 'BSA_SERVICE_HOST=192.14.14.25,NBBSA_TOTAL_STREAMS=1,NBBSA_JOB_COOKIE={8FDDD645-52BE-4C77-B98D-FC755CF9370A},NBBSA_DB_DEVICE_NAME=Oracle-Win::\\candc-ora1.candc-uk.com\abc';

 

 RESTORE CONTROLFILE FROM 'BE_03j2skbl_1_1';

 

 ALTER DATABASE MOUNT;

 

 RESTORE

     DATABASE;

 

 RELEASE CHANNEL ch0;

 }

 

connected to target database: abc (not mounted)
 

using target database control file instead of recovery catalog

allocated channel: ch0

channel ch0: sid=157 devtype=SBT_TAPE

channel ch0: Symantec/BackupExec/1.1.0
 

sent command to channel: ch0
 

Starting restore at 06-DEC-07
 

channel ch0: restoring control file

channel ch0: restore complete, elapsed time: 00:01:46

output filename=D:\ORACLE\DATABASES\ABC\CONTROL\CONTROL01.CTL

output filename=D:\ORACLE\DATABASES\ABC\CONTROL\CONTROL02.CTL

output filename=D:\ORACLE\DATABASES\ABC\CONTROL\CONTROL03.CTL

Finished restore at 06-DEC-07
 

database mounted
 

Starting restore at 06-DEC-07

Starting implicit crosscheck backup at 06-DEC-07

released channel: ch0

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 12/06/2007 01:32:52

ORA-01460: unimplemented or unreasonable conversion requested
 

Recovery Manager complete.

Open in new window

0
Comment
Question by:jimbobmcgee
  • 4
  • 3
9 Comments
 
LVL 2

Expert Comment

by:hamita
ID: 20418501
1) connect to database, wchih while be restored.
2) execute command : ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
3) Start the oracle server in nomount state and create the control file in target server .
4) After controlfile created recover Database using
recover database using backup controlfile until cancel
5) Once recovery completed open the Databse in RESETLOGS mode.
SQL>alter database open resetlogs;
0
 
LVL 16

Author Comment

by:jimbobmcgee
ID: 20418660
Thanks, hamita;  I must admit I'm a bit of a beginner with Oracle.  I have followed your steps up to 3, which I took to mean:

    3.1 SHUTDOWN IMMEDIATE
    3.2 STARTUP NOMOUNT

but after shutting down, I get ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.  If I run LSNRCTL SERVICES I cannot find my database in the list, so I guess that's the problem, but how do I fix this?  I am using LDAP (over AD) to resolve names and if I TNSPING the database, I get a reply.

I will also need clarification on what you mean by 'create the control file in target server'.

J.
0
 
LVL 2

Expert Comment

by:hamita
ID: 20418766
what is your operating system?
windows or unix, linux???
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Expert Comment

by:hamita
ID: 20418796
1.Check the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties
In this case this had -

oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oraserver)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PROD)))

2. Then check the oupt from lsnrctl status and lsnrctl services, in this case we saw -
lsnrctl status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASERVER)(PORT=1521)))
STATUS of the LISTENER
------------------------
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /u01/app/oracle/OraHome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/OraHome_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORASERVER)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORASERVER)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORASERVER)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "ORABDE.world" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PROD.world" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully
lsnrctl services
----------------
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraserver)(PORT=1521)))
Services Summary...
Service "PROD.world" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "PROD", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:552 refused:0 state:ready
LOCAL SERVER
Service "PROD.world" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: oraserver, pid: 19216>
(ADDRESS=(PROTOCOL=tcp)(HOST=oraserver)(PORT=32803))
The command completed successfully

3. Then check the tnsnames.ora used, in this case we had -
tnsnames.ora
-------------
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORASERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)

SQL> select * from global_name;

4. Last of all check the database GLOBAL_NAME, in this case we had -
GLOBAL_NAME
--------------------------------------------------------------------------------
PROD


So In this case the reason for the emoms.log showing -
ERROR conn.ConnectionService verifyRepositoryEx.418 - Invalid Connection Pool. ERROR = Listener
refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Was that the SERVICE_NAME contained  PROD but the entry for
oracle.sysman.eml.mntr.emdRepConnectDescriptor= in
$ORACLE_HOME/host_sid/sysman/config/emoms.properties contained just PROD for the SERVICE_NAME

The fix would be -Amend the entry oracle.sysman.eml.mntr.emdRepConnectDescriptor in emoms.properties

example -
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oraserver)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PROD)))
0
 
LVL 16

Author Comment

by:jimbobmcgee
ID: 20419247
I am running on Windows, and have found and looked at the emoms.properties file, but I'm not sure how the above applies to me.

The server is intended to house a number of development databases (not schemas), and I currently have two installed.  The one I am using to test backup/recovery is the one I have shutdown.  TNSPING can see both databases, via their LDAP-registered names, but SQLPLUS cannot.  SQLPLUS can see the second database, though.

J.
0
 
LVL 2

Accepted Solution

by:
hamita earned 500 total points
ID: 20419792
shutdown all database services, and disable second db services
open 1st database services and try it
0
 
LVL 16

Author Comment

by:jimbobmcgee
ID: 20419925
Any way to do it without shutting down the second database?
I have got people using the second one and I'm trying not to have to take them down too.

It surely must be possible to shutdown and startup a database without affecting other services...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
This video shows how to recover a database from a user managed backup
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now