Category: database

Convert Physical database into a Snapshot Standby Database

What are the benefits of Snapshot standby database?
Snapshot Standby Database is introduced from Oracle 11gR1. A Snapshot Standby database is a physical standby that is temporarily disconnected from Data Guard configuration, and started in READ-WRITE mode.It functions as a updateable Stand-alone Database, which is an exact Replica of Primary Database which is normally used for testing purpose. We can revert the Snapshot standby database to Physical Standby database after the testing is completed.
Steps to convert Physical database into a Snapshot Standby Database:
1. First step to convert to Snapshot standby database is to enable FLASHBACK feature on the Physical Standby, if not already enabled.
Ensure there is sufficient space available in Flash Recovery Area (FRA) for Flashback Logs.
2. Stop redo apply on the physical standby database:
SQL>alter database recover managed standby database cancel;
3. Turn on flashback logging:
SQL>alter database flashback on;
4. Convert the standby database into a snapshot standby database:
( For RAC Configurations: Shutdown all other instances except one, which is used as Snapshot standby database)
SQL>alter database convert to snapshot standby;
5.Shut down the snapshot standby database and startup the database (it will be opened for read/write access):
SQL>shutdown immediate;
SQL>startup
After the Snapshot mode is enabled on the database, perform the required testing and revert back to Physical Standby mode after the testing is complete.
Steps to Revert the Physical Standby Database Back to its Original State
1. Change the standby database back into its standby mode (from snapshot mode) as follows:
SQL>startup mount force;
SQL>alter database convert to physical standby;
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
2. Enable redo log apply.
SQL>alter database recover managed standby database using current logfile disconnect;
3.On the primary database, issue the following statement to re-enable archiving to the physical standby database:
SQL>alter system set log_archive_dest_state_2=enable scope=both;

Protection Modes in Oracle Dataguard

We have 3 Protection modes available in Oracle DataGuard
1. Maximum Performance :
This is the default protection mode. It provides the highest level of data protection that is possible
without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. 
2. Maximum Availability :
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.
3. Maximum Protection :
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

Oracle Recovery Manager Troubleshooting ::ORA-27211: Failed to load Media Management Library


This is an error during Database Clone using RMAN.

I was trying to restore database using RMAN Backup

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


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


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


RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database


RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel 
ORA-19554: error allocating device, device type: SBT_TAPE, device name:


ORA-27211: Failed to load Media Management Library


Additional information: 

Cause: Backup Configuration was modified.

Solution:
Use the below command to Resolve the error and continue with Database Restore

rman auxiliary / 
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to ‘DEV’ backup location ‘/Daily_Backup/Backup_16042018’;
}
exit;
EOF

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 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) 
 ) 
 ) 

Command to check LUN details on a Linux server

Logical Unit Number (LUN) is part of Storage Management in Oracle Automated Storage Management (ASM).


Below command is useful to check the available LUN’s on a linux server.


As root user,


Run the command ls -la /dev/disk/by-id/  




Sample Output below:


[root@erptestdb ~]# ls -la /dev/disk/by-id/
total 0
drwxr-xr-x 2 root root 240 Jan 29 16:17 .
drwxr-xr-x 6 root root 120 Jan 29  2018 ..
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdd
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdd1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdf
lrwxrwxrwx 1 root root  10 Jan 29 16:17 scsi-3********************************-part1 -> ../../sdf1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdc
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdc1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3********************************-> ../../sdb
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdb1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sde
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sde1
[root@erptestdb ~]#

What is Edition Based Redefinition (EBR) in Oracle?



Edition Based Redefinition (EBR) is a new feature introduced from Oracle Software Version 11g R2.Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema.This feature facilitates Online patching of database objects in R12.2 online Patching.

Editionable and Non-editionable Objects


Below schema object types are editionable in the database:

SYNONYM
VIEW
SQL translation profile

All PL/SQL object types:

FUNCTION
LIBRARY
PACKAGE and PACKAGE BODY
PROCEDURE
TRIGGER
TYPE and TYPE BODY

If a schema object type is editionable in the database, then it can be editionable in schemas.All other schema object types are noneditionable in the database and in every schema, and objects of that type are always noneditioned. Tables are always noneditioned objects.


To view the editions related information in the database, use the below datadictionary tables.

DBA_EDITIONS: SELECT * FROM dba_editions;

DBA_OBJECTS_AE: DBA_OBJECTS_AE view shows all objects, of all editions.