Database Administration(oracle 10g)

Before any other concept, the first thing you need to be cleared is with ARCHITECTURE of the database.

under database administration there are many concepts which need to be discussed before implementation's.these includes:

1.Auditing of Database :

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object

Auditing is typically used to:

• Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content
  
  
• Deter users (or others) from inappropriate actions based on that accountability
  
  
• Investigate suspicious activity
  
  For example, if some user is deleting data from tables, then the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
  
  
• Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations
  
  
• Monitor and gather data about specific database activities
  
  For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
  
  
• Detect problems with an authorization or access control implementation
  

SQL> alter system set audit_trail=db,extended scope=spfile;

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.


Total System Global Area 603979776 bytes

Fixed Size 1291988 bytes

Variable Size 234883372 bytes

Database Buffers 360710144 bytes

Redo Buffers 7094272 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter audit


NAME TYPE VALUE

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

audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN

\RAC\ADUMP

audit_sys_operations boolean FALSE

audit_trail string DB, EXTENDED

SQL>

SQL> audit all by scott by access;


Audit succeeded.


SQL> select * from dba_audit_trail;


no rows selected


SQL> conn scott/tiger

Connected.

SQL>

SQL> create table test(id number);


Table created.

SQL> conn sys/sys as sysdba

Connected.

SQL>

SQL> select username,TIMESTAMP,SQL_TEXT from dba_audit_trail;


USERNAME TIMESTAMP SQL_TEXT

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

SCOTT 28-JAN-11

SCOTT 28-JAN-11 create table test(id number)

2. import/export of database

The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.

An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.

Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.

working queries of import/export :-

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
/ade/aime_10.2_lnx_push/oracle/md/admin

SYS SUBDIR
/oracle/app/product/10.2.0/db_1/demo/schema/order_entry//2002/Sep

SYS DATA_FILE_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/sales_history/


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS WORK_DIR
/ade/aime_10.2_lnx_push/oracle/work

SYS LOG_FILE_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/log/

SYS MEDIA_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/product_media/


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS XMLDIR
/oracle/app/product/10.2.0/db_1/demo/schema/order_entry/

SYS DATA_PUMP_DIR
/oracle/app/product/10.2.0/db_1/rdbms/log/


8 rows selected.

SQL>
SQL>
SQL> define
DEFINE _DATE = "18-FEB-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "gedit" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SQL>
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ mkdir /oracle/dir
[oracle@localhost ~]$ exit
exit

SQL>
SQL>
SQL> create directory dir as '/oracle/dir';

Directory created.

SQL>
SQL> grant read,write on directory dir to public;

Grant succeeded.

SQL>
SQL> grant exp_full_database,imp_full_database to system;

Grant succeeded.

SQL>
SQL>
SQL> --table level export and import
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ expdp scott/tiger tables=emp,dept directory=dir dumpfile=ed.dmp logfile=ed.log


Export: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:16:46

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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp,dept directory=dir dumpfile=ed.dmp logfile=ed.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/oracle/dir/ed.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 09:17:03

[oracle@localhost ~]$ exit
exit

SQL> drop table scott.emp purge;

Table dropped.

SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> host
[oracle@localhost ~]$ impdp scott/tiger tables=emp dumpfile=ed.dmp directory=dir logfile=emp.log

Import: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:18:43

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
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp dumpfile=ed.dmp directory=dir logfile=emp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 09:18:46

[oracle@localhost ~]$ exit
exit

SQL> select * From scott.emp
2 where comm is not null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30



2.Schema or User level export and import

SQL> --schema level export
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ expdp schemas=scott directory=dir dumpfile=scott.dmp logfile=scott.log

Export: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:21:43

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

Username: system/sys

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=scott directory=dir dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/oracle/dir/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:22:08

[oracle@localhost ~]$ exit
exit

SQL> drop user scott cascade;

User dropped.

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> host
[oracle@localhost ~]$ impdp schemas=scott dumpfile=scott.dmp directory=dir

Import: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:23:07

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

Username: system/sys

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=scott dumpfile=scott.dmp directory=dir
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
. . imported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 09:23:13

[oracle@localhost ~]$ exit
exit

SQL> conn scott/tiger
Connected.
SQL>
SQL> select * From tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
EMP TABLE



3.Exporting and Importing full database:-

]$ expdp full=y dumpfile=full.dmp directory=dir logfile=full.log

]$ impdp full=y dumpfile=full.dmp directory=dir logfile=full.log




More database concepts will be shared soon. hope for good feedback 😀