Please help with problems setting up an Auxiliary Instance for a RMAN Dupliate Restore

Posted on 2005-05-06
Last Modified: 2012-05-05
I have been given the task of restoring a hot/online backup and my Oracle DBA and ADMIN skills are weak.

I have created the password file using ORAPWD and have it in my <ORACLE_HOME>\database directory.  
I have edited the init.ora files in the default location and set remote_login_passwordfile=EXCLUSIVE.
I have edited the listener.ora and tnsnames.ora to add entries for the new SID.

But I cannot get Oracle Net Connectivity
after starting SQLPLUS with /NOLOG option here's what I get when I try to CONNECT:
SQL> CONNECT sys/password_set_in_ORAPWD@existingSIDdup AS SYSDBA
ORA-12154: TNS:could not resolve service name

Thanks for your help.  
Question by:pt_wood
    LVL 21

    Expert Comment

    Hi pt_wood,
    > estoring a hot/online backup
    Firstable,If You have a hot backup then Your DB is in a good shape!!!
    Secondly,try to locate Your Oracle Home
    Hope It helps
    LVL 21

    Expert Comment

    In Your<ORACLE_HOME>\network\admin directory 2 files at least must be available:
    tnsnames.ora and sqlnet.ora
    LVL 21

    Expert Comment

    Try to edit TNSNAMES.ORA as follows:
    existingSIDdup =
        (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
        (CONNECT_DATA = (SID = existingSIDdup))
    where is Your Db host IP
    LVL 21

    Expert Comment

    Now ,put attention to the second file sqlnet.ora,
    it can have these lines:
    put # before each one of them
    LVL 21

    Expert Comment

    In any case even if You can't connect in remote ,You'll be able to connect from the db host by using:
    set ORACLE_SID=existingSIDdup

    LVL 21

    Expert Comment

    Now ,to the proccess of the Recovery itself:
    You start here:
    LVL 21

    Expert Comment

    But first You must deside which do You want to perform,Complete or Incomplete !!!
    Complete recovery involves using redo data or incremental backups combined with a backup of a database, tablespace, or datafile to update it to the most current point in time - this means that if the reason of Your recovery process is some logical failure - You don't wan't Complete Recovery  there.
    Here the steps for it anyway:
    Mount the database
    Ensure that all datafiles you want to recover are online
    Restore a backup of the whole database or the files you want to recover
    Apply online or archived redo logs, or a combination of the two
    If you are performing complete recovery on a tablespace or datafile, then you must:

    Take the tablespace or datafile to be recovered offline if the database is open
    Restore a backup of the datafiles you want to recover
    Apply online or archived redo logs, or a combination of the two
    LVL 21

    Expert Comment

    2nd One :Incomplete Recovery
    Incomplete recovery uses a backup to produce a noncurrent version of the database. In other words, you do not apply all of the redo records generated after the most recent backup. You usually perform incomplete recovery of the whole database in the following situations:

    Media failure destroys some or all of the online redo logs.
    A user error causes data loss, for example, a user inadvertently drops a table.
    You cannot perform complete recovery because an archived redo log is missing.
    You lose your current control file and must use a backup control file to open the database.
    If You have one of the situations above ,You have no choise but to use Incomplete Recovery
    LVL 21

    Expert Comment

    Please,note that:
    "to perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes".
     The RESETLOGS operation creates a new incarnation of the database--in other words, a database with a new stream of log sequence numbers starting with log sequence 1.
    This also means that You can throw away all Your old backups,they will be not usefull now!!!

    LVL 21

    Expert Comment

    Here  the exerts for You if decided to proceed:
    Media Recovery Options
    Because you are not completely recovering the database to the most current time, you must tell Oracle when to terminate recovery. You can perform the following types of media recovery.

    Type of Recovery Function
    Time-based recovery
     Recovers the data up to a specified point in time.
    Cancel-based recovery
     Recovers until you issue the CANCEL statement (not available when using Recovery Manager).
    Change-based recovery
     Recovers until the specified SCN.
    Log sequence recovery
     Recovers until the specified log sequence number (only available when using Recovery Manager).
    LVL 21

    Accepted Solution

    Now the easiest way to recover a db is to use the RMAN COMMANDLINE UTILITY:
    TYPE RMan at cmd prompt:
    RMAN Restore and Recovery
    The basic RMAN recovery commands are RESTORE and RECOVER. Use RESTORE to restore datafiles from backup sets or from image copies on disk, either to their current location or to a new location. You can also restore backup sets containing archived redo logs. Use the RMAN RECOVER command to perform media recovery and apply archived logs or incremental backups.

    RMAN automates the procedure for recovering and restoring your backups and copies. For example, run the following commands from within RMAN to restore and recover the database to its current time:

    SHUTDOWN IMMEDIATE; # shuts down database
    STARTUP MOUNT; # starts and mounts database
    RESTORE DATABASE; # restores all datafiles
    RECOVER DATABASE; # recovers database using all available redo
    ALTER DATABASE OPEN; # reopens the database

    You can also use the SQLPLUS :
    If you do not use RMAN, then you can restore backups with operating system utilities and then run the SQL*Plus RECOVER command to recover the database. You should follow these basic steps:

    After identifying which files are damaged, place the database in the appropriate state for restore and recovery. For example, if some but not all datafiles are damaged, then take the affected tablespaces offline while the database is open.
    Restore the files with an operating system utility. If you do not have a backup, it is sometimes possible to perform recovery if you have the necessary redo logs dating from the time when the datafiles were first created and the control file contains the name of the damaged file.

    If you cannot restore a datafile to its original location, then relocate the restored datafile and change the location in the control file.

    Restore any necessary archived redo log files.
    Use the SQL*Plus RECOVER command to recover the datafile backups.

    Author Comment

    I was able to get the Aux instance set up - the issues turned out to be in the configuration of Net Naming, Listeners and Intelligent Agent - got all that worked out - too much to go into here.

    The latest stumbling block is how to connect to backup files copied from another host so I can use them for the DUPLICATE command - i think it's channels - but I'm still hunting.

    I have my Aux instance and the backups of Target ready to go.

    Thanks for your help oleggold.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 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

    21 Experts available now in Live!

    Get 1:1 Help Now