Resolving ORA-01652

Hi,

Steps we can follow to resolve the error ORA-01652 is as follows:

Solution 1:

We can check for held TEMP segments with this query:
SQL> set linesize 1000
SQL>select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;


TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 201 169216 70912 15 5 TEST1 oracle ACTIVE
TEMP 201 178688 128 9 1450 TEST2 INACTIVE
TEMP 201 240128 128 150 299 APEX_PUBLIC_USER apex_admin INACTIVE



This command may remove a TEMP segment, try:

alter tablespace xxxxx coalesce;

alter tablespace TEMP coalesce;

(OR)

Solution 2:

We can also use a drop segments event to remove temporary space from a tablespace:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';

where:
x is the value for file# from Tablespace.

(OR)

Solution 3:Add the temp file for Temporary tablespace

SQL> SELECT FILE_NAME||' '||TABLESPACE_NAME||' '||BYTES/1024/1024
2 FROM DBA_TEMP_FILES;

FILE_NAME||''||TABLESPACE_NAME||''||BYTES/1024/1024
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u04/TESTDB/TESTDB_temp_01.dbf TEMP 1900

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/TESTDB/TESTDB_temp_02.dbf' SIZE 200M;

Tablespace altered.


SQL> SELECT TABLESPACE_SIZE/1024/1024||' '||ALLOCATED_SPACE/1024/1024||' '||FREE_SPACE/1024/1024
FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_SIZE/1024/1024||''||ALLOCATED_SPACE/1024/1024||''||FREE_SPACE/1024/1024
--------------------------------------------------------------------------------------------------------------------------
2100 1898 1519

SQL> select tablespace_name, total_blocks, used_blocks, free_blocks
2 FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP 242688 74112 168576


SQL> set linesize 1000

SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;

TABLESPACE_NAME SUM(BYTES_USED)/1024/1024 SUM(BYTES_FREE)/1024/1024
------------------------------ ------------------------- -------------------------
TEMP 1898 202


Then,resize or add the datafile related to a Tablespace if require or add a new datafile to current tablespace


Addind Datafile to existing Tablespace:
ALTER TABLESPACE RMRTM ADD DATAFILE '/u05/TESTDB/RMRTM_DATA_01.DBF' SIZE 200M;


SQL> SELECT *FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE '%RM%';

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/u04/TESTDB/RMRTM_DATA_01.DBF 39 RMRTM 3221225472 393216 AVAILABLE 39 YES 3221225472 393216 12800 3220176896 393088 ONLINE



SQL> SET LINESIZE 1000
SQL> SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACEF.FREE_SPACE),'999,999')"USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;


TABLESPACE_NAME USEDMB FREEMB TOTALMB FREE
------------------------------ -------- -------- -------- ------

RMRTM 2,748 324 3,072 11 %


SQL>ALTER TABLESPACE RMRTM ADD DATAFILE '/u05/TESTDB/RMRTM_DATA_01.DBF' SIZE 200M;

Tablespace altered

(OR)

we can resize the existing Datafile if the mount point have enough space

Note:
Resizing Datafile can be done as givne below:

ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE 200M;

(OR)

Solution 4:Create a new Temporary Tablespace and make this temporary tablespace default for our Database.


Step 1:Create the new temporary Tablespace

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u05/TESTDB/TEMP02.dbf' SIZE 256M
REUSE AUTOEXTEND ON NEXT 256M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;

Tablespace created.

Step 2:Make this temporary tablespace default at Database level

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Database altered.

Where:
'TEMP02' is the DEFAULT TEMPORARY TABLESPACE name in the above case.

Step 3:Bounce our Database so that actual temporary space is release from 'temp' Tablespace and Drop tablespace 'temp' including content

we have to Make sure We delete on temp data files and the actual space is recovered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 855641944 bytes
Database Buffers 205520896 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL> Drop tablespace temp including contents;

Tablespace dropped.

SQL> select *from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u05/TESTDB/TEMP02.dbf
3 TEMP02 268435456 32768 ONLINE
1 YES 8589934592 1048576 32768 267386880 32640

Now,we can see the new Temporary tablespace is 'TEMP02' with enough free space.

Note:We can adopt either of one solution

Hope it helps...


Happy troubleshooting...


Best regards,

Rafi.

Installation and Configuring of Oracle Data Integrator on Linux

Hi,
There are lot of ETL tools available in the market like Informatica,Datastage...Here is one from Oracle,This tool is called ODI(Oracle Data Integrator) which is the ELT(Estraction Loading and Transformation) tool as per Oracle concepts.

Below are few steps for Installation and Configuration of ODI:

Below I'm describing the steps which I've followed to installed the Oracle Data Integrator(ODI)11g version 11.0.1.5.0 on linux platform,The Detailed step along with the installation Roadmap is given below:

Installation Roadmap:
The following tasks provide a brief description of the Oracle Data Integrator Installation Procedure, including a description of the installable components, pre and post-installation tasks, and process flow. This tasks are can be said as the Installation Roadmap.

Task1: Complete the installation planning requirements
Prior to installation you must prepare your system environment for installation. Review the general installation requirements for Oracle Fusion Middleware, as well as any specific configuration requirements for Oracle Data Integrator.

Task2:Pre-requistics Software should be installed.

1)Install and configure an Oracle Database
Refer previous posts of mine:

2)Install Oracle WebLogic Server:

Weblogic pre-installation steps:
Check the below Certification matrix wls.xls to check the supported Weblogic server for
Various Operating system.For our operating system RHEL 5.3 the supported
Weblogic server software is 10.3.

SQL> select name from v$database;

NAME
---------
FMWDB

SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> show parameter processes
SQL> alter system set processes=1000 scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> show parameter processes

export FMW_HOME=$ORACLE_BASE/product/FMW

[root@testnode1FMW /]# mkdir -p Middleware/SOASuite11gR1
[root@testnode1FMW /]# chown oracle:oinstall /Middleware/SOASuite11gR1/
[root@testnode1FMW /]# chmod -R 755 /Middleware/SOASuite11gR1/

We have to create Middleware home directory for installation FUSION MIDDLEWARE COMPONENTS in this particular by granting the ownership to the ‘oracle’ user with all the required privileges.

Once your database is ready, we can install the next step, the WebLogic server.
When you are ready to install, complete the following.

I granted all the ownership to the oracle user and permission where the executable of weblogic software is present.I launched the installation on vm server

#xserver +
#xclock
=>This shoud display time,Use GUI mode for installing weblogic so that you can see the dispaly cleary..

,We have entered the Fusion Middlware Home path which has all the ownership and permission so that ‘oracle’ user can install weblogic server here.

Enter the common path like: /Middleware/

In step5 Make uninformed to ‘yes’ and proceed..

Choose Typical installation and proceed...

click next...
proceed...

3) Install Oracle Repository Creation Utility (RCU)

Note: The Oracle Repository Creation Utility (RCU) allows us to create and load a Master Repository and a Work Repository in a single database schema. RCU supports only the Oracle, Microsoft SQL Server and DB2 Databases. RCU only supports a single schema containing both the Master Repository and one Work Repository. Other technologies and configurations are supported by manually creating the repositories using the Oracle Data Integrator Studio after the installation phase.

Useful pre-requistics steps:


[oracle@testnode1FMW admin]$ hostname
testnode1FMW.apac.bosch.com
[oracle@testnode1FMW admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:23

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

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
TNS-12541: TNS:no listener
[oracle@testnode1FMW admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:30

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

Starting /u01/app//product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/diag/tnslsnr/testnode1FMW/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testnode1FMW.apac.bosch.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-JUL-2011 18:26:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/testnode1FMW/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testnode1FMW.apac.bosch.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@testnode1FMW admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:40

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

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
OK (30 msec)

2) Please do below step in Database side,If you are installing all the Oracle Fusion Middleware products,For SOA installation alone setting open_cursors=300,but as we are installating all the Oracle Fusion middleware products we need to set open_cursors=1000.

Conn “/as sysdba” in Database.

SQL> alter system set open_cursors=1000;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1000

Now it is time to set up the database schema.
Note: If we have installed SOA 11g before and you are not upgrading, you must drop your existing database schema before reinstalling or create a second schema for this installation. We cannot reuse an existing schema.

Configure Schema using RCU
Now create the new schema.

Give the ownership and permission to ‘oracle’ user to run the repository creation utility.

[root@testnode1FMW /]# chown oracle:oinstall /Reposit_create_soft/
[root@testnode1FMW /]# chmod -R 755 /Reposit_create_soft/


[root@testnode1FMW Reposit_create_soft]# su - oracle
[oracle@testnode1FMW ~]$ pwd
/home/oracle
[oracle@testnode1FMW ~]$ cd /Reposit_create_soft/
[oracle@testnode1FMW Reposit_create_soft]$ ls -altr
total 389240
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 2 oracle oinstall 4096 Jul 27 10:36 .
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
[oracle@testnode1FMW Reposit_create_soft]$ unzip ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip

[oracle@testnode1FMW /]$ cd Reposit_create_soft/
[oracle@testnode1FMW Reposit_create_soft]$ ls -altr
total 389244
drwxr-xr-x 32 oracle oinstall 4096 Apr 26 05:04 rcuHome
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 11:41 .
[oracle@testnode1FMW Reposit_create_soft]$ cd rcuHome/
[oracle@testnode1FMW rcuHome]$ ls
assistants common inventory jdk lib nls oraInst.loc plsql rcu relnotes sqlplus xdk
BC4J css javavm jlib modules oracleRoot.sh owm portal rdbms root.sh srvm
bin instantclient jdbc ldap network oracore perl precomp readme.htm sqlj util
[oracle@testnode1FMW rcuHome]$ cd bin

Go to path ‘/Reposit_create_soft_/rcuHome/bin’ as seen in the screenshot.

$ export Display

On Linux(UNIX):
./rcu

Step 4: Install Oracle Data Integrator 11g
Run Oracle Universal Installer (OUI) to install Oracle Data Integrator 11g. The Oracle Universal Installer automates many of the ODI installation and configuration tasks.

:Create ODI_HOME directory and grant ‘oracle’ user ownership
Create the ‘ODI_HOME’ directory.and Grant the ownership and permission to ‘oracle’ user for accessing ‘ODI_HOME’ directory.
[root@testnode1FMW Middleware]# cd /Middleware/
[root@testnode1FMW Middleware]# ls
JDev11g SOASuite11gR1
[root@testnode1FMW Middleware]# mkdir ODI_HOME
[root@testnode1FMW Middleware]# chown oracle:oinstall /Middleware/ODI_HOME/
[root@testnode1FMW Middleware]# chmod -R 755 /Middleware/ODI_HOME/
[root@testnode1FMW Middleware]# ls
JDev11g ODI_HOME SOASuite11gR1
[root@testnode1FMW Middleware]# ls -altr
total 24
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
drwxr-x--- 13 oracle oinstall 4096 Aug 1 14:45 SOASuite11gR1
drwxr-xr-x 2 oracle oinstall 4096 Aug 2 18:24 JDev11g
drwxr-xr-x 2 oracle oinstall 4096 Aug 3 12:56 ODI_HOME
drwxr-xr-x 5 root root 4096 Aug 3 12:56 .
[oracle@testnode1FMW common]$ cd
[oracle@testnode1FMW ~]$ cd /ODI_11gsoft/
[oracle@testnode1FMW ODI_11gsoft]$ ls -altr
total 1887820
drwxrwxr-x 3 root root 4096 Apr 23 09:27 Disk2
drwxr-xr-x 5 root root 4096 Apr 30 03:14 Disk1
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
-rw-r--r-- 1 root root 1931212170 Aug 4 11:44 ofm_odi_generic_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 4 oracle oinstall 4096 Aug 4 14:29 .
[oracle@testnode1FMW ODI_11gsoft]$ su
Password:
[root@testnode1FMW ODI_11gsoft]# unzip ofm_odi_linux_11.1.1.5.0_32_disk1_1of1.zip

Verify the JDK home before launching the installation:

Our JDK home = /Middleware/SOASuite11gR1/jdk160_24

Granting ‘oracle’ user ownership and permission:

[root@testnode1FMW ~]# cd /ODI_11gsoft/
[root@testnode1FMW ODI_11gsoft]# ls -altr
total 1887820
drwxrwxr-x 3 root root 4096 Apr 23 09:27 Disk2
drwxr-xr-x 5 root root 4096 Apr 30 03:14 Disk1
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
-rw-r--r-- 1 root root 1931212170 Aug 4 11:44 ofm_odi_generic_11.1.1.5.0 _disk1_1of1.zip
drwxr-xr-x 4 oracle oinstall 4096 Aug 4 14:29 .
[root@testnode1FMW ODI_11gsoft]# chown -R oracle:oinstall /ODI_11gsoft/Disk1
[root@testnode1FMW ODI_11gsoft]# chown -R oracle:oinstall /ODI_11gsoft/Disk2
[root@testnode1FMW ODI_11gsoft]# ls -altr
total 1887820
drwxrwxr-x 3 oracle oinstall 4096 Apr 23 09:27 Disk2
drwxr-xr-x 5 oracle oinstall 4096 Apr 30 03:14 Disk1


Login as ‘oracle’ user for launching the ODI 11g installation.

- Download the Oracle Data Integrator 11g and start the installer.
- Note: JDK home = /Middleware/SOASuite11gR1/jdk160_24


Click Next

- ODI step 2 : Select the ODI components to install. ODI Studio is the main component.


Select the components which you want…Select All for complete installations..
Click Next


Click Next

Note: An Application server should already be installed,We had installed Weblogic server in the same path.

Click Next

Select the Configure Repositories option, to configure the ODI Studio and the Stand Alone Agent with an existing Master and Work repositories pair.

Provide the Database Connection information
Enter User:DEV_ODI_REPO =>User used for odi metadata repository in Database ‘FMWDB’

Pass:welcome123

Click Next

Create the SUPERVISOR password
ODI Pass:welcome123

Click Next


Note: If we are installing agent we can provide the installation information as Agent=ODI_AGENT and Port:20910(Default port)=>This is the port which Agent will be listening…


Select the WorkRepository created with RCU

I created one response file in /home/oracle directory to review installation steps later


Click Install to install ODI 11g


Our ODI 11g installation and Configuration is successful

Click Next


I created the configuration file to refer later.


Click OK
Click Finish….


Hope it helps...

Happy ODI learning

HOW TO RESOLVE ROW-CHAINING

We know the row-chaining happen,when a row that is too large cannot fit on a single block.

Eg: If we have a 2k blocksize but our row size is 4k, we will use 3 blocks to store that row in pieces.

Row-chaining can occur because of below reasons:

1)A table have a long/long raw Data types in some cases
2) A table's rowsize exceeds the blocksize and
3)Any table with more then 255 columns

We can resolve ROW-CHAINING by following below 2 steps:

Step:1.FIND OUT IF ROW-CHAINING IS OCCURING.
$sqlplus scott/tiger
SQL>exec dbms_stats.gather_schema_stat(‘scott’)
OR
SQL>exec dbms_stat.gather_table_stat('scott','emp')
SQL>select chaint_cnt from user_tables where TABLE_NAME='EMP';


NOTE: Any non-zero value means there is row-chaining.

Step 2:RESOLVE THE CHAINING

 RUN THE FOLLOWING COMMAND TO CREATE chained_rows table.

$sqlplus "/as sysdba"

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

SQL>ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;

SQL>create table temp_emp as
Select * from emp
Where rowid in (select HEAD_ROWID from chained_rows
where TABLE_NAME= ‘EMP’;

SQL>DELETE FROM EMP WHERE ROWID IN(
select HEAD_ROWID from chained_rows
where TABLE_NAME= ‘EMP’);

SQL>INSERT INTO EMP SELECT * FROM TEMP_EMP;

SQL>DROP TABLE TEM-_EMP;
SQL>DROP TABLE CHAINED_ROWS;

Note:Remember,ROW CHAINING occurs because of UPDATE operations and not because of INSERT operations.

Hope it helps...

Happy Oracle DBA learning

Best regards,

Rafi.

.

Refreshing schemas in oracle Databases

Hi,
Schema refresh task might be regular for DBA's working on Database migration project.Schema refresh is done to make our production Database data in sync with developmnent,test and performance environment.

Below I'm describing one such task.Lot of time we might need to do a set of schemas so it is very important we make a document or plan for doing this task effectively.In the below task we have 2 environments .PRODDB(production) and TESTDB(test).I'm refreshing TESTDB by taking Data from PRODB,here only one schema is refreshed.

Source side:


Preparatory Steps:

Create directory or use an exiting directory by giving read and write permission for 'system' Database user to use that direcotry(TEST_MIG).

SQL> grant read,write on directory TEST_MIG to system;

Grant succeeded.

SQL> alter user system identified by TESTDBdba account unlock;

PRODDB:

Step 1:Exporting the Data from the source Database(PRODDB in our case)

vi expdp_refresh_schema_sep27.sh

$ expdp system/PRODDB@PRODDB DUMPFILE=REFRESH_SCHEMA.DMP DIRECTORY=DATA_PUMP_DIR SCHEMAS=REFRESH_SCHEMA LOGFILE=REFRESH_SCHEMA.log

$ nohup sh expdp_refresh_schema_sep27.sh>refresh_schema.out &

Step 2:Copying the dump file(Source Data) to Target Database server

We can use 'winscp' tool(A graphical utility for copying files from windows to linux or viceversa) or ftp or scp or tar or rsync for coping Data from source server to target server.

Step 3:Moving Data into the target Database.


$ impdp system/TESTDBdba@TESTDB DUMPFILE=REFRESH_SCHEMA.DMP DIRECTORY=TEST_MIG REMAP_SCHEMA=REFRESH_SCHEMA:REFRESH_SCHEMA LOGFILE=REFRESH_SCHEMA.log


Step 4:Verify the Data in Source and Target Databases.

Note:
In oracle 11g rel2,version:11.2.0.1.0 there are about 44 Distinct object_types comparing to previous versions this number is huge.

SQL> select *from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


SQL> select distinct object_type from dba_objects;

OBJECT_TYPE
-------------------
EDITION
INDEX PARTITION
CONSUMER GROUP
SEQUENCE
TABLE PARTITION
SCHEDULE
QUEUE
RULE
JAVA DATA
PROCEDURE
OPERATOR

OBJECT_TYPE
-------------------
LOB PARTITION
DESTINATION
WINDOW
SCHEDULER GROUP
DATABASE LINK
LOB
PACKAGE
PACKAGE BODY
LIBRARY
PROGRAM
RULE SET

OBJECT_TYPE
-------------------
CONTEXT
TYPE BODY
JAVA RESOURCE
XML SCHEMA
TRIGGER
JOB CLASS
UNDEFINED
DIRECTORY
MATERIALIZED VIEW
TABLE
INDEX

OBJECT_TYPE
-------------------
SYNONYM
VIEW
FUNCTION
JAVA CLASS
JAVA SOURCE
INDEXTYPE
CLUSTER
TYPE
RESOURCE PLAN
JOB
EVALUATION CONTEXT

44 rows selected.

Source Database:


PRODDB:
---------

SQL> select count(*) from dba_objects
where owner='REFRESH_SCHEMA';

COUNT(*)
----------
132

SQL> select count(*) from dba_tables
where owner='REFRESH_SCHEMA';

COUNT(*)
----------
34

SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN('TABLE','JOB','VIEW','PACKAGE','TRIGGER','SYNONYM','FUNCTION','PROCEDURE','TYPE')
ORDER BY OBJECT_TYPE;
SQL> SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN('TABLE','JOB','VIEW','PACKAGE','TRIGGER','SYNONYM','FUNCTION','PROCEDURE','TYPE')
ORDER BY OBJECT_TYPE;
2 3 4
COUNT(*)
----------
62


SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN


TARGET DATABASE:


TESTDB:
-------------
SQL> select count(*) from dba_objects
where owner='REFRESH_SCHE'; 2

COUNT(*)
----------
131

SQL> select count(*) from dba_tables
where owner='APEX4_DEV'; 2

COUNT(*)
----------
34

SQL> SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN('TABLE','JOB','VIEW','PACKAGE','TRIGGER','SYNONYM','FUNCTION','PROCEDURE','TYPE')
ORDER BY OBJECT_TYPE;

COUNT(*)
----------
62

Hope it helps.Happy refreshing our Database environments



Best regards,

Rafi.