Category: database

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.

What is the use of MMON background process in Oracle Database?

MMON (Manageability Monitor)


MMON (Manageability Monitor) is a background process introduced in Oracle 10g. It gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.