Import Oracle database
Execution time: 5 minutes + copy dumpfile time + import wait time = 20-60 minutes for a standard censhare database.
Prerequisites
ssh access to the database server as user oracle
(optional) ssh access to the censhare application server
Check and drop the current CORPUS user
SHOW the non-default schemas in the database.
{ # Run as oracle_user@database_host
sqlplus -s / as sysdba <<EOF
set pages 100 lines 120 feed off
select username "1.CHECK_EXISTING_USERS" from dba_users
where account_status='OPEN' and username not like '%SYS%' and username not in ('MGMT_VIEW','DBSNMP');
EOF
echo "# Normally you will see here nothing or only CORPUS, which has to be dropped with the next script. \n" ;
} || {
echo -e "\n\n There is a problem! You must be connected as oracle and the database must be opened."
}
- If you see many CORPUS_XXX, then you have check with CheckJDBC.sh.
- If you don't see any CORPUS schemas, then this is an empty or not a standard database.
- If you see only CORPUS, then you have to drop it with the next script.
This script does the following:
ASK for schema name
- DROP the schema
{
# ask for username to drop
unset USER_TO_DROP CONFIRM;
echo -n -e "\nEnter database user to DROP [CORPUS]: " ;
read USER_TO_DROP ;
test -z "$USER_TO_DROP" && USER_TO_DROP=CORPUS ;
# ask for confirmation
echo -n "Drop database user '$USER_TO_DROP', Are you sure? (N/y) :"
unset ans; read ans
test "$ans" != "y" -a "$ans" != "Y" && {
echo "OK: no user has been dropped. Please check and rerun the script."
} || {
# drop the user
echo -e "drop user $USER_TO_DROP cascade;\nquit;" | sqlplus -s / as sysdba
}
} || {
echo -e "\n\n There is a problem! You must be connected as oracle and the database must be opened."
}
Check and create the tablespaces
SHOW the non-default tablespaces in the database.
{
sqlplus -s / as sysdba <<EOF
set pages 100 lines 120 feed off
select tablespace_name "2.CHECK_FOR_CORPUS_TABLESPACES" from dba_tablespaces
where contents='PERMANENT' and tablespace_name not in ('SYSTEM','SYSAUX','USERS');
EOF
echo "# It's OK if you see above CORPUS, CORPUS_LOG and CORPUS_CTX[v3 only], otherwise you have to create the tablespaces with the next script." ;
}
If you see more than 3 CORPUS tablespaces, then you have to ask the contact person below.
Create CORPUS tablespaces only If you don't see them in the list above.
{
sqlplus -s / as sysdba <<EOF
CREATE TABLESPACE "CORPUS"
LOGGING
DATAFILE '$HOME/orahome/oradata/corpus/CORPUS.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CORPUS_LOG"
LOGGING
DATAFILE '$HOME/orahome/oradata/corpus/CORPUS_LOG.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
EOF
} || {
echo -e "\n\n There is a problem! You must be connected as oracle and the database must be opened."
}
Copy the dump file
SHOW the default directory in which the dump file must be copied.
{
sqlplus -s / as sysdba <<EOF
set pages 100 lines 70 feed off
select DIRECTORY_PATH "3.COPY_DUMP_FILE_TO" from dba_directories where directory_name='DATA_PUMP_DIR';
EOF
} || {
echo -e "\n\n There is a problem! You must be connected as oracle and the database must be opened."
}
Switch to NOARCHIVELOG mode (optional)
du -sh /opt/oracle/oradata/corpus
{
sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;
EOF
}
Import
This script does the following:
ASK for filename
IMPORT
{
while
echo -n "Enter dump file name to import (without the full path) : ";
do read DUMP_FILE_NAME; [ -z $DUMP_FILE_NAME ] || break;
done;
echo -n "Enter user to be imported [CORPUS]:"
unset USER_TO_IMPORT; read USER_TO_IMPORT
test -z "$USER_TO_IMPORT" && USER_TO_IMPORT=CORPUS
impdp \"/ as sysdba\" schemas=$USER_TO_IMPORT \
dumpfile=$DUMP_FILE_NAME logfile=$DUMP_FILE_NAME_$(date '+%Y-%m-%d_%H%M%S').ilog
}
Ignore this error if you are using censhare v4 or later:
ORA-01919: Rolle 'CORPUS_URL_DATASTORE_ROLE' nicht vorhanden
Alternative: Import to a different database schema
The below command can be used to import the database schema to a different schema.
{
while
echo -n "Enter dump file name to import (without the full path) : ";
do read DUMP_FILE_NAME; [ -z $DUMP_FILE_NAME ] || break;
done;
echo -n "Enter user to be imported [CORPUS]:"
unset USER_TO_IMPORT; read USER_TO_IMPORT
test -z "$USER_TO_IMPORT" && USER_TO_IMPORT=CORPUS
ORIGINAL_TABLESPACE_NAME=$USER_TO_IMPORT
echo -n "Enter desired user to be remapped [CORPUS_TEST]:"
unset NEW_USER; read NEW_USER
test -z "$NEW_USER" && NEW_USER=CORPUS_TEST
NEW_TABLESPACE_NAME=$NEW_USER
impdp \"/ as sysdba\" remap_schema=$USER_TO_IMPORT:$NEW_USER remap_tablespace=$ORIGINAL_TABLESPACE_NAME:$NEW_TABLESPACE_NAME dumpfile=$DUMP_FILE_NAME logfile=$DUMP_FILE_NAME_$(date '+%Y-%m-%d_%H%M%S').ilog
}
Switch back to ARCHIVELOG mode (optional)
{
sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
EOF
}
Cleanup
GENERATE a command to check for old export files.
{
sqlplus -s / as sysdba <<EOF
set pages 100 lines 70 feed off
select 'ls -ltrh '|| DIRECTORY_PATH "CLEANUP_UNUSED_FILES" from dba_directories where directory_name='DATA_PUMP_DIR';
EOF
} || {
echo -e "\n\n There is a problem! You must be connected as oracle and the database must be opened."
}