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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment