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 in 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
}