Table Re-organization And Index Re-Build

• As a Oracle Database Administrator before doing table Re-org activity ensure export backup for tables
• During table Re-org archives are getting generated frequently
• Keep monitor archive mount point during the reorganization activity
• Create temporary tablespace for Re-org activity

1. Take export backup dump for the table

EXP file=TB_TXN_HISTORY-BEFORE-REORG.dmp log=TB_TXN_HISTORY-BEFORE-REORG.log tables=MY_APP.TB_TXN_HISTORY

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

MYORADB   READ WRITE

2. Check and list out the no. of invalid objects at database level and schema level

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

  --------

         7

SQL> select count(*) from dba_objects where status='INVALID' and owner='MY_APP';

  COUNT(*)

  --------

         4

3. Check the no. of rows available in table

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS from dba_tables where OWNER='MY_APP' and TABLE_NAME IN('TB_TXN_HISTORY');

OWNER   TABLE_NAME          TABLESPACE_NAME              NUM_ROWS

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

MY_APP  TB_TXN_HISTORY      TS_MY_APP_DAT                25983023

4. Check the owner,tablespace,size and segment type

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME, BYTES/1024/1024 from dba_segments where owner='MY_APP' and SEGMENT_NAME In('TB_TXN_HISTORY');

OWNER  SEGMENT_NAME    SEGMENT_TYPE  TABLESPACE_NAME BYTES/1024/1024

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

MY_APP TB_TXN_HISTORY  TABLE         TS_MY_APP_DAT   8748

5. Check indexes related to table and Indexes Size

SQL> select OWNER,INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS,DEGREE,BLEVEL from dba_indexes where owner='MY_APP' and table_name='TB_TXN_HISTORY';

OWNER    INDEX_NAME           TABLE_NAME      TABLESPACE_NAME  STATUS   DEGREE  BLEVEL

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

MY_APP   IX_TB_TXN_HISTORY01  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

MY_APP   IX_TB_TXN_HISTORY02  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

MY_APP   IX_TB_TXN_HISTORY03  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       2

MY_APP   IX_TB_TXN_HISTORY04  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

MY_APP   IX_TB_TXN_HISTORY05  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

SQL>  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,

BYTES/1024/1024 from dba_segments where owner='MY_APP' and segment_name in('IX_TB_TXN_HISTORY01', 'IX_TB_TXN_HISTORY02','IX_TB_TXN_HISTORY03','IX_TB_TXN_HISTORY04', 'IX_TB_TXN_HISTORY05');

OWNER  SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME           BYTES/1024/1024

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

MY_APP IX_TB_TXN_HISTORY01       INDEX              TS_MY_APP_IDX             2304

MY_APP IX_TB_TXN_HISTORY02       INDEX              TS_MY_APP_IDX             2624

MY_APP IX_TB_TXN_HISTORY03       INDEX              TS_MY_APP_IDX             1008

MY_APP IX_TB_TXN_HISTORY04       INDEX              TS_MY_APP_IDX             2295

MY_APP IX_TB_TXN_HISTORY05       INDEX              TS_MY_APP_IDX             3264

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024

from dba_segments where segment_name in('TB_TXN_HISTORY');

OWNER    SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME     BYTES/1024/1024

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

MY_APP   TB_TXN_HISTORY            TABLE              TS_MY_APP_DAT       8748

6.1 we can re-org the table in same tablespace if sufficient free space available in same tablespace.

 SQL>Alter table MY_APP.TB_TXN_PALLETIZING move tablespace TS_MY_APP_DAT;

6.2 We can re-org the table in another tablespace if sufficient free space is not available

 6.2.1 Create New temporary tablespace to re-org the table

 SQL> Create tablespace TEMP_REORG datafile '/ILAPP/DATA1/TEMP_REOG01.dbf' size 10240M autoextend off;

 6.2.2 Reorg table to newly created tablespace

 SQL> Alter table MY_APP.TB_TXN_PALLETIZING move tablespace TEMP_REORG;

 6.2.3 Revert back table to original tablespace after releasing fregmentation

 SQL>Alter table MY_APP.TB_TXN_PALLETIZING move tablespace TS_MY_APP_DAT;

7. Rebulid all indexes related to table

SQL> alter index MY_APP.IX_TB_TXN_HISTORY01 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY02 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY03 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY04 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY05 rebuild tablespace TS_MY_APP_IDX online;

Note : Stabdard Edition doesn't support online rebuild

8. Check indexes status and degree

SQL> select OWNER,INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS,DEGREE,BLEVEL from dba_indexes where table_name='TB_TXN_HISTORY' and owner='MY_APP';

OWNER    INDEX_NAME           TABLE_NAME           TABLESPACE_NAME   STATUS  DEGREE    BLEVEL

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

MY_APP   IX_TB_TXN_HISTORY01  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

MY_APP   IX_TB_TXN_HISTORY02  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

MY_APP   IX_TB_TXN_HISTORY03  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         2

MY_APP   IX_TB_TXN_HISTORY04  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

MY_APP   IX_TB_TXN_HISTORY05  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

9. Cross check segments size after re-org

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where owner='MY_APP' and SEGMENT_NAME In('TB_TXN_HISTORY');

OWNER    SEGMENT_NAME      SEGMENT_TYPE    TABLESPACE_NAME           BYTES/1024/1024

------   --------------    ------------    ---------------           ---------------
MY_APP   TB_TXN_HISTORY    TABLE           TS_MY_APP_DAT             5120

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS from dba_tables where OWNER='MY_APP' and TABLE_NAME IN('TB_TXN_HISTORY');

OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS

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

MY_APP                         TB_TXN_HISTORY                 TS_MY_APP_DAT                    49863007

SQL> select OWNER,INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS,DEGREE,BLEVEL from dba_indexes where owner='MY_APP' and table_name in('TB_TXN_HISTORY');

OWNER  INDEX_NAME           TABLE_NAME          TABLESPACE_NAME STATUS   DEGREE  BLEVEL

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

MY_APP IX_TB_TXN_HISTORY01  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

MY_APP IX_TB_TXN_HISTORY02  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       2

MY_APP IX_TB_TXN_HISTORY03  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

MY_APP IX_TB_TXN_HISTORY04  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

MY_APP IX_TB_TXN_HISTORY05  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

SQL>  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where owner='MY_APP' and segment_name in('PK_TB_TXN_HISTORY','PK_TB_TXN_LOTTRANSACTION02','IX_TB_TXN_LOTTRANSACTION03','IX_TB_TXN_LOTTRANSACTION04','IX_TB_TXN_LOTTRANSACTION05','IX_TB_TXN_PALLETIZING01','PK_TB_TXN_PALLETIZING02','IX_TB_TXN_PALLETIZING03','PK_TB_TXN_PALLETIZING03','IX_TB_TXN_HISTORY01','IX_TB_TXN_HISTORY03','IX_TB_TXN_HISTORY05','IX_TB_TXN_HISTORY06','IX_TB_TXN_HISTORY02','IX_TB_TXN_LOTTRANSACTION01','PK_TB_TXN_PALLETIZING04');

OWNER  SEGMENT_NAME                        SEGMENT_TYPE       TABLESPACE_NAME BYTES/1024/1024

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

MY_APP IX_TB_TXN_HISTORY01                 INDEX              TS_MY_APP_IDX             1024

MY_APP IX_TB_TXN_HISTORY02                 INDEX              TS_MY_APP_IDX             1800

MY_APP IX_TB_TXN_HISTORY03                 INDEX              TS_MY_APP_IDX             750

MY_APP IX_TB_TXN_HISTORY04                 INDEX              TS_MY_APP_IDX             1098

MY_APP IX_TB_TXN_HISTORY05                 INDEX              TS_MY_APP_IDX             2004

10 Finally cross check all invalid objects at db level and schema level

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

  --------

         7

SQL> select count(*) from dba_objects where status='INVALID' and owner='MY_APP';

  COUNT(*)

  --------

         4

Note:  As per oracle recomend perform gather stats

exec DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME =>'MY_APP', TABNAME =>'TB_TXN_HISTORY', ESTIMATE_PERCENT => 50, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 4, GRANULARITY => 'ALL', CASCADE =>TRUE, NO_INVALIDATE =>FALSE);

######################################################  All The Best ######################################################

Oracle Auditing

Auditing in Oracle:

The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary.

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

init parameters
Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.
From Oracle 11g, auditing is enabled for some system level privileges.

SQL> show parameter audit
NAME                  TYPE VALUE
-----------------     --------   ----------
audit_file_dest       string ?/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string NONE
audit_trail           string DB
transaction_auditing boolean TRUE

AUDIT_TRAIL can have the following values.

AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}
Download Pdf Multiples Base Table Cause Problems Change Number Parameter

The following list provides a description of each value:

NONE or FALSE -> Auditing is disabled. Default until Oracle 10g.

DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.

DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.

XML-> Auditing is enabled, with all audit records stored as XML format OS files.

XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.

OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.

Note: In Oracle 10g Release 1, DB_EXTENDED was used in place of "DB,EXTENDED". The XML options were brought in Oracle 10g Release 2.

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.

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.

Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS (no need to run this, if you ran catalog.sql at the time of database creation).

Start Auditing

Syntax of audit command:
audit {statement_option|privilege_option} [by user] [by {session|access}] [whenever {successful|not successful}]
Sql Statements Table All System The Users Download Pdf Multiples Base Table

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

There are three levels that can be audited:

Statement level

Auditing will be done at statement level.
Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.

SQL> audit table by scott;

Audit records can be found in DBA_STMT_AUDIT_OPTS.

SQL> select * from DBA_STMT_AUDIT_OPTS;

Object level

Auditing will be done at object level.
These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.

SQL> audit insert, update, delete on scott.emp by hr;

Audit records can be found in DBA_OBJ_AUDIT_OPTS.

SQL> select * from DBA_OBJ_AUDIT_OPTS;

Privilege level

Auditing will be done at privilege level.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.

SQL> audit create tablespace, alter tablespace by all;

Specify ALL PRIVILEGES to audit all system privileges.

Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;

Audit options

BY SESSION

Specify BY SESSION if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.

SQL> audit create, alter, drop on currency by xe by session;
SQL> audit alter materialized view by session;

BY ACCESS 

Specify BY ACCESS if you want Oracle database to write one record for each audited statement and operation.

If you specify statement options or system privileges that audit data definition language (DDL) statements, then the database automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;

WHENEVER [NOT] SUCCESSFUL 
Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed.
Specify WHENEVER NOT SUCCESSFUL to audit only SQL statements and operations that fail or result in errors.

If you omit this clause, then Oracle Database performs the audit regardless of success or failure.

SQL> audit insert, update, delete on hr.emp by hr by session whenever not successful;
SQL> audit materialized view by pingme by access whenever successful;

Examples
Auditing for every SQL statement related to roles (create, alter, drop or set a role).

SQL> AUDIT ROLE;

Auditing for every statement that reads files from database directory

SQL> AUDIT READ ON DIRECTORY ext_dir;

Auditing for every statement that performs any operation on the sequence

SQL> AUDIT ALL ON hr.emp_seq;

View Audit Trail

The audit trail is stored in the base table SYS.AUD$.
It's contents can be viewed in the following views:
· DBA_AUDIT_TRAIL
· DBA_OBJ_AUDIT_OPTS
· DBA_PRIV_AUDIT_OPTS
· DBA_STMT_AUDIT_OPTS
· DBA_AUDIT_EXISTS
· DBA_AUDIT_OBJECT
· DBA_AUDIT_SESSION
· DBA_AUDIT_STATEMENT
· AUDIT_ACTIONS
· DBA_AUDIT_POLICIES
· DBA_AUDIT_POLICY_COLUMNS
· DBA_COMMON_AUDIT_TRAIL
· DBA_FGA_AUDIT_TRAIL (FGA_LOG$)
· DBA_REPAUDIT_ATTRIBUTE
· DBA_REPAUDIT_COLUMN

The audit trail contains lots of data, but the following are most likely to be of interest:
 Username - Oracle Username.
 Terminal - Machine that the user performed the action from.
 Timestamp - When the action occurred.
 Object Owner - The owner of the object that was interacted with.
 Object Name - name of the object that was interacted with.
 Action Name - The action that occurred against the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE)

Fine Grained Auditing (FGA), introduced in Oracle9i, allowed recording of row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete.
From Oracle 10g, FGA supports DML statements in addition to selects.

Several fields have been added to both the standard and fine-grained audit trails:
EXTENDED_TIMESTAMP - A more precise value than the existing TIMESTAMP column.
PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
GLOBAL_UID - Global Universal Identifier for an enterprise user.
INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
OS_PROCESS - Operating system process id for the oracle process.
TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
SCN - System change number of the query. This column can be used in flashback queries.
SQL_BIND - The values of any bind variables if any.
SQL_TEXT - The SQL statement that initiated the audit action.
The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization parameter is set.
Maintenance
The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.

Only users who have been granted specific access to SYS.AUD$ can access the table to select, alter or delete from it. This is usually just the user SYS or any user who has permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.

Auditing modifications of the data in the audit trail itself can be achieved as follows

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

To delete all audit records from the audit trail:

SQL> DELETE FROM sys.aud$;

From Oracle 11g R2, we can change audit table's (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT package.

Disabling Auditing

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;

######################################################  All The Best ######################################################

Oracle Database Archive Enabling

How to enable Archivelog mode in Oracle database 11g Mode of Logging

There are two types of logging modes in Oracle database :-

1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to re-enter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup. After that users have to re-enter their data.

How to check log mode in Oracle database 11g :-

[cognos@rac1 u02]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012

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

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /backup/orcl/

Oldest online log sequence     1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

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

ORCL      NOARCHIVELOG

Currently the ORCL database is in NOARCHIVELOG mode

To change the Oracle database in ARCHIVELOG mode. PFB below mentioned steps:-

1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile;

System altered.

Note -- To change this parameter while database is open, your database has to run with scope=SPFILE option, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE

2. After that you need to shut down your database and start again in MOUNT mode

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u02/archive

log_archive_dest_10                  string

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archive
Oldest online log sequence     6
Next log sequence to archive   8

Current log sequence           8

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/archive

Oldest online log sequence     6

Next log sequence to archive   8

Current log sequence           8

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

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

ORCL      ARCHIVELOG

Database changed to ARCHIVELOG mode.

Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database immediately because in recovery scenarios you can recover your database from the last backup taken in this mode.


To change the Oracle database in NOARCHIVELOG mode. PFB below mentioned steps:-
1.Shutdown your running database.

SQL> shut immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.

2. Start your database in MOUNT mode.

SQL>    startup mount;
ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size                  2176368 bytes
Variable Size            1023412880 bytes
Database Buffers          654311424 bytes
Redo Buffers                7024640 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            D:/data_files/archive
Oldest online log sequence     6

Current log sequence           8

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
XYZ       READ WRITE

SQL>
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            D:/data_files/archive
Oldest online log sequence     6
Current log sequence           8


SQL> select name,log_mode from v$database;

-----     ------------
ORCL      NOARCHIVELOG

######################################################  All The Best ######################################################

Load data using SQL*Loader

How to load data using SQL*Loader

SQL*Loader uses the following files:

Loader control file: Contain input format, output tables, and optional conditions
Input data files: Contain data in the format defined in the control file
Parameter file (optional file): Consist of command line parameters for the load
Log file:  Automatically created by SQL loader.
Bad file: Contain rejected records.
Discard file (if required): Store all records that did not satisfy the selection criteria.

SQL*Loader Control File Example

LOAD DATA
INFILE ’ /SQL_LOADER / Employee_Details.csv’
BADFILE ’ Employee_Details.bad’
DISCARDFILE ’ Employee_Details.dsc’
APPEND
INTO TABLE LGHR.employee
WHEN (57) = ’.’
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS(EMP_NO ,
ename "UPPER(:ename)",
DOJ "TO_TIMESTAMP(:DOJ,'MM/DD/YYYY HH:MI:SS AM')",
sal CHAR TERMINATED BY WHITESPACE  "TO_NUMBER(:sal,’$99,999.99’)",
FLAG TERMINATED BY WHITESPACE
)

SQL*Loader Command

sqlldr userid=system/manager 
control=LGHR_employee_table.ctllog=LGHR_employee_table.log


Control file explanation:

1. LOAD DATA specifies the beginning of a new data load.
2. INFILE specifies the name of a file containing data 
that you want to load.
3. BADFILE specifies the name of a file into which
rejected records are placed.
4. DISCARDFILE specifies the name of a file into which
discarded records are placed.
5. APPEND for loading data into a table that is not empty. 
To load data into a table that is empty, you use the INSERT keyword.
6. INTO TABLE for identify tables, fields, and data types. 
It defines the relationship between records in the data file and tables in the database.
7. WHEN specifies one or more field conditions that each record must match.In this example SQL*Loader
will only load the record if the 57th character is 
a decimal point.
8. TRAILING NULLCOLS tells SQL*Loader to treat any 
relatively positioned columns that are not present in the record as null columns.
9. The remainder of the control file contains the field list, which provides information about column formats in the table that is being loaded.

######################################################  All The Best ######################################################

How To Copy or Move files in Database Offline Mode

             

1. Before looking at the tasks involved to perform the moving files, let's look at the current files:                                    

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME     SUBSTR(FILE_NAME,1,70)

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

SYSTEM             /home/oracle/OraHome1/databases/ora9/system.dbf

UNDO               /home/oracle/OraHome1/databases/ora9/undo.dbf

DATA               /home/oracle/OraHome1/databases/ora9/data.dbf

SQL> select member from v$logfile;

MEMBER

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

/home/oracle/OraHome1/databases/ora9/redo1.ora

/home/oracle/OraHome1/databases/ora9/redo2.ora

/home/oracle/OraHome1/databases/ora9/redo3.ora

SQL> select name from v$controlfile;

NAME

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

/home/oracle/OraHome1/databases/ora9/ctl_1.ora

/home/oracle/OraHome1/databases/ora9/ctl_2.ora

/home/oracle/OraHome1/databases/ora9/ctl_3.ora

Now, as the files to be moved are known, the database can be shut down:

2. Create pfile and shutdown DB

 SQL> create pfile from spfile;

File created.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Create directory structure(/home/oracle/databases/ora9) and copy all files from old location to new location.

$ cp /home/oracle/OraHome1/databases/ora9/system.dbf  /home/oracle/databases/ora9/system.dbf

$ cp /home/oracle/OraHome1/databases/ora9/undo.dbf    /home/oracle/databases/ora9/undo.dbf

$ cp /home/oracle/OraHome1/databases/ora9/data.dbf    /home/oracle/databases/ora9/data.dbf

$

$ cp /home/oracle/OraHome1/databases/ora9/redo1.ora   /home/oracle/databases/ora9/redo1.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo2.ora   /home/oracle/databases/ora9/redo2.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo3.ora   /home/oracle/databases/ora9/redo3.ora

$

$ cp /home/oracle/OraHome1/databases/ora9/ctl_1.ora   /home/oracle/databases/ora9/ctl_1.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_2.ora   /home/oracle/databases/ora9/ctl_2.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_3.ora   /home/oracle/databases/ora9/ctl_3.ora

The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.

$ cp /home/oracle/OraHome1/dbs/initORA9.ora /home/oracle/databases/ora9/init.ora

The new location for the control files must be written into the (copied) init.ora file:

/home/oracle/databases/ora9/init.ora

4. Open Pfile and make change control files location

control_files = (/home/oracle/databases/ora9/ctl_1.ora,

                 /home/oracle/databases/ora9/ctl_2.ora,

                 /home/oracle/databases/ora9/ctl_3.ora)

$ sqlplus "/ as sysdba"

5. Startup Database in Exclusive mode with modified controlfile

SQL> startup exclusive mount pfile=/home/oracle/databases/ora9/init.ora

ORACLE instance started.

Total System Global Area  143725064 bytes

Fixed Size                   451080 bytes

Variable Size             109051904 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

6. Rename all files(Database files and Redo log files) in mount stage

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/system.dbf' to '/home/oracle/databases/ora9/system.dbf';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/undo.dbf'   to '/home/oracle/databases/ora9/undo.dbf';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/data.dbf'   to '/home/oracle/databases/ora9/data.dbf';

SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo1.ora'  to '/home/oracle/databases/ora9/redo1.ora';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo2.ora'  to '/home/oracle/databases/ora9/redo2.ora';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo3.ora'  to '/home/oracle/databases/ora9/redo3.ora';

SQL> Database altered.

7. Shutdown Database and startup with modified Pfile , Create SP file from current and startup with newly created spfile

SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down.  SQL> startup pfile='/home/oracle/databases/ora9/init.ora' SQL>create spfile from pfile; SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL>startup; ORACLE instance started. Total System Global Area  143725064 bytes Fixed Size                   451080 bytes Variable Size             109051904 bytes Database Buffers           33554432 bytes Redo Buffers                 667648 bytes Database mounted. Database opened.;

8. Finally verify the Control, Data & Redolog files location.

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME    SUBSTR(FILE_NAME,1,70)

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

SYSTEM             /home/oracle/databases/ora9/system.dbf

UNDO               /home/oracle/databases/ora9/undo.dbf

DATA               /home/oracle/databases/ora9/data.dbf

SQL> select member from v$logfile;

MEMBER

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

/home/oracle/databases/ora9/redo1.ora

/home/oracle/databases/ora9/redo2.ora

/home/oracle/databases/ora9/redo3.ora

SQL> select name from v$controlfile;

NAME

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

/home/oracle/databases/ora9/ctl_1.ora

/home/oracle/databases/ora9/ctl_2.ora

/home/oracle/databases/ora9/ctl_3.ora

*********************************************** All The Best ***********************************************