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.

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 &