Skip to main content

ORACLE DATABASE CREATE SCRIPT




/* Script creates PVPROD02 database

Actions include;
   Create Database
   Create Tablespaces
   Setup Dictionary
   Create Application Roles
   Create Application Users

*/

------------------------------------------------------------------------------------------
-- set SID
------------------------------------------------------------------------------------------
define CR_SID = PVPROD02

spool create_db_&CR_SID

set time on
set timing on
set echo on
set pagesize 4000
set linesize 2000
set trims on
 
connect / as sysdba

startup  nomount


------------------------------------------------------------------------------------------
-- Create Database
------------------------------------------------------------------------------------------

CREATE DATABASE &CR_SID ARCHIVELOG
    USER SYS IDENTIFIED BY sys4&CR_SID
    USER SYSTEM IDENTIFIED BY system4&CR_SID
    MAXLOGFILES 50
    MAXLOGMEMBERS 4
    MAXDATAFILES 1000
    MAXINSTANCES 8
    MAXLOGHISTORY 20000
    CHARACTER SET UTF8
LOGFILE
  GROUP 01 ('/u4002/oradata/PVPROD02/log_01a.ora', '/u4003/oradata/PVPROD02/log_01b.ora') SIZE 200M,
  GROUP 02 ('/u4002/oradata/PVPROD02/log_02a.ora', '/u4003/oradata/PVPROD02/log_02b.ora') SIZE 200M,
  GROUP 03 ('/u4002/oradata/PVPROD02/log_03a.ora', '/u4003/oradata/PVPROD02/log_03b.ora') SIZE 200M,
  GROUP 04 ('/u4002/oradata/PVPROD02/log_04a.ora', '/u4003/oradata/PVPROD02/log_04b.ora') SIZE 200M,
  GROUP 05 ('/u4002/oradata/PVPROD02/log_05a.ora', '/u4003/oradata/PVPROD02/log_05b.ora') SIZE 200M,
  GROUP 06 ('/u4002/oradata/PVPROD02/log_06a.ora', '/u4003/oradata/PVPROD02/log_06b.ora') SIZE 200M,
  GROUP 07 ('/u4002/oradata/PVPROD02/log_07a.ora', '/u4003/oradata/PVPROD02/log_07b.ora') SIZE 200M,
  GROUP 08 ('/u4002/oradata/PVPROD02/log_08a.ora', '/u4003/oradata/PVPROD02/log_08b.ora') SIZE 200M
--   CHARACTER SET US7ASCII
--   NATIONAL CHARACTER SET AL16UTF16
DATAFILE  '/u4004/oradata/PVPROD02/system_01.dbf' SIZE 600M 
    EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE  '/u4004/oradata/PVPROD02/sysaux_01.dbf' SIZE 500M REUSE
UNDO TABLESPACE UNDO
    DATAFILE '/u4005/oradata/PVPROD02/UNDO_01.dbf' SIZE 1000M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMPTEMP
    TEMPFILE '/u4006/oradata/PVPROD02/temptemp_01.dbf' SIZE 1000M AUTOEXTEND OFF
;


------------------------------------------------------------------------------------------
-- Create Tablespaces
------------------------------------------------------------------------------------------


--CREATE  TABLESPACE TEMP
--      DATAFILE '/u4006/oradata/PVPROD02/temp_01.dbf' SIZE 2000M,
--               '/u4006/oradata/PVPROD02/temp_02.dbf' SIZE 2000M
--      EXTENT MANAGEMENT LOCAL;

--DROP TABLESPACE TEMP;

CREATE TEMPORARY TABLESPACE TEMP
      TEMPFILE '/u4006/oradata/PVPROD02/temp_01.dbf' SIZE 2000M,
               '/u4006/oradata/PVPROD02/temp_02.dbf' SIZE 2000M
      ;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE TEMPTEMP INCLUDING CONTENTS AND DATAFILES;


CREATE TABLESPACE TOOLS LOGGING
      DATAFILE '/u4007/oradata/PVPROD02/tools_01.dbf' SIZE 500M
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO
;

CREATE TABLESPACE USERS LOGGING
      DATAFILE '/u4007/oradata/PVPROD02/users_01.dbf' SIZE 500M
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO
;


------------------------------------------------------------------------------------------
-- ROMT
------------------------------------------------------------------------------------------
CREATE TABLESPACE ROMTAPP LOGGING
      DATAFILE '/u4010/oradata/PVPROD02/romtapp_01.dbf' SIZE 2000M REUSE
      EXTENT MANAGEMENT LOCAL 
      SEGMENT SPACE MANAGEMENT AUTO
;

CREATE TABLESPACE ROMTRPT LOGGING
      DATAFILE '/u4011/oradata/PVPROD02/romtrpt_01.dbf' SIZE 2000M REUSE
      EXTENT MANAGEMENT LOCAL 
      SEGMENT SPACE MANAGEMENT AUTO
;



/***************************************************************
------------------------------------------------------------------------------------------
-- TEMP_DBA
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE TEMP_DBA
       TEMPFILE '/u4006/oradata/PVPROD02/TEMP_DBA_01.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_02.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_03.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_04.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_05.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_06.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_07.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_08.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_09.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF
       EXTENT MANAGEMENT LOCAL
;
************************************************************/




----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------



select name from v$datafile;

------------------------------------------------------------------------------------------
-- Setup Dictionary
------------------------------------------------------------------------------------------

set echo off
set timing off
set time off
spool create_post_db_&CR_SID

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
-- @?/rdbms/admin/catrep.sql 


-- Create PLUSTRACE role
@?/sqlplus/admin/plustrce.sql

connect system/system4&CR_SID

@?/sqlplus/admin/pupbld.sql

set echo on
set timing on
set time on
spool create_post1_db_&CR_SID


------------------------------------------------------------------------------------------
-- Create Application Roles
------------------------------------------------------------------------------------------


--Role of old connect
CREATE ROLE "CONNECT_SCHEMA"  NOT IDENTIFIED;
GRANT CREATE CLUSTER TO "CONNECT_SCHEMA";
GRANT CREATE DATABASE LINK TO "CONNECT_SCHEMA";
GRANT CREATE SEQUENCE TO "CONNECT_SCHEMA";
GRANT CREATE SESSION TO "CONNECT_SCHEMA";
GRANT CREATE SYNONYM TO "CONNECT_SCHEMA";
GRANT CREATE TABLE TO "CONNECT_SCHEMA";
GRANT CREATE VIEW TO "CONNECT_SCHEMA";
GRANT CREATE PROCEDURE TO "CONNECT_SCHEMA";
GRANT CREATE TRIGGER TO "CONNECT_SCHEMA";
GRANT CREATE TYPE TO "CONNECT_SCHEMA";


------------------------------------------------------------------------------------------
-- Create DBA Users
------------------------------------------------------------------------------------------

CREATE USER DBADMIN
IDENTIFIED BY DBADMIN
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
GRANT SELECT ANY DICTIONARY TO DBADMIN;
GRANT UNLIMITED TABLESPACE TO DBADMIN;
GRANT CONNECT TO DBADMIN;
GRANT DBA TO DBADMIN;
GRANT EXP_FULL_DATABASE TO DBADMIN;
GRANT IMP_FULL_DATABASE TO DBADMIN;
GRANT OEM_MONITOR TO DBADMIN;
GRANT SELECT_CATALOG_ROLE TO DBADMIN;




CREATE USER BBUSER IDENTIFIED BY BBUSER PASSWORD EXPIRE
 DEFAULT TABLESPACE USERS  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO BBUSER;

CREATE USER ATRACHTENBERG IDENTIFIED BY ATRACHTENBERG PASSWORD EXPIRE
 DEFAULT TABLESPACE USERS  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, CONNECT_SCHEMA, RESOURCE, DBA, PLUSTRACE TO ATRACHTENBERG;

CREATE USER DPAUL IDENTIFIED BY DPAUL PASSWORD EXPIRE
 DEFAULT TABLESPACE USERS  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, CONNECT_SCHEMA, RESOURCE, DBA, PLUSTRACE TO DPAUL;
GRANT EXECUTE ANY PROCEDURE TO DPAUL;

ALTER USER SYS IDENTIFIED BY harpo;
ALTER USER SYSTEM IDENTIFIED BY chico;



----------------------------------------------------------------------------------------

connect / as sysdba
shutdown immediate
startup


-- alter user sysman identified by sysman4&CR_SID;

spool create_post2_db_&CR_SID

define default_tablespace='tools'
define temporary_tablespace='temp'
define perfstat_password='perfstat'
@?/rdbms/admin/spcreate
undefine perfstat_password

spool off
quit;

Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more