Please check required Space on storage for DB FY1314 = 1.5 * Database size of last year database FY1213
And follow following steps to create FY 1314 DB and database objects structure
1) Take Export Full Dump of apx1213 DB
Note: Please remove backup tables like (sauda050612 ldfibs070712) before take export
2) Create apx1314 Database same as last year database apx1213 (tablespace name, datafiles configuration should be same)
Email: Create Database Template for Database(APX1314) using existing Database(APX1213)
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible from dba_data_files;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible from dba_temp_files;
Tablespace
Datafile
Size
Auotextend
SYSTEM
SYSTEM01.ORA
500M
100M
SYSAUX
SYSAUX01.ORA
500M
100M
UNDOTBS1
UNDOTBS01.ORA
1000M
500M
INDX
INDEX01.ORA
same as before
same as before
USR
USERS01.ORA
same as before
same as before
TEMPORARY
TEMP01.ORA
1000M
500M
Keep Redolog size same as last year DB
SELECT LOG_MODE FROM V$DATABASE;
Archive log should be enabled
Tick on Use Bigfile tablespace Datafiles for USERS01.ORA, INDEX01.ORA. Or you can add datafiles whenever required
Parameters: db_block_size, sga_target, sga_max_size, utl_file_dir, processes, audit_trail, job_queue_processes, cursor_sharing, open_cursors and other should be same as last year.
3) Create Schema User LDBO using only attached scripts createuserLDBO10g_run_from_DB_server.SQL (Note: Don’t use previously having createuserldbo.sql)
Note: attached createuserLDBO10g_run_from_DB_server.SQL have all housekeeping job like analyze, purge queue, compile
Same time, Please scheduler database backup dump job using following or attached script and run, check & verify and confirm to Kshitij
/*
Note: Please change the details as per you client database name net services name (apx1213srv), user(ldbo) password (ldbo), oracle installation location(D:\oracle\product\10.2.0\db_1\BIN), dump location(E:\exp1314)
There should be entry of Net Manager(d:\oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora) in oracle database server
Also create folder where you want backup like following E:\exp1314
Change the backup job start as per server availability
*/
conn sys@apx1314srv as sysdba
begin
dbms_scheduler.create_job
(job_name => 'exp1314',
job_type => 'EXECUTABLE',
number_of_arguments => 5,
job_action => 'D:\oracle\product\10.2.0\db_1\BIN\exp.exe',
start_date => '01-APR-13 11:00.00.00 PM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
comments=>'export dump');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',1,'system/oracle@apx1314srv');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',2,'full=Y');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',3,'file=E:\exp1314\exp1314.DMP');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',4,'log=E:\exp1314\exp1314.log');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',5,'STATISTICS= NONE');
end;
/
exec dbms_scheduler.run_job('exp1314');
4) Login into LDBO user at FY1314 db and drop and recreate Database Link
Match with last year database
Con ldbo/ldbo@apx1213srv
select owner,db_link,username,host from dba_db_links;
Con ldbo/ldbo@apx1314srv
select owner,db_link,username,host from dba_db_links;
Con ldbo/ldbo@apx1314srv
Spool c:\yep1314\drop_links.sql
SELECT 'DROP ' || decode (owner,'PUBLIC','PUBLIC DATABASE LINK ','DATABASE LINK ') || DB_LINK || ';' FROM DBA_DB_LINKS WHERE USERNAME !=' ';
@ c:\yep1314\drop_links.sql
Con ldbo/ldbo@apx1213srv
Spool c:\yep1314\create_links.sql
SELECT 'CREATE ' || decode (owner,'PUBLIC','PUBLIC DATABASE LINK ','DATABASE LINK ') || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS WHERE USERNAME !=' ';
/* put the password in above file and also change the connect string*/
Con ldbo/ldbo@apx1314srv
@ c:\yep1314\create_links.sql
Database link creation is below
Note: Please change the password and connect string as per client software
CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE PUBLIC DATABASE LINK "LNK_CCM"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_DIGITAL"
CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_PREVIOUSYEARBALANCE"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1213SRV';
5) Create Database structure (previously called optable) using exp dump (rows=N) or expdp (content=metadata_only)
imp ldbo/ldbo@apx1314srv FILE=D:\apx1213.dmp ROWS=N FULL=Y CONSTRAINTS=Y GRANTS=Y INDEXES=Y STATISTICS=NONE
or
impdp ldbo/ldbo@apx1314SRV directory=DPUMP_DIR1 dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT
6) Login into LDBO user at FY1314 and drop and recreate Sequence
Sequences should be started from 1. Therefore we drop and recreate Sequences
Con ldbo/ldbo@apx1314srv
SET LINE 9999
SPOOL C:\YEP1314\SEQ1314.SQL
select 'DROP SEQUENCE ' || Sequence_Name || ';' from dba_Sequences where Sequence_Owner='LDBO';
SPOOL OFF
@ C:\YEP1314\SEQ1314.SQL
Create Sequence LDBO.Dematuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Saudauniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Billuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Jobbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Delbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Turnbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Fwdbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Extbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Dpuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Bankuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Payrequniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Riskuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Portuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Axiscms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Pmsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Debarreduniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Bkrecouniquenumber minvalue 50000000 maxvalue 9999999999 start with 50000000 increment by 1 nocache;
Create Sequence LDBO.Sq_Smsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Offlinenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Phytodemnumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Ipouniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Trademodification minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_SpicePoa minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Idbicms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Scripuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Clientuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Brokeruniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Nsdlcdsldpuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Orarupee minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Rtgssequence minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Bobbentopool minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Clientcode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Familycode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_NigPortclose Minvalue 1 maxvalue 9999999999 Start With 1 Increment by 1 noCache;
Create Sequence LDBO.Seqclientsequence minvalue 1 maxvalue 99 start with 1 increment by 1;
Create Sequence LDBO.Sq_collateral minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Nsdlkra minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.LdOraclebatchid minvalue 1 maxvalue 9999999999 nocache;
Create Sequence LDBO.Sq_Limittokenno minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.SQ_CDSLXMLUNIQUENUMBER minvalue 10000 maxvalue 99999;
/*Missing Sequence */
select object_name from user_objects@LNK_PREVIOUSYEARBALANCE where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%'
minus
select object_name from user_objects where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%';
7) Create splogin,tglogin manually from SYS user only (already present in createuserLDBO10g_run_from_DB_server.SQL )
SELECT owner,object_name FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');
Owner Object_name
SYS SPLOGININFORMATION
SYS TGLOGININFORMATION
8) Compile All Invalid Objects
conn sys@apx1314srv as sysdba
EXEC sys.UTL_RECOMP.recomp_serial;
select object_name from dba_objects where owner='LDBO' and status='INVALID';
SELECT owner,object_name,status FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');
9) Login into LDBO user at FY1314 db and Drop and recreate Database directory
Match with last year database and change the path as per your requirement
conn sys@apx1213srv as sysdba
select 'create or replace directory '||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;
10) if Synonym is missing then create synonym from last year database apx1213 and run into current year apx1314
conn sys@apx1314srv as sysdba
Select synonym_Name From Dba_Synonyms@LNK_PREVIOUSYEARBALANCE Where table_Owner='LDBO'
Minus
Select synonym_Name From Dba_Synonyms Where table_Owner='LDBO';
conn sys@apx1213srv as sysdba
set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
SPOOL C:\YEP1314\SYNONYMS1213.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name, 'PUBLIC') metadata
from dba_synonyms
where owner = 'PUBLIC'
and table_owner = 'LDBO'
order by synonym_name;
SPOOL OFF
conn sys@apx1314srv as sysdba
@C:\YEP1314\SYNONYMS1213.SQL
11) Create Users from Last years
/* Missing Users */
conn sys@apx1213srv as sysdba
select username from dba_users@LNK_PREVIOUSYEARBALANCE
minus
select username from dba_users;
conn sys@apx1213srv as sysdba
set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
spool c:\yep1314\user_script.sql
SELECT DBMS_METADATA.GET_DDL('PROFILE',profile) FROM DBA_PROFILES WHERE profile != 'DEFAULT' GROUP BY PROFILE;
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) DDL FROM DBA_USERS where username in (select username from dba_ts_quotas);
SELECT DBMS_METADATA.GET_DDL('ROLE', role) FROM dba_roles;
spool off
conn sys@apx1314srv as sysdba
@c:\yep1314\user_script.sql
12) Create Object roles and user roles from last year using db link lnk_previousyearbalance
Conn ldbo/ldbo@apx1314srv
BEGIN
FOR cur_rec IN (SELECT role
FROM dba_roles@lnk_previousyearbalance) LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege
FROM DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP
BEGIN
EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name
FROM dba_tab_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name
FROM dba_col_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,granted_role
FROM dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
13) If you have partitioned tables in last year then create DEMATMAIN table as partitioned (partitions should be same as TRANSACTIONS table) which is attached
Drop the dematmain table and recreate partitioned DEMATMAIN table using attached script
Conn ldbo/ldbo@apx1314srv
Select TABLE_NAME From Dba_Part_Tables@LNK_PREVIOUSYEARBALANCE where owner='LDBO'
Minus
Select TABLE_NAME From Dba_Part_Tables where owner='LDBO';
14) Match Last and Current Year Database Objects (Run the following command in last and current year database and match the records)
Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*) from user_objects where status='INVALID';
Select count(*) From Dba_Synonyms Where Table_Owner='LDBO';
select count(*) from dba_Constraints where owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
Select TABLE_NAME From Dba_Part_Tables where owner='LDBO';
Conn ldbo/ldbo@apx1314srv
/*Missing Object */
Select object_Name From Dba_objects@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO'
Minus
Select object_Name From Dba_objects Where Owner='LDBO';
/*invalid objects */
Select object_Name From Dba_objects@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO' and status='INVALID'
Minus
Select object_Name From Dba_objects Where Owner='LDBO' and status='INVALID';
/*Missing Tables */
Select table_Name From Dba_tables@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO'
Minus
Select table_Name From Dba_tables Where Owner='LDBO';
/*Missing Synonyms */
Select synonym_Name From Dba_Synonyms@LNK_PREVIOUSYEARBALANCE Where table_Owner='LDBO'
Minus
Select synonym_Name From Dba_Synonyms Where table_Owner='LDBO';
/*Missing Sequences */
select * from user_objects@LNK_PREVIOUSYEARBALANCE where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%'
minus
select * from user_objects where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%';
/*Missing Constraints */
Select Constraint_Name From Dba_Constraints@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO' and (Constraint_Name not like 'SYS%' and Constraint_Name not like 'BIN%')
Minus
Select Constraint_Name From Dba_Constraints Where Owner='LDBO' and (Constraint_Name not like 'SYS%' and Constraint_Name not like 'BIN%');
/* Missing Users */
select username from dba_users@LNK_PREVIOUSYEARBALANCE
minus
select username from dba_users;
/* Missing Roles */
select role from dba_roles@LNK_PREVIOUSYEARBALANCE
minus
select role from dba_roles;
select GRANTEE from dba_tab_privs where owner='LDBO'@LNK_PREVIOUSYEARBALANCE
minus
select GRANTEE from dba_tab_privs where owner='LDBO';
15) Import Lookup tables data using DB link LNK_PREVIOUSYEARBALANCE
Conn ldbo/ldbo@apx1314srv
SET LINE 9999
SET FEEDBACK OFF
Spool c:\yep1314\lookuptables.sql
SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";'
FROM dba_constraints a
Where A.Owner='LDBO' And A.Constraint_Type In ('R')
And (A. Table_Name Like '%SMSPROVIDER%' OR A.Table_Name Like '%LOOKUP%');
select 'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM ' || TABLE_NAME || '@LNK_PREVIOUSYEARBALANCE;' from dba_tables where owner='LDBO'
and (Table_Name Like '%SMSPROVIDER%' OR Table_Name Like '%LOOKUP%');
Commit;
SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'
FROM dba_constraints a
Where A.Owner='LDBO' And A.Constraint_Type In ('R')
And (A. Table_Name Like '%SMSPROVIDER%' OR A.Table_Name Like '%LOOKUP%');
spool off
@ c:\yep1314\lookuptables.sql
INSERT INTO TBLSOFTWAREACCESSLEVEL select * from TBLSOFTWAREACCESSLEVEL@LNK_PREVIOUSYEARBALANCE;
Commit;
/* Check and verify constraints, it should not be disabled */
select Constraint_Name from Dba_Constraints Where Owner='LDBO' AND status !='ENABLED';
Also check the row count in Lookup tables
16) Analyze whole database after all above steps
conn sys@apx1314srv as sysdba
exec DBMS_SCHEDULER.run_job ('ANALYZE_FULL');
Note: After Master transmission and other bulk transmission activities , it is required to run ANALYZE_FULL job for performance
