Export Oracle database
Execution time: 5 minutes + export wait time = 10-40 minutes for a standard censhare database.
Prerequisites
ssh access to the database server as user oracle
Check prerequisites
SHOW the non-default database schemas. You have to note the name.
Usually it's only CORPUS, but on some databases there are many CORPUS_xxx and you have to check with CheckJDBC.sh.
CODE
{ # as oracle_user@database_host
sqlplus -s / as sysdba <<EOF
set pages 100 lines 120 feed off
select username "1.CHOOSE_USER_TO_EXPORT" from dba_users where account_status='OPEN' and username not like '%SYS%' and username not in ('MGMT_VIEW','DBSNMP') order by 1;
EOF
echo "# It's OK if you see above only CORPUS, otherwise check with CheckJDBC.sh from corpus at the application server." ;
}GENERATE a command to check the free space:
CODE
{ sqlplus -s / as sysdba <<EOF
set pages 100 lines 70 feed off
select 'du -sh $HOME/oradata/corpus' "2.CHECK_CURRENT_DB_SIZE" from dual;
select 'df -h '|| DIRECTORY_PATH "3.CHECK_FREE_SPACE" from dba_directories where directory_name='DATA_PUMP_DIR';
EOF
}Export
This script asks for the schema name. Take it from the list above and export it.
ASK for the schema name, see the list above
EXPO the schema
CODE
{ # as oracle_user@database_host
unset USER_TO_EXPORT;
echo -n -e "\nEnter database user to export [CORPUS]: " ;
read USER_TO_EXPORT ;
test -z "$USER_TO_EXPORT" && USER_TO_EXPORT=CORPUS ;
expdp \"/ as sysdba\" schemas=$USER_TO_EXPORT \
dumpfile=$USER_TO_EXPORT_$(date '+%Y-%m-%d_%H%M%S').dmp logfile=$USER_TO_EXPORT_$(date '+%Y-%m-%d_%H%M%S').elog \
flashback_scn=$(echo -e "select to_char(current_scn) from v\$database;" | sqlplus -s / as sysdba 2>/dev/null| grep [0-9])
}Cleanup (optional)
GENERATE a command to check for old export files.
CODE
{ # as oracle_user@database_host
sqlplus -s / as sysdba <<EOF
set pages 100 lines 70 feed off
select 'cd '|| DIRECTORY_PATH ||' ; ls -ltrh' "3. CLEANUP_UNUSED_FILES" from dba_directories where directory_name='DATA_PUMP_DIR';
EOF
}