Oracle: September 2012

Thursday, September 27, 2012

Datapump PARTITION_OPTION parameter

PARTITION_OPTIONS= NONE | DEPARTITION | MERGE

· NONE: The partitions are created exactly as they were on the system the export was taken from.

· DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-) partition name.

· MERGE: Combines all partitions into a single table.

Here I want to Import schema1 partitioned tables to schema2 non-partitioned tables, to do so

1) Take data pump complete Export of schema1

2) Import into schema2 as below

NOTE : Here “schema2” is not there but creates automatically during the Datapump import process

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

$ impdp dumpfile=xxx.dmp directory=EXP remap_schema=schema1:schema2 PARTITION_OPTIONS=merge

Import: Release 11.2.0.2.0 - Production on Thu Sep 27 20:38:11 2012

Username: / as sysdba

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

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

Master table "SYS"."SYS_IMPORT_FULL_05" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_05": /******** AS SYSDBA dumpfile=xxx.dmp directory=EXP remap_schema= schema1:schema2 PARTITION_OPTIONS=merge

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/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCHEMA2"."TABLE1":"SYS_P23561" 14.92 KB 34 rows

-------- OUTPUT TRIMMED ------------------

Job "SYS"."SYS_IMPORT_FULL_05" completed with 12 error(s) at 20:39:53

A new schema2 will be created and all the partitioned tables are created as Single tables (non-partitioned)

Tuesday, September 18, 2012

STEPS TO CREATE ORACLE DATABASE MANUALLY ON LINUX

Step 1:
Prepare the database creation script. Following is my script "testdb.sql"

CREATE DATABASE TESTDB

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/TESTDB/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/TESTDB/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/TESTDB/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/TESTDB/system01.dbf' SIZE 200M,

'/opt/oracle/TESTDB/users01.dbf' SIZE 100M

sysaux datafile '/opt/oracle/TESTDB/sysaux01.dbf' SIZE 200M

undo tablespace undotbs1

datafile '/opt/oracle/TESTDB/undotbs01.dbf' SIZE 100M

CHARACTER SET WE8MSWIN1252

;

NOTE : You can get this similar script , when you perform ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’, we can edit this script.

Step 2:

Create all the necessary directories.

Oracle:/opt/oracle $ mkdir TESTDB

Similarly create all necessary directories

Step 3:

Prepare the init file(pfile) [initTESTDB.ora]

TESTDB.__db_cache_size=436207616

TESTDB.__java_pool_size=4194304

TESTDB.__large_pool_size=4194304

TESTDB.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment

TESTDB.__pga_aggregate_target=432013312

TESTDB.__sga_target=641728512

TESTDB.__shared_io_pool_size=0

TESTDB.__shared_pool_size=184549376

TESTDB.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/TESTDB/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/opt/oracle/TESTDB/control01.ctl','/opt/oracle/fra/TESTDB/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='TESTDB'

*.db_recovery_file_dest='/opt/oracle/fra'

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest='/opt/oracle/'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'

#*.local_listener='LISTENER_TESTDB'

*.memory_target=1073741824

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS'

Step 4:

Now perform the following steps:

$ export ORACLE_SID=TESTDB

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008

Connected to an idle instance.

SQL >

SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initTESTDB.ora

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1263176 bytes

Variable Size 167774648 bytes

Database Buffers 427819008 bytes

Redo Buffers 7122944 bytes

SQL> @testdb.sql

Database created.

Step 5:

So your database is created. Now just run the catalog.sql and catproc.sql scripts.

You will find the in $ cd $ORACLE_HOME/rdbms/admin

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

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

This will create all dictionary views, now verify

SQL> select name from v$database;

NAME

---------

TESTDB

Okay, now your database is ready to use.

Steps to change database name only

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.7 (Tikanga)

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

Now, I want to change only database name from DUMMYDB to DEMODB for my standalone dev database

Follow the same steps from my last post Steps to Rename database name and DBID

NOTE: This is tested on my Standalone Development database, for RAC please see [Doc ID 464922.1]

STEP 1 ) Shut down the database and open database in mount

SQL> select name from v$database;

NAME

---------

DUMMYDB

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

DUMMYDB OPEN

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 683674632 bytes

Database Buffers 377487360 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL>

Step 2) Invoke the DBNEWID utility (nid) specifying the new database name in DBNAME and set=YES

$ nid TARGET=/ dbname=DEMODB SETNAME=YES

DBNEWID: Release 11.2.0.2.0 - Production on Tue Sep 18 14:00:18 2012

Connected to database DUMMYDB (DBID=471263051)

Connected to server version 11.2.0

Control Files in database:

/opt/oracle/oradata/TESTDB/control01.ctl

/opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl

Change database name of database DUMMYDB to DEMODB? (Y/[N]) => Y

Proceeding with operation

Changing database name from DUMMYDB to DEMODB

Control File /opt/oracle/oradata/TESTDB/control01.ctl - modified

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - modified

Datafile /opt/oracle/oradata/TESTDB/system01.db - wrote new name

Datafile /opt/oracle/oradata/TESTDB/users01.db - wrote new name

Datafile /opt/oracle/oradata/TESTDB/sysaux01.db - wrote new name

Datafile /opt/oracle/oradata/TESTDB/undotbs01.db - wrote new name

Control File /opt/oracle/oradata/TESTDB/control01.ctl - wrote new name

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - wrote new name

Instance shut down

Database name changed to DEMODB.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

Step 3) Make changes to pfile and password file and opened the database with resetlogs option

$ cd $ORACLE_HOME/dbs

Edit pfile ----> initDEMODB.ora

Vi initDEMODB.ora

Change ‘dbname’

NOTE: database need not to be open with RESTLOGS option

$ export ORACLE_SID=DEMODB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 14:03:10 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 616565768 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

Database mounted.

Database opened.

SQL> select name from v$database;

NAME

---------

DEMODB

Now, your database name has been changed :-)

See: How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Doc ID 863800.1)

Monday, September 17, 2012

Steps to Change database name and DBID

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.7 (Tikanga)

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

Here I’m renaming TESTDB database name to DEMODB database including DBID

STEP 1 ) Shut down the database and open database in mount

SQL> select name from v$database;

NAME

---------

TESTDB

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

TESTDB OPEN

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 683674632 bytes

Database Buffers 377487360 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL>

Step 2) Invoke the DBNEWID utility (nid) specifying the new database name in DBNAME

$ nid target=/ dbname=DEMODB

DBNEWID: Release 11.2.0.2.0 - Production on Mon Sep 17 14:45:30 2012

Connected to database TESTDB (DBID=2577169847)

Connected to server version 11.2.0

Control Files in database:

/opt/oracle/oradata/TESTDB/control01.ctl

/opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl

Change database ID and database name TESTDB to DEMODB? (Y/[N]) => y

Proceeding with operation

Changing database ID from 2577169847 to 3790527754

Changing database name from TESTDB to DEMODB

Control File /opt/oracle/oradata/TESTDB/control01.ctl - modified

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - modified

Datafile /opt/oracle/oradata/TESTDB/system01.db - dbid changed, wrote new name

Datafile /opt/oracle/oradata/TESTDB/sysaux01.db - dbid changed, wrote new name

Datafile /opt/oracle/oradata/TESTDB/users01.db - dbid changed, wrote new name

Datafile /opt/oracle/oradata/TESTDB/undotbs01.db - dbid changed, wrote new name

Control File /opt/oracle/oradata/TESTDB/control01.ctl - dbid changed, wrote new name

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to DEMODB.

Modify parameter file and generate a new password file before restarting.

Database ID for database DEMODB changed to 3790527754.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

Step 3) Make changes to pfile and password file and opened the database with resetlogs option

$ cd $ORACLE_HOME/dbs

Edit pfile ----> initDEMODB.ora

Vi initDEMODB.ora

Change ‘dbname’

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 17 19:28:23 2012

Connected to an idle instance.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 616565768 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME

---------

DEMODB

Also see:

Changing Database Id (DBID)

Steps tochange database name only

How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Doc ID 863800.1)