How to alter Database add Logfile correctly

  Add a New Logfile Member SQL> alter database add logfile '+DATA' to group 1; alter database add logfile '+DATA/tansid_p2/redo01a.log' to group 1                                                        * ERROR at line 1: ORA-00933: SQL command not properly ended Error happened because missed  MEMBER  keyword. SQL> alter database add logfile member '+DATA/TANSID_P2/redo01a.log','+DATA/TANSID_P2/redo01b.log' to group 1; Database altered. Multiple logfiles are allowable to be added in one statement as long as you use comma to delimit them. Create a New Logfile Group Need to specify  THREAD   then   GROUP , logfile member follows. SQL> alter database add logfile thread 1 group 5 ('+DATA/TANSID_P2/redo05a.log','+DATA/TANSID_p2/redo05b.log') size 10m; Database altered. SQL> alter database add logfile thread 2 group 6 ('+DATA/TANSID_p2/redo06a.log','+DATA/TANSID_p2/redo06b.log') size 10m; Database altered. Create a St

How to manage Standby Redo Logs (SRL) in Standby Database

Standby Redo Logs (SRL) is similar to Online Redo Log (ORL) and only difference between two is that Standby Redo Log is used to store redo data received from another database (primary database).Standby Redo Logs are only used if have the LGWR as transport mode to Remote Standby Database.  Q. How to check using Real-Time Apply ? SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; Q. How to identify standby redo logs ? SQL> select * from v$standby_log; Q. How to check members of standby redo log file SQL> select * from  v$logfile where type=’STANDBY’; Q. How to add Standby Redo Log File to a Specific Group Number SQL> alter database add standby logfile group 6 ( '/<full path for srl>/log06a.dbf', '/<full path for srl>/log06b.dbf' ) size 250m; Following are the steps to drop and create new standby database: 1. Check Primary Redo Thread Number and size. SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#,

Oracle Database Native Network Encryption

Network encryption is one of the most important security hardening strategies to be adopted in any enterprise infrastructure. It will ensure confidential data transmitted over network is encrypted and will prevent from malicious attacks. Network encryption guarantees that data exchanged between the clients (Application) and Database System or indeed between any two endpoints should be securely transmitted and transparently decrypted. Moreover, compliance with mandatory laws like HIPAA dictates or highly recommends to implement tools of encryption of in-transit and/or at-rest data as protection from theft or malicious attacks. Oracle RDBMS Enterprise Edition offers the solutions to encrypt and secure over-transmit data, It is important to note that Network Encryption Option is part of the Oracle Enterprise Edition and doesn’t  require any seperate licence for Oracle Advanced Security Option. Native Network Encryption can be configured by updating the sqlnet.ora configuration file on

Save state of Pluggable database in Oracle

  Pluggable Database Saved State in Oracle Saved State is a feature in Oracle for saved the OPEN state of all PDBS when CDB is restart. It will bring PDBs to same state if it open then it bring the PDB in Open State or if PDB is closed when CDB restart then it bring PDB in closed state. Open the database in READ-WRITE/READ-ONLY mode it save state. Check the Save State for PDBS select,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id; SELECT con_name, instance_name, state FROM cdb_pdb_saved_states; When database gets unplug and plug this saved state will be lost. Save the existing State of PDB alter pluggable database PDB1 save state; Pluggable database altered. Discard or unsaved the existing state of PDB alter pluggable database HYDB discard state; Pluggable database altered. Show the pdbs Show pdbs Preserving the Open Mode of All PDBs ALTER PLUGGABLE DATABASE ALL SAVE STATE; Preserving the Open Mode of Multiple PDBs ALTER PLUGGABLE DATABASE PDB1 , PDB2 S

ASM: Device "/dev/” is already labeled for ASM disk"

When trying to create ASMLib disk by using oracleasm utility the process fails complaining that the disk is already marked as ASM disk, see example below: # /etc/init.d/oracleasm createdisk ASMB2B_DISK02 /dev/mapper/ ion_vdisk1p1  Marking disk "ASMB2B_DISK02" as an ASM disk:               [FAILED] Under  /var/log/oracleasm we can find the following entry: Device "/dev/mapper/ion_vdisk1p1" is already labeled for ASM disk "" The disk to be created as ASMLib disk was part of ASM, or it was used as ASMLib in a previous configuration. If the disk was part of ASM before, it has already an ASM header. Therefore, the disk cannot be created as ASMLib disk again. The disks header needs to be renamed or removed to create a new ASMLib disk.  Try to rename/relabel the ASMLib disks by # /etc/init.d/oracleasm renamedisk <device_name> <new_label> or # /etc/init.d/oracleasm force-rename <device_name> <new_label>   2. Recreate ASMLib volumes by p erf

How to Clean Up a Failed Grid Infrastructure Installation

This article is about how to clean up a failed Grid Infrastructure installation. It specifically describes what do if the "" script fails during this process and want to start all over again. On all cluster nodes except the last, run the following command as the "root" user. # perl $GRID_HOME/crs/install/ -verbose -deconfig -force On the last cluster node, run the following command as the "root" user. # perl $GRID_HOME/crs/install/ -verbose -deconfig -force -lastnode This final command will blank the OCR configuration and voting disk. If above command doesn't work then please use  $/Grid_home/crs/install/ -deconfig -force Now "" can be rerun , but need to prepare ASM disks before doing so. If /Grid_home/crs/install/ -deconfig -force Once you attempt an installation, your ASM disks are marked as being used, so they can no longer be used as candidate disks. To revert them to candidate disk d

How to check file version in Oracle EBS

  Method I : ADIDENT utility To find out a file version, oracle have provided ‘ADIDENT’ utility (AD Utility).  adident Header <file_name> adident Header OKCGTM.pll Method II : Strings utility Strings is a UNIX based utility which is used to  print the strings of printable characters in files.  strings -a <file_name>|grep Header strings -a OKCGTM.pll|grep Header Method III : SQL Query Following SQL query can be used the file version number and also the patch number which brought the file to the current version SELECT af . app_short_name "Application" , af . filename "File Name" , afv . VERSION "File Version" , NVL ( aap . patch_name , 'Default Installation' ) "Patch Number" , '$' || fa . basepath || '/' || af . subdir "Location" FROM apps . ad_files af , apps . ad_patch