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-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

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes ...
2 comments
Read more