2016年12月5日 星期一

Oracle DB Export/Import (linux *.sh 檔案)

#!/bin/sh
export ORACLE_HOME=/oradb/oracle/oracle/product/10.2.0/db_1/
export PATH=./:$PATH:$ORACLE_HOME/bin:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
BACKUP_DIR=/oradb/dmpdir
SCRIPT_DIR=/home/oracle/dailybuild
exp [SchemaName]/[SchemaNamePwd]@XXDB_PROD compress=n consistent=Y buffer=204800 file=$BACKUP_DIR/XXDB_PROD_[SchemaName].dmp LOG=$BACKUP_DIR/exp_XXDB_PROD_[SchemaName].log

reloadFlag=$BACKUP_DIR/plmdebug_is_reloading_now

systemDbLogin="system/******@XXDB"

if [ ! -f "$reloadFlag" ]; then
touch "$reloadFlag"
echo "Start to reload [SchemaName]..."

#========================== Reload Start ==========================
sqlplus $systemDbLogin <  $SCRIPT_DIR/[SchemaName].sql
sqlplus $systemDbLogin <  $SCRIPT_DIR/[SchemaName]_password_tmp.sql
sqlplus $systemDbLogin <  $SCRIPT_DIR/alter.sql
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
imp $systemDbLogin commit=y buffer=1024000 file=$BACKUP_DIR/XXDB_PROD_[SchemaName].dmp fromuser=[SchemaName] touser=[SchemaName] LOG=$BACKUP_DIR/imp_[SchemaName].log
sqlplus $systemDbLogin <  $SCRIPT_DIR/[SchemaName]_password.sql
#========================== Reload End ==========================

echo "[SchemaName] reload accomplished."
rm "$reloadFlag"
else
echo "[SchemaName] is reloading now! Abort this request!"
fi



#[SchemaName].sql 
drop user [SchemaName] cascade;

create user [SchemaName] identified by [SchemaName];

alter user [SchemaName] 
default tablespace [SchemaName]
temporary tablespace TEMP
account unlock ;

grant unlimited tablespace to [SchemaName];
grant connect to [SchemaName];
grant resource to [SchemaName];
grant create session to [SchemaName];
grant create table to [SchemaName];
grant create any synonym to [SchemaName];
grant create public synonym to [SchemaName];
grant create view to [SchemaName];
grant create sequence to [SchemaName];
grant create session to [SchemaName];
grant create procedure to [SchemaName];
grant create trigger to [SchemaName];
grant create type to [SchemaName];
grant create database link to [SchemaName];
grant create public database link to [SchemaName];
grant drop public database link to [SchemaName];
grant create materialized view to [SchemaName];
grant create any materialized view to [SchemaName];
grant drop any materialized view to [SchemaName];
grant drop any synonym to [SchemaName];
grant drop public synonym to [SchemaName];

#[SchemaName]_password.sql 
ALTER USER [SchemaName] IDENTIFIED BY [SchemaName];

#[SchemaName]_password_tmp.sql 
ALTER USER [SchemaName] IDENTIFIED BY [SchemaName]_tmp;

#alter.sql
alter system set deferred_segment_creation=false scope=both;