Scripts use datapump to export and import data on table level.
~~~~~~~~~~~~~~~~~ export data from source db ~~~~~~~~~~~~~~~~
-- exp_init.par to export data with filters on tables (for Golden Gate initial load)
DIRECTORY=EXP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=exp_init.log
PARALLEL=8
EXCLUDE=INDEX
EXCLUDE=CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=TRIGGER
EXCLUDE=SYNONYM
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM
QUERY=BUS_UNIT_COST:"WHERE BE_ID <> 250681 and BE_ID <> 251013",
CLAIM:"WHERE BE_ID <> 250681 and BE_ID <> 251013"
-- Shell script to call the .par file. Use "nohup" to run it.
export ORACLE_SID=SOURCEDB
PWD=`/path/XXXX`
$ORACLE_HOME/bin/expdp userID/$PWD parfile=exp_init.par
~~~~~~~~~~~~~~~~~~~~ import ~~~~~~~~~~~~~~~~~~~~~
-- optional: drop indexes first (and disable triggers if any)
SQL> alter table userid.CLAIM drop PRIMARY KEY drop INDEX;
SQL> drop INDEX userid.clm_INDX1;
-- imp_init.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=imp_init.log
PARALLEL=8
TABLE_EXISTS_ACTION=TRUNCATE
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM
-- Shell script for importing
export ORACLE_SID=TARGETDB
echo $ORACLE_SID
PWD=`/path/XXXX`
$ORACLE_HOME/bin/impdp userID/${PWD} parfile=imp_init.par
-- after import, re-create indexes, such as
SQL> CREATE UNIQUE INDEX userid.CLM_PK ON userid.CLAIM
(CLM_ID, BE_ID)
parallel 4 nologging
TABLESPACE CLM_INDEX01;
SQL> ALTER TABLE userid.CLAIM ADD (
CONSTRAINT CLM_PK
PRIMARY KEY (CLM_ID, BE_ID)
USING INDEX userid.CLM_PK
ENABLE VALIDATE);
SQL> grant select on userid.CLAIM to READONLY_RO;
... ... ...
Thursday, September 10, 2009
Wednesday, August 26, 2009
How to find conflicting patches with opatch
You can use OPatch from the $ORACLE_HOME/OPatch folder (%ORACLE_HOME%/OPatch for Windows), to find out if installing a certain patch will conflict or not. The steps are as follows:
1. Download the patch which you intend to apply
2. Unzip the patch file
3. Change to the directory where the patch has been downloaded
4. Run the following command:
> opatch query -all
This will simulate the patch application procedure and you will find out if it is going to conflict with any of the existing patches.
You can read on more such OPatch options from the ../OPatch/docs/Users_Guide.txt file.
Note 459556.1
307706.1 (How To Determine Patch Is Superset/Subset Patch)
1. Download the patch which you intend to apply
2. Unzip the patch file
3. Change to the directory where the patch has been downloaded
4. Run the following command:
> opatch query -all
This will simulate the patch application procedure and you will find out if it is going to conflict with any of the existing patches.
You can read on more such OPatch options from the ../OPatch/docs/Users_Guide.txt file.
Note 459556.1
307706.1 (How To Determine Patch Is Superset/Subset Patch)
Thursday, August 20, 2009
Install standalone Discoverer for 11i
There are several installations involved to make Discoverer work with Oracle EBS 11i. Here are the steps I went through on a SuSe 10 server.
1. OS level
$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP2 (i586) - Kernel \r (\l).
$ uname -a
Linux ebsapp1d 2.6.16.60-0.21-bigsmp #1 SMP Tue May 6 12:41:02 UTC 2008 i686 i686 i386 GNU/Linux
2. Download the right CDs from Oracle.
The Oracle 10.1.2.0.2 BI standalond is in two Zip files (for Linux). It does not install the Applications Server (AS) 10g Infrastructure tier!
3. Read the "Oracle BI Installation Guide" coming with the installation Zip files.
Make sure rpm OPENMOTIF release meets the requirement. These worked well on my Prod server:
$ rpm -aq | grep openmotif
openmotif21-libs-2.1.30MLI4-143.9
openmotif-libs-2.2.4-21.12
If it is not, Reports service will have issues (see Note 762394.1). I had to follow Note 726970.1 to stop growing 200+ Reports sessions on OS level on my Dev server.
4. Install Oracle Buiness Intelligence 10g (10.1.2.0.2)
During the installation, two warnings pop up. In the firsst one, go to the command line and manually run "opmnctl startall". In the second one, apply patch 6690831.
This installation puts Discoverer in version 10.1.2.48.18
5. Install 10gAS Upgrade (patch 5983622) to upgrade BI standalone from 10.1.2.0.2 to 10.1.2.3
This is reqiured to make Discoverer work on SuSe 10. It upgrades Discoverer to 10.1.2.55.26
6. Apply patch 7595032 (Discoverer CP4).
Discoverer will not work with EBS 11i without this CP. (I hit issue described in Note 761287.1). Before patch 7595032 installation worked, patche 680880 (for opatch) and patch 4398431(for JDBC) were applied.
1. OS level
$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP2 (i586) - Kernel \r (\l).
$ uname -a
Linux ebsapp1d 2.6.16.60-0.21-bigsmp #1 SMP Tue May 6 12:41:02 UTC 2008 i686 i686 i386 GNU/Linux
2. Download the right CDs from Oracle.
The Oracle 10.1.2.0.2 BI standalond is in two Zip files (for Linux). It does not install the Applications Server (AS) 10g Infrastructure tier!
3. Read the "Oracle BI Installation Guide" coming with the installation Zip files.
Make sure rpm OPENMOTIF release meets the requirement. These worked well on my Prod server:
$ rpm -aq | grep openmotif
openmotif21-libs-2.1.30MLI4-143.9
openmotif-libs-2.2.4-21.12
If it is not, Reports service will have issues (see Note 762394.1). I had to follow Note 726970.1 to stop growing 200+ Reports sessions on OS level on my Dev server.
4. Install Oracle Buiness Intelligence 10g (10.1.2.0.2)
During the installation, two warnings pop up. In the firsst one, go to the command line and manually run "opmnctl startall". In the second one, apply patch 6690831.
This installation puts Discoverer in version 10.1.2.48.18
5. Install 10gAS Upgrade (patch 5983622) to upgrade BI standalone from 10.1.2.0.2 to 10.1.2.3
This is reqiured to make Discoverer work on SuSe 10. It upgrades Discoverer to 10.1.2.55.26
6. Apply patch 7595032 (Discoverer CP4).
Discoverer will not work with EBS 11i without this CP. (I hit issue described in Note 761287.1). Before patch 7595032 installation worked, patche 680880 (for opatch) and patch 4398431(for JDBC) were applied.
Friday, July 24, 2009
Create and fast refresh materialized view
In 11G, I used below statements to create a materialized view and refresh it.
WHENEVER SQLERROR CONTINUE
drop materialized view erp.db1_CLNCL_INDEX;
--
-- create MVIEW basing on a remote table
--
create materialized view erp.db1_CLNCL_INDEX
tablespace mat_view_data01
build deferred
using index tablespace mat_view_indx01
refresh fast on demand with primary key
as select * from user1.CLNCL_INDEX@db1
-- where TRUNC("CREATE_TS") > sysdate - 1100
-- FAST REFRESH does not like SYSDATE. Error ORA-12015
where TRUNC("CREATE_TS") > to_date('01/01/2009', 'MM/DD/YYYY')
;
-- Run "complete" refresh one time, before "fast" refresh. It takes hours.
EXEC DBMS_MVIEW.REFRESH(LIST => 'ERP.DB1_CLNCL_INDEX', METHOD => 'C', ATOMIC_REFRESH => FALSE, PARALLELISM =>'6');
-- Run FAST refresh. It can be put into a shell script to run daily.
EXEC DBMS_MVIEW.REFRESH(LIST => '"ERP"."DB1_CLNCL_INDEX"', METHOD => 'F', PARALLELISM =>'6');
~~~~~~~~~~~~~~~~ Shell script for crontab ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/bin/ksh
## Call sql script to refresh MVs
# Please specify the variables
export ORACLE_SID=DBNAME
export ORACLE_HOME=/app/oracle/product/11.2.0.3
export PATH=$ORACLE_HOME/bin:${PATH}
export script_dir=/path/to/dba # Where you save the text log files
Passwd=`XXXXX`
current_dir=`pwd`
cd $script_dir
DT=`date +"%h %d,%y %H:%M"`
BKUPLOG="$script_dir/DBNAME_mvFresh_out.txt"
BKUPLOG_ERR="$script_dir/DBNAME_mvFresh_out_err.txt"
if [ -f $BKUPLOG ]; then
mv $BKUPLOG ${BKUPLOG}_old
fi
if [ -f $BKUPLOG_ERR ]; then
mv $BKUPLOG_ERR ${BKUPLOG_ERR}_old
fi
exec 1>$BKUPLOG
exec 2>$BKUPLOG_ERR
echo "Start at $DT"
echo
if [ -f DBNAME_fast_refresh.sql ]; then
echo "file DBNAME_fast_refresh.sql does exist!!"
else
echo "file DBNAME_fast_refresh.sql does not exist" | mailx -s "$script_dir" a1@gmail.com,a2@gmail.com
exit 1;
fi
$ORACLE_HOME/bin/sqlplus -S /nolog <<-ENDSQL
connect ${Passwd}
show user
@DBNAME_fast_refresh.sql
exit;
ENDSQL
DT=`date +"%h %d,%y %H:%M"`
echo
echo "finish at $DT"
if [ -n "`grep "ORA-" $BKUPLOG`" ] || [ -s $BKUPLOG_ERR ]; then
cat $BKUPLOG | mailx -s "Error in $script_dir" a1@gmail.com,a2@gmail.com
fi
cd $current_dir
exit
~~~~~~~~~~~~
$ view DBNAME_fast_refresh.sql
spool DBNAME_MVrefresh.lst
EXEC DBMS_MVIEW.REFRESH(LIST => '"ERP"."DB1_CLNCL_INDEX"', METHOD => 'F', PARALLELISM =>'6');
select '1', to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') datetime from dual;
-- put more statements here to refresh more MVWs
spool off
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In 10G, below SQL is helpful for finding the materialized view and the master table from the MLOG name.
SQL> select distinct
ds.master_owner "MasterTable owner",
ds.master "MasterTable name",
ds.owner "MView owner", ds.name "Snapshot/Mview name",
dsl.log_owner "Log owner", dsl.log_table "MLog name"
FROM dba_snapshot_logs dsl, dba_snapshots ds
WHERE dsl.log_table like '%MLOG$_BOM_OPERATION_SEQUEN%'
and ds.master = dsl.master
--and ds.owner = dsl.log_owner
order by 3;
Note that the information in DBA_SNAPSHOT may not be accurate. For example, Materialized view ENI_DBI_MFG_STEPS_JOIN_MV is created by a join of bom.bom_operational_routings and bom.bom_operation_sequences. But, DBA_SNAPSHOT only reports table BOM_OPERATION_SEQUENCES as the master.
DBA_MVIEWS does not have the MASTER column.
WHENEVER SQLERROR CONTINUE
drop materialized view erp.db1_CLNCL_INDEX;
--
-- create MVIEW basing on a remote table
--
create materialized view erp.db1_CLNCL_INDEX
tablespace mat_view_data01
build deferred
using index tablespace mat_view_indx01
refresh fast on demand with primary key
as select * from user1.CLNCL_INDEX@db1
-- where TRUNC("CREATE_TS") > sysdate - 1100
-- FAST REFRESH does not like SYSDATE. Error ORA-12015
where TRUNC("CREATE_TS") > to_date('01/01/2009', 'MM/DD/YYYY')
;
-- Run "complete" refresh one time, before "fast" refresh. It takes hours.
EXEC DBMS_MVIEW.REFRESH(LIST => 'ERP.DB1_CLNCL_INDEX', METHOD => 'C', ATOMIC_REFRESH => FALSE, PARALLELISM =>'6');
-- Run FAST refresh. It can be put into a shell script to run daily.
EXEC DBMS_MVIEW.REFRESH(LIST => '"ERP"."DB1_CLNCL_INDEX"', METHOD => 'F', PARALLELISM =>'6');
~~~~~~~~~~~~~~~~ Shell script for crontab ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/bin/ksh
## Call sql script to refresh MVs
# Please specify the variables
export ORACLE_SID=DBNAME
export ORACLE_HOME=/app/oracle/product/11.2.0.3
export PATH=$ORACLE_HOME/bin:${PATH}
export script_dir=/path/to/dba # Where you save the text log files
Passwd=`XXXXX`
current_dir=`pwd`
cd $script_dir
DT=`date +"%h %d,%y %H:%M"`
BKUPLOG="$script_dir/DBNAME_mvFresh_out.txt"
BKUPLOG_ERR="$script_dir/DBNAME_mvFresh_out_err.txt"
if [ -f $BKUPLOG ]; then
mv $BKUPLOG ${BKUPLOG}_old
fi
if [ -f $BKUPLOG_ERR ]; then
mv $BKUPLOG_ERR ${BKUPLOG_ERR}_old
fi
exec 1>$BKUPLOG
exec 2>$BKUPLOG_ERR
echo "Start at $DT"
echo
if [ -f DBNAME_fast_refresh.sql ]; then
echo "file DBNAME_fast_refresh.sql does exist!!"
else
echo "file DBNAME_fast_refresh.sql does not exist" | mailx -s "$script_dir" a1@gmail.com,a2@gmail.com
exit 1;
fi
$ORACLE_HOME/bin/sqlplus -S /nolog <<-ENDSQL
connect ${Passwd}
show user
@DBNAME_fast_refresh.sql
exit;
ENDSQL
DT=`date +"%h %d,%y %H:%M"`
echo
echo "finish at $DT"
if [ -n "`grep "ORA-" $BKUPLOG`" ] || [ -s $BKUPLOG_ERR ]; then
cat $BKUPLOG | mailx -s "Error in $script_dir" a1@gmail.com,a2@gmail.com
fi
cd $current_dir
exit
~~~~~~~~~~~~
$ view DBNAME_fast_refresh.sql
spool DBNAME_MVrefresh.lst
EXEC DBMS_MVIEW.REFRESH(LIST => '"ERP"."DB1_CLNCL_INDEX"', METHOD => 'F', PARALLELISM =>'6');
select '1', to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') datetime from dual;
-- put more statements here to refresh more MVWs
spool off
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In 10G, below SQL is helpful for finding the materialized view and the master table from the MLOG name.
SQL> select distinct
ds.master_owner "MasterTable owner",
ds.master "MasterTable name",
ds.owner "MView owner", ds.name "Snapshot/Mview name",
dsl.log_owner "Log owner", dsl.log_table "MLog name"
FROM dba_snapshot_logs dsl, dba_snapshots ds
WHERE dsl.log_table like '%MLOG$_BOM_OPERATION_SEQUEN%'
and ds.master = dsl.master
--and ds.owner = dsl.log_owner
order by 3;
Note that the information in DBA_SNAPSHOT may not be accurate. For example, Materialized view ENI_DBI_MFG_STEPS_JOIN_MV is created by a join of bom.bom_operational_routings and bom.bom_operation_sequences. But, DBA_SNAPSHOT only reports table BOM_OPERATION_SEQUENCES as the master.
DBA_MVIEWS does not have the MASTER column.
Thursday, July 16, 2009
Create database (10.2.0.4) for moving an 11i instance
This task is part of moving an EBS 11i instance from outside hosting Linux servers to in-house servers. There are certain steps for creating a new database for using datapump Export / Import to move an Oracle EBS (11i) database.
1. Install 10gR2 software (note 362203.1)
- Install the base 10.2.0.1 software
- Install 10g products from the 10g Companion CD (Guide B19079-01, p. 3-6)
- Perform 10.2.0.4 patch set (6810189) installation
- Create nls/data/9idata directory by a Perl script
2. Download two EBS 11i patches (note 362205.1)
- 4873830. It includes a script for generating the SQL of creating the target database and tablespaces.
- 7225862. It provides necessary SQL scripts for target database preparation.
3. Get the init file from the source database server
- Comment out UNDO_TABLESPACES and UNDO_MANAGEMENT
- Do not use below PLSQL parameters (so they will be the defaults)
PLSQL_V2_COMPATIBILITY
PLSQL_CODE_TYPE
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
4. Modify adcrdb.sql to meet the file system layout. Below sample does not include all tablespace statements.
CREATE DATABASE
MAXDATAFILES 1301
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 (
'/u03/oracle/oradata/dpulti/log01a.log' , '/u02/oracle/oradata/dpulti/log01b.log'
) SIZE 300M ,
GROUP 2 (
'/u03/oracle/oradata/dpulti/log02a.log' , '/u02/oracle/oradata/dpulti/log02b.log'
) SIZE 300M ,
GROUP 3 (
'/u03/oracle/oradata/dpulti/log03a.log' , '/u02/oracle/oradata/dpulti/log03b.log'
) SIZE 300M ,
GROUP 4 (
'/u03/oracle/oradata/dpulti/log04a.log' , '/u02/oracle/oradata/dpulti/log04b.log'
) SIZE 300M
DATAFILE
'/u03/oracle/path/to/system01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M,
'/u03/oracle/path/to/system02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
SYSAUX DATAFILE
'/u03/oracle/path/to/sysaux01.dbf' SIZE 1800M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
/
5. Restart the new database after un-commenting out UNDO_TABLESPACES and UNDO_MANAGEMENT
6. To insure the database will have all necessary components for EBS 11i apps, run a series of SQL scripts detailed in 362205.1
- if get issue on adpostcrdb.sql, check note 236583.1
7. Make sure temp tablespace has temp datafiles.
8. Run @?/rdbms/admin/utlrp.sql to compile any invalid objects. Here are the counts by each owner in 10.2.0.4 after all finish:
SQL> select owner, count(*) from all_objects group by owner order by owner;
OWNER COUNT(*)
------------------------------ ----------
CTXSYS 312
DBSNMP 46
DMSYS 189
EXFSYS 176
MDSYS 906
OLAPSYS 718
ORACLE_OCM 8
ORDPLUGINS 10
ORDSYS 1721
OUTLN 8
PUBLIC 19889
SI_INFORMTN_SCHEMA 8
SYS 23047
SYSTEM 426
TSMSYS 2
XDB 338
16 rows selected.
SQL> select count(*) from all_objects where status = 'INVALID';
COUNT(*)
----------
0
9. Back up the database.
10. If PLSQL_NATIVE_LIBRARY_DIR will be used, create subdirectories.
After the the database is ready, run datapump to import the database.
~~~~~~~~~~~~ export from source database in hosting Linux server ~~~~~~~~~~~~~~
1. Identify and purge some materialized view logs. They can be very big and waste time in exporting and importing.
2. Remove rebuild index parameter in spatial indexes (See Note 362205.1, Section 1).
Login as MST, and execute:
SQL> alter index MST_MD_HYDROS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
SQL> alter index MST_MD_ADM_BNDS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
3. Connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
4. Create directory dpump_dir1 as SYSTEM
SQL> connect system/*******
SQL> CREATE OR REPLACE DIRECTORY DPUMP_DIR1 AS '/path/to/backup/export';
5. Shutdown all 11i Application servers
6. Run export
$ cat expfile.dat
directory=dpump_dir1
PARALLEL=2
dumpfile=dba%U.dmp
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
logfile=expdpapps.log
$ nohup expdp system/****** parfile=expfile.dat > ${LOGFILE} 2>&1 &
~~~~~~~~~~ import to the new database in in-house AIX server~~~~~~~~~~~~~
$ export ORACLE_SID=db_id
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus system/PWD
SQL> create directory dmpdir as '/mnt/usb/export';
$ cat impdp.dat
#$Header: auexpdp.dat 115.4 2005/12/20 16:02:25 apydimar noship $
directory=dmpdir
parallel=2
dumpfile=dba%U.dmp
# full=y
transform=oid:n
logfile=impdpapps.log
$ nohup impdp system/PWD parfile=impdp.dat &
1. Install 10gR2 software (note 362203.1)
- Install the base 10.2.0.1 software
- Install 10g products from the 10g Companion CD (Guide B19079-01, p. 3-6)
- Perform 10.2.0.4 patch set (6810189) installation
- Create nls/data/9idata directory by a Perl script
2. Download two EBS 11i patches (note 362205.1)
- 4873830. It includes a script for generating the SQL of creating the target database and tablespaces.
- 7225862. It provides necessary SQL scripts for target database preparation.
3. Get the init file from the source database server
- Comment out UNDO_TABLESPACES and UNDO_MANAGEMENT
- Do not use below PLSQL parameters (so they will be the defaults)
PLSQL_V2_COMPATIBILITY
PLSQL_CODE_TYPE
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
4. Modify adcrdb.sql to meet the file system layout. Below sample does not include all tablespace statements.
CREATE DATABASE
MAXDATAFILES 1301
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 (
'/u03/oracle/oradata/dpulti/log01a.log' , '/u02/oracle/oradata/dpulti/log01b.log'
) SIZE 300M ,
GROUP 2 (
'/u03/oracle/oradata/dpulti/log02a.log' , '/u02/oracle/oradata/dpulti/log02b.log'
) SIZE 300M ,
GROUP 3 (
'/u03/oracle/oradata/dpulti/log03a.log' , '/u02/oracle/oradata/dpulti/log03b.log'
) SIZE 300M ,
GROUP 4 (
'/u03/oracle/oradata/dpulti/log04a.log' , '/u02/oracle/oradata/dpulti/log04b.log'
) SIZE 300M
DATAFILE
'/u03/oracle/path/to/system01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M,
'/u03/oracle/path/to/system02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
SYSAUX DATAFILE
'/u03/oracle/path/to/sysaux01.dbf' SIZE 1800M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
/
5. Restart the new database after un-commenting out UNDO_TABLESPACES and UNDO_MANAGEMENT
6. To insure the database will have all necessary components for EBS 11i apps, run a series of SQL scripts detailed in 362205.1
- if get issue on adpostcrdb.sql, check note 236583.1
7. Make sure temp tablespace has temp datafiles.
8. Run @?/rdbms/admin/utlrp.sql to compile any invalid objects. Here are the counts by each owner in 10.2.0.4 after all finish:
SQL> select owner, count(*) from all_objects group by owner order by owner;
OWNER COUNT(*)
------------------------------ ----------
CTXSYS 312
DBSNMP 46
DMSYS 189
EXFSYS 176
MDSYS 906
OLAPSYS 718
ORACLE_OCM 8
ORDPLUGINS 10
ORDSYS 1721
OUTLN 8
PUBLIC 19889
SI_INFORMTN_SCHEMA 8
SYS 23047
SYSTEM 426
TSMSYS 2
XDB 338
16 rows selected.
SQL> select count(*) from all_objects where status = 'INVALID';
COUNT(*)
----------
0
9. Back up the database.
10. If PLSQL_NATIVE_LIBRARY_DIR will be used, create subdirectories.
After the the database is ready, run datapump to import the database.
~~~~~~~~~~~~ export from source database in hosting Linux server ~~~~~~~~~~~~~~
1. Identify and purge some materialized view logs. They can be very big and waste time in exporting and importing.
2. Remove rebuild index parameter in spatial indexes (See Note 362205.1, Section 1).
Login as MST, and execute:
SQL> alter index MST_MD_HYDROS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
SQL> alter index MST_MD_ADM_BNDS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
3. Connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
4. Create directory dpump_dir1 as SYSTEM
SQL> connect system/*******
SQL> CREATE OR REPLACE DIRECTORY DPUMP_DIR1 AS '/path/to/backup/export';
5. Shutdown all 11i Application servers
6. Run export
$ cat expfile.dat
directory=dpump_dir1
PARALLEL=2
dumpfile=dba%U.dmp
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
logfile=expdpapps.log
$ nohup expdp system/****** parfile=expfile.dat > ${LOGFILE} 2>&1 &
~~~~~~~~~~ import to the new database in in-house AIX server~~~~~~~~~~~~~
$ export ORACLE_SID=db_id
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus system/PWD
SQL> create directory dmpdir as '/mnt/usb/export';
$ cat impdp.dat
#$Header: auexpdp.dat 115.4 2005/12/20 16:02:25 apydimar noship $
directory=dmpdir
parallel=2
dumpfile=dba%U.dmp
# full=y
transform=oid:n
logfile=impdpapps.log
$ nohup impdp system/PWD parfile=impdp.dat &
Subscribe to:
Comments (Atom)