Rakesh Soni - DBA

crontab - scheduling a shell script (for oracle export)

2009-11-06T03:21:00.000-08:00

Cron is a time-based job scheduler in Unix-like computer operating systems. 'cron' is short for Chronograph. Cron enables users to schedule jobs (commands or shell scripts) to run automatically at a certain time or date.

> connected with oracle user.
[oracle@oraclelinux bin]$ pwd
/bin

crontab -e - opens the user's crontab file for viewing/editing

crontab -l - simply lists the crontab file's contents for the user. Think of it as a "cat" function for the crontab.

-------------------------------
[oracle@oraclelinux bin]$ crontab -e

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=oracle
HOME=/
*/5 * * * * sh /home/oracle/rakesh.sh

-----------------------------------------
The script will execute every five minute.

http://en.wikipedia.org/wiki/Cron
http://www.crontabrocks.org/

--------------------------------------------------
rakesh.sh shell script :
--------------------------------------------------
#!bin/bash
#PATH=$PATH:$HOME/bin;export PATH
unset USERNAME
#DISPLAY=10.142.200.141:0.0; export DISPLAY
#Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=jsildb; export ORACLE_SID
ORACLE_HOME_LISTENER=LISTENER; export ORACLE_HOME_LISTENER
ORATAB=/etc/oratab; export ORATAB
#ORACLE_TERM=xterm; export ORACLE_TERM
export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
export PATH=/usr/sbin/:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:lib:usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")

exp rakesh/rakesh@jsildb file=/home/oracle/erp_backups/rakesh_$DATE.dmp log=/home/oracle/erp_backups/rakesh_$DATE.log statistics=none

export bash script in linux with timestamp...

2009-11-03T20:42:00.000-08:00

I have created export bash script in linux operating system to export schema and attached time of export with dump & log file..

#!bin/bash
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")
/u01/app/oracle/product/10.2.0/db_1/bin/exp rakesh/rakesh@rockdb file=/home/oracle/backups/rakesh$DATE.dmp log=/home/oracle/backups/rakesh$DATE.log statistics=none

ORA-00031: session marked for kill

2009-07-29T22:12:00.000-07:00

Today, i was trying to kill a session as explained in
http://rakeshocp.blogspot.com/2009/04/kill-session.html but i was getting error "ORA-00031: session marked for kill" i got to know that we need to kill an OS session then. I wanted to kill "hassan's" session.
select spid, osuser, s.program from v$process p, v$session s where p.addr=s.paddr;
SPID OSUSER PROGRAM
------------ ------------------------------ ----------------
4844 rakesh.kumar
7168 rakesh.kumar plsqldev.exe
6012 Hassan plsqldev.exe

C:\Documents and Settings\Administrator>orakill icpora 6012
Kill of thread id 6012 in instance icpora successfully signalled.
C:\Documents and Settings\Administrator>

Thanks to the Don Burleson
http://www.dba-oracle.com/t_kill_process_windows.htm

When your query takes too long

2009-06-16T20:28:00.001-07:00

What to do when your query is too slow? Check the link below for details:

http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0

Data Pump

2007-10-27T23:04:00.000-07:00

Oracle Data Pump
Oracle 10g's new DataPump utility is designed as the eventual replacement for the original Oracle Import and Export utilities.

Some of the drawbacks in traditional Export and Import that needed to be addressed.
<> Operations are difficult to restart.
<> Execution is client-side and single-threaded.
<> Dump files can grow enormously.
<> Tuning mechanisms are limited.
<> Progress monitoring is difficult.
<> Database object filtering features are limited.

Enter The DataPump: Features Overview
Fortunately, Oracle 10g has addressed many of these issues with the DataPump, a new set of utilities that significantly expands the capabilities of the original Export and Import utilities.
<> Server-side parallel execution.
<> Improved control and restartability of operations.
<> Simplified monitoring of operational status..
<> Automated performance tuning.
<> Improved database object filtering.
<> Export dump file control enhancements.

for details visit of above visit http://www.databasejournal.com/features/oracle/article.php/3489491

DataPump 10g at Oracle Website by Arup Nanda
http://www.oracle.com/technology/pub/articles/10gdba/week4_10gdba.html

=============================================================================
Data Pump Export
First you need create a Directory for Data Pump Utility. and must grant read, write priviledges to user.

SQL> create or replace directory expdp as 'D:\expdp';
grant read, write on directory expdp to rakesh;

SQL> GRANT exp_full_database to rakesh;

---------------------------------------------------------------------------------------------Exporting with DataPump
H:>expdp erp/********@live-db tables=client_type directory=exp_dir dumpfile=datapump_erp.dmp

---------------------------------------------------------------------------------------------Importing with DataPump
H:\>impdp aa/********@rock tables=client_type directory=SYS_DMP REMAP_SCHEMA=erp:aa remap_tablespace=erp:users dumpfile=datapump_erp.dmp

-------------------Live Database----------------------TestDatabase-----
DB-Name----------Live-db---------------------------------rock---------
DB-Users-------------erp------------------------------------aa----------
Directoy-----------EXP_DIR----------------------------SYS_DMP------
Tablespaces----------ERP---------------------------------USERS-------

Above chart might help Young, novice DBA's to get any idea, how we can export from one database to another with DataPump.

Note: Before importing you Manually MUST COPY dumpfile from exp_dir to sys_dir.
---------------------------------------------------------------------------------------------
SQL> column directory_path format a70
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ -----------------------------------
SYS -------------EXPDP---------------------D:\expdp_dir
SYS-------------ABC------------------------d:\abc_dir
SYS-------------WORK_DIR----------------C:\ADE\aime_10.2_nt_push\oracle/work
SYS-------------SYS_DMP------------------D:\exp_dir
SYS-------------DATA_PUMP_DIR---------D:\oracle\product\10.2.0\admin\rock\dpdump\
SYS-------------ADMIN_DIR---------------C:\ADE\aime_10.2_nt_push\oracle/md/admin
6 rows selected.

SQL> select * from dba_directories;

SQL> GRANT read, write ON DIRECTORY expdp TO hr;
Grant succeeded.
SQL>

SQL> CREATE DIRECTORY hrdir AS 'D:\hr_dir';
Directory created.

SQL> grant read, write on directory hrdir to hr;
Grant succeeded.
SQL>

Exporting EMPLOYESS table...

H:\>expdp hr/hr@rock directory=hrdir dumpfile=hr_emptab.dmp logfile=hr_emptab.log tables=EMPLOYEES

Export: Release 10.2.0.1.0 - Production on Friday, 12 June, 2009 12:02:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@rock directory=hrdir dumpfile=hr_emptab.dmp logfile=hr_emptab.log tables=EMPLOYEES
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "HR"."EMPLOYEES" 15.78 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is: D:\HR_DIR\HR_EMPTAB.DMP
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:03:01

The above export command has also exported indexes, views, functions, packages, constraints, we need to export only TABLE EMPLOYEES, not other objects, check the below command.

H:\>expdp hr/hr@rock directory=hrdir dumpfile=hr_only_EMP_table.dmp logfile=hr_emp_table.log tables=EMPLOYEES exclude=index, view, REF_CONSTRAINT, function, trigger, constraint

Export: Release 10.2.0.1.0 - Production on Friday, 12 June, 2009 12:17:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@rock directory=hrdir dumpfile=hr_only_EMP_table.dmp logfile=hr_emp_table.log tables=EMPLOYEES exclude=index, view, REF_CONSTRAINT, function, trigger, constraint
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "HR"."EMPLOYEES" 15.78 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is: D:\HR_DIR\HR_ONLY_EMP_TABLE.DMPJob "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:18:01

COLD BACKUP batch file

2009-06-02T23:41:00.000-07:00

We have scheduled a Cold backup of DR site: check the steps below
create a coldbackup.bat
-----------------------------
cd D:\oracle\product\10.2.0\db_1\BIN
sqlplus "sys/rock@rockstd as sysdba" @D:\COLDBACKUP_WEEKLY\coldbackup.sql
-----------------------------
steps for creating batch file: http://rakeshocp.blogspot.com/2007/09/batch-file-scripts.html
-----------------------------
coldbackup.sql
-----------------------------
ALTER DATABASE recover managed standby database cancel;
SHUTDOWN IMMEDIATE;
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\DATAFILES
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\CONTROLFILES
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\REDOLOGS
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\ARCHIVELOGS
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\PARAMETERFILES
host copy D:\oracle\product\10.2.0\db_1\database\INITrockstd.ORA F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\PARAMETERFILES;
host copy E:\DATAFILES\*.DBF F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\DATAFILES;
host copy E:\REDOLOGS\*.LOG F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\REDOLOGS;
host copy E:\CONTROLFILES\*.CTL F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\CONTROLFILES;
host copy E:\ARCHIVES\*.ARC F:\ROCKSTD_Coldbackup\"%DATE:/=_%\ARCHIVELOGS;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
exit;
exit

Scripts..

2008-09-10T06:36:00.000-07:00

create user
------------
create user abc
identified by abc
default tablespace tbs1
temporary tablespace temp
quota unlimited on tbs1/

grant connect, create job, create operator, create snapshot, create procedure, create sequence, create synonym, create table, create trigger, create view, debug connect session to abc/
--------------------------------------------------------
only a DBA can import a file exported by another DBA
--------------------------------------------------------
C:\Documents and Settings\Administrator>imp userid=system file=E:\exp_full_db_Sat_26_04_2008_1100PM.dmp fromuser=sa_test touser=sa_test

H:\>imp userid=system@testapp file=D:\abamco_amc.DMP fromuser=abamco_amc touser=amcerptest
--------------------------
FULL_DB_EXPORT.bat
--------------------------
@echo off
for /f "tokens=1,2,3,4 delims=/ " %%a in ('date /t') do set fdate=%%a_%%c_%%b_%%d

for /f "tokens=1,2,3,4,5,6 delims=: " %%i in ('time /t') do set HHMMSS=_%%i%%j%%k%%l%%m%%n

exp system/******* file=D:\Export\exp_full_db_%fdate%%HHMMSS%.dmp full=y log=D:\Export\exp_full_db_%fdate%%HHMMSS%.log
--------------
HOT_BACKUP.bat
--------------
cd D:\oracle\product\10.2.0\db_1\BIN
sqlplus system/***** @F:\HOT_BACKUP\hotbackup.sql
-------------------
HOt Backup.sql
-------------------
host MD F:\HOT_BACKUP\"%DATE:/=_%"
host MD F:\HOT_BACKUP\"%DATE:/=_%"\ARCHIVELOGS
alter tablespace SYSTEM begin backup;
host copy E:\DATAFILES\SYSTEM01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\DATAFILES\SYSTEM02.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace SYSTEM end backup;
alter tablespace UNDOTBS1 begin backup;
host copy E:\DATAFILES\UNDOTBS01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX begin backup;
host copy E:\DATAFILES\SYSAUX01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace SYSAUX end backup;
alter tablespace USERS begin backup;
host copy E:\DATAFILES\USERS01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace USERS end backup;
alter tablespace ABCD begin backup;
host copy E:\DATAFILES\ABCD01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\DATAFILES\ABCD02.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace ABCD end backup;
alter tablespace AXIS_ALERT begin backup;
host copy E:\DATAFILES\AXIS_ALERT01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace AXIS_ALERT end backup;
alter tablespace ERP begin backup;
host copy E:\DATAFILES\ERP01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace ERP end backup;
host copy D:\oracle\product\10.2.0\db_1\database\INITDB.ora
F:\HOT_BACKUP\"%DATE:/=_%;
host copy D:\oracle\product\10.2.0\db_1\database\PWDDB.ora
F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\REDOLOGS\REDO01.LOG F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\REDOLOGS\REDO02.LOG F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\REDOLOGS\REDO03.LOG F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\CONTROLFILES\CONTROL01.CTL F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\CONTROLFILES\CONTROL02.CTL F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\CONTROLFILES\CONTROL03.CTL F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\ARCHIVE\*.ARC F:\HOT_BACKUP\"%DATE:/=_%\ARCHIVELOGS
exit;
exit

----------------
verify_db.bat
---------------
cd D:\oracle\product\10.2.0\db_1\BIN

sqlplus "sys/******@DBSTD as sysdba" @D:\verifyDBSTD.sql
---------------
verifyDBSTD.sql
------------
col name format a70

set pages 0

set line 3000

spool verifyDBSTD.txt

select name, applied, to_char(completion_time, 'fmDD-MON-YYYY HH:MI:SS PM') from v$archived_log where completion_time >= '24-MAR-2009' order by name desc;

select process, status from v$managed_standby;

spool off

exit;
---------------------
RMAN_BACKUP.bat
---------------
cd D:\oracle\product\10.2.0\db_1\BIN\

rman target sys/rock@rock cmdfile c:\rman_backup.rcv log c:\rman_log.txt
-------------------
c:\rman_backup.rcv
-------------------
backup as compressed backupset incremental level 0 database plus archivelog;
restore database validate;
exit;
--------------------------
RMAN_BACKUP_Cumulative.bat
--------------------------
backup as compressed backupset incremental level 1 cumulative database plus archivelog;
exit;
---------------------------------
forums.oracle.com
Thread: Incremental Backups are Cumulative Vs Differential??
-------------------------------------------------------
The followings are Weekly Full Backup and Daily Backup scripts:
--------------------
Weekly Full Backup
--------------------
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
allocate channel ch1 type disk format
'/u02/db/backup/RMAN/backup_%d_%t_%s_%p_%U.bck';
backup incremental level 0 database plus archivelog delete all input;backup current controlfile;
backup spfile;
release channel ch1;
}
-----------------
Daily Backup:
-----------------
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
allocate channel ch1 type disk format '/u02/db/BACKUP/RMAN/backup_%d_%t_%s_%p_%U.bck';
backup incremental level 1 cumulative database plus archivelog delete all input;delete noprompt obsolete;
delete noprompt archivelog all backed up 2 times to disk;
backup current controlfile;
backup spfile;
release channel ch1;
}

Failover Steps

2009-06-01T01:36:00.000-07:00

Failover:
A failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss.

We performed a failover in our Disastor Recovery scenario. Where the primary database was taken offline (as unavialble), and standby database was acticated as Primary Database.

The Standby database will now be activated as the new Primary database.

SQL> conn sys/****@rockstd as sysdba
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;

Switchover Steps

2009-05-29T00:24:00.000-07:00

Once the standby database is setup using Data Guard and is configured properly, we should test switchover and failover scenarios to be better prepared for a real life disaster situation.

A switchover allows the primary database to switch roles with its standby database. There is no data loss during a switchover. We can switch back to the original Primary database later by performing another switchover. A switchover is performed on a database when the need arises to upgrade or change configuration settings on the Primary database. The Standby database can temporarily be used as the Primary database for zero downtime while the actual Primary database is being upgraded or changed.

Switchover Configuration:
--------------------------
Database Role---------Service-----
Primary-------------ROCKPRIM
Standby-------------ROCKSTD

Before Switchover:
--------------------------

1. Verify the primary database instance is open and the standby database instance is mounted.
2. Verify there are no active users connected to the databases, if there are active sessions then kill active sessions.
3. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

select sequence#, applied from v$archvied_log; or select max(sequence#)
from v$archived_log where applied = ‘YES’;

4. Make sure that log_archive_dest_2 entry is specified in both databases.

Switchover Steps:
------------------
1. Initiate the switchover on the primary database ROCKPRIM:

SQL> conn sys/****@ROCKPRIM as sysdba
SQL> alter database commit to switchover to physical standby with session shutdown;

2. After step 1 finishes, Switch the original physical standby database ROCKSTD to primary role:

SQL> conn sys/****@ROCKSTD as sysdba
SQL> alter database commit to switchover to primary;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

3. Immediately after issuing command in step 2. Shut down and restart the former primary instance ROCKPRIM:

SQL> conn sys/****@ROCKPRIM as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY

* Connect at new primary database ROCKSTD, and perform a SWITCH LOGFILE to start sending redo data to the standby database ROCK.

TDE - Transparent Data Encryption

2009-05-19T02:25:00.000-07:00

Encrypt sensitive data transparently without writing a single line of code.

It's your organization's worst nightmare: Someone has stolen backup tapes of your database. Sure, you built a secure system, encrypted the most sensitive assets, and built a firewall around the database servers. But the thief took the easy approach: He took the backup tapes, ostensibly to restore your database on a different server, start the database on it, and then browse the data at his leisure. Protecting the database data from such theft is not just good practice; it's a requirement for compliance with most laws, regulations, and guidelines. How can you protect your database from this vulnerability?

- Encrypt the sensitive data and store encryption key in a separate location called wallet.
- Without the keys stolen data is worthless.
- Define a column as encrypted.
- When user insert the data, the database transparently encrypts it and stores in the column, similarly when users select the column the database automatically decrypts it.
- Backups and archive logs are also in encrypted format.
- If the data on the disk is stolen, it can't be retrieved without master key, which is in the wallet not part of the stolen data.
- Even if the wallet is stolen, the master key can't be retrieved from it without the wallet password.

How does it work :
1. Specify wallet location in sqlnet.ora D:\oracle\product\10.2.0\admin\rock and place following lines:
ENCRYPTION_WALLET_LOCATION =(SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=D:\oracle\product\10.2.0\admin\rock)))

2. Create the wallet
Now you must create the wallet and set the password to access it. To do this, issue the following as a SYS user with the ALTER SYSTEM privilege:
SQL> alter system set encryption key authenticated by "soni";
System altered.

This command
-Creates the wallet in the location specified in Step 1
-Sets the password of the wallet as "soni"
-Opens the wallet for TDE to store and retrieve the master key

- Restart the database

Every time you open the database, you'll have to open the wallet using the same password as follows:
SQL> alter system set encryption wallet open authenticated by "soni";
System altered.

You can close the wallet like this:
alter system set encryption wallet close;

The wallet must be open for TDE to work. If the wallet is closed, you can access all nonencrypted columns, but not encrypted columns.

On a regular schema, suppose you have a table of account holders as follows:

SQL> conn hr/hr@rock
Connected.

SQL>
create table accounts
(
ACC_NO NUMBER(2),
ACC_NAME VARCHAR2(30),
SSN VARCHAR2(9)
)
/

Currently, the table has all data in clear text. You want to convert the column SSN, which holds the Social Security Number, to be stored as encrypted. You can issue

alter table accounts modify (ssn encrypt);

SQL> desc accounts;

Name Null? Type

-------------------------- -------- ----------------------------
ACC_NO NUMBER(2)
ACC_NAME VARCHAR2(30)
SSN VARCHAR2(9) ENCRYPT

This statement does two things:
-It creates an encryption key for the table. If you change another column in the same table to use the encrypted format, the same table key will be used.
-It converts all values in the column to encrypted format.

Using Data Pump with TDE
By default, if you use the Data Pump export utility (EXPDP) to export data from a table with encrypted columns, the data in the resulting dump file will be in clear text, even the encrypted column data. The following command exports the ACCOUNTS table—with its encrypted columns—and returns a warning:

SQL> conn sys/rock@rock as sysdba
Connected.
SQL>

SQL> create or replace directory expdp as 'D:\expdp_dir';
Directory created.
SQL>

SQL> grant read, write on directory expdp to hr;
Grant succeeded.
SQL>

H:\>expdp hr/hr@rock directory=expdp tables=accounts
Export: Release 10.2.0.1.0 - Production on Wednesday, 20 May, 2009 9:27:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@rock directory=expdp tables=accountsEstimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE. . exported "HR"."ACCOUNTS" 5.585 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is: D:\EXPDP_DIR\EXPDAT.DMP
Job "HR"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 09:27:48
H:\>

This is just a warning, not an error; the rows will still be exported.
To protect your encrypted column data in the data pump dump files, you can password-protect your dump file when exporting the table. This password, specified as an ENCRYPTION_PASSWORD parameter in the EXPDP command, applies to this export process only; this is not the password of the wallet.

SQL> grant read, write on directory expdp to soni;
Grant succeeded.

H:\>expdp hr/hr@rock directory=expdp encryption_password=soni tables=accounts

H:\>impdp hr/hr@rock directory=expdp encryption_password=soni tables=accounts

-----------------------------------------------------------------------------------------------
for more details check the link below :

http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html

AUTOTRACE and TKPROF

2009-05-12T20:44:00.000-07:00

As we have seen the result of select count(*) from cat; with TKPROF utility http://rakeshocp.blogspot.com/2009/05/tkprof.html now we will compare the result with autotrace.

SQL> show user
USER is "HR"
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn sys/rock@rock as sysdba
Connected.

SQL> grant plustrace to hr;
grant plustrace to hr
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> conn sys/rock@rock as sysdba
Connected.
SQL> @D:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;
Role created.
SQL>SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL>
SQL> grant plustrace to hr;
Grant succeeded.
SQL> show userUSER is "SYS"
SQL> conn hr/hr@rock
Connected.
SQL> set autotrace on
SQL>

SQL> select count(*) from cat;
COUNT(*)
----------
21

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view

SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
333 recursive calls
0 db block gets
1060 consistent gets
7 physical reads
0 redo size
411 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

You don’t need any special system privileges in order to use the EXPLAIN PLAN statement. However, you do need to have INSERT privileges on the plan table, and you must have sufficient privileges to execute the statement you are trying to explain. The one difference is that in order to explain a statement that involves views, you must have privileges on all of the tables that make up the view. If you don’t, you’ll get an “ORA-01039: insufficient privileges on underlying objects of the view” error.
http://www.dbspecialists.com/files/presentations/use_explain.html

TKPROF

2009-05-11T21:53:00.000-07:00

TKPROF stands for Transient Kernel PROFiler.

The TKPROF program converts Oracle trace files into a more readable form. If you have a problem query you can use TKPROF to get more information. To get the most out of the utility you must enable timed statistics by setting the init.ora parameter or performing the following command:

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
System altered.

SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql;
Table created.

SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
Synonym created.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;
Grant succeeded.

SQL> conn hr/hr@rock
Connected.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
SQL> select count(*) from cat;
COUNT(*)
----------
20

SQL> ALTER SESSION SET SQL_TRACE = FALSE;
Session altered.

A trace file will be created at D:\oracle\product\10.2.0\admin\rock\udump, the new trace file is rock_ora_4844.trc. This can then be interpreted using TKPROF at the commmand prompt as follows:

H:\>tkprof D:\oracle\product\10.2.0\admin\rock\udump\rock_ora_4844.trc D:\oracle\product\10.2.0\admin\rock\udump\hr.txt explain=hr/hr@rock table=sys.plan_table

The resulting output file contains the following type of information for all SQL statements processed, including the ALTER SESSION commands:

----------------------------------------------------------------------------------------
TKPROF: Release 10.2.0.1.0 - Production on Tue May 12 11:38:44 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: D:\oracle\product\10.2.0\admin\rock\udump\rock_ora_4844.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call -----count---cpu---elapsed---disk-----query----current---- rows--
------- ------ -------- ---------- --------- ---------- ---------- ----------
Parse----0------0.00----0.00-------0-------0---------0----------0-----
Execute--1------0.00----0.00-------0-------0---------0----------0-----
Fetch----0------0.00----0.00-------0-------0---------0----------0-----
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total-----1-------0.00----0.00-------0------0----------0----------0-----

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 169 (HR)
********************************************************************************
select text
from view$
where rowid=:1

call -----count---cpu---elapsed---disk-----query----current---- rows--
------- ------ -------- ---------- --------- ---------- ---------- ---------
Parse----1-----0.00----0.00------0-------0----------0---------0-----
Execute--1-----0.00----0.00------0-------0----------0---------0-----
Fetch----1-----0.00----0.00------ 0-------2----------0---------1-----
------- ------ -------- ---------- ---------- ---------- ---------- --------
total-----3-----0.00----0.00-------0-------2----------0---------1-----

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=22 us)
********************************************************************************
select count(*)
from cat

call -----count---cpu---elapsed---disk-----query----current---- rows--
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse-----1-----0.00---0.06------0--------0---------0---------0--------
Execute--1------0.00---0.00------0--------0---------0---------0--------
Fetch----2------0.00---0.01------0---------968------0----------1--------
------- ------ -------- ---------- ---------- ---------- ---------- -----------
total-----4------0.00----0.07-----0---------968------0----------1--------
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 169 (HR)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=968 pr=0 pw=0 time=10364 us)
20 FILTER (cr=968 pr=0 pw=0 time=10117 us)
45 TABLE ACCESS FULL OBJ$ (cr=920 pr=0 pw=0 time=9744 us)
0 TABLE ACCESS CLUSTER TAB$ (cr=48 pr=0 pw=0 time=266 us)
16 INDEX UNIQUE SCAN I_OBJ# (cr=32 pr=0 pw=0 time=126 us)(object id 3)

error during execute of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 84
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call -----count---cpu---elapsed---disk-----query----current---- rows--
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse-----1------0.00---0.00-----0--------0---------0----------0------
Execute---1------0.00---0.00-----0--------0---------0----------0------
Fetch-----0------0.00---0.00-----0--------0---------0----------0------
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total------2-------0.00---0.00-----0-------0---------0----------0-------
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 169 (HR)

********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call -----count---cpu---elapsed---disk-----query----current---- rows--
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse----2-------0.00---0.06-----0--------0----------0--------0-------
Execute--3-------0.00---0.00-----0--------0----------0--------0-------
Fetch-----2-------0.00---0.01-----0--------968-------0---------1------
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total------7--------0.00---0.07----0--------968--------0--------1-------

Misses in library cache during parse: 2
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call -----count---cpu---elapsed---disk-----query----current---- rows--
------- ------ -------- ---------- -------- ---------- -------- ----------
Parse----1------0.00----0.00-----0--------0---------0----------0----
Execute--1----- 0.00----0.00-----0--------0---------0----------0----
Fetch----1------0.00----0.00-----0--------2---------0----------1-----
------- ------ -------- ---------- ---------- ---------- ------- ----------
total-----3------0.00----0.00-----0--------2---------0----------1------

Misses in library cache during parse: 1
Misses in library cache during execute: 1
3 user SQL statements in session.
1 internal SQL statements in session.
4 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: D:\oracle\product\10.2.0\admin\rock\udump\rock_ora_4844.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
1 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
56 lines in trace file.
7 elapsed seconds in trace file.

----------------------------------------------------------------------------------------
http://www.oracle-base.com/articles/8i/TKPROFAndOracleTrace.php

tuning SQL statement...AUTOTRACE

2009-05-11T01:56:00.000-07:00

I am trying to use autotrace utility to check the execution plan of table, and later I will create index to check whether the performance is improved.

SQL> conn sys/rock@rock as sysdba
Connected.
SQL> create table test as select * from all_objects;
Table created.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname=>'TEST',force=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> select count (*) from test;
COUNT(*)
----------
49771

SQL> desc test
Name
------------------------------------------------------------------
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
SQL>
SQL> select count (distinct(data_object_id)) from test;
COUNT(DISTINCT(DATA_OBJECT_ID))
-------------------------------
3504

SQL> autotrace on;

SQL> select * from test where data_object_id=151596;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------
RMAN2 TF_P 151596

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 93 154 (2) 00:00:02
* 1 TABLE ACCESS FULL TEST 1 93 154 (2) 00:00:02
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA_OBJECT_ID"=151596)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
1202 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> create index test_data_idx on test(data_object_id)
Index created.
SQL> select * from test where data_object_id=151596;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------
RMAN2 TF_P 151596

Execution Plan
----------------------------------------------------------
Plan hash value: 2271528063
---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 93 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 1 93 2 (0) 00:00:01
* 2 INDEX RANGE SCAN TEST_DATA_IDX 1 1 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=151596)

Statistics
----------------------------------------------------------
149 recursive calls
0 db block gets
28 consistent gets
1 physical reads
0 redo size
1206 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory) 0 sorts (disk)
1 rows processed

Conclusion: By creating index the cost of CPU is improved from 154% to 2 %, because the row was accessed by INDEX ROWID.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Explain plan Vs AUTOTRACE
http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php

Oracle Tuning..

2009-04-10T05:30:00.000-07:00

Oracle top down tuning tips
http://www.dba-oracle.com/art_tuning1.htm
Oracle Database Performance Tuning FAQs http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

Oracle Tuning Tips
http://it.toolbox.com/blogs/all-about-dev/oracle-tuning-tips-8151
What's New in Oracle Performance? (10g)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/whatsnew.htm

AWR - Automatic Workload Repository

Learn to use the new feature that collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information When you have a database performance problem, what is the first thing you do to address it? One common approach is to see if a pattern exists: Answering questions such as "Is the same problem recurrent?", "Does it occur during a specific time period?", and "Is there a link between two problems?" will almost always lead to a better diagnosis.

As a DBA you probably have invested in a third-party or homegrown tool to collect elaborate statistics during database operation and derive performance metrics from them. In a crisis, you access those metrics for comparisons to the present. Replaying these past events can shed light on current problems, so continuously capturing relevant statistics becomes important for performance analysis. For some time, Oracle's solution in this area has been its built-in tool, Statspack. While it can prove invaluable in certain cases, it often lacks the robustness required by performance troubleshooting exercises. Oracle Database 10g offers a significant improvement: the Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well.

http://www.oracle.com/technology/pub/articles/10gdba/week6_10gdba.html

Database General - Oracle Performance Tuning – Part 1
http://www.databasejournal.com/features/oracle/article.php/3548291/Oracle-Performance-Tuning--Part-1.htm

kill session -

2009-04-28T00:41:00.000-07:00

Killing session - I want to kill all sessions of DB user abm_test.

alter system kill session 'session-id,session-serial' immediate;
This command kills a session. The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).

SQL> show user
USER is "SYS"
SQL>

SQL> select * from v$session
where username = 'ABM_TEST';

SQL> select SID, SERIAL#, USERNAME, osuser, PROGRAM from v$session where username = 'ABM_TEST';

SID SERIAL# USERNAME OSUSER PROGRAM
-------- --------- --------------------- --------------------------- -------------
184 54 ABM_TEST rakesh.kumar sqlplus.exe

SQL> alter system kill session '184,54' immediate;
System altered.
SQL>

Auto start of cmd file after Machine Reboot

2009-04-26T23:28:00.000-07:00

Yesterday, Systems department has restarted the machine after their activity, Databases restarted automatically but there are some other batch files & Authentication servers exe file which needs to be run for ERP connectivity. So I have configured for auto execution of batch files whenever the Machine is restarted, check the below steps.

run >
regedit
HKEY_LOCAL_MACHINE>
SOFTWARE>
MICROSOFT>
WINDOWS>
CURRENVERSION>
RUN> Then right click new>
string value >
assigned a name to file,
specified complete path of cmd file in double quotes.

DONE

RMAN - Recovery Catalog

2009-04-23T03:01:00.001-07:00

I am trying to configure RMAN Catalog..
target db in linux = db name rock, db id 3304705878
recovery cat in xp = db name rock db id 3255117147 (rocklinux in tns entry for accessing Linux Dabaase)
Enter user-name: sys@rock as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

1. sql> create tablespace rman_ts
datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\rman_01.dbf' size 20m
autoextend on

2. create user rman1
identified by rman1
quota unlimited on rman_tsd
efault tablespace rman_ts;

3.SQL> grant recovery_catalog_owner to rman1;
Grant succeeded.
SQL> grant connect, resource to rman1;
Grant succeeded.

H:\>rman catalog rman1/rman1@rock log=D:\catalog.log
RMAN> create catalog tablespace rman_ts;
RMAN> exit

H:\>rman target sys/rock@rocklinux

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 22 12:49:23 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ROCK (DBID=3304705878)

RMAN> connect catalog rman1/rman1@rock

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

SQL> conn rman1/rman1@rock
Connected.
SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 3304705878 ROCK 516893 20-APR-09

Oracle 10.2 - (RMAN + SYS user Audit_Trail = XML, Extended) bug

2009-04-07T01:59:00.000-07:00

After enabling auditing for sys users, as discussed in my below post.
http://rakeshocp.blogspot.com/2008/12/auditing-sys-user-in-10g.html
When we are trying to connect database with RMAN utility, it fails and gives below error.

H:\>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 7 14:59:17 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target sys/rock@rock

RMAN-00571: =======================================================
RMAN-00569: ============= ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: =======================================================
ORA-09817: Write to audit file failed.
RMAN>

I got to know its bug in Oracle 10g Release 10.2.0.1.0. Check my post at oracle forums .
http://forums.oracle.com/forums/message.jspa?messageID=3282187#3282187

...

Undo tablespace issue....

2009-04-02T23:28:00.000-07:00

There were some issues in HARD Diskk of Test Machine, and systems department wanted to format/replace the hard disk (F Drive, where Oracle is installed). So I closed the test database, and later systems departmentd copied all folders, including Oracle folder, where all datafiles, control files, redo logs are stored. They restored all folders after completing their activity.

After that the systems department wanted the Database team to verify whether the Oracle is working properly or not. When we started Oracle services, and Oracle Database, it was automatically shuting down the instance, after checking the alert.log file we got below error:

DEBUG: Replaying xcb 0x4e7d126c, pmd 0x4deeb4b8 for failed op 8
Errors in file f:\oracle\product\10.2.0\admin\icpora\udump\icpora_ora_4192.trc:
ORA-00600: internal error code, arguments: [4194], [49], [44], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [49], [44], [], [], [], [], []

Metalink says:

A mismatch has been detected between Redo records and rollback (Undo) records.

We are validating the Undo record number relating to the change being applied againstthe maximum undo record number recorded in the undo block.This error is reported when the validation fails.

ARGUMENTS:

Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block

IMPACT:PROCESS FAILUREPOSSIBLE ROLLBACK SEGMENT CORRUPTION

SUGGESTIONS:This error may indicate a rollback segment corruption.This may require a recovery from a database backup depending on the situation.

We deleted untobs01.dbf and created undotbs2 tablespace undotbs02.dbf

1. alter database datafile '/home/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
2. create undo tablespace undotbs2 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 1024m;
3. ALTER SYSTEM SET undo_tablespace='UNDOTBS2'

The system started working properly, the Full Database export batch file runs in nights, when we checked the log of export file it indicated below error:

EXP-00008: ORACLE error 376 encountered
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ICPORA\UNDOTBS01.DBF'
EXP-00000: Export terminated unsuccessfully

when we checked the v$recover_file, data file 2 is not found for recovery. so when we issued the drop tablespace undotbs1 command, we got error

ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU01$’ found, terminate dropping tablespace

select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU01$ NEEDS RECOVERY UNDOTBS1
_SYSSMU02$ NEEDS RECOVERY UNDOTBS1
_SYSSMU03$ NEEDS RECOVERY UNDOTBS1
_SYSSMU04$ NEEDS RECOVERY UNDOTBS1
_SYSSMU05$ NEEDS RECOVERY UNDOTBS1
_SYSSMU06$ NEEDS RECOVERY UNDOTBS1
_SYSSMU07$ NEEDS RECOVERY UNDOTBS1
_SYSSMU08$ NEEDS RECOVERY UNDOTBS1

We closed the, database edited init.ora file and added parameters (comment undo_management=auto and entry for corrupted_rollback_segments
#undo_management=AUTO
undo_tablespace=UNDOTBS2
_corrupted_rollback_segments =('_SYSSMU01$','_SYSSMU02$','_SYSSMU03$','_SYSSMU04$','_SYSSMU05$','_SYSSMU06$','_SYSSMU07$','_SYSSMU08$'

1. STARTUP RESTRICT MOUNT pfile=C:\Oracle\init.ora
2. Drop rollback segment "_SYSSMU01$"
3. Droped all segments one by one that we entered in initfile. (step 2 drop command)
4. drop TABLESPACE UNDOTBS1;
5. shutdown
6. deleted entry of _corrupted_rollback_segments and removed comment from undo_management
7. STARTUP MOUNT pfile=C:\Oracle\init.ora8 and later opened the database.
8. Taken Full database export, it was successfully exported.

Speciall thanks to the writer/owner of below page, It helped me alot.
http://www.my-whiteboard.com/oracle-dba/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files.html

Scenarios (My topics at Oracle Forums)

2007-11-07T21:34:00.000-08:00

My database in ArchiveLog Mode and I created a table TEST, Later I made the whole database backup (RMAN Full DB backup). After that, I deleted the table TEST, and restored from full database backup. Surprisingly, there wasn't table TEST. Why?

Read at oracle forums, and how experts helped me in recovering TEST table.

http://forums.oracle.com/forums/thread.jspa?threadID=581258&start=0&tstart=0

-----------------------------------------------------------------------------------------------------------

Recovering dropped table from Backup. (User Managed Backup & Recovery)

http://forums.oracle.com/forums/thread.jspa?threadID=584733&tstart=0

Lost all Online Redo Log Files.

http://forums.oracle.com/forums/message.jspa?messageID=3134695#3134695

---------------------------------------------
Loss of one data file and all controlfile
So If datafile and all controlfiles are lost, then we can recover the controlfile and database though we can not recover the lost datafile. ( HOT BACKUP Scenario)

http://forums.oracle.com/forums/thread.jspa?threadID=872397&tstart=0

(To be Continued)

creating new database from old database cold backup.

2009-02-18T03:58:00.000-08:00

I am writing below steps that will create a new database ICPORA that will be a complete replica of old database ICPORA.

Scenario: I have new database ROCK and old database ICPORA, I have cold backup of ICPORA, and Now I want recreate ICPORA at ROCK database. I have taken backup of both database.

1. Copy all datafiles, controlfiles, redologs, archivelogs, init files from ICPORA to new database ROCK (oradata). Delete all files from ROCK database after backup and paste ICPORA files.

2. Check init.ora file and modify/specify the correct paths for ArchiveLog, Controlfiles and other files.

3. If you have new database with the old DB name then go to step 5 otherwise step 4. In my situation I have new database with name ROCK, so I need ICPORA service/instance to operate, for that I need to create new service ICPORA.

4. create new service with old database name with ORADIM utility.

H:\>oradim -new -sid icpora -intpwd icpora -startmode m -pfile 'D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA'

Instance created.

5.
H:\>set oracle_sid=icpora

H:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 18 15:19:02 2009
Copyright (c) 1982

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes

SQL> startup mount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\DATAFILES\SYSTEM01.DBF'

Note: The above error is encountered because the contents/information stored in the control file is different from the current situation. (i.e paths of files). We need to recreate the controlfile, the below command will generate a script for recreating a controlfile in udump folder.

SQL> alter database backup controlfile to trace;
Database altered.

6. Copy the important contents of script generated in udump folder and modify with new paths and execute it. Note execute in NOMOUNT Mode.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ICPORA" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 18688
7 LOGFILE
8 GROUP 1 'D:\oracle\product\10.2.0\oradata\icpora\REDO01.LOG' SIZE 100M,
9 GROUP 2 'D:\oracle\product\10.2.0\oradata\icpora\REDO02.LOG' SIZE 100M,
10 GROUP 3 'D:\oracle\product\10.2.0\oradata\icpora\REDO03.LOG' SIZE 100M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\oracle\product\10.2.0\oradata\icpora\SYSTEM01.DBF',
14 'D:\oracle\product\10.2.0\oradata\icpora\SYSTEM02.DBF',
15 'D:\oracle\product\10.2.0\oradata\icpora\SYSAUX01.DBF',
16 'D:\oracle\product\10.2.0\oradata\icpora\ABAMCO01.DBF',
17 'D:\oracle\product\10.2.0\oradata\icpora\ABAMCO02.DBF',
18 'D:\oracle\product\10.2.0\oradata\icpora\EDP01.DBF',
19 'D:\oracle\product\10.2.0\oradata\icpora\UNDOTBS2.DBF',
20 'D:\oracle\product\10.2.0\oradata\icpora\USERS01.DBF',
21 'D:\oracle\product\10.2.0\oradata\icpora\AXIS_ALERT01.DBF',
22 'D:\oracle\product\10.2.0\oradata\icpora\RISKMETER01.DBF'
23 CHARACTER SET WE8MSWIN1252
24 ;

Control file created.

SQL> RECOVER DATABASE
Media recovery complete.
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> alter database open;
Database altered.

Creating Stand By Database (Steps)

2007-12-04T22:50:00.000-08:00

I have created standby database with following steps:

You will also find Primary Database initfile and Standby Database initfile at the bottom of the page

PRIMARY DATABASE
------------------------------

1. Change initrock.ora file

*.log_archive_dest_1='LOCATION=D:\oracle\Archive_rock'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_2='SERVICE=ROCKSTD LGWR ASYNC'
*.log_archive_dest_state_2=ENABLE
*.fal_server='ROCKSTD'
*.fal_client='ROCK'
*.db_unique_name='rock'

2. Shutdown immediate;
2.1. Startup mount;
3. alter database create standby controlfile as 'd:\rockstd_control.ctl';
4. alter database open;

5.0 Shutdown immediate;
5.1 Startup Nomount;
5.2 Create Spfile from Pfile;
5.3 D:\oracle\product\10.2.0\db_1\database\SPFILEROCK.ORA, changes are recorded in current spfile and copy pfile from (D:\oracle\product\10.2.0\db_1\database) to standby database.

6. add at TNSNAMES.ora file

Rockstd is service name, which will be created at Standby database.
ABM-D0011.abamco.com is the hostname where standby database will be created.

rockstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ABM-D0011.abamco.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rockstd)
)
)

Copied all datafiles, archivelogFiles, redologFiles, ControlFile, Initfile to STAND BY Database...
-----------------------------------------------------------------------------------

STAND BY DATABASE
--------------------------

1. Added at TNSNAMES.ORA
Rock is the service name of Primary Database.
HOST = 10.142.192.28 is the IP address of Primary Database.

rock =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.142.192.28)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rock)
)
)

2. copied datafiles, redologs and rockstd_control at rock (new folder in OraData)

3. copy & paste initrock at D:\oracle\product\10.2.0\db_1\database and rename it to initrockstd.ora

4. check initrockstd file parameters and create folder (rock) and subfolders( adump, bdump, cdump, udump) in D:\oracle\product\10.2.0\admin

5. create folder (flash_recovery_area) at D:\oracle\product\10.2.0\rock\flash_recovery_area. the path is defined in parameter db_recovery_file_dest in initrockstd file.

6. intit file
*.fal_server='ROCK'
*.fal_client='ROCKSTD'
*.db_unique_name='rockstd'
*.standby_file_management=AUTO

7. Place the initrockstd file in oracle_home database folder.

8. REMOVE LOG_ARCHIVE_DEST_2 PARAMETER.

9. CHANGE LOG_ARCHIVE_DEST_1 PARAMETER and specify path for archived log file destination, copy all archives and paste at specified destination. MoreOver datafiles, redologs and control file must be in same specified path in Standby database as it has been specified at Primary Database.

10. create service rockstd using ORADIM Utility.

H:\>oradim -new -sid rockstd -syspwd rock -startmode m -pfile D:\oracle\product\10.2.0\db_1\database\initrockstd.ora
password should be same as primary sys password.

11. H:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 3 12:34:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys@rockstd as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 62915964 bytes
Database Buffers 96468992 bytes
Redo Buffers 7139328 bytes

SQL> alter database mount standby database;

Database altered.

12. Add additional Standby Redo logs

SQL> alter database add standby logfile 'D:\oracle\product\10.2.0\oradata\rock\REDO04.log' size 50m;

Database altered.

SQL> alter database add standby logfile 'D:\oracle\product\10.2.0\oradata\rock\REDO05.log' size 50m;

Database altered.

SQL> alter database add standby logfile 'D:\oracle\product\10.2.0\oradata\rock\REDO06.log' size 50m;

Database altered.

13. Start Recovery
alter database recover managed standby database disconnect from session;

14. Name and applied status of Archived.
select name, applied from v$archived_log;

15.
SQL> select process, status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
MRP0 WAIT_FOR_LOG

ARCH.... receives archives from primary db. (Maximum 30 can be enabled.)
RFS....... applies received archives to standby db.
MRP0....resolves gaps of archived b/w fal_server & fal_client.

16. check alert_rockstd at D:\oracle\product\10.2.0\admin\rock\bdump for alerts.
------------------------------------------------------------------------------------

Adding datafile or creating tablespace at Primary Database.
If you have not set the STANDBY_FILE_MANAGEMENT initialization parameter to auto, you must re-create the control file on the standby database.

http://download-uk.oracle.com/docs/cd/A97630_01/server.920/a96653/sbydb_manage_ps.htm

If you have created another tablespace or added datafile to an existing tablespace at Primary Database and didn't set the STANDBY_FILE_MANAGEMENT initialization parameter to auto Then Archived at Standby database will not apply, and obviously there will be a gap between Primary & StandBy database archivelogs.
I have added tablespace testing1 and added datafile testing1.dbf at Primary database. (Shown below at Primary Database Section)

When you query the select name from v$datafile, you will find unnamed00004 filename instead of testing1.dbf.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\ABAMCO_TEST01.DBF

6 rows selected.
We learn that datafiles are not copid at standby database, for that one need to manually copy the datafiles.

Following steps must be followed after adding tablespace/datafile at Primary Database.
----------------------------------------------------------------------------------------

PRIMARY Database.

1. You need to manually copy datafile & recreate controlfile, Copy and paste both files at Standby Database.

SQL> create tablespace testing1
2 datafile 'D:\oracle\product\10.2.0\oradata\rock\testing1.dbf' size 50m;

Tablespace created.

SQL> alter tablespace testing1 begin backup;

Tablespace altered.

Copy the testing1.dbf datafile at any location.

SQL> alter tablespace testing1 end backup;

Tablespace altered.

SQL> alter database create standby controlfile as 'd:\rockstd_control.ctl';

Database altered.

SQL>

----------------------------------
Standby Database

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

1. Copy and paste controlfile & datafile at D:\oracle\product\10.2.0\oradata\rock
2.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 62915964 bytes
Database Buffers 96468992 bytes
Redo Buffers 7139328 bytes

3.
SQL> alter database mount standby database;

Database altered.

4.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>
------------------------------------------------------

Primary Database initrock.ora (Initfile)
-------------------------------------------
rock.__db_cache_size=83886080
rock.__java_pool_size=4194304
rock.__large_pool_size=4194304
rock.__shared_pool_size=67108864
rock.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/rock/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/rock/bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0oradata\rock\control01.ctl',
'D:\oracle\product\10.2.0\oradata\rock\control02.ctl',
'D:\oracle\product\10.2.0\oradata\rock\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/rock/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='rock'
*.db_unique_name='rock'
*.db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rockXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/rock/udump'
*.log_archive_dest_1="location=D:\oracle\Archive_rock"
*.log_archive_format='arc_%s_%t_%r.arc'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_2='SERVICE=rockstd LGWR ASYNC'
*.log_archive_dest_state_2=ENABLE
*.fal_server='ROCKSTD'
*.fal_client='ROCK'
-------------------------------------------

Stnadby Database initrockstd.ora file
rock.__db_cache_size=83886080
rock.__java_pool_size=4194304
rock.__large_pool_size=4194304
rock.__shared_pool_size=67108864
rock.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/rock/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/rock/bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0oradata\rock\rockstd_control.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/rock/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='rock'
*.db_unique_name='rockstd'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\oradata\rock\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rockXDB)
'*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/rock/udump'
*.log_archive_dest_1="location=D:\oracle\product\10.2.0oradata\rock\archive"
*.log_archive_format='arc_%s_%t_%r.arc'
*.log_archive_dest_state_2=ENABLE
*.fal_server='ROCK'
*.fal_client='ROCKSTD'
*.standby_file_management=auto
--------------------------------------------------------------------------

Standby Database Maintenance

The Standby Database is maintained by setting the database in Recovery Mode. In this particular state the database is in a mount (not open) state and the database is allowed to receive the archived log files from the primary database and apply them for synchronization. In this mode the standby database is not open for any user connections and it only receives the archived logs from the primary database via the Log Transfer Services and applies the changes recorded in them through Log Apply Services.

In order to put the standby database in recover mode we issue the following commands:

--Mount the standby database and makes sure that it is not open for any user connections.
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

-- Put the standby database in recovery mode and ready to receive archived logs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The command above puts the database in recovery mode and ready to receive the archived logs from the primary database. It also ensures that and users cannot connect to the standby database and any previously connected users are disconnected.

The standby database can also be put into read only mode for reporting purposes. This is accomplished by issuing the commands:

-- Cancel the recovery mode and stop the reception of applied logs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Put the standby database in read only mode for reporting purposes.
SQL> ALTER DATABASE OPEN READ ONLY;

This process can also be reversed and the database can be put back into recovery mode by reissuing the command

-- Put the standby database back into recovery mode.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Switchover and Failover Operations

Standby Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a SWITCHOVER and can be performed using the following statements:

-- Connect to primary database and switchover to standby
SQL> CONNECT sys/password@ICPORA AS SYSDBA
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown the primary database
SQL> SHUTDOWN IMMEDIATE;

-- Mount the old primary database as the new standby database
SQL> STARTUP NOMOUNT PFILE=C:\oracle\ora92\database\initICPORA.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- Convert the standby database to a primary database
SQL> CONNECT sys/password@ICPSTD AS SYSDBA
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SQL> SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
SQL>STARTUP PFILE=C:\oracle\ora92\database\initICPSTD.ora
The SWITCHOVER operation is useful in scenarios where the primary database needs to be temporarily taken offline or unavailable for user sessions for maintenance purposes. Switchover is a temporary switch from the primary database to the standby database.
Standby Database Failover

A FAILOVER occurs when database failover causes a standby database to be converted to a primary database. This process is not temporary and the failing over to the standby database will cause the primary to be unavailable to be online again.

A FAILOVER operation is initiated by issuing the following commands:

-- Finish the recovery mode on the standby database
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

--Activate standby database as the new primary database
SQL>ALTER DATABASE ACTIVATE STANDBY DATABASE;

It is important to note that one should not FAILOVER to a standby database other than in an emergency, because the failover operation is an unplanned transition that may result in the loss of application data.

Once a FAILOVER operation is performed, there is no going back. This is because the original primary database is not operational anymore and the standby database that you fail over to the primary role is no longer capable of returning to being a standby database in the original configuration.

-------------
-------------

When there is Link down or Network Problem, then obviously it's gonna be gap between Primary and Standby Databases Archived logs, So there are two solutions for that:
1. Manuall resolve Gaps,
2. Automatically

1. Manual (Recommended for few logs)
Step.1: Manually copy all missing archived logs from Primary Database and paste at Standby Database Archived Log Folder. (Copy one file at a time Recommended)

Step.2: Register Archived logs at Standby Database (One by One)
1. Conn sys/password@standbydb as sysdba
2. Shutdown Immediate;
3. Startup nomount;
4. Alter database mount Standby Database;
5. alter database register logfile 'd:\abc\abc.log';
6. alter database recover managed standby database disconnect from session;

2. Automatic (Recommended by large number of logs)
The Production/Primary database must be taken offline, so its required that Automatic option should be used when there is NO or LESS activity on the Primary Database.

Step.1: Connect at Standby Database
conn sys/password@standbydb as sysdba
alter database recover managed standby database cancel;

Step.2: Connect at Primary Database
1. Shutown immediate;
2. Startup nomount;
3. Alter database mount;
4. Alter database open;

Step.3: Connect at Standby database
1. Alter database recover managed standby database parrallel 8 nodelay disconnect from session;

Oracle Interview Questions

2007-10-03T08:41:00.000-07:00

These are the Oracle DBA interview Questions that were asked to me for Fresh /Assitant Oracle DBA Job.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

1- You have written in your CV that As a DBA u can Test a Backup, How?
2- You have written in your CV, that you can monitor physical and logical backup, how?
3- You have written in your CV, that you can Rebuild Index to rectify segment fragmentation. (Coalesce)
4- You has written in your CV, that you can Automatic Schedule a logical backup. how?
5- HVM, when it will be decreased? How can I do it? (Truncate table)
6- Alert log file? What it contains?
7- Control file? What information it contains? When it is read?
8- I have lost my Control File & don’t have any Backup; Can I start DB & operate? How? (You create a control file in no mount mode.)
9- Mount- No Mount?
10- SGA? It’s Components?
11- Checkpoint? Why Checkpoint? Where it’s recorded? When it’s recorded?
12- LGWR? When it writes?
13- Duties of DBA?
14- What’s Statspack? How can I use Statspack?
15- Methods of Backup – (Cold & Hot Backup)?
16 -Why do you want to be DBA? Why Not a Developer?
17- What is RECOVERY Catalog? Why we need it? Complete command/steps of creating Recovery Catalog? How will it know about the Primary Database? -What role/Privileges are given to user when he is connected to Recovery Catalog? -How can I connect with RMAN? It’s Steps?
18- RMAN Incremental Backups? What are Differential & Cumulative Backups?
19- Write a statement/command for exporting all the objects of owner ‘HR’?
20- Direct=y
21- Standby Databases?
22- RAC?
23- Partitioning (List, Range, Hash)
24- If my application is slow, how can we make it efficient, faster? -How will you tune if application is slow?
25- RMAN Backups?
26- ADDM.
27- AWR.
28- Flashback? What is flashback in 10g?
29- What is command for restoring from recycle bin?

2nd interview.
30- Difference between User & Schema?
31- Cluster key? 32- RAC?
33- Stand By database?
33- What are duties of DBA?
34- What is Difference among NOMOUNT & MOUNT & OPEN modes? When Database will be available for read?
35- What is SGA? And what are its components?
36- How can we allocate SGA? How we can know it proper size? How should we estimate? How can we calculate size of SGA?
37- If database is running 24 hours a day, 7 days a week when I come to office in morning what should I do Check? What files, views, or Services should I check?
38- How can you perform Fragmentation in tables?
39-Suppose, if I export table data and then import? Does it perform fragmentation?
40- Interviewer Asked from me:-”Tell me if you are DBA, u left the office at night, and when u arrive at office in the morning”, what you will check in database?
41- How can you schedule a job?
42- RAID?
43- New feature of 10g.
44- What is Database? RDBMS? Rules?

HR Interview Questions?
1. Tell me more about yourself.
2. Why did you choose this career?
3. Why do you want to work here at ABC Inc.?
4. What would you say is your strength? Your weakness?
5. What did you learn from your last job/internship/educational experience?
7. Why do you want this job?

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
1- You have written in your CV that As a DBA u can Test a Backup, How?
Regularly test the backup & recovery srtategy, becaue it shows the integrity of the backups, the validity of the backup and recovery method, and the reduction of the problems before the occur in a production database.

Testing the backup and recovery plan ensures:
<> That the backup and recovery methods are sound
<> Integrity of backups
<> Ensures that the backup and recovery strategy meets business needs.
<> It minimizes problems before they occur in a production environment.
<> It ensures that personnel can react quickly and effectively in case any errors arise, avoiding a crisis situation.

2- You have written in your CV, that you can monitor physical and logical backup, how?
http://www.oracle.com/technology/deploy/availability/htdocs/BR_Overview.htm
A backup is a representative copy of data. This copy can include important parts of a database such as the control file, redo logs, and datafiles. A backup protects data from application error and acts as a safeguard against unexpected data loss, by providing a way to restore original data. Backups are divided into physical backups and logical backups. Physical backups are copies of physical database files. The phrase "backup and recovery" usually refers to the transfer of copied files from one location to another, along with the various operations performed on these files.
In contrast, logical backups contain data that is exported using SQL commands and stored in a binary file. Oracle records both committed and uncommitted changes in redo log buffers. Logical backups are used to supplement physical backups. Restoring a physical backup means reconstructing it and making it available to the Oracle server. To recover a restored backup, data is updated using redo records from the transaction log. The transaction log records changes made to the database after the backup was taken.
http://www.oracle.com/technology/deploy/availability/htdocs/BR_Overview.htm

Types of Backup
There are two kinds of database backups available to us physical backups and logical backups.
Physical Backup is an actual physical copy of the files of the database copied from one location to another.
Logical Backup is a copy of the data in the database but not a copy of the physical files.

Cold Backup
A cold backup is a physical backup.
During a cold backup the database is closed and not available to users. All files of the database are copied (image copy). The datafiles do not change during the copy so the database is in sync upon restore.
Used when: Service level allows for some down time for backup

Hot Backup
A hot backup is a physical backup. In a hot backup the database remains open and available to users. All files of the database are copied (image copy). There may be changes to the database as the copy is made and so all log files of changes being made during the backup must be saved too. Upon a restore, the changes in the log files are reapplied to bring the database in sync.
Used when: A full backup of a database is needed Service level allows no down time for the backup

Logical Backup
A logical backup is an extract of the database. All SQL statements to create the objects and all SQL statements to populate the objects are included in the extract. Oracle provides a utility export, to create the extract. A partner utility, import, is used to bring the data back into the database.
A logical backup can be done at the table, schema(or proxy owner), or database level. That is, we can extract only a list of specified tables, a list of specified schemas or the full database.

Used to:
Move or archive a database
Move or archive a table(s)
Move or archive a schema(s)
Verify the structures in the database.

3- You have written in your CV, that you can Rebuild Index to rectify segment fragmentation. (Coalesce)
Index: An index is a tree structure that allows direct access to a row in table.
Rebuild: An index contains deleted entries and should be rebuild, such a case of index on Order number of an Orders tables, where completed orders are deleted & new orders with higher numbers are added.

ALTER INDEX orders_index REBUILD
TABLESPACE index02;

<> Above command moves an index to a different TableSpace.
<> Improve space utilization by removing deleted entries.

COALESCE: If you encounter index fragmentation then you can rebuild or coalesce. Coalescing an index is a block rebuild that is performed online.
I.e. Merging B-tree index leaf blocks that can be freed for reuse.

ALTER INDEX hr.employees_idx COALESE;

----------------------------------------------------------
When one must rebuild Indexes, read the at the forums.oracle.com at below thread.
http://forums.oracle.com/forums/thread.jspa?threadID=580451&tstart=0

4- You has written in your CV, that you can Automatic Schedule a logical backup. how?
http://rakeshocp.blogspot.com/2007/09/batch-file-scripts.html
Visit above blog page for Automatic Scheduling of logical backup.

5- HVM, when it will be decreased? How can I do it? (Truncate table)
High water mark: The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

The high water mark is divides a segment into used blocks free blocks
Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted.
Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan.
Oracle keeps track of the high water mark for a segment in the segment header.
Moving the high water mark
In normal DB operations, the high water mark only moves upwards, not downwards. The exceptions being the truncate.
If there is a lot of free space below the high water mark, one might consider to use alter table move statements. See On shrinking table sizes.
http://www.adp-gmbh.ch/blog/2005/july/20.html

truncate table table_name;
truncate cluster cluster_name;

A statement like delete from tablename deletes all records in the table, but it does not free any space (see On table sizes). In order to free the space as well, use truncate. However, a truncate can not be rolled back.
Truncate Table: Basically, a truncate statement resets the high water mark to its initial position.
A truncate statement cannot be used on a synonym.

Ways for reseting high water mark.
- export/import
-CTAS (Create table as select)
- dbms_redefinition

6- Alert log file? What it contains?
Location Defined by BACKGROUND_DUMP_DEST
Alert log file should be the 1st place when diagnosing day-to-day operations or errors.

Keeps record of
<> When DB was started & shutdown
<> List of all non default initialization parameters.
<> Startup of Background processes.
<> Log sequence number LGWR writing to.
<> Info abt log switch
<> Creation of tablespace & undo segments
<> Alter statement that has been issued.
<> Information abt errors messages ora-600 & extent errors.

7- Control file? What information it contains? When it is read?
Small binary file
Defines current state of physical database.
Required.
<> At mount state during startup
<>To operate the database
Linked to a single database.
Loss may require recovery.

Control file contains
<> Database Name & Identified
<> Timestamp of DB creation
<> Tablespace names
<> Names & location of data files & online redo log files
<> Current online redo log file information
<> Checkpoint information
<> Begin & end of undo segments
<> Redo log archive information
<> Backup Information
Read at the Mount mode of the database.

8- I have lost my Control File & don’t have any Backup; Can I start DB & operate? How? (You create a control file in no mount mode.)
Yes, we can operate the database, follow the steps below:
<> Start instance if necessary.
<> Shut down instance if start failed.
<> Start the Instance in Mount mode.
<> Run the trace file script to recreate the control file.
<> Determine if full back up is required and perform one if necessary.
<> Ensure that instance is started & database is open.

http://www.orafaq.com/wiki/Control_file_recovery

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/control.htm#i1006360
Steps for Creating New Control Files
Complete the following steps to create a new control file.
1. Make a list of all datafiles and redo log files of the database.
If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.SELECT MEMBER FROM V$LOGFILE;SELECT NAME FROM V$DATAFILE; SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.

2. Shut down the database.
If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.

3. Back up all datafiles and redo log files of the database.

4. Start up a new instance, but do not mount or open the database:

5. STARTUP NOMOUNT
6.
7. Create a new control file for the database using the CREATE CONTROLFILE statement.
When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

8. Store a backup of the new control file on an offline storage device. See "Backing Up Control Files" for instructions for creating a backup.

9. Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the new name.

10. Recover the database if necessary. If you are not recovering the database, skip to step 9.
If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.
If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.
See Also:
Oracle Database Backup and Recovery Basics and Oracle Database Backup and Recovery Advanced User's Guide for information about recovering your database and methods of recovering a lost control file

11. Open the database using one of the following methods:
o If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.
o ALTER DATABASE OPEN;
o If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.
o ALTER DATABASE OPEN RESETLOGS;
The database is now open and available for use.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/control.htm#i1006360
====================

Trace File:
When one of the Oracle background processes (such as dbwr, lgwr, pmon, smon and so on ) encounter an exception, they will write a trace file.
These trace files are also recorded in the alert.log.
Trace files are also created for diagnostic dump events.
An ORA-00600 error also produces a trace file.

9- Mount- No Mount?
NOMOUNT: An oracle instance can be started in NOMOUNT stage only during database creation or the recreation of control files.
MOUNT: To perform specific maintenance operations, don’t open the database.
• Renaming datafiles
• Enabling & disabling archive redo log file archiving options
• Performing full database recovery

10- SGA? It’s Components?
SGA: Shared Global Area is allocated at instance startup, and is fundamental component of Oracle Instance.

Size defined by SGA_MAX_SIZE.
Components
• Shared Pool (SHARED_POOL_SIZE)
• Database Buffer Cache (DB_CACHE_SIZE)
• Redo Log Buffer (LOG_BUFFER)Additional Components
• Large Pool
• Java Pool

11- Checkpoint? Why Checkpoint? Where it’s recorded? When it’s recorded?
Database Checkpoint: Checkpoints are you used to determine where recovery should start.
Checkpoint position – where recovery should start
Checkpoint queue – link list of dirty blocks
The position in the redo log where recovery should start is referred as to as the checkpoint position.

Types of Checkpoint
1- Full check point
<> All dirty buffers are written
<> Shutdown normal, immediate, or transactional
Alter System Checkpoint
2- Incremental Checkpoint
3- Partial Checkpoint
<> Alter tablespace begin backup
<> Alter tablespace offline normal

When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.
Every 3 seconds CKPT records the RBA from the oldest entry to in the checkpoint queue in the control file. This RBA represents the point in the redo log at which instance recovery is to begin after an instance failure. It can do this because all of the data blocks represented in prior redo records are guaranteed to have been written do disk by DBWn
On the event on a log switch, does CKPT also write this information to the header of the datafiles.

12- LGWR? When it writes?
LGWR performs sequential writes from the Redo Log Buffer to the Online redo Log files under the following situations
<> At Commit
<> When the Redo Log Buffer is one-third full
<> When there is more than 1 MB of changes recorded in the Redo Log Buffer
<> Befoere DBW writes modified blocks
<> Every 3 seconds

13- Duties of DBA?
· To plan & Create Databases
· To Manage Database Availability
· To Manage Physical & Logical Structure
· To Manage Storage based on design
· To manage security
· Network Administration
· Backup & Recovery
· Database Tuning

14- What’s Statspack? How can I use Statspack?
STATSPACK: STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ESTAT's successor, incorporating many new features. STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.

UTLBSTAT - UTLESTAT
The BSTAT-ESTAT utilities capture information directly from the Oracle's in-memory structures and then compare the information from two snapshots in order to produce an elapsed-time report showing the activity of the database. If we look inside utlbstat.sql and utlestat.sql, we see the SQL that samples directly from the view: V$SYSSTAT;

insert into stats$begin_stats select * from v$sysstat;
insert into stats$end_stats select * from v$sysstat;

How can I use Statspack
Create PERFSTAT Tablespace
The STATSPACK utility requires an isolated tablespace to obtain all of the objects and data. For uniformity, it is suggested that the tablespace be called PERFSTAT, the same name as the schema owner for the STATSPACK tables. It is important to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space.

SQL> CREATE TABLESPACE perfstat
DATAFILE '/u01/oracle/db/AKI1_perfstat.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
Run catdbsyn.sql as SYS
Run dbmspool.sql as SYS
$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"SQL> start spcreate.sql
Adjusting the STATSPACK Collection Level
STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table.

SQL> SELECT * FROM stats$level_description ORDER BY snap_level;

Level 0: This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information.
Level 5: This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels.
Level 6: This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels.
Level 7: This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels.
Level 10: This level includes capturing Child Latch statistics, along with all data captured by lower levels.

15- Methods of Backup – (Cold & Hot Backup)?
A cold backup, also called an offline backup, is a database backup when the database is offline and thus not accessible for updating. This is the safest way to back up because it avoids the risk of copying data that may be in the process of being updated. However, a cold backup involves downtime because users cannot use the database while it is being backed up.
When system downtime must be minimized, a hot backup can provide an alternative to the cold backup. A hot backup can be done even as users access the database, but some method must be used to ensure that data being updated is noted and can be copied when the update is complete.

16 -Why do you want to be DBA? Why Not a Developer?
I think being an Oracle DBA is really rewarding. It can be a highly demanding job, but I feel that this is part of the exciting challenge of being a DBA. But I choose this path for myself and I am really happy with it. It is an exciting and rewarding job.

17- What is RECOVERY Catalog? Why we need it? Complete command/steps of creating Recovery Catalog? How will it know about the Primary Database? -What role/Privileges are given to user when he is connected to Recovery Catalog? -How can I connect with RMAN? It’s Steps?
What is RECOVERY Catalog
• Recovery Catalog is schema that is created in a separate tablespace.
• RMAN propagates information about the database structure, archived redo log files, and datafile copies into the recovery catalog from the control file of target database.

http://www.idevelopment.info/data/Oracle/DBA_tips/RMAN_9i/RMAN9_4.shtml

To use RMAN, a recovery catalog is not necessary. Remember that RMAN will always use the control file of the target database to store backup and recovery operations. To use a recovery catalog, you will first need to create a recovery catalog database and create a schema for it. The catalog (database objects) will be located in the default tablespace of the schema owner. Please note that the owner of the catalog cannot be the SYS user.

The recovery catalog database should be created on a different host, on different disks, and in a different database from the target databse you will be backing up. If you do not, the benefits of using a recovery catalog are lost if you loose the database and need to restore.

The first step is to create a database for the recovery catalog. For the purpose of this example, I created an Oracle 9.2.0 database named CATDB. The database has the following installed:

• You have access to the SYS password for the database.
• A temporary tablespace named TEMP already exists.
• A normal tablespace named TOOLS exists and will be used to store the recovery catalog.
• The database is configured in the same way as all normal databases, for example, catalog.sql and catproc.sql have been successfully run.

Now, let's create the recovery catalog:

1. Start SQL*Plus and then connect with SYSDBA privileges to the database containing the recovery catalog:
% sqlplus "sys/change_on_install as sysdba"
2. Create a user and schema for the recovery catalog:
3. SQL> CREATE USER rman IDENTIFIED BY rman
4. DEFAULT TABLESPACE tools
5. TEMPORARY TABLESPACE temp
6. QUOTA UNLIMITED ON tools;
7.
User created.
8. Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with privileges to maintain and query the recovery catalog: 9.

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;
10. Grant succeeded.
11. Grant other desired privileges to teh RMAN user:
12. SQL> GRANT CONNECT, RESOURCE TO rman;
13. Grant succeeded.
14. After creating the catalog owner you should now create the catalog itself by using the CREATE CATALOG command within the RMAN interface. This command will create the catalog in the default tablespace of the catalog owner. you will need to connect to the database that will contain the catalog as teh catalog owner as follows:
15. % rman catalog rman/rman@catdb
16.
17. Recovery Manager: Release 9.2.0.1.0 - Production
18.
19. Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
20.
21. connected to recovery catalog databaserecovery catalog is not installed
22. Now, run the CREATE CATALOG command to create the catalog. Note that this process can take several minutes to complete.
23. RMAN> create catalog;
24. recovery catalog created

Registering the Target Database
Before using RMAN using a recovery catalog, you will need to register the taget database(s) in the recovery catalog. RMAN will obtain all information it needs to register the target database from the database itself.
As long as each target database has a distinct DBID, you can register more than one target database in the same recovery catalog. Each database registered in a given catalog must have a unique database identifier (DBID), but not necessarily a unique database name.
You can use either the command-line utilities provided by RMAN or the Oracle Enterprise Manager GUI to register the target database. For the purpose of this example, I will be using the command-line utilities. I will be registering a database named TARGDB to a recovery catalog within a database named CATDB. The target database must be either mounted or opened in order to register it.

% . oraenv
ORACLE_SID = [TARGDB] ? TARGDB
% rman target backup_admin/backup_admin catalog rman/rman@catdb
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: TARGDB (DBID=2457750772)
connected to recovery catalog database

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

18- RMAN Incremental Backups? What are Differential & Cumulative Backups?
Incremental backup is a backup that includes only those blocks that have changed since the previous backup.
click the link below for details about Differential and Cumulative Backups.

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1005.htm

19- Write a statement/command for exporting all the objects of owner ‘HR’?
Exp user=hr/hr full=y direct=y

20- Direct=y
Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance

21- Standby Databases?
A standby database is a transactionally consistent copy of the primary database. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database by transmitting primary database redo data to the standby system and then applying the redo logs to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
A standby database can be either a physical standby database or a logical standby database:

Physical standby database
Provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.
Logical standby database
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. It is kept synchronized with the primary database by transforming the data in the redo logs received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Thus, a logical standby database can be used concurrently for data protection and reporting.

For more details about Stand By databases visit the link below.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/concepts.htm

22- RAC?
Real Application Clusters harnesses the processing power of multiple interconnected computers. Real Application Clusters software and a collection of hardware known as a cluster unite the processing power of each component to create a robust computing environment.

You can use Real Application Clusters to deliver high performance, increased throughput, and high availability. Before deploying Real Application Clusters, however, you should understand Real Application Clusters processing.

In Real Application Clusters environments, all active instances can concurrently execute transactions against a shared database. Real Application Clusters coordinates each instance's access to the shared data to provide data consistency and data integrity.

Harnessing the power of clusters offers obvious advantages. A large task divided into subtasks and distributed among multiple nodes is completed sooner and more efficiently than if you processed the entire task on one node. Cluster processing also provides increased performance for larger workloads and for accommodating rapidly growing user populations.

For more details visit the link below.
http://download-uk.oracle.com/docs/cd/B10501_01/rac.920/a96597/psintro.htm

23- Partitioning (List, Range, Hash)

More details: http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/
Partitioning provides facilities for implementing large, scalable applications. Enables control over tables & indexes at a lower level of granularity than is possible with basic enterprise edition.

CREATE TABLESPACE part1
DATAFILE 'c:\temp\part01.dbf' SIZE 50
MBLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

CREATE TABLESPACE part2
DATAFILE 'c:\temp\part02.dbf' SIZE 50M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

CREATE TABLESPACE part3
DATAFILE 'c:\temp\part03.dbf' SIZE 50M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

CREATE TABLESPACE part4

DATAFILE 'c:\temp\part04.dbf' SIZE 50M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

HASH Partitioning
Hash Partitioning, which maps data to partitions based on a hashing algorithm, evenly distributing data between the partitions. This is typically used where ranges aren't appropriate, i.e. customer number, product ID
Enables partitioning of data that does not lend itself to range or list partitioning.To view the numbers Oracle uses for hashing:
SELECT program, sql_hash_value, prev_hash_value FROM gv$session;

CREATE TABLE hash_part (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL,
prof_hist_comments VARCHAR2(2000))
PARTITION BY HASH (prof_history_id)
PARTITIONS 3
STORE IN (part1, part2, part3);

desc prof_hist

SELECT table_name, tablespace_name, partitioned
FROM user_tables;

desc user_tab_partitions

SELECT partition_name, tablespace_name
FROM user_tab_partitions;
---------------------------------------------------------------------------

LIST Partitioning
Explicitly controls how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition.

CREATE TABLE list_part (
deptno NUMBER(10),
deptname VARCHAR2(20),
quarterly_sales NUMBER(10,2),
state VARCHAR2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA')
TABLESPACE part1,
PARTITION q1_southwest VALUES ('AZ', 'CA', 'NM')
TABLESPACE part2,
PARTITION q1_northeast VALUES ('NY', 'VT', 'NJ')
TABLESPACE part1,
PARTITION q1_southeast VALUES ('FL', 'GA')
TABLESPACE part2,
PARTITION q1_northcent VALUES ('MN', 'WI')
TABLESPACE part1,
PARTITION q1_southcent VALUES ('OK', 'TX')
TABLESPACE part2);

SELECT table_name, tablespace_name, partitioned
FROM user_tables;

SELECT partition_name, tablespace_name, high_valueFROM user_tab_partitions;

INSERT INTO list_part VALUES (10, 'A', 1000, 'OR');
INSERT INTO list_part VALUES (20, 'B', 1000, 'AZ');
INSERT INTO list_part VALUES (10, 'A', 1000, 'WA');
INSERT INTO list_part VALUES (20, 'B', 1000, 'WA');
INSERT INTO list_part VALUES (10, 'A', 1000, 'AZ');
INSERT INTO list_part VALUES (20, 'B', 1000, 'CA');
COMMIT;

SELECT * FROM list_part;
SELECT * FROM list_part PARTITION(q1_northwest);
---------------------------------------------------------------------------

RANGE Partition
Range Partitioning, which maps data to partitions based on a range of column values (usually a date column)

CREATE TABLE range_part (prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL,
ph_comments VARCHAR2(200))
PARTITION BY RANGE (record_date) (
PARTITION yr0
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
TABLESPACE part1,
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) TABLESPACE part2,
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
TABLESPACE part3,
PARTITION yr9 VALUES LESS THAN (MAXVALUE)
TABLESPACE part4);

SELECT table_name, tablespace_name, partitioned
FROM user_tables;

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;

24- If my application is slow, how can we make it efficient, faster? -How will you tune if application is slow?

25- RMAN Backups?
RMAN 9i
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmintro.htm

26- ADDM.
ADDM: Automatic database diagnostic monitor. ADDM enables Oracle to diagnose its own performance problems. For example, ADDM identifies the most resource intensive SQL statements and passes that statement to the SQL tuning advisor.

ADDM automatically monitors the state of the database at short, regular intervals (by default: 30 mins) which leads to database performance diagnostics. It promises that you can forget all of your scripts that link the many v$ views. ADDM can be run from Enterprise Manager or through a PL/SQL interface.

SQL tuning advisor In my last column, I focused on one of Oracle Database 10g's quick wins for DBAs—using the new SQL Tuning Advisor to quickly tune poorly performing SQL statements—and touched only briefly on the new built-in diagnostic engine,
ADDM details http://www.oracle-base.com/articles/10g/AutomaticDatabaseDiagnosticMonitor10g.php

27- AWR.
AWR automatic workload repository looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). This allows retrieving information about workload changes and database usage patterns.
This information is the basis for all self-management decisions. For example, it is thus possible to identify the SQL statements that have the
· larges CPU consumption
· most buffer gets
· disk reads
· most parse calls
· shared memory
The data is fed from MMON.
The information is stored in the sysaux tablespace.

28- Flashback? What is flashback in 10g?
With Oracle's flashback, it's possible to sort of travel back in time. That is, flashback provides a means to see data that was altered through DDL operations and the state of database objects that were changed with DML operations. Oracle subcategorises flashback into three categories: • Flashback queryGet previous data with the as of clause of a select statement. See Flashback query example 1.
• Flashback version queryGet changes of data between a time interval using the versions between clause of a select statement. See Flashback version query example 1.
• Flashback transaction queryFind changes of a specific transaction through the flashback_transaction_query view. See Flashback transaction version query example 1. • Flashback Table
• Flashback dropUndrops a dropped table.
• flashback database In order to make flashback possible, Oracle uses undo.

http://www.adp-gmbh.ch/ora/admin/flashback/version_query_ex_1.html

29- What is command for restoring from recycle bin?
To reinstate the table, all you have to do is use the FLASHBACK TABLE command:

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

PURGE RECYCLEBIN;But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:

DROP TABLE RECYCLETEST PURGE;
This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.

2nd interview.
30- Difference between User & Schema?
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes. (There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.)

31- Cluster key?
A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together. For example, the emp and dept table share the deptno column. When you cluster the emp and dept tables (see Figure 18-1), Oracle physically stores all rows for each department from both the emp and dept tables in the same data blocks.

The cluster key is the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.

32- RAC?
Go to Question/Answer 22

33- What are duties of DBA?
• To plan & Create Databases
• To Manage Database Availability
• To Manage Physical & Logical Structure
• To Manage Storage based on design
• To manage security
• Network Administration
• Backup & Recovery
• Database Tuning
• Import & Export
• Creating tables
• Creating Schemas and Objects

34- What is Difference among NOMOUNT & MOUNT & OPEN modes? When Database will be available for read?
NOMOUNT
When you issue the startup command, the first thing the database will do is enter the nomount stage. During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured. For example, the sizes of all of the memory areas in Oracle are defined within the parameter file.
After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started. Together, we call these processes and the associated allocated memory the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage. If you issue the startup command, then Oracle will automatically move onto the next stage of the startup, the mount stage.
The database can be configured on NOMOUNT mode
<> When Creating a Database
<> When re-creating Control File.

MOUNT MODE
When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles.
In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.
Some forms of recovery require that the database be opened in mount stage.

OPEN MODE
The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.

35- What is SGA? And what are its components?
System Global Area: The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. Oracle allocates the SGA when an instance starts and deallocates it when the instance shuts down. Each instance has its own SGA.
Users currently connected to an Oracle database share the data in the SGA. For optimal performance, the entire SGA should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and to minimize disk I/O.
SGA Components: The information stored in the SGA is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool.
Database Buffer Cache of the SGA: Database buffers store the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently (and often, the most frequently) used data is kept in memory, less disk I/O is necessary, and performance is improved.
Redo Log Buffer of the SGA: The redo log buffer stores redo entries—a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log, which is used if database recovery is necessary. The size of the redo log is static.
Shared Pool of the SGA: The shared pool contains shared memory constructs, such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database. A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. A single shared SQL area is used by multiple applications that issue the same statement, leaving more shared memory for other uses.

36- How can we allocate SGA? How we can know it proper size? How should we estimate? How can we calculate size of SGA?
Check SGA Size Tips at following links.
http://www.dba-oracle.com/t_sga_sizing.htm

38- How can you perform Fragmentation in tables?
(No Comments)

check this link for 10g Shrink. http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-09-10.8045550726

39-Suppose, if You export table data and then import? Does it perform fragmentation?
Yes..

40- Interviewer Asked from me:-”Tell me if you are DBA, u left the office at night, and when u arrive at office in the morning”, what you will check in database?
(No Comments)
I have recently joined a Company, and we have a Checklist that I used to check in the morning, that containts:
Primary Database:
* OracelServiceABC
* OracleListnerABC
* ArchivedLogSequence #
Backups:
* HOT BACKUPS
* Export Full Database
Standby Databases:
* OracelServiceABCYZ
* OracleListnerABCYZ
* ArchivedLogSequence #
* DataGuard Status

41- How can you schedule a job?
(No comments)
You can check in my blog for schedule a batch job.
http://rakeshocp.blogspot.com/2007/09/batch-file-scripts.html

42- RAID?
In computing, specifically computer storage, a Redundant Array of Independent Drives (or Disks), also known as Redundant Array of Inexpensive Drives (or Disks), (RAID) is an umbrella term for data storage schemes that divide and/or replicate data among multiple hard drives. RAID can be designed to provide increased data reliability or increased I/O performance, or both.

Follow the links below for RAID information
http://www.ecs.umass.edu/ece/koren/architecture/Raid/raidhome.html
http://en.wikipedia.org/wiki/RAID
http://www.dba-oracle.com/oracle_tips_raid_usage.htm

43- New feature of 10g for DBAz?
CHeck 10g new features for DBAz http://www.oracle.com/technology/pub/articles/10gdba/index.html

44- What is Database? RDBMS? Rules?
DATABASE: A collection of information organized in such a way that a computer program can quickly select desired pieces of data.

RDBMS: a type of database management system (DBMS) that stores data in the form of related tables. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.
Almost all full-scale database systems are RDBMS's. Small database systems, however, use other designs that provide less flexibility in posing queries.

Rules: Codd's 12 rules are a set of 12 rules proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required from a database management system in order for it to be considered relational i.e and RDBMS. visit the link below for Codd's 12 rules.
http://en.wikipedia.org/wiki/Codd

I have tried my level best to help Fresh DBAz to get an idea of Questions that are mostly asked for fresh DBA job.
I couldn't answer few questions thats why i have answered no comments in Answer.

Regards!!
Rakesh Kumar Soni.

About Mee..

2007-09-21T12:36:00.000-07:00

Auditing SYS user in 10g

2008-12-25T21:21:00.000-08:00

There are two parameters that one need to set to audit SYS users in 10g.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

AUDIT_TRAIL: You can view audit information in TEXT format or in XML format depending on the parameter audit trail setting.

SQL> show parameter audit
NAME TYPE VALUE
----------------------- --------- -------------------------------------------

audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1
\RDBMS\AUDIT
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>
we set audit_trail parameter to value "xml, extended"
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
SQL> alter system set audit_trail=xml,extended scope=spfile;
System altered.
SQL> show parameter audit

SQL> show parameter audit

NAME TYPE VALUE

----------------------- --------- -------------------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1 \RDBMS\AUDIT audit_sys_operations boolean FALSE

audit_trail string NONE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 79693180 bytes
Database Buffers 79691776 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1
\RDBMS\AUDIT
audit_sys_operations boolean FALSE
audit_trail string XML, EXTENDED

SQL> alter system set audit_file_dest='D:\oracle\product\10.2.0\admin\rock\audit' scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.
SQL>

shutdown & restart the database.

SQL> show parameter audit
NAME TYPE VALUE
------------------------ ------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0ADMIN
\ROCK\AUDIT
audit_sys_operations boolean TRUE
audit_trail string XML, EXTENDED

SQL> create user xyz222
2 identified by xyz222;
User created.
SQL>

NOw we will check whether these commands are audited or not, I will run below query.

set linesize 3333
column db_user format a10
column os_user format a24
column os_host format a20
column extended_timestamp format a20
column sql_text format a300

select db_user, os_user, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_text
from v$xml_audit_trail
where db_user in ('sys', 'SYS', '/')
order by 4
/

DB_USER OS_USER OS_HOST TO_CHAR(EXTENDED_TIMES SQL_TEXT
---------- ------------------------ -------------------- ----------------------
/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:47:23 PM select db_user, os_uSER, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_textfrom v$xml_audit_trailwhere db_user in ('sys', 'SYS', '/')order by 4

/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:48:38 PM create user xyz222identified by *

/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:48:56 PM select db_user, os_uSER, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_textfrom v$xml_audit_trailwhere db_user in ('sys', 'SYS', '/')order by 4

Auditing other users than sys.
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php