How to Connect Oracle to MS-Access

You can configure heterogeneous service to allow an oracle database to connect with Microsoft Access database from about any platform Linux or Windows.

Prepare MS-Access Environment:

Create a database in MS-Access and create at least one table in this database. While creating the database ensure *.mdb file is used to save the Access database. For Example: you can see in the below picture a table named ‘shahid’ is created under the database db1.

Define ODBC Connectivity:

Open the utility Microsoft ODBC Administrator and click on ‘Add’ button to add a new data source by providing description, service name and user ID & password. Generally if you expand the Start->Programs->ORACLE-HOME->Configuration and Migration tools path, you will find this utility. Finally test your connection before moving to next step.

Prepare the Oracle Environment:

Configure the Oracle Listener on the Windows machine. Add the following entry in LISTENER.ORA and TNSNAME.ORA

 (SID_LIST =

  (SID_DESC =

   (SID_NAME = hsodbc)

   (ORACLE_HOME = D:\Oracle\ora92)

   (PROGRAM = hsodbc)

  ))

ACCESS_DB.WORLD =

  (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = DBSERVER) (PORT = 1521)

  )

  (CONNECT_DATA = (SID = hsodbc)

  )

  (HS=OK)

 )

Ensure to check the tnsping for new entry and if needed you can start/stop listener from the command line:

C:\>tnsping access_db
C:\>lsnrctl stop
C:\>lsnrctl start

Configure Oracle HS: Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name.

HS_FDS_CONNECT_INFO = odbc1

HS_FDS_TRACE_LEVEL = off

Connect Oracle with Sys user and execute this script.

SQL>@D:\oracle\RDBMS\ADMIN\caths.sql

SQL> select * from SYS.HS_FDS_CLASS;

FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID

-------------  ------------------ -------------

BITE Built-In Test Environment 1

Create a database link to access from Oracle database

SQL> CREATE DATABASE LINK access_db USING ‘access_db.world’; 

Database link created.

The tables in the MS-Access database can now be accessed from the Oracle environment.

SQL> SELECT * FROM shahid_table@access_db;

        ENO ENAME        SALARY     ADDRESS

 ---------- ------------ --------   ---------------------

         1 shahid       6000       Riyadh

         2 xyz           5000       Delhi

         3 pwz          4000       Banglore

         4 bwz          3000       Mumbai

SQL> CREATE TABLE my_oracle_tab AS SELECT * FROM Shahid_table@access_db;

Table created.

Question with Answer on Oracle database Patches

Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.

The discussion will especially help for those beginners who are preparing for interview and inexperienced to apply the patches. In this article you will find all those things briefly with an example. For more details please study the oracle documentation and try to search with separate topics on this blog.

What are different Types of Patches?

Regular Patcheset: To upgrade to higher version we use database patchset. Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc. The importance of PSU is automatically minimized once a regular patchset is released for a given version. It is mainly divided into two types:

Security or Critical Patch Update (CPU): Critical patch update quarterly delivered by oracle to fix security issues.

Patch set updated (PSU): It include CPU and bunch of other one-off patches. It is also quarterly delivered by oracle.

Interim (one-off) Patch: It is also known as patchset exception or one-off patch or interim patch. This is usually a single fix for single problem or enhancement. It released only when there is need of immediate fix or enhancement that cannot wait until for next release of patchset or bundle patch. It is applied using OPATCH utility and is not cumulative.

Bundle Patches: Bundle Patches includes both the quarterly security patches as well as recommended fixes (for Windows and Exadata only). When you try to download this patch you will find bundle of patches (different set of file) instead of single downloaded file (usually incase patchset).

Is Opatch (utility) is also another type of patch?

OPatch is utility from oracle corp. (Java based utility) that helps you in applying interim patches to Oracle's software and rolling back interim patches from Oracle's software. Opatch also able to Report already installed interim patch and can detect conflict when already interim patch has been applied. This program requires Java to be available on your system and requires installation of OUI. Thus from the above discussion coming to your question it is not ideal to say OPATCH is another patch.

When we applying single Patch, can you use OPATCH utility?

Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset

When you applying Patchsets, You can use OUI.

Yes, Patcheset uses OUI. A patch set contains a large number of merged patches, to change the version of the product or introduce new functionality. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patch sets and the Patch Set Assistant are usually applied through OUI-based product specific installers.

Can you Apply OPATCH without downtime?

As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).

You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from patcheset or patch bundle at ORACLE_HOME?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.

For Example:

opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate

This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.

How can you get minimum/detail information from inventory about patches applied and components installed?

You can try below command for minimum and detail information from inventory
C:\ORACLE_HOME\Opatch\opatch lsinventory –invPtrLoc “location of oraInst.loc file”

$ORACLE_HOME\OPatch\opatch lsinventory -detail -invPtrLoc “location of oraInst.loc file”
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?

Critical Patch Update (CPU) was the original quarterly patches that were released by oracle to target the specific security fixes in various products. CPU is a subset of patchset updates (PSU). CPU are built on the base patchset version where as PSU are built on the base of previous PSU

Patch Set Updates (PSUs) are also released quarterly along with CPU patches are a superset of CPU patches in the term that PSU patch will include CPU patches and some other bug fixes released by oracle. PSU contain fixes for bugs that contain wrong results, Data Corruption etc but it doe not contain fixes for bugs that that may result in: Dictionary changes, Major Algorithm changes, Architectural changes, Optimizer plan changes

Regular patchset: Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. The importance of PSU is minimizing once a regular patchset is released for a given version. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc. 

If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1

PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?

CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach. 

How can you find the PSU installed version?

PSU references at 5^th place in the oracle version number which makes it easier to track such as (e.g. 10.2.0.3.1). To determine the PSU version installed, use OPATCH utility:

OPATCH lsinv -bugs_fixed | grep -i  PSU

To find from the database:

Select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version, substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;

Note: You can find the details from the above query if you already executed the catbundle.sql

Click to Check Existing Oracle Database Patch Status

Will Patch Application affect System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.

Can you stop applying a patch after applying it to a few nodes? What are the possible issues?

Yes, it is possible to stop applying a patch after applying it to a few nodes. There is a prompt that allows you to stop applying the patch. But, Oracle recommends that you do not do this because you cannot apply another patch until the process is restarted and all the nodes are patched or the partially applied patch is rolled back.

How you know impact of patch before applying a patch?

OPATCH <option> -report

You can use the above command to know the impact of the patch before actually applying it.

How can you run patching in scripted mode?

opatch <option> -silent

You can use the above command to run the patches in scripted mode.

Can you use OPATCH 10.2 to apply 10.1 patches?

No, Opatch 10.2 is not backward compatible. You can use Opatch 10.2 only to apply 10.2 patches.

What you will do if you lost or corrupted your Central Inventory?

In that case when you lost or corrupted your Central Inventory and your ORACLE_HOME is safe, you just need to execute the command with –attachHomeflag, OUI automatically setup the Central Inventory for attached home.

What you will do if you lost your Oracle home inventory (comps.xml)?

Oracle recommended backup your ORACLE_HOME before applying any patchset. In that case either you can restore your ORACLE_HOME from the backup or perform the identical installation of the ORACLE_HOME.

When I apply a patchset or an interim patch in RAC, the patch is not propagated to some of my nodes. What do I do in that case?

In a RAC environment, the inventory contains a list of nodes associated with an Oracle home. It is important that during the application of a patchset or an interim patch, the inventory is correctly populated with the list of nodes. If the inventory is not correctly populated with values, the patch is propagated only to some of the nodes in the cluster.

OUI allows you to update the inventory.xml with the nodes available in the cluster using the -updateNodeList flag in Oracle Universal Installer.

When I apply a patch, getting the following errors:

"Opatch Session cannot load inventory for the given Oracle Home <Home_Location> Possible causes are: No read or write permission to ORACLE_HOME/.patch_storage; Central Inventory is locked by another OUI instance; No read permission to Central Inventory; The lock file exists in ORACLE_HOME/.patch_storage; The Oracle Home does not exist in Central Inventory". What do I do?

This error may occur because of any one or more of the following reasons:

–        The ORACLE_HOME/.patch_storage may not have read/write permissions. Ensure that you give read/write permissions to this folder and apply the patch again.

–        There may be another OUI instance running. Stop it and try applying the patch again.

–        The Central Inventory may not have read permission. Ensure that you have given read permission to the Central Inventory and apply the patch again.

–        The ORACLE_HOME/.patch_storage directory might be locked. If this directory is locked, you will find a file named patch_locked inside this directory. This may be due to a previously failed installation of a patch. To remove the lock, restore the Oracle home and remove the patch_locked file from the ORACLE_HOME/.patch_storage directory.

–        The Oracle home may not be present in the Central Inventory. This may be due to a corrupted or lost inventory or the inventory may not be registered in the Central Inventory.

We should check for the latest security patches on the Oracle metalink website http://metalink.oracle.com/ and we can find the regular security alert at the location http://technet.oracle.com/deploy/security/alert.htm  

Caution: It is not advisable to apply the patches directly into the production server. The ideal solution is to apply or test the patches in test server before being moved into the production system.

For more about oracle Patch:

About Patching in Oracle

How to Apply Critical Patch Update on RAC

Apply patches on 9.2.0.1.0 to upgrade 9.2.0.7.0

Roll forward a Physical Standby DB using RMAN incr. Backup

Scenario: There can be a situation where standby DB logs far behind the primary DB leading to archive gap. It could be due to any of the reason:

Suppose network is down between primary and standby server or standby server is down due to any hardware failure for long time and in between down time, you have remove archive log file from primary server without taking backup of archive log files.

In the above case, we can use RMAN incremental backups to synchronize a physical standby database with the primary database. By using FROM SCN command with RMAN incremental backup command (to take backup from that particular SCN to till), we can create a backup on the primary database that starts at the standby database’s current SCN, which can then be used to roll forward the standby database.

Detect the Log Gap between Primary and Standby Server

SQL> Select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)

------- ----------------

1       997

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

------- ----------------

1       714

Thus the standby database is lagging behind of primary database 283 archives.

Note: The gap between standby and primary server due to network failure can be automatically detected by dataguard once the connection is re-established. Check the link for Script: Gap Detection on Standby Database

Step 1: On the standby database, stop the managed recovery process (MRP):

SQL> ALTER DATABASE recover managed standby database CANCEL;

Step 2: Find the SCN On the standby database:
SQL> SELECT current_scn from V$DATABASE;

CURRENT_SCN

-----------

1438

Step 3: On the Primary DB take RMAN incremental backup (from the SCN that is currently recorded) and transfer this to standby site:
RMAN> RMAN TARGET SYS/ORACLE@SADHAN

RMAN> BACKUP incremental from 1438 database format ‘H:\oraback\sad_inc_%U’ tag ‘EMERGENCY_BACKUP’;

Now also take the standby controlfile backup of primary database controlfile

RMAN> BACKUP current controlfile for standby format ‘H:\oraback\sadstby_%U.ctl’;

Transfer these backup on standby server on the same location or directory where you have created on the primary server incase of different location you need to catalog that location for accessing the backup on standby site with RMAN> CATALOG START WITH ‘H:\oraback’;

Step 4: Recover the standby database with incremental backup:
RMAN> RECOVER DATABASE noredo;

Step 5: Startup the Standby DB in NOMOUNT to restore controlfile

RMAN> shutdown immediate;

RMAN> Startup nomount;

RMAN> Restore STANDBY CONTROLFILE from ‘H:\oraback\sadstby_ 14n7p3st_1_1.ctl

Step 6: Mount the database and Recover the Standby DB:
RMAN> Alter database MOUNT;
Step 7: If the primary and standby DB datafile location are identical, skip to step8
If the primary and standby database datafiles directories are different, then you need to catalog the standby datafiles, and switch the standby DB to use cataloged data files. 
RMAN> CATALOG start with ‘+ORADATA/SADHAN’;
RMAN> SWITCH database to COPY;
Step 8: If the primary & standby DB redologs location are identical, skip to step 9.
If the primary and standby database redologs directories are different then use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths such as: LOG_FILE_NAME_CONVERT=’D:\sadhan’,’D:\sadstby’
Step 9: On the standby site, clear all standby redo log groups

SQL> Alter database CLEAR Logfile GROUP 1;
SQL> Alter database CLEAR Logfile GROUP 2;
SQL> Alter database CLEAR Logfile GROUP 3;
Step 10: On the standby site, restart Flashback (Optional):
SQL> Alter database FLASHBACK OFF;
SQL> Alter database FLASHBACK ON;
Step 11: On the standby database, restart MRP:
SQL> Alter database recover MANAGED STANDBY DATABASE disconnect from session;

SQL> select process, status, sequence# from v$managed_standby;

Step12: Check the synchronization between physical and standby server:

SQL> Select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)

------- ----------------

1       1001

SQL> Select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

------- ----------------

1       1001

Thus we can see that the maximum archivelog sequence generated on primary database and applied on standby database is same that means the standby db is synchronized with primary database. Now we can generate some archivelog on primary database and we can check that it is shipped and applied on the standby site.

SQL> alter system switch logfile;

SQL> /

System altered.

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)

------- ----------------

1       1003

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

------- ----------------

1       1003

Check related post:

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

Creating Physical Standby DB using RMAN (without shutting down the prod DB)

Here we are going to create standby database from RMAN backup without shutting down the production database.

Steps:

(1)   Take RMAN FULL backup of Primary database

(2)   Create standby controlfile on primary site.

(3)   Move the Backup and Standby control file to standby site

(4)   Create or copy PFILE to standby DB and modify it accordingly

(5)   Configure tns and listerner for both primary and standby database.

(6)   Start the standby database in NOMOUNT then perform the next step

Take RMAN backup on the production system disk

Rman target sys/****@sadhan catalog/catalog@rman

RMAN> BACKUP database plus archivelog;

Create standby controlfile on Primary database

RMAN> backup current controlfile for standby format ‘H:\oraback\sadstby_cfile.ctl’;
RMAN> SQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;

Move the Backup to the Standby Server

You can move the backup from primary site to standby site by using any of the below method:

–         You can move the backup exactly the same location on standby as they were created on primary database.

–         You can use CATALOG BACKUPIECE command (on 10g onwards) if you are moving the backup to a different location.

–         If the backup are on NFS then mount the NFS on standby server with the same name as you mounted on primary database.

–         Use FTP or SCP to move the backup.

–         If you taken the backup on tape then you must do proper changes on standby server to restore the backup using tape.

Perform log switches on primary and record the last log sequence number.

SQL> alter system switch logfile;
SQL> alter system switch logfile;

Note: Do not forget to make same folder structures in the destination server.
Create Pfile in primary server after making necessary change transfer to standby site:

SQL> connect target /

SQL> create pfile=’D:\ORABACK\sadstby.sql’ from spfile;

background_dump_dest='D:\oracle\admin\sadstby\bdump'
control_files='D:\oracle\oradata\sadstby\control01.ctl','D:\oracle\oradata\sadstby\control01.ctl'
core_dump_dest='D:\oracle\sadstby\cdump'
DB_UNIQUE_NAME= SADSTBY
db_name='SADHAN'
instance_name='sadstby'
log_archive_format='%t_%s_%r.dbf'
log_archive_dest_2='SERVICE=SADHAN VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=SADHAN'
log_archive_dest_state_2='DEFER'
log_archive_dest_1='LOCATION=E:\ORACLE\SADSTBY\ARCHIVE'
remote_archive_enable='true'
fal_server='SADHAN'
fal_client='SADSTBY'
log_file_name_convert=('D:\oracle\oradata\sadhan', 'D:\oracle\oradata\sadstby')
db_file_name_convert=('D:\oracle\oradata\sadhan', 'D:\oracle\oradata\sadstby')

Configure Network File for standby

#Assume Primary database is already configure. You have to do necessary addition for standby database.

#Update Listener.ora in standby server:
(SID_DESC =
(GLOBAL_DBNAME = SADSTBY)
(ORACLE_HOME = d:\oracle)
(SID_NAME = SADSTBY)
)

#Update tnsnames.ora on primary node:
SADSTBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.222)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADSTBY)
)
)

Create the password file for standby database.

orapwd file=orapwSADSTBY password=oracle entries=10

Startup the standby database in nomount phase

sqlplus “/ as sysdba”
SQL> startup nomount pfile=’D:\ORABACK\sadstby.sql’;

Connected to an idle instance.

SQL> Create spfile from pfile=’D:\ORABACK\sadstby.sql’;

SQL> shutdown immediate;

SQL> startup nomount;

Duplicate the database by connecting to target, auxiliary and catalog.

connect target /
connect catalog catalog/catalog@rman

connect auxiliary sys/****@sadstby
connected to target database: SADHAN (DBID=63198018)
connected to recovery catalog database
connected to auxiliary database: SADHAN (not mounted)

RMAN> run { 

# Set the last log sequence number
set until sequence = 450 thread = 1;
allocate auxiliary channel ch1 type disk; 

allocate auxiliary channel ch2 type disk;
duplicate target database for standby dorecover nofilenamecheck;
}
RMAN> exit

Once the standby database is created, RMAN can back up the standby database and archived redo logs as part of your backup strategy. These standby backups are fully interchangeable with primary backups. In other words, you can restore a backup of a standby datafile to the primary database, and vice versa.

Take the Standby in Managed recovery Mode and check sync the logs from primary are shipped over to standby archive destination.

SQL> Select max(sequence#) from v$archive_log;

MAX(SEQUENCE#)

--------------

            58

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

            60

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE  LOG_MODE

--------- ---------- ------------

SADHAN    MOUNTED    ARCHIVELOG

SQL> select recovery_mode from v$archive_dest_status;

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------

            58

SQL> alter database recover managed standby database disconnect;
Database altered.

Creating Physical Standby DB through RMAN Restore & Recovery

Here in below example we will see step by step procedure how to create standby database without using Recovery Manager DUPLICATE TARGET DATABASE command.

Steps:

1. Backup the database with all Archivelogs and controlfile for standby
2. Move all your backup and standby controlfile to the standby server.
3. Make proper changes in Pfile of both primary and standby server.
4. Perform Restore & Recovery on Standby site
5. Put the Standby DB in Recovery Managed Mode.

Take a full valid backup of Primary database

C:\> rman target /

connected to target database: SADHAN (DBID=63198018)

RMAN>run

{

allocate channel ch1 type disk;

allocate channel ch2 type disk;

backup incremental level 0 format 'H:\ORABACK\i001_%T_%u_s%s_p%p' database;

backup format 'E:\ORABACK\i001_arch_%T_%u_s%s_p%p' archivelog all;

backup current controlfile for standby format'E:\ORABACK\i001_CF_%T_%u_s%s_p%p' ; 

}

Create Standby Control file on Primary database

C:\>RMAN TARGET /

connected to target database: SADHAN (DBID=63198018)

RMAN> backup current controlfile for standby format=’H:\oraback\sadstby_CFile.%U’;

Starting backup at 25-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=158 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including standby control file in backupset

channel ORA_DISK_1: starting piece 1 at 25-FEB-13

channel ORA_DISK_1: finished piece 1 at 25-FEB-13

piece handle=H:\oraback\sadstbycontrol.ctl tag=TAG20130225T020241 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 25-FEB-13

Move the Backup to the Standby Server

You can move the backup from primary site to standby site by using any of the below method:

–         You can move the backup exactly the same location on standby as they were created on primary database.

–         You can use CATALOG BACKUPIECE command (on 10g onwards) if you are moving the backup to a different location.

–         If the backup are on NFS then mount the NFS on standby server with the same name as you mounted on primary database.

–         Use FTP or SCP to move the backup.

–         If you taken the backup on tape then you must do proper changes on standby server to restore the backup using tape.

Make proper changes in Pfile for both primary and standby server:

Copy the Primary Database PFILE to Standby site and make necessary change. Consider you have already primary database pfile required changing.

log_archive_dest_2='SERVICE=SADSTBY'

db_unique_name='sadstby'

instance_name='sadstby'

db_file_name_convert='D:\oracle\oradata\sadhan','D:\oracle\oradata\sadstby'

log_file_name_convert='D:\oracle\oradata\sadhan','D:\oracle\oradata\sadstby'

standby_archive_dest='E:\oracle\sadstby\Archive'

Note: Do not forget to create service for standby database and make sure to tns network connectivity for primary as well as standby database.

Perform the Restore & Recovery on Standby site:

C:\> rman target /

connected to target database (not started)

RMAN> startup nomount;

RMAN> SET DBID = 63198018

executing command: SET DBID

RMAN> restore controlfile from ‘H:\oraback\sadstbycontrol.ctl’  --for 9i

RMAN> restore standby controlfile from 'H:\oraback\sadstbycontrol.ctl'; --for 10g onwards

RMAN> sql 'alter database mount standby database';

RMAN> restore database;

Now try to list all the archivelogs which are backed up and from this list identify the maximum sequence for recovery.

RMAN> list backup of archivelog all;

RMAN> recover database until sequence 58;

Note: Do not be panic if you find any error indicating file was not restored sufficiently with old backup. You can safely ignore that error and move for next step.

Put the Standby DB in Recovery Managed Mode:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Now try to generate some log and check the sync the log from primary are shipped over to standby archive destination.