ORACLE DBA

Encrypting RMAN backup Configuring encryption for Oracle Recovery Manager (RMAN) is always a good step in the right direction. There may be different reasons for encrypting RMAN backup.  For greater security of data; Customer requirement (e.g, most companies requires encryption for any database file containing SSN, Credit Card number, date of birth, etc); or Complying with laws or regulations. Option 1: Use global security wallet to encrypt backup *configure the encryption wallet. create a directory called "Wallet" in $ORACLE_BASE/admin/$ORACLE_SID mkdir /home/oracle/app/oracle/admin/orcl/wallet *Issue this command as SYS: SQL>  alter system set encryption key identified by "oracle1"; *Open the wallet: SQL>  alter system open encryption wallet identified by "oracle1"; *Log in to rman to encrypt backup rman target / RMAN>  configure encryption for database on; RMAN>  backup database; Option 2: Configure encryption right from...

All About V$ views This can help a lot to an Oracle DBA to get info about different views: Advisors: Information related to cache advisors V$PGA_TARGET_ADVICE V$PGA_TARGET_ADVICE_HISTOGRAM V$MTTR_TARGET_ADVICE V$PX_BUFFER_ADVICE V$DB_CACHE_ADVICE V$SHARED_POOL_ADVICE V$JAVA_POOL_ADVICE V$STREAMS_POOL_ADVICE (10.2) V$SGA_TARGET_ADVICE (10.2) V$ADVISOR_PROGRESS (10.2) ASM V$ASM_ALIAS (10.1) V$ASM_CLIENT(10.1) V$ASM_DISK(10.1) V$ASM_DISK_STAT(10.2) V$ASM_DISKGROUP(10.1) V$ASM_DISKGROUP_STAT(10.2) V$ASM_FILE(10.1) V$ASM_OPERATION(10.1) V$ASM_TEMPLATE(10.1) Backup/recovery Information related to database backups and recovery including last backup,archive logs,state of files for backup,and recovery V$ARCHIVE V$ARCHIVED_LOG V$ARCHIVE_DEST V$ARCHIVE_DEST_STATUS V$ARCHIVE_GAP V$ARCHIVE_PROCESSES V$BACKUP V$BACKUP_ASYNC_IO V$BACKUP_CORRUPTION V$BACKUP_DATAFILE V$BACKUP_DEVICE V$BACKUP_PIECE V$BACKUP_REDOLOG V$BACKUP_SET V$BACKUP_SYNC_IO V$BLOCK_CHANGE_TRACKING V$COPY_CORRUPTION V$DATA...

Select the TOP N rows from a table in Oracle Below is the examples to find the top 5 employees based on their salary. Option 1: Using RANK() SELECT employee_name, salary FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank FROM employee ) WHERE salary_rank <= 5; Option 2: Using Dense_Rank() SELECT employee_name, salary FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank FROM employee ) WHERE salary_dense_rank <= 5; Option 3: Using inner query This is an example of using an inner-query with an ORDER BY clause: SELECT * FROM (SELECT * FROM employee ORDER BY salary DESC) WHERE ROWNUM < 5; Option 4: Using count distinct combination SELECT * FROM employee e WHERE 5 >= (SELECT COUNT(DISTINCT salary) FROM employee b WHERE b.salary >= e.salary) ORDER BY salary DESC;

RMAN Hot Backup Script The following unix shell script will do a full RMAN hot backup to your database and will copy the backup files compressed to the directory you will specify. This is a hot-backup, and the database must to be in ARCHIVELOG mode for this to work. A backup retention policy with a recovery window of 2 days is defined in this script. With this retention policy RMAN will keep archive logs and backup files necessary to recover to any point of time within those 2 days in the recovery window. Older backups and archivelogs not needed to satisfy this retention policy will be automatically deleted by this script. No RECOVERY CATALOG is being used with this script, instead database controlfiles are used to record the RMAN repository information. Make sure you set the rman CONFIGURE CONTROLFILE AUTOBACKUP parameter to ON in in RMAN in order to take extra backups of the controlfile and spfile (RMAN will not backup init.ora files) as extra protection. This script will d...

Oracle 11g New Feature Parameter DDL_LOCK_TIMEOUT : DDL waiting time for DML Locks Oracle 11g introduced a new parameter ddl_lock_timeout which controls the waiting time duration for a DDL statement wait for a DML Lock. Prior to 11g and in 11g if you are not specifiying the ddl_lock_timeout, if you perform a ddl operation on a table which is locked by a end user DML, immediately you will get error “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.  But in Oracle 11g if you specify DDL_LOCK_TIMEOUT, the ddl session will wait till the duration mentioned to release the DML lock on the table. It will not through the immediate error message. Syntax: ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 120 ; ALTER SESSION SET DDL_LOCK_TIMEOUT = 120 ; The maximum value can be specified is 1,000,000. The default value zero indicates NOWAIT. The values are in seconds. Example: Session 1: 22:09:35 SQL> alter system set ddl_lock_timeout=60; System altered...

Run sql commands in background  [[same as nohup in os]] vi DBSTATUS.sql -------------- spool test.log select name from v$database; spool off; exit vi DBS.sh ----------------- #! /bin/ksh export ORACLE_SID=dbname export ORACLE_HOME=/ora01/app/oracle/product/10.2.0 export PATH=$ORACLE_HOME/bin:$PATH cd /u01 sqlplus / as sysdba @DBSTATUS.sql exit 0 $ chmod 777 DBSTATUS.sql DBS.sh crontab -e ----------------- 31 12 19 04 * /u01/DBS.sh mail bolisettyvaas@gmail.com crontab -l ----------------- 31 12 19 04 * /u01/DBS.sh mail bolisettyvaas@gmail.com

Flashback database using guaranteed restore point↓♚ FlashBack Restore Point in oracle 1.  Requirements for Guaranteed Restore Points The COMPATIBLE initialization parameter must be set to 10.2 or greater. The database must be running in ARCHIVELOG mode. A flash recovery area must be configured Guaranteed restore points use a mechanism similar to flashback logging. Oracle must store the required logs in the flash recovery area. Oracle 10.2 If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point  ALTER DATABASE FLASHBACK ON; Oracle 11.x There is no need to mount the database. Flashback can be tunred on at open state. SQL_BOLISETTY >>  ALTER DATABASE FLASHBACK ON; 2. Creating Restore points [CREATE RESTORE POINT] # Create Normal restore points SQL_BOLISETTY >>  CREATE RESTORE POINT before_upgrade;  #Create guaranteed restore points SQL_BOLISETTY ...