Friday, 13 April 2018

Oracle Database Recovery Manager Concepts:Script to Restore Oracle Database from RMAN Backup

During Oracle Database Cloning,restore Oracle database from RMAN backup taken on Source environment to the Target environment.


Steps for Database restore using Oracle Recovery Manager (RMAN)

1.Connect to target database 

Startup the Target database in Nomount stage

SQL> Startup nomount

2.Run the below Command to Restore Oracle database using RMAN 

rman auxiliary / 
run
{
duplicate database to 'DEV' backup location '/DB_BACKUP/Daily_Backup/daily_bkp_04102018';
}


Where DEV is the target instance for Restore
Backup Location - Directory where RMAN backup is located

3. Verify that Oracle database is up and running.

Resolve Error ORA-28500 while accessing Heterogenous Database link (ORACLE TO MSSQL)

After creating a heterogenous database link from Oracle database  to MSSQL Server, getting error message "ORA-28500"

SQL>select sysdate from dual@DBLINK_MSSQL;
                         *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from MSSQL_INTG


Reason

$ telnet MSSQLDB 1433
MSSQLDB/1433: Name or service not known

$ telnet 10.x.x.x 1433
Trying 10.x.x.x...
Connected to 10.x.x.x (10.x.x.x).
Escape character is '^]'.
^]
telnet> logout
^CConnection closed by foreign host.


I am able to telnet to MSSQL database Server using IP address , but cannot telnet to MSSQL server using hostname


Solution

Contact your Network admin to allow access to 1433 using Servername (MSSQLDB)

(or)

Change the below configuration

1. Set Oracle Home to point to your Gateway Oracle HOME

2. Navigate to $ORACLE_HOME/dg4msql/admin

Change below values in initdg4msql.ora

changed HS_FDS_CONNECT_INFO from MSSQLDB to IP address

#
HS_FDS_CONNECT_INFO=10.x.x.x:1433//wfcdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


3. Restart the listener in Gateway Oracle HOME

4. Verify the database link is working fine.

SQL>select sysdate from dual@DBLINK_MSSQL;

==>Above Query should return current system date.

Resolve ORA-28545 When trying to access database link between Oracle database and MSSQL

Heterogenous database link between Oracle database and MSSQL was created using the below command.

SQL> create public database link mssql connect to "integration" identified by "*****" using 'dg4msql'; 

Database link created. 

Error Message

SQL> select sysdate from dual@mssql 
2 ; 
select sysdate from dual@mssql 

ERROR at line 1: 
ORA-28545: error diagnosed by Net8 when connecting to an agent 
Unable to retrieve text of NETWORK/NCR message 65535 
ORA-02063: preceding 2 lines from MSSQL 


LISTENER_GTWY = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclegtwy.dmn)(PORT = 1526)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526)) 


(SID_NAME=dg4msql) 
(ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1) 
(ENV="LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib") 
(PROGRAM=dg4msql) 


Resolution 
Listener file on the Oracle Database gateway server should look like below 

LISTENER_GTWY = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST =oraclegtwy.dmn)(PORT = 1526)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526)) 



SID_LIST_LISTENER_GTWY =------------------------>Missing 
 (SID_LIST= 
 (SID_DESC= 
 (SID_NAME=dg4msql) 
 (ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1) 
 (ENV="LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib") 
 (PROGRAM=dg4msql) 
 ) 
 )