Sunday, September 2, 2007

temp tablespace generation

Location : /u01/home/oracle/admin/XEROXDEV/udump

CREATE CONTROLFILE REUSE DATABASE "XEROXDEV" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 256
MAXINSTANCES 1
MAXLOGHISTORY 10890
LOGFILE
GROUP 5 (
'/u01/oradata/XEROXDEV/redo05a.log',
'/u01/oradata/XEROXDEVredo05b.log'
) SIZE 10M,
GROUP 6 (
'/u01/oradata/XEROXDEV/redo06a.log',
'/u01/oradata/XEROXDEVredo06b.log'
) SIZE 10M,
GROUP 7 (
'/u01/oradata/XEROXDEV/redo07a.log',
'/u01/oradata/XEROXDEVredo07b.log'
) SIZE 10M,
GROUP 8 (
'/u01/oradata/XEROXDEV/redo08a.log',
'/u01/oradata/XEROXDEVredo08b.log'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/XEROXDEV/system01.dbf',
'/u01/oradata/XEROXDEV/general_data01.dbf',
'/u01/oradata/XEROXDEV/xerox_cc01.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414.dbf',
'/u01/oradata/XEROXDEV/ext414.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_02.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_03.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_04.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_05.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_06.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_07.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_08.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_09.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_10.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_11.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_12.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_13.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_14.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_15.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_16.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_17.dbf',
'/u01/oradata/XEROXDEV/xerox_ext414_18.dbf',
'/u01/oradata/XEROXDEV/users01.dbf',
'/u01/oradata/XEROXDEV/TSAP2.idx',
'/u01/oradata/XEROXDEV/undotbs2_01.dbf',
'/u01/oradata/XEROXDEV/general_index01.dbf',
'/u01/oradata/XEROXDEV/inventory_data01.dbf',
'/u01/oradata/XEROXDEV/inventory_index01.dbf',
'/u01/oradata/XEROXDEV/order_data01.dbf',
'/u01/oradata/XEROXDEV/order_index01.dbf',
'/u01/oradata/XEROXDEV/pbe_data01.dbf',
'/u01/oradata/XEROXDEV/pbe_index01.dbf',
'/u01/oradata/XEROXDEV/security_data01.dbf',
'/u01/oradata/XEROXDEV/security_index01.dbf',
'/u01/oradata/XEROXDEV/tp_data01.dbf',
'/u01/oradata/XEROXDEV/tp_index01.dbf',
'/u01/oradata/XEROXDEV/trx_log_data_part01.dbf',
'/u01/oradata/XEROXDEV/trx_log_data_part02.dbf',
'/u01/oradata/XEROXDEV/trx_log_data_part03.dbf',
'/u01/oradata/XEROXDEV/trx_log_data_part04.dbf',
'/u01/oradata/XEROXDEV/trx_log_index_part01.dbf',
'/u01/oradata/XEROXDEV/trx_log_index_part02.dbf',
'/u01/oradata/XEROXDEV/trx_log_index_part03.dbf',
'/u01/oradata/XEROXDEV/trx_log_index_part04.dbf',
'/u01/oradata/XEROXDEV/trx_log_data01.dbf',
'/u01/oradata/XEROXDEV/trx_log_index01.dbf',
'/u01/oradata/XEROXDEV/utility_data01.dbf',
'/u01/oradata/XEROXDEV/utility_index01.dbf',
'/u01/oradata/XEROXDEV/wc_data01.dbf',
'/u01/oradata/XEROXDEV/wc_index01.dbf',
'/u01/oradata/XEROXDEV/extranet_data01.dbf',
'/u01/oradata/XEROXDEV/extranet_index01.dbf',
'/u01/oradata/XEROXDEV/ARCHIVE_data01.dbf',
'/u01/oradata/XEROXDEV/ARCHIVE_index01.dbf'
CHARACTER SET WE8ISO8859P1
;

From: gln_sarma [mailto:gln_sarma@iss-global.com] Sent: Saturday, September 01, 2007 6:45 AMTo: 'gln_sarma'; 'Raj Mohabe'Cc: ccdba@iss-global.comSubject: RE: Task completed pl. look into this control, log, temp files moved

Raj,

How to check further the dependency of /u02 ?


Controlfile

Copied the controlfile to /u01 and started db

Logfile : created new 4 groups and logfiles
Droped existing

temp file

SQL> CREATE TEMPORARY TABLESPACE TEMP_01 TEMPFILE
'/u01/oradata/XEROXDEV/temp_01.dbf' SIZE 3000M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
2 3
Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_01;

Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/XEROXDEV/temp_01.dbf
From: gln_sarma [mailto:gln_sarma@iss-global.com] Sent: Friday, August 31, 2007 11:33 PMTo: 'Raj Mohabe'Cc: ccdba@iss-global.comSubject: Task completed pl. look into this

Raj,

I am thru with this exercise. I checked from my side. Check and you can inform back.


Sever
192.168.202.52
oracle/c@rp00l
Sid
XEROXDEV

Task
Moving all the 52 datafiles from /u02 to /u01

Requirement
1 Shutdown immediate
‘ cp /u01/oradata/XEROXDEV
cp /u02/oradata/XEROXDEV/*.idx .
cp /u02/oradata/XEROXDEV/*.dbf .
preparation of movement statement
execution



13 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_10.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_10.dbf';
14 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_11.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_11.dbf';
15 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_12.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_12.dbf';
16 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_13.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_13.dbf';
17 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_14.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_14.dbf';
18 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_15.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_15.dbf';
19 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_16.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_16.dbf';
20 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_17.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_17.dbf';
21 alter database rename file '/u02/oradata/XEROXDEV/xerox_ext414_18.dbf' to '/u01/oradata/XEROXDEV/xerox_ext414_18.dbf';
22 alter database rename file '/u02/oradata/XEROXDEV/users01.dbf' to '/u01/oradata/XEROXDEV/users01.dbf';

23 alter database rename file '/u02/oradata/XEROXDEV/general_index01.dbf' to '/u01/oradata/XEROXDEV/general_index01.dbf';
24 alter database rename file '/u02/oradata/XEROXDEV/inventory_data01.dbf' to '/u01/oradata/XEROXDEV/inventory_data01.dbf';
25 alter database rename file '/u02/oradata/XEROXDEV/inventory_index01.dbf' to '/u01/oradata/XEROXDEV/inventory_index01.dbf';
26 alter database rename file '/u02/oradata/XEROXDEV/order_data01.dbf' to '/u01/oradata/XEROXDEV/order_data01.dbf';
27 alter database rename file '/u02/oradata/XEROXDEV/order_index01.dbf' to '/u01/oradata/XEROXDEV/order_index01.dbf';
28 alter database rename file '/u02/oradata/XEROXDEV/pbe_data01.dbf' to '/u01/oradata/XEROXDEV/pbe_data01.dbf';
29 alter database rename file '/u02/oradata/XEROXDEV/pbe_index01.dbf' to '/u01/oradata/XEROXDEV/pbe_index01.dbf';
30 alter database rename file '/u02/oradata/XEROXDEV/security_data01.dbf' to '/u01/oradata/XEROXDEV/security_data01.dbf';
31 alter database rename file '/u02/oradata/XEROXDEV/security_index01.dbf' to '/u01/oradata/XEROXDEV/security_index01.dbf';
32 alter database rename file '/u02/oradata/XEROXDEV/tp_data01.dbf' to '/u01/oradata/XEROXDEV/tp_data01.dbf';
33 alter database rename file '/u02/oradata/XEROXDEV/tp_index01.dbf' to '/u01/oradata/XEROXDEV/tp_index01.dbf';
34 alter database rename file '/u02/oradata/XEROXDEV/trx_log_data_part01.dbf' to

'/u01/oradata/XEROXDEV/trx_log_data_part01.dbf';
35 alter database rename file '/u02/oradata/XEROXDEV/trx_log_data_part02.dbf' to

'/u01/oradata/XEROXDEV/trx_log_data_part02.dbf';
36 alter database rename file '/u02/oradata/XEROXDEV/trx_log_data_part03.dbf' to

'/u01/oradata/XEROXDEV/trx_log_data_part03.dbf';
37 alter database rename file '/u02/oradata/XEROXDEV/trx_log_data_part04.dbf' to

'/u01/oradata/XEROXDEV/trx_log_data_part04.dbf';
38 alter database rename file '/u02/oradata/XEROXDEV/trx_log_index_part01.dbf' to

'/u01/oradata/XEROXDEV/trx_log_index_part01.dbf';
39 alter database rename file '/u02/oradata/XEROXDEV/trx_log_index_part02.dbf' to

'/u01/oradata/XEROXDEV/trx_log_index_part02.dbf';
40 alter database rename file '/u02/oradata/XEROXDEV/trx_log_index_part03.dbf' to

'/u01/oradata/XEROXDEV/trx_log_index_part03.dbf';
41 alter database rename file '/u02/oradata/XEROXDEV/trx_log_index_part04.dbf' to

'/u01/oradata/XEROXDEV/trx_log_index_part04.dbf';
42 alter database rename file '/u02/oradata/XEROXDEV/trx_log_data01.dbf' to '/u01/oradata/XEROXDEV/trx_log_data01.dbf';
43 alter database rename file '/u02/oradata/XEROXDEV/trx_log_index01.dbf' to '/u01/oradata/XEROXDEV/trx_log_index01.dbf';
44 alter database rename file '/u02/oradata/XEROXDEV/utility_data01.dbf' to '/u01/oradata/XEROXDEV/utility_data01.dbf';

45 alter database rename file '/u02/oradata/XEROXDEV/utility_index01.dbf' to '/u01/oradata/XEROXDEV/utility_index01.dbf';
46 alter database rename file '/u02/oradata/XEROXDEV/wc_data01.dbf' to '/u01/oradata/XEROXDEV/wc_data01.dbf';
47 alter database rename file '/u02/oradata/XEROXDEV/wc_index01.dbf' to '/u01/oradata/XEROXDEV/wc_index01.dbf';
48 alter database rename file '/u02/oradata/XEROXDEV/extranet_data01.dbf' to '/u01/oradata/XEROXDEV/extranet_data01.dbf';
49 alter database rename file '/u02/oradata/XEROXDEV/extranet_index01.dbf' to '/u01/oradata/XEROXDEV/extranet_index01.dbf';
50 alter database rename file '/u02/oradata/XEROXDEV/ARCHIVE_data01.dbf' to '/u01/oradata/XEROXDEV/ARCHIVE_data01.dbf';
51 alter database rename file '/u02/oradata/XEROXDEV/ARCHIVE_index01.dbf' to '/u01/oradata/XEROXDEV/ARCHIVE_index01.dbf'
;
52 alter database rename file '/u02/oradata/XEROXDEV/TSAP2.idx' to '/u01/oradata/XEROXDEV/TSAP2.idx';
52 executed

SQL> alter database open;

Database altered.


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/XEROXDEV/system01.dbf
/u01/oradata/XEROXDEV/general_data01.dbf
/u01/oradata/XEROXDEV/xerox_cc01.dbf
/u01/oradata/XEROXDEV/xerox_ext414.dbf
/u01/oradata/XEROXDEV/ext414.dbf
/u01/oradata/XEROXDEV/xerox_ext414_02.dbf
/u01/oradata/XEROXDEV/xerox_ext414_03.dbf
/u01/oradata/XEROXDEV/xerox_ext414_04.dbf
/u01/oradata/XEROXDEV/xerox_ext414_05.dbf
/u01/oradata/XEROXDEV/xerox_ext414_06.dbf
/u01/oradata/XEROXDEV/xerox_ext414_07.dbf

NAME
--------------------------------------------------------------------------------
/u01/oradata/XEROXDEV/xerox_ext414_08.dbf
/u01/oradata/XEROXDEV/xerox_ext414_09.dbf
/u01/oradata/XEROXDEV/xerox_ext414_10.dbf
/u01/oradata/XEROXDEV/xerox_ext414_11.dbf
/u01/oradata/XEROXDEV/xerox_ext414_12.dbf
/u01/oradata/XEROXDEV/xerox_ext414_13.dbf
/u01/oradata/XEROXDEV/xerox_ext414_14.dbf
/u01/oradata/XEROXDEV/xerox_ext414_15.dbf
/u01/oradata/XEROXDEV/xerox_ext414_16.dbf
/u01/oradata/XEROXDEV/xerox_ext414_17.dbf
/u01/oradata/XEROXDEV/xerox_ext414_18.dbf

NAME
--------------------------------------------------------------------------------
/u01/oradata/XEROXDEV/users01.dbf
/u01/oradata/XEROXDEV/TSAP2.idx
/u01/oradata/XEROXDEV/undotbs2_01.dbf
/u01/oradata/XEROXDEV/general_index01.dbf
/u01/oradata/XEROXDEV/inventory_data01.dbf
/u01/oradata/XEROXDEV/inventory_index01.dbf
/u01/oradata/XEROXDEV/order_data01.dbf
/u01/oradata/XEROXDEV/order_index01.dbf
/u01/oradata/XEROXDEV/pbe_data01.dbf
/u01/oradata/XEROXDEV/pbe_index01.dbf
/u01/oradata/XEROXDEV/security_data01.dbf

NAME
--------------------------------------------------------------------------------
/u01/oradata/XEROXDEV/security_index01.dbf
/u01/oradata/XEROXDEV/tp_data01.dbf
/u01/oradata/XEROXDEV/tp_index01.dbf
/u01/oradata/XEROXDEV/trx_log_data_part01.dbf
/u01/oradata/XEROXDEV/trx_log_data_part02.dbf
/u01/oradata/XEROXDEV/trx_log_data_part03.dbf
/u01/oradata/XEROXDEV/trx_log_data_part04.dbf
/u01/oradata/XEROXDEV/trx_log_index_part01.dbf
/u01/oradata/XEROXDEV/trx_log_index_part02.dbf
/u01/oradata/XEROXDEV/trx_log_index_part03.dbf
/u01/oradata/XEROXDEV/trx_log_index_part04.dbf

NAME
--------------------------------------------------------------------------------
/u01/oradata/XEROXDEV/trx_log_data01.dbf
/u01/oradata/XEROXDEV/trx_log_index01.dbf
/u01/oradata/XEROXDEV/utility_data01.dbf
/u01/oradata/XEROXDEV/utility_index01.dbf
/u01/oradata/XEROXDEV/wc_data01.dbf
/u01/oradata/XEROXDEV/wc_index01.dbf
/u01/oradata/XEROXDEV/extranet_data01.dbf
/u01/oradata/XEROXDEV/extranet_index01.dbf
/u01/oradata/XEROXDEV/ARCHIVE_data01.dbf
/u01/oradata/XEROXDEV/ARCHIVE_index01.dbf

54 rows selected.

No /u02
SQL> select name from v$datafile where name like '/u02%';

no rows selected

Db started
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1108416700 bytes
Fixed Size 452796 bytes
Variable Size 268435456 bytes
Database Buffers 838860800 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
Posted by at No comments:

db creation and import

Raj

Import completed

AMATPOP ON 35
On 23
SQL> select owner,count(*) from dba_objects group by owner;

OWNER COUNT(*)
------------------------------ ----------
CC 1
DBSNMP 8
EXTAMAT 46
OUTLN 7
PUBLIC 1581
READONLY 1
SONIC35 61
SYS 3867
SYSTEM 406
WCAMATPO 617



OWNER COUNT(*)
------------------------------ ----------
CC 1
DBSNMP 8
EXTAMAT 46
OUTLN 7
PUBLIC 1584
READONLY 1
SONIC35 60
SYS 3878
SYSTEM 458
WCAMATPO 613



Server
192.168.202.23

Sid
AMATPOP

Folder creation
oracle.DANKAPRD.HI04L>pwd
/u01/home/oracle/admin

Dump location
oracle.DANKAPRD.HI04L>mkdir AMATPOP
oracle.DANKAPRD.HI04L>cd AMATPOP/
oracle.DANKAPRD.HI04L>mkdir bdump udump cdump

Scripts copy
>mkdir create
oracle.DANKAPRD.HI04L>ls -lt
total 16
drwxr-xr-x 2 oracle dba 4096 Aug 30 20:50 create
drwxr-xr-x 2 oracle dba 4096 Aug 30 20:48 bdump
drwxr-xr-x 2 oracle dba 4096 Aug 30 20:48 cdump
drwxr-xr-x 2 oracle dba 4096 Aug 30 20:48 udump
oracle.DANKAPRD.HI04L>cd ..

Copy creation scripts
scp oracle@192.168.202.35:/u01/home/oracle/admin/AMATPOP/create/cr*.sql .

Copy initAMATPOP
cd /u01/home/oracle/product/9.2.0.6EE/dbs
oracle.AMATPOP.HI04L>scp oracle@192.168.202.35:/u01/home/oracle/product/9.2.0.4/dbs/initAMATPOP.ora .

Basic changes
Making the db in no archive for creation
Main datafiles in /u01 syste, undo, temp, control files
Others in /u02/ and /u03


AMATOP
oracle.AMATPOP.HI04L>cd /u03
oracle.AMATPOP.HI04L>mkdir oradata
oracle.AMATPOP.HI04L>cd oradata/
oracle.AMATPOP.HI04L>mkdir AMATAPOP

cd /u01/oradata
mkdir AMATPOP
cd /u02/oradata
mkdir AMATPOP

U03





U02

U01
DB Creation
@ /u01/home/oracle/admin/AMATPOP/create/cr91.sql


SQL> spool cr91.log
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1292966348 bytes
Fixed Size 453068 bytes
Variable Size 234881024 bytes
Database Buffers 1056964608 bytes
Redo Buffers 667648 bytes
SQL> CREATE DATABASE AMATPOP
2 MAXINSTANCES 1
3 MAXLOGHISTORY 1
4 MAXLOGFILES 32
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 256
7 DATAFILE '/u01/oradata/AMATPOP/system01.dbf' SIZE 250M
8 AUTOEXTEND ON NEXT 20M MAXSIZE 1900M
9 EXTENT MANAGEMENT LOCAL
10 DEFAULT TEMPORARY TABLESPACE TEMP
11 TEMPFILE '/u02/oradata/AMATPOP/temp01.dbf' SIZE 40M
12 AUTOEXTEND ON NEXT 20M MAXSIZE 1900M
13 UNDO TABLESPACE "UNDOTBS"
14 DATAFILE '/u01/oradata/AMATPOP/undotbs01.dbf' SIZE 200M
15 AUTOEXTEND ON NEXT 20M MAXSIZE 2000M
16 CHARACTER SET WE8ISO8859P1
17 NATIONAL CHARACTER SET AL16UTF16
18 LOGFILE GROUP 1 ('/u01/oradata/AMATPOP/redo01a.log',
19 '/u02/oradata/AMATPOP/redo01b.log') SIZE 50M,
20 GROUP 2 ('/u01/oradata/AMATPOP/redo02a.log',
21 '/u02/oradata/AMATPOP/redo02b.log') SIZE 50M,
22 GROUP 3 ('/u01/oradata/AMATPOP/redo03a.log',
23 '/u02/oradata/AMATPOP/redo03b.log') SIZE 50M,
24 GROUP 4 ('/u01/oradata/AMATPOP/redo04a.log',
25 '/u02/oradata/AMATPOP/redo04b.log') SIZE 50M;

Database created.

Executed
Package creation
@ $ORACLE_HOME/rdbms/admin/catalog.sql
@ $ORACLE_HOME/rdbms/admin/catproc.sql
Executed
Executed

Basic tablespace creation
SQL> set echo on
SQL> spool cr92.log
SQL>
SQL> CREATE TABLESPACE WCAMATPO_DATA LOGGING
2 DATAFILE '/u02/oradata/AMATPOP/wcamatpo_data01.dbf' SIZE 100M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE 2000M
4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE WCAMATPO_INDX LOGGING
DATAFILE '/u02/oradata/AMATPOP/wcamatpo_indx.dbf' SIZE 100M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE EXTAMATPO_DATA LOGGING
DATAFILE '/u02/oradata/AMATPOP/ext_data01.dbf' SIZE 100M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE EXTAMATPO_INDX LOGGING
DATAFILE '/u02/oradata/AMATPOP/extamatpo_indx.dbf' SIZE 100M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE "TOOLS" LOGGING
DATAFILE '/u02/oradata/AMATPOP/tools01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
spool off
exit;
Tablespace created.

SQL> 2 3 4
Tablespace created.

SQL> 2 3 4
Tablespace created.

SQL> 2 3 4
Tablespace created.

SQL> SQL> 2 3 4
Tablespace created.
Created all /u02
User creation
CREATE USER SONIC35
IDENTIFIED BY VALUES '93BAC9763808485A'
DEFAULT TABLESPACE EXTAMATPO_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for SONIC35
GRANT CONNECT TO SONIC35;
GRANT RESOURCE TO SONIC35;
GRANT SELECT_CATALOG_ROLE TO SONIC35;
ALTER USER SONIC35 DEFAULT ROLE ALL;
-- 1 System Privilege for SONIC35
GRANT UNLIMITED TABLESPACE TO SONIC35;
Done

CREATE USER OUTLN
IDENTIFIED BY VALUES '4A3BA55E08595C81'
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for OUTLN
GRANT CONNECT TO OUTLN;
GRANT RESOURCE TO OUTLN;
ALTER USER OUTLN DEFAULT ROLE ALL;
-- 2 System Privileges for OUTLN
GRANT UNLIMITED TABLESPACE TO OUTLN;
GRANT EXECUTE ANY PROCEDURE TO OUTLN;
-- 1 Object Privilege for OUTLN
GRANT EXECUTE ON SYS.OUTLN_PKG TO OUTLN;


CREATE USER OSDBA
IDENTIFIED BY VALUES '4BBB77BF087CBCDE'
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for OSDBA
GRANT DBA TO OSDBA;
GRANT CONNECT TO OSDBA;
GRANT RESOURCE TO OSDBA;
ALTER USER OSDBA DEFAULT ROLE ALL;
-- 1 System Privilege for OSDBA
GRANT UNLIMITED TABLESPACE TO OSDBA;

Done

CREATE USER MONITORONLY
IDENTIFIED BY VALUES '5986DBDFB0C18CCD'
DEFAULT TABLESPACE TOOLS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for MONITORONLY
GRANT CONNECT TO MONITORONLY;
ALTER USER MONITORONLY DEFAULT ROLE ALL;


CREATE USER EXTAMAT
IDENTIFIED BY VALUES '96C1153646820892'
DEFAULT TABLESPACE EXTAMATPO_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for EXTAMAT
GRANT CONNECT TO EXTAMAT;
GRANT RESOURCE TO EXTAMAT;
GRANT SELECT_CATALOG_ROLE TO EXTAMAT;
ALTER USER EXTAMAT DEFAULT ROLE ALL;
-- 1 System Privilege for EXTAMAT
GRANT UNLIMITED TABLESPACE TO EXTAMAT;
-- 5 Object Privileges for EXTAMAT
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON WCAMATPO.T_EBU TO EXTAMAT;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON WCAMATPO.T_ITM_EBU_ITEM TO EXTAMAT;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON WCAMATPO.T_ITM_ITEM TO EXTAMAT;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON WCAMATPO.T_NIM_INVENTORY TO EXTAMAT;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON WCAMATPO.T_NIM_REPLENISH_ITEM TO EXTAMAT;
Done

CREATE USER WCAMATPO
IDENTIFIED BY VALUES '7E09C14A82F1F9EA'
DEFAULT TABLESPACE WCAMATPO_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for WCAMATPO
GRANT CONNECT TO WCAMATPO;
GRANT RESOURCE TO WCAMATPO;
GRANT SELECT_CATALOG_ROLE TO WCAMATPO;
ALTER USER WCAMATPO DEFAULT ROLE ALL;
-- 1 System Privilege for WCAMATPO
GRANT UNLIMITED TABLESPACE TO WCAMATPO;
Done
Check users
On 35 12 users
SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
SONIC35
OSDBA
MONITORONLY
EXTAMAT
CC
DBMONITOR
READONLY
WCAMATPO

12 rows selected.
Tallied
SPACE REQ
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments where owner='WCAMATPO' and segment_name not in ('INSTANCE');

SUM(BYTES)/(1024*1024*1024)
---------------------------
29.9572449

SQL> select sum(bytes)/(1024*1024*1024) from dba_segments where owner='WCAMATPO' and segment_name not in ('INSTANCE') and
2 segment_type not in ('INDEX');

SUM(BYTES)/(1024*1024*1024)
---------------------------
24.2778015

CALC ON 35 for par file given
Others
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments where owner not in ('WCAMATPO');

SUM(BYTES)/(1024*1024*1024)
---------------------------
4.03012848
Others
Datafiles creation
/u01 -8gb
/u02 -12 gb
/u03 -10 gb


SQL> alter database datafile '/u01/oradata/AMATPOP/undotbs01.dbf' resize 2000m;

Database altered.


alter database datafile '/u02/oradata/AMATPOP/wcamatpo_data01.dbf' resize 2000m;



ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE '/u02/oradata/AMATPOP/wcamatpo_data02.dbf' SIZE 2000M AUTOEXTEND OFF;
SQL> ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE '/u02/oradata/AMATPOP/wcamatpo_data05.dbf' SIZE 2000M AUTOEXTEND OFF;

Tablespace altered.
SQL> ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE '/u03/oradata/AMATPOP/wcamatpo_data06.dbf' SIZE 2000M AUTOEXTEND off;

Tablespace altered.

SQL> ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE '/u02/oradata/AMATPOP/wcamatpo_data02.dbf' SIZE 2000M AUTOEXTEND OFF;

Tablespace altered.



SQL> ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE '/u02/oradata/AMATPOP/wcamatpo_data03.dbf' SIZE 2000M AUTOEXTEND OFF;

Tablespace altered.

SQL> ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE '/u02/oradata/AMATPOP/wcamatpo_data04.dbf' SIZE 2000M AUTOEXTEND OFF
2 /

Tablespace altered.


SQL> ALTER TABLESPACE WCAMATPO_DATA ADD DATAFILE
2 '/u03/oradata/AMATPOP/wcamatpo_data07.dbf' SIZE 2000M AUTOEXTEND OFF,
3 '/u03/oradata/AMATPOP/wcamatpo_data08.dbf' SIZE 2000M AUTOEXTEND OFF,
4 '/u03/oradata/AMATPOP/wcamatpo_data09.dbf' SIZE 2000M AUTOEXTEND ON NEXT 50M MAXSIZE 2000M,
5 '/u03/oradata/AMATPOP/wcamatpo_data10.dbf' SIZE 2000M AUTOEXTEND ON NEXT 50M MAXSIZE 2000M,
6 '/u01/oradata/AMATPOP/wcamatpo_data11.dbf' SIZE 2000M AUTOEXTEND ON NEXT 50M MAXSIZE 2000M,
7 '/u01/oradata/AMATPOP/wcamatpo_data12.dbf' SIZE 2000M AUTOEXTEND ON NEXT 50M MAXSIZE 2000M
8 /

Import process : WCAMATPO targeting the main user , even if it fails will have time to work

Import
nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp parfile=wcamatpo_tables.par log=impHI10L_AMATPOP.29.08.07.log &

[1] 27494
Nohup.out
. . importing table "ACTIVEMESSAGEENVELOPE" 0 rows imported
. . importing table "ACTIVEMESSAGESTORE" 0 rows imported
. . importing table "ACTIVEMESSAGETOKEN" 0 rows imported
. . importing table "ACTIVEPAYLOAD" 0 rows imported
. . importing table "ACTIVEPROCESS" 0 rows imported
. . importing table "ACTIVEROLE" 0 rows imported
. . importing table "ACTIVEROLEDEF" 0 rows imported
. . importing table "ACTIVEWFINSTANCE" 0 rows imported
. . importing table "ADDRESSEDMESSAGE" 0 rows imported
. . importing table "ARCH_T_EVT_NOTIFICATION"oracle.DANKAPRD.HI04L>
Completed at 7-15 pm took 11hrs to complete
Instance table creation

nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 fromuser=READONLY touser=READONLY commit=y log=impREADONLY.log &
Readonly
29301

nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 fromuser=DBMONITOR touser=DBMONITOR commit=y log=impDBMONITOR.log &
DBMONITOR
29506


nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 fromuser=CC touser=CC commit=y log=impCC.log &
CC 29528

nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 fromuser=EXTAMAT touser=EXTAMAT commit=y log=impEXTAMAT.log &
29677

nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 fromuser=MONITORONLY touser=MONITORONLY commit=y log=impMONITORONLYlog &
29583

nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 fromuser=SONIC35 touser=SONIC35 commit=y log=impSONIC35.log &
30450

nohup imp osdba/orasys07 file=HI10L_AMATPOP.29.08.07.dmp buffer=200000 full=y rows=N commit=y log=impSTRUC.log &
30450









GLN Sarma
M: 9912595535
Integrated Software Solutions
6th Floor, Triveni Complex, Abids Hyderabad, AP, IndiaPhone: +91-40-40047761 / 24752301www.iss-global.com
Posted by at No comments:
Subscribe to: Posts (Atom)