Mohamed Iqbal: February 2016

Friday, February 19, 2016

Data Pump Export and import Commands with examples

cd /u01/app/TEST_DIR
mkdir test_dir

conn sqlplus '/as sysdba'

CREATE DIRECTORY test_dir AS '/u01/app/TEST_DIR/';
GRANT READ, WRITE ON DIRECTORY test_dir TO system;

Particular table
=================
expdp test/test@testdb tables=kmi directory=TEST_DIR dumpfile=kmi_180216.dmp logfile=kmi_180216.log

impdp test/test@testdb directory=TEST_DIR dumpfile=kmi_180216.dmp logfile=imp_kmi_180216.log remap_schema=test:test1

or

impdp testone/testone@testdb directory=TEST_DIR dumpfile=kmi_180216.dmp logfile=imp_kmi_180216.log remap_schema=test:test1

Full Schema
========

expdp system/manager schemas=test directory=TEST_DIR dumpfile=exp_test_full_180216.dmp logfile=exp_test_full_180216.log

impdpsystem/manager schemas=SCOTT directory=TEST_DIR dumpfile=exp_test_full_180216.dmp logfile=imp_test_full_180216.log remap_schema=test:test1

EXCLUDE/INCLUDE
================

single table
*************
expdp system/manager schemas=test DIRECTORY=TEST_DIR DUMPFILE=exp_test_full_180216.dmp logfile=exp_test_full_180216.log exclude=table:"IN('EMPLOYEE')"

Two tables
**********

expdp system/manager schemas=test DIRECTORY=TEST_DIR DUMPFILE=exp_test_full_180216.dmp logfile=exp_test_full_180216.log exclude=table:"in$'EMPLOYEE'\,'ATTENDANCE'$"

Only procedure/triggers/funcion/type/dblinks
***********************************************

expdp system/manager schemas=test DIRECTORY=TEST_DIR dumpfile=exp_test_full_180216.dmp logfile=exp_test_full_180216.log exclude=table

impdp system/manager directory=TEST_DIR dumpfile=exp_test_full_180216.dmp logfile=imp_test_full_180216.log remap_schema=test:test1

Tuesday, February 16, 2016

Re: ORA-01653: unable to extend table SYS.AUD$ by 62 in tablespace SYSTEM

Toay Morning ,I am getting following error while connecting schema

ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 62 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 62 in tablespace SYSTEM

when i check tablespace there is no SYSTEM tablespace

10:30:08 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name order by 1;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
test_ts 19210.375
SYSAUX 1877.9375
UNDOTBS1 554.875

SYSTEM tablespace was missing

Solution
========

conn sqlplus '/as sysdba'

10:30:33 SQL> Create table Aud$_backup as select * from aud$ tablespace SYSAUX;

Table Created.

Elapsed: 00:00:00.01
10:30:54 SQL>
10:31:20 SQL>
10:31:20 SQL> truncate table aud$;

Table truncated.

10:32:10 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name order by 1;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
test_ts 19210.375
SYSAUX 1877.9375
SYSTEM 1.015625
UNDOTBS1 554.875

Finally ,I added SYSTEM tablesapce (This is optional )

alter tablespace SYSTEM add datafile '/u01/test/system02.dbf' size 2G;

11:12:58 SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
test_ts 19171.375
SYSAUX 1877.875
SYSTEM 2049.02344
UNDOTBS1 549.875

Immediately take full backup and bounce the database
(If the SYSTEM tablespace not added there is no need to take backup)

Suggestions and Comments are Most Welcome

Happy learning

Monday, February 15, 2016

How to export from oracle 11.2 and import into 10.2 version

First : Using Datapump from Oralce 11.2 and import into 10.2 version

Step 1: Create Directory

cd /u01

mkdir test_dir

conn sqlplus '/as sysdba'

CREATE OR REPLACE DIRECTORY test_dir AS '/u01';

GRANT READ, WRITE ON DIRECTORY test_dir TO system;

Step 2: Export Table using Datapump

expdp test/test directory=test_dir dumpfile=exp_test_tables.dmp log=exp_test_tables tables=employee version=10.2

[oracle@testdb]$ expdp test/test directory=test_dir dumpfile=exp_test_tables.dmp log=exp_test_tables tables=employee version=10.2

Export: Release 11.2.0.1.0 - Production on Tue Feb 16 11:54:37 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=exp_test_tables" Location: Command Line, Replaced with: "logfile=exp_test_tables"

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting "TEST_TS"."SYS_EXPORT_TABLE_01": test/******** directory=test_dir dumpfile=exp_test_tables.dmp logfile=exp_test_tables tables=employee version=10.2 reuse_dumpfiles=true

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 80 MB

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/STATISTICS/TABLE_STATISTICS

. . exported "test_ts"."EMPLOYEE" 66.70 MB 290559 rows

Master table "test_ts"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST_TS.SYS_EXPORT_TABLE_01 is:

/u01/exp_employee.dmp

Job "TEST_TS"."SYS_EXPORT_TABLE_01" successfully completed at 11:54:42

Step 3: I just Copied this exp_test_tables.DMP file into target DB 10.2 Directory and import it

[oracle@testdb]$impdp test/test directory=test_dir dumpfile=exp_employee.dmp tables=employee remap_schema=test:test1 remap_tablespace=test_ts:test_test_ts1

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 16 February, 2016 12:48:28

Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

Master table "TEST1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=test_dir dumpfile=exp_test_tables.dmp logfile=exp_test_tables tables=employee remap_schema=test:test1 remap_tablespace=test_ts:test_ts1

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST1"."EMPLOYEE" 66.70 MB 290559 rows

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/STATISTICS/TABLE_STATISTICS

Job "TEST1"."SYS_IMPORT_TABLE_01" completed with no error(s) at 12:48:40

Now I sucessfully import it

Happy learning

Undotablespace Manual creation in Oracle 10g

The following scripts below undotablespace creation in oracle 10g

Step 1:

SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 2078264 bytes
Variable Size 4160752072 bytes
Database Buffers 117440512 bytes
Redo Buffers 14696448 bytes
Database mounted.

Step 2:

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

Step 3:

Change the UNDO_MANAGEMENT to ‘MANUAL’

alter system set undo_management='MANUAL' scope=spfile;

Step 4 :

As, the above parameter is a static one, bounce the database

SQL> shu immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 2078264 bytes

Variable Size 4160752072 bytes

Database Buffers 117440512 bytes

Redo Buffers 14696448 bytes

Database mounted.

Database opened.

Step 5 :

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE

'/u01/TEST/undotbs21.dbf' SIZE 4294967296,

'/u04/TEST/undotbs22.dbf' SIZE 4294967296,

'/u02/TEST/undotbs23.dbf' SIZE 6442450944,

'/u03/TEST/undotbs24.dbf' SIZE 6442450944

BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

Step 6:

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string UNDOTBS1

Set the undo_tablespace to the new Undo Tablespace name and undo_management back to ‘AUTO’

SQL> alter system set undo_tablespace='UNDOTBS' scope=spfile;

System altered.

SQL> alter system set undo_management='AUTO' scope=spfile;

System altered.

Step 7:

Bounce the database

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 2078264 bytes

Variable Size 4160752072 bytes

Database Buffers 117440512 bytes

Redo Buffers 14696448 bytes

Database mounted.

Database opened.

Check the Alert log for if any errors and check the undo parameters

Step 8 :

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS

STEPS TO CREATE ORACLE DATABASE 10g MANUALLY ON LINUX

This article shows you steps to create a database manually on Linux.

Step 1:

First create all the necessary directories. Followings are my directories:

cd /u01

mkdir oradata

cd oradata

mkdir TEST

cd /u01

mkdir admin

cd admin

mkdir TEST

cd TEST

mkdir bdump

mkdir cdump

mkdir udump

cd /u02

mkdir oradata

cd oradata

mkdir TEST

cd /u03

mkdir oradata

cd oradata

mkdir oradata

cd /u04

mkdir oradata

cd oradata

mkdir TEST

Step 2:

create orapassword file

cd $ORACLE_HOME/dbs

orapwd file=orapwTEST password=sys

Step 3:

Prepare the init file. Like this one [initTEST.ora ]

vi initTEST.ora

db_domain=''

db_name=TEST

compatible='9.2.0.0.0'

background_dump_dest='/u01/admin/TEST/bdump'

core_dump_dest='/u01/admin/TEST/cdump'

user_dump_dest='/u01/admin/TEST/udump'

control_files='/u01/oradata/TEST/control01.ctl','/u02/oradata/TEST/control02.ctl','/u03/oradata/TEST/control03.ctl'

instance_name=TEST

db_block_size=8192

undo_management=AUTO

undo_tablespace=UNDOTBS

export ORACLE_SID=TEST

sqlplus /nolog

Step 4:

Now perform the following steps:

export ORACLE_SID=TEST

sqlplus /nolog

sql>connect sys as sysdba

sys

startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initTEST.ora'

CREATE DATABASE TEST

LOGFILE GROUP 1 ('/u01/oradata/TEST/redo01.log') SIZE 50M, GROUP 2 ('/u02/oradata/TEST/redo02.log') SIZE 50M, GROUP 3 ('/u03/oradata/TEST/redo03.log') SIZE 50M,GROUP 4 ('/u04/oradata/TEST/redo04.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/oradata/TEST/system01.dbf' SIZE 2G

SYSAUX DATAFILE '/u01/oradata/TEST/sysaux.dbf' SIZE 2G

UNDO TABLESPACE UNDOTBS DATAFILE '/u01/oradata/TEST/undotbs.dbf' SIZE 2G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> alter database default temporary tablespace temp ;

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initTEST.ora' ;

SQL> select name , open_mode from v$database ;

NAME OPEN_MODE

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

TEST READ WRITE

SQL> shu immediate ;

SQL> startup ;

Okay, now your database is ready to use.

Friday, February 12, 2016

oracle 11g database manual creation

1)[oracle@localhost]cd /u01

[oracle@localhost/u01]mkdir test

[oracle@localhost/u01/test]cd test

mkdir adump diag flash_recovery_area

2) cd $ORACLE_HOME/dbs
orapwd file=orapwtest password=sys

3) [oracle@localhost test]$ cd $ORACLE_HOME/dbs

4) [oracle@localhost dbs]$ vi inittest.ora

db_name='test'

memory_target=1G

processes = 150

audit_file_dest='/u01/test/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/test/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/test/diag'

dispatchers='(PROTOCOL=TCP) (SERVICE=test)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

# You may want to ensure that control files are created on separate physical

# devices

control_files = (/u01/test/ora_control1.ctl,/u01/test/ora_control2.ctl)

compatible ='11.2.0'

:wq!

6) sqlplus '/as sysdba'

startup nomount pfile='$ORACLE_HOME/dbs/inittest.ora';

CREATE DATABASE test

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

LOGFILE

GROUP 1 '/u01/test/redo1.log' SIZE 50M,

GROUP 2 '/u01/test/redo2.log' SIZE 50M,

GROUP 3 '/u01/test/redo3.log' SIZE 50M

DATAFILE

'/u01/test/system.dbf' size 200M REUSE

sysaux datafile '/u01/test/sysaux.dbf' size 2G

undo tablespace UNDOTBS1

datafile '/u01/test/undo1.dbf' size 2G

DEFAULT TEMPORARY TABLESPACE temp1

TEMPFILE '/u01/test/temp01.dbf'

SIZE 2G REUSE

CHARACTER SET AL32UTF8;

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/manager

SQL> conn system

Enter password:

Connected.

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

then shutdown the database and startup

7) create tablespace

CREATE TABLESPACE test_ts DATAFILE

'/u01/test/test_ts_01.dbf' SIZE 4294967296,

'/u01/test/test_ts_02.dbf' SIZE 4294967296,

'/u01/test/test_ts_03.dbf' SIZE 4294967296,

'/u01/test/test_ts_04.dbf' SIZE 4294967296,

'/u01/test/test_ts_05.dbf' SIZE 4294967296

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

create user

create user test

identified by test

default tablespace test_ts

temporary tablespace temp1;

if you have any doubt contact iqsha77@gmail.com