Accueil‎ > ‎

Oracle Old IMP command

posted 17 Nov 2016, 03:07 by Christophe Noël   [ updated 21 Nov 2016, 02:26 ]
Quelques notes.

# Put the environment Variables in /etc/profile.d/oracle.sh (this will be loaded for all sessions)
export ORACLE_SID=SOA
export ORACLE_HOME=/tools/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# load it without rebooting as this:
source /etc/profile.d/oracle.sh

# Connect as system
sqlplus "/as SYSDBA"

# Drop all tablespace useless

# Un tablespace est un espace logique qui contient les objets stockés dans la base de données comme les tables ou les indexes. 
# Un tablespace est composé d'au moins un datafile, c'est à dire un fichier de données qui est physiquement présent sur le serveur à l'endroit stipulé lors de sa création. 
# Chaque datafile est constitué de segments d'au moins un extent (ou page) lui-même constitué d'au moins 3 blocs : l'élément le plus petit d'une base de données. 

# LISTING TABLESPACE
SELECT TABLESPACE_NAME "TABLESPACE",   INITIAL_EXTENT "INITIAL_EXT",   NEXT_EXTENT "NEXT_EXT",   MIN_EXTENTS "MIN_EXT",   MAX_EXTENTS "MAX_EXT",   PCT_INCREASE   FROM DBA_TABLESPACES;

# DROP tablespace and associated data files
DROP TABLESPACE SSA_IAS_TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SSA_MDS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SSA_ORABAM INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SSA_IAS_IAU INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SSA_IAS_IAU INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SSA_ORABAM INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE SWE_DATA INCLUDING CONTENTS AND DATAFILES;

# OK NOW REPLACE IN THE DUMP THE TABLESPACE CREATION WITH RIGHT VALUES
# As it is too big create the tablespaces manually.

OLD:
METRICSE
BEGINSYS
CONNECT SYSTEM
METRICSEtablespace definitions
CREATE TABLESPACE "SYSAUX" BLOCKSIZE 8192 DATAFILE  '/ssa/oradata/soa/sysaux01.dbf' SIZE 807403520 REUSE AUTOEXTEND ON NEXT 10485760  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT SPACE MANAGEMENT AUTO
CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE  '/ssa/oradata/soa/undotbs01.dbf' SIZE 6885M REUSE AUTOEXTEND ON NEXT 5242880  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE  '/ssa/oradata/soa/temp01.dbf' SIZE 7691M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1048576
CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE  '/ssa/oradata/soa/users01.dbf' SIZE 2048M REUSE AUTOEXTEND ON NEXT 1310720  MAXSIZE 32767M, '/ssa/oradata/soa/users02.dbf' SIZE 10240M REUSE, '/ssa/oradata/soa/users03.dbf' SIZE 5120M REUSE AUTOEXTEND ON NEXT 8388608  MAXSIZE 32767M, '/ssa/oradata/soa/users04.dbf' SIZE 20480M REUSE AUTOEXTEND ON NEXT 8388608  MAXSIZE 32767M, '/ssa/oradata/soa/users05.dbf' SIZE 2048M REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE  ONLINE PERMANENT  SEGMENT SPACE MANAGEMENT AUTO

CREATE TABLESPACE "EXAMPLE" BLOCKSIZE 8192 DATAFILE  '/ssa/oradata/soa/example01.dbf' SIZE 328335360 REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  NOLOGGING SEGMENT SPACE MANAGEMENT AUTO

CREATE TABLESPACE "SSA_SERVICEDATA" BLOCKSIZE 8192 DATAFILE  '/ssa/oradata/soa/SSA_SERVICEDATA.dbf' SIZE 11534336 REUSE AUTOEXTEND ON NEXT 204800  MAXSIZE 209715200  EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT SPACE MANAGEMENT AUTO

CREATE TABLESPACE "SSA_SWEDB" BLOCKSIZE 8192 DATAFILE  '/swedb/SSA_SWEDB.dbf' SIZE 256000M REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 33554431M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO

WITH FOLLOWING

CREATE TABLESPACE "SSA_SERVICEDATA" BLOCKSIZE 8192 DATAFILE  '/data/SSA_SERVICEDATA.dbf' SIZE 11534336 REUSE AUTOEXTEND ON NEXT 204800  MAXSIZE 209715200  EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT SPACE MANAGEMENT AUTO

CREATE BIGFILE TABLESPACE "SSA_SWEDB" BLOCKSIZE 8192 DATAFILE  '/data/bigSWEData.dbf' SIZE 100G REUSE AUTOEXTEND ON NEXT 200M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO


# CREATE tablespace as BIG FILE (in this cas no maxsize and 250M extends)
CREATE BIGFILE TABLESPACE SWE_DATA  DATAFILE '/data/bigSWEData.dbf' BLOCKSIZE 8K SIZE 100G AUTOEXTEND ON NEXT 250M;

# Proceed the import

# type exactly as this if require SYSDBA (another option is to create a new user with required rights)
imp \'SYS/SOApw#001 AS SYSDBA\' FILE='/data/completedb.sql' FULL=Y COMMIT=Y FEEDBACK=1000 LOG="/data/fullDump1.log"

#Check if listener is blocked
lsnrctl services
#Blocked listener needs to be restarted
lsnrctl stop
lsnrctl start

#check database mounted
select name,open_mode,database_role from v$database;
Comments