How to restore RMAN cold backup from non ASM to an ASM instance with new database name

 the task was to restore the backup from non ASM environment to another server with ASM, but under different SID.

Source database: DBSTREP
Target database: DBSTREP2

Step 1) Prepare the source database for cold backup

1 2 3 4 5 6 7

$ export ORACLE_SID=DBSTREP $ export ORACLE_HOME=/oracle/product/11.1.0/db $ sqlpplus /nolog SQL> conn / as sysdba SQL> shutdown immediate SQL> startup mount SQL> alter system set large_pool_size=100M;

Step 2) Make a cold backup of the source database

1 2 3 4 5 6 7 8 9 10 11 12

$ rman target / RMAN> run      {            allocate channel c1  type disk format '/st2_data04/backup/dbstrep/dbstrep_%p_%s_%T.bkp';            backup database TAG 'DBSTREP';            backup current controlfile TAG 'DBSTREP_CTRLFILE';            backup spfile TAG 'DBSTREP_SPFILE';            release channel c1;      }      exit;

Step 3) Create control file from the source database

1 2 3

$ sqlpplus /nolog SQL> conn / as sysdba SQL> alter database backup controlfile to trace;

Then, change the existing path to the datafiles to point to the ASM Diskgroup. Do not forget to set the new name for the database ‘SET DATABASE “DBSTREP2″‘.

The control file create “create_ct_dbstrep2.sql” should look like:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "DBSTREP2" RESETLOGS  NOARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '+BSCSDATA00/DBSTREP2/redo01.log'  SIZE 50M,   GROUP 2 '+BSCSDATA00/DBSTREP2/redo02.log'  SIZE 50M,   GROUP 3 '+BSCSDATA00/DBSTREP2/redo03.log'  SIZE 50M DATAFILE   '+BSCSDATA00/DBSTREP2/system01.dbf',   '+BSCSDATA00/DBSTREP2/sysaux01.dbf',   '+BSCSDATA00/DBSTREP2/undotbs01.dbf',   '+BSCSDATA00/DBSTREP2/users01.dbf',   '+BSCSDATA00/DBSTREP2/example01.dbf',   '+BSCSDATA00/DBSTREP2/t_rep01.dbf' CHARACTER SET WE8ISO8859P9; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE TEMP ADD TEMPFILE '+BSCSDATA00/DBSTREP2/temp01.dbf'      SIZE 62914560  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; EXIT;

Step 4) Create PFILE from SPFILE from the source database “DBSTREP”

1 2 3

$ sqlpplus /nolog SQL> conn / as sysdba SQL> create pfile from spfile;

Step 5) Generate and save the output of the script that generate DBMS_BACKUP_RESTORE.
You will need it latter. Adjust script to your DBNAME/Datafile Path.

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> SELECT      'SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => '          || file_id          || ', toname => '          || '''+BSCSDATA00'          || SUBSTR (file_name,                     INSTR (file_name, '/DBSTREP'),                     LENGTH (file_name)                    )          || ''');' cmd     FROM dba_data_files ORDER BY file_id /

Output:
================================================================================================
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 1, toname => ‘+BSCSDATA00/DBSTREP/system01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 2, toname => ‘+BSCSDATA00/DBSTREP/sysaux01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 3, toname => ‘+BSCSDATA00/DBSTREP/undotbs01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 4, toname => ‘+BSCSDATA00/DBSTREP/users01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 5, toname => ‘+BSCSDATA00/DBSTREP/example01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 6, toname => ‘+BSCSDATA00/DBSTREP/t_rep01.dbf’);

Step 6) Copy the backup of source database “DBSTREP” to the target server.

1	1@/st2_data04/backup/dbstrep$ scp *.bkp *.ora oraclert@destination:/CDR/rman_backup/dbstrep

step 7) Copy the PFILE of source database “DBSTREP” to the target server in $ORACLE_HOME/dbs.
Modify it, so it points to the new sid “DBSTREP2″. Also create required folders on disk.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

*.audit_file_dest='/oraclert/admin/DBSTREP2/adump' *.audit_trail='none' *.compatible='11.1.0.0.0' *.control_files='+BSCSDATA00/DBSTREP2/control01.ctl','+BSCSDATA00/DBSTREP2/control02.ctl','+BSCSDATA00/DBSTREP2/control03.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='DBSTREP2' *.ddl_lock_timeout=10 *.diagnostic_dest='/oraclert' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DBSTREPXDB)' *.large_pool_size=0 *.local_listener='LISTENER' *.open_cursors=300 *.pga_aggregate_target=209715200 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_max_size=1073741824 *.sga_target=1073741824 *.undo_tablespace='UNDOTBS1'

Step 8 – Connect to the target database “DBSTREP2″ and start it in NOMOUNT.
After that run the script “restore_all.sql” which uses DBMS_BACKUP_RESTORE package.

1 2 3 4 5 6

$ export ORACLE_SID=DBSTREP2 $ export ORACLE_HOME=/oracle/product/11.1.0/db $ sqlpplus /nolog SQL> conn / as sysdba SQL> startup nomount SQL> @restore_all.sql

Contents of the “restore_all.sql”:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

DECLARE    v_handle         VARCHAR2 (500);             v_is_done        BOOLEAN  := FALSE;         /*    The t_input_files PL/SQL table entries must reflect the backuppieces     comprising the backupset    */                                       TYPE t_input_files IS TABLE OF VARCHAR2 (1000)       INDEX BY BINARY_INTEGER;         /*    The v_max_backup_pieces variable must reflect the number of backuppieces    */    v_max_backup_pieces   NUMBER := 6;    v_input_files         t_input_files;                    BEGIN         /*    If the backup containt more one backupset (example: was taken with more than 1 allocated channel),    then you should add them in the array accordingly 2,3,4 ...    */     v_input_files (1) := '/CDR/rman_backup/dbstrep/dbstrep_1_5_20101210.bkp';    /* v_input_files (2) := '/CDR/rman_backup/dbstrep/dbstrep_1_6_20101210.bkp'; */    /* v_input_files (3) := '/CDR/rman_backup/dbstrep/dbstrep_1_7_20101210.bkp'; */         /* Number of backup pieces in a backupset */    v_max_backup_pieces := 6;         v_handle := SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE;    /* Start the restore conversation; From file system to ASM Diskgroup (+BSCSDATA00) */    SYS.DBMS_BACKUP_RESTORE.RESTORESETDATAFILE;    SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO                     (dfnumber      => 1,                      toname        => '+BSCSDATA00/DBSTREP2/system01.dbf'                     );    SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO                     (dfnumber      => 2,                      toname        => '+BSCSDATA00/DBSTREP2/sysaux01.dbf'                     );    SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO                     (dfnumber      => 3,                      toname        => '+BSCSDATA00/DBSTREP2/undotbs01.dbf'                     );    SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO                     (dfnumber      => 4,                      toname        => '+BSCSDATA00/DBSTREP2/users01.dbf'                     );    SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO                     (dfnumber      => 5,                      toname        => '+BSCSDATA00/DBSTREP2/example01.dbf'                     );    SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO                     (dfnumber      => 6,                      toname        => '+BSCSDATA00/DBSTREP2/t_rep01.dbf'                     );                          FOR i IN 1 .. v_max_backup_pieces    LOOP       DBMS_OUTPUT.PUT_LINE('Restoring piece '|| to_char(i) || ' from backup file: ' || v_input_files(i) || '...');       SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE (done        => v_is_done,                                                        handle      => v_input_files(i),                                                        params      => NULL                                                  );       IF v_is_done       THEN          GOTO all_done;       END IF;            END LOOP;    <<all_done>>         SYS.DBMS_BACKUP_RESTORE.devicedeallocate; END; /

Step 9) Check if the “restore_all.sql” has restored all datafiles on the ASM diskgroup.

1 2 3

$ export ORACLE_SID=+ASM $ export ORACLE_HOME=/oracle/product/11.1.0/asm $ asmcmd

ASMCMD> cd BSCSDATA00/DBSTREP2
ASMCMD> ls -al
Name
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
example01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1016.737392407
sysaux01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1014.737392407
system01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1015.737391459
t_rep01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1013.737392407
temp01.dbf => +BSCSDATA00/DBSTREP2/TEMPFILE/TEMP.1025.737393513
undotbs01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1017.737392407
users01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1018.737392407

Great, DBMS_BACKUP_RESTORE did the job. Now, let us re-create the controlfile;

Step 10) Re-create the control file

1 2 3 4 5 6

$ export ORACLE_SID=DBSTREP $ export ORACLE_HOME=/oracle/product/11.1.0/db $ sqlpplus /nolog SQL> conn / as sysdba SQL> shutdown immediate SQL> @create_ct_dbstrep2.sql

After the control file is successfully recreated, then your target database “DBSTREP2″ is ready. Just double check again the location of redo logs and control files.

1 2 3 4

$ export ORACLE_SID=+ASM $ export ORACLE_HOME=/oracle/product/11.1.0/asm $ asmcmd

ASMCMD> cd BSCSDATA00/DBSTREP2
ASMCMD> ls -al
Name
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
control01.ctl => +BSCSDATA00/DBSTREP2/CONTROLFILE/Current.1019.737393459
control02.ctl => +BSCSDATA00/DBSTREP2/CONTROLFILE/Current.1020.737393459
control03.ctl => +BSCSDATA00/DBSTREP2/CONTROLFILE/Current.1021.737393459
example01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1016.737392407
redo01.log => +BSCSDATA00/DBSTREP2/ONLINELOG/group_1.1022.737393467
redo02.log => +BSCSDATA00/DBSTREP2/ONLINELOG/group_2.1023.737393469
redo03.log => +BSCSDATA00/DBSTREP2/ONLINELOG/group_3.1024.737393469
sysaux01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1014.737392407
system01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1015.737391459
t_rep01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1013.737392407
temp01.dbf => +BSCSDATA00/DBSTREP2/TEMPFILE/TEMP.1025.737393513
undotbs01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1017.737392407
users01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1018.737392407

Enable Diagnostics in Oracle apps

How to enable Oracle apps Diagnostics-> Examine, for certain users?

Steps 1

Navigate to System Administrator responsibility> Profile> System>

Steps 2

Enter profile name: Utilities:Diagnostics

Enter Application User for whom you want to enable Diagnostics-> Examine

Steps 3

Give Yes at User level and Save the Changes

Note –

You can set Yes at Site level also if you want to enable this option for all Oracle application users

Steps 4

Again navigate to System Administrator responsibility> Profile> System>

Enter profile name: Hide Diagnostics menu entry

Enter Application User for whom you do not want to hide Diagnostics menu entry

Steps 5

Give No at User level and Save the Changes

Note –

You can set No at Site level also if you do not want to hide menu entry option for all Oracle application users

Steps 6

Congratulations you have successfully enabled Diagnostics-> Examine

Logout from Oracle Application and login again. Now can see Diagnostics-> Examine option

How to setup audit trail on oracle apps

General Description
The Audit Trail is an Oracle inbuilt functionality that lets audit specific columns belonging to selected tables. This implementation enhances the security of the system.

Purpose
Purpose of this document is to instruct the user to setup the Oracle AuditTrail on the following tables:

• FND_PROFILE_OPTION_VALUES (Profile Option Updates)
• FND_USER (New User Creations or Updates)
• WF_LOCAL_USER_ROLES (Responsibility Assignments)

Getting Started
For each audited table (i.e. FND_USER), the system will create a shadow table named tablename_A (i.e. FND_USER_A). The maximum size of the shadow table name is 26 characters.
The columns to be audited should be selected carefully to avoid an impact on the performances of the system.

Configure the Profile Options
To enable the Audit Trail, it’s required to set 2 profile options at site level:
System Administrator -> Profile -> System
1. Profile option ‘Sign-On:Audit Level’ set to ‘FORM’

2. Profile Option ‘AuditTrail:Activate’ set to ‘YES’

Enable Audit on the Table Owner
According to which table we want to audit, there can be a different table owner on which the auditing should be enabled.
the table owner can be checked running the following query:

SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME = 'FND_PROFILE_OPTION_VALUES'
OR TABLE_NAME = 'FND_USER'
OR TABLE_NAME = 'WF_LOCAL_USER_ROLES'

The query above will show that the owner of all the tables we want to audit is the user ‘APPLSYS’.
To auditing on the user ‘APPLSYS’ can be enabled in the following way:
System Administrator -> Security -> AuditTrail -> Install
Querying the username ‘APPLSYS’ and making sure that the Audit Checkbox is enabled.

Create and Audit Group
The Audit Group will contain the list of the tables to be audited for our purpose.
System Administrator -> Security -> AuditTrail -> Groups
The Group State should be set to ‘Enable Requested’
Then we create the table list adding the following User Table Names:

• FND_PROFILE_OPTION_VALUES
• Define an Application User
• WF_LOCAL_USER_ROLES

The User Table Name of the FND_USER table is ‘Define an Application User’.

Selecting the Columns to Audit in FND_PROFILE_OPTION_VALUES
The columns can be set opening this form:
System Administrator -> Security -> AuditTrail -> Tables
First Table to query is FND_PROFILE_OPTION_VALUES. The columns to audit are in the image below.

• LEVEL_VALUE_APPLICATION_ID – Set by Default
• LEVEL_VALUE – Set by Default
• LEVEL_ID – Set by Default
• PROFILE_OPTION_ID – Set by Default
• APPLICATION_ID – Set by Default
• PROFILE_OPTION_VALUE – Needed to log the old profile option value
• LAST_UPDATE_DATE – Needed to log Date and Time of when the profile option value was updated
  The other columns are not relevant for the auditing purpose.

Selecting the Columns to Audit in FND_USER
Second Table to query is FND_USER. The columns to audit are in the image below.

• USER_ID – Set by Default
• USER_NAME – Needed to log the username updates
• ENCRYPTED_FOUNDATION_PASSWORD – Needed to identify when a password have been reset or when an account have been locked
• ENCRYPTED_USER_PASSWORD – Needed to identify when a password have been reset or when an account have been locked
• DESCRIPTION – Needed to log the accounts description updates
• END_DATE – Needed to log the updates of the accounts end-date
• START_DATE – Needed to log the updates of the accounts start-date
• PASSWORD_LIFESPAN_DAYS – Needed to log the updates of the accounts lifespan
• EMPLOYEE_ID – Needed to log the updates of the accounts lifespan
• EMAIL_ADDRESS – Needed to log the updates of the accounts email address
• The other columns are optional or not relevant for the auditing purpose.
• (*) LAST_LOGON_DATE – Not to be Audited to avoid logging not needed records 

Selecting the Columns to Audit in WF_LOCAL_USER_ROLES
Third Table to query is WF_LOCAL_USER_ROLES. The columns to audit are in the image below

Testing the AuditTrail Setup
After enabling the AuditTrail it’s needed to test that everything is working properly. The test can be performed executing the following actions:

• Create a new user ‘TEST01’
• Edit the description of the user ‘TEST01’
• Assign a responsibility to the user ‘TEST01’
• Set a profile option for the user ‘TEST01’

Running the following queries, the output should contain relevant information related to what was done.

SELECT * FROM APPLSYS.FND_USER_A
SELECT * FROM APPLSYS.WF_LOCAL_USER_ROLES_A
SELECT * FROM APPLSYS.FND_PROFILE_OPTION_VALUE_A

Purging the Auditing Tables
It would be wise to create some policy establishing how often the auditing tables should be purged and where and how the data should be archived.
To Purge the auditing table it’s enough to change the ‘Group State’ of the Audit Group setting the value ‘Disable – Purge Table’
System Administrator -> Security -> AuditTrail -> Groups

 Then the concurrent program ‘AuditTrail Update Tables’ should be executed again.

Gather Schema and Tables Statistics

Gather Schema and Tables Statistics

Gather Schema Statistics – Concurrent Program 
Connect as System Administrator
Request – Run
Select - Gather Schema Statistics

Click OK button

Estimate Percent: Using any value larger than 50 will force a compute statistics to be
gathered; any value less than 50 only provide estimated statistics. Computed statistics in
some cases could provide a significant performance improvement for Application
modules.
Click the Schedule button to schedule as per your company needs, As a general rule,
schedule the Gather Schema Statistics concurrent program to run once a week, during off
hours, for your entire database

Gather Table Statistics Concurrent Program 
If you have volatile tables that are updated, inserted into or deleted from frequently, then
you should consider running Gather Table Statistics for those tables more frequently,
perhaps nightly during off hours. In the following figure, we’ve chosen a particular table,
FND_CONCURRENT_REQUESTS, and selected 99 for the percent to analyze to ensure
that the table is analyzed using compute, rather than estimate.

Note- Using these two Concurrent Programs also generates statistics on the associated
indexes.

Oracle Tutorials - Index - Data Structure for Query Performance

Oracle Tutorials - Index - Data Structure for Query Performance

What Is an Index?

Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

How To Create a Table Index?

If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use the CREATE INDEX statement as shown in the following script:

CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,

  subject VARCHAR(80) NOT NULL,

  description VARCHAR(256) NOT NULL,

  create_date DATE DEFAULT (sysdate));

Table created.

 

CREATE INDEX tip_subject ON tip(subject);

Index created.

How To List All Indexes in Your Schema?

If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:

SELECT index_name, table_name, uniqueness 

  FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';

INDEX_NAME              TABLE_NAME            UNIQUENES

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

EMP_EMAIL_UK            EMPLOYEES             UNIQUE

EMP_EMP_ID_PK           EMPLOYEES             UNIQUE

EMP_DEPARTMENT_IX       EMPLOYEES             NONUNIQUE

EMP_JOB_IX              EMPLOYEES             NONUNIQUE

EMP_MANAGER_IX          EMPLOYEES             NONUNIQUE

EMP_NAME_IX             EMPLOYEES             NONUNIQUE

As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.

What Is an Index Associated with a Constraint?

An index associated with a constraint because this constraint is required to have an index. There are two types of constraints are required to have indexes: UNIQUE and PRIMARY KEY. When you defines a UNIQUE or PRIMARY KEY constraint in a table, Oracle will automatically create an index for that constraint. The following script shows you an example:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

  first_name VARCHAR(80) NOT NULL,

  last_name VARCHAR(80) NOT NULL,

  birth_date DATE NOT NULL,

  social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

SELECT index_name, table_name, uniqueness 

  FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME              TABLE_NAME            UNIQUENES

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

SYS_C004123             STUDENT               UNIQUE

SYS_C004124             STUDENT               UNIQUE

The result confirms that Oracle automatically created two indexes for you.

How To Drop an Index?

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

  first_name VARCHAR(80) NOT NULL,

  last_name VARCHAR(80) NOT NULL,

  birth_date DATE NOT NULL,

  social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

SELECT index_name, table_name, uniqueness 

  FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME              TABLE_NAME            UNIQUENES

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

SYS_C004129             STUDENT               UNIQUE

SYS_C004130             STUDENT               UNIQUE

STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP INDEX STUDENT_BIRTH_DATE;

Index dropped.

Can You Drop an Index Associated with a Unique or Primary Key Constraint?

You can not delete the index associated with a unique or primary key constraint. If you try, you will get an error like this: ORA-02429: cannot drop index used for enforcement of unique/primary key.

What Happens to Indexes If You Drop a Table?

If you drop a table, what happens to its indexes? The answer is that if a table is dropped, all its indexes will be dropped too. Try the following script to see yourself:

What Happens to the Indexes If a Table Is Recovered?

If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recycle bin. However, the indexes' names will not be the original names. Indexes will be recovered with the system assigned names when they were dropped into the cycle bin. 

How To Rebuild an Index?

If you want to rebuild an index, you can use the "ALTER INDEX ... REBUILD statement as shown in the following SQL script:

ALTER INDEX EMP_NAME_IX REBUILD;

Statement processed.

How To See the Table Columns Used in an Index?

You can a list of indexes in your schema from the USER_INDEXES view, but it will not give you the columns used in each index in the USER_INDEXES view. If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you:

SELECT index_name, table_name, column_name 

  FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';

INDEX_NAME           TABLE_NAME       COLUMN_NAME

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

EMP_EMAIL_UK         EMPLOYEES        EMAIL

EMP_EMP_ID_PK        EMPLOYEES        EMPLOYEE_ID

EMP_DEPARTMENT_IX    EMPLOYEES        DEPARTMENT_ID

EMP_JOB_IX           EMPLOYEES        JOB_ID

EMP_MANAGER_IX       EMPLOYEES        MANAGER_ID

EMP_NAME_IX          EMPLOYEES        LAST_NAME

EMP_NAME_IX          EMPLOYEES        FIRST_NAME

How To Create a Single Index for Multiple Columns?

If you know a group of multiple columns will be always used together as search criteria, you should create a single index for that group of columns with the "ON table_name(col1, col2, ...)" clause.

CREATE INDEX student_names ON student(first_name,last_name);

Index created.

DIFFERENTIAL & CUMULATIVE INCREMENTAL BACKUP

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the parent of incremental backups whose level is greater than 0. When you are planning for the Incremental backup in your system. You have start with incremental level 0 then you can proceed with Differential & cumulative incremental backup

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

NOTE: you should consider whether you want to spend more time on backup (or) restore/recovery. 

If you are going for the FASTER BACKUP è differential incremental backup, it will do backup quickly. Since it has to take the backup only from last level 1 backup (if no level 1, then it takes a level 0 backup).  But when you want to restore then you need to have all the differential level 1 backup and LEVEL 0 backup to restore.

If you are going for the FASTER RESTORE è cumulative incremental backup, it will little time in doing the backup. Since it will be taking the backup from the last level 0 backup(it will ignore even though you have the level 1 backup).  During the restore, it requires only one LEVEL 0 backup and last LEVEL 1 cumulative backup.

A level 1 incremental backup can be either of the following types:

èDifferential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

IF YOU ARE NOT SPECIFYING THE WORD CUMULATIVE, THEN ORACLE WILL TAKE IT AS A DIFFERENTIAL BACKUP

è Cumulative incremental backup, which backs up all blocks changed after the most recent incremental backup at level 0

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

How to enable auditing in oracle database?

Following are steps to enable auditing in oracle database
1) In the initialization parameter file add an entry
audit_trail=db
2) Shutdown the database.
3) startup the database using the init file.
4) After logging into the database at the SQL prompt run the following script
SQL>@/rdbms/admin/cataudit.sql
With the help of this script various views are created which will help you to monitor the auditing information:-
a) STMT_AUDIT_OPTION_MAP:-Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
b) AUDIT_ACTIONS:- Contains descriptions for audit trail action type codes
c) ALL_DEF_AUDIT_OPTS:- Contains default object-auditing options that will be applied when objects are created
d) DBA_STMT_AUDIT_OPTS:-Describes current system auditing options across the system and by user
e) DBA_PRIV_AUDIT_OPTS:-Describes current system privileges being audited across the system and by user
f) USER_OBJ_AUDIT_OPTS & DBA_AUDIT_TRAIL describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
g) USER_AUDIT_TRAIL & USER_AUDIT_TRAIL:- Lists all audit trail entries. USER view shows audit trail entries relating to current user.
h) DBA_AUDIT_OBJECT & USER_AUDIT_OBJECT:-Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user.
i) DBA_AUDIT_SESSION & USER_AUDIT_SESSION:-Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
j) DBA_AUDIT_STATEMENT & USER_AUDIT_STATEMENT :- Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
k) DBA_AUDIT_EXISTS:-Lists audit trail entries produced BY AUDIT NOT EXISTS
5) After completion of the script SQL prompt will return.
6)Run the following commands to enable the various auditing
a)SQL>audit create session;
b)SQL>audit create user;
c)SQL>audit drop user;
d)SQL>audit create user;
e)SQL>audit drop tablespace;
f)SQL>audit grant any role;
g)SQL>audit grant any privelege;
h)SQL>audit alter system;
i)SQL>audit alter session;
j)SQL>audit delete on AUD$ by access;
k)SQL>audit insert on AUD$ by access;
l)SQL>audit update on AUD$ by access;
CAUTION:-
As the table AUD$ which contains all the auditing data is created in the system tablespace,
so as the auditing information grows the size of the system tablespace also increases,
so it is advisable to move this particular table AUD$ to some another tablespace.
THE COMMAND TO MOVE THE TABLE IS:-
SQL>create table AUDX tablespace as select * from AUD$;
SQL>rename AUD$ to AUD$$;
SQL>rename AUDX to AUD$;
TO check whether AUD$ table has shifted to the new tablespace write the following query in the SQL prompt
SQL>select table_name,tablespace_name from dba_tables where table_name=’AUD$’;

 


_________________________________________________________________________________

If you want to audit a specific user run the following:

audit alter table, select table, insert table, update table, delete table, grant table, grant
procedure by USERNAME;

To stop audit for that user run:

noaudit alter table, select table, insert table, update table, delete table, grant table,
grant procedure by USERNAME;

To enable auditing for a specific object do:

AUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;

To stop auditing:

NOAUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;


To see the results:

SQL> select * from dba_audit_trail;