PT - Joins Methods and Examples

1. Nested Loop Join
2. hash table Join
3. Sort-merge Join

##########  Nested Loop Join #################

SQL> SELECT ENAME, DNAME FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_NO=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |   113 |  3616 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |         |   113 |  3616 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |   113 |  2147 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | DEPT_IX |   113 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPT_NO"=10)
   5 - access("EMP"."DEPT_NO"=10)

SQL>

##########  Hash table Join  #############

SQL> SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_LAST_NAME FROM ORDERS O,  CUSTOMERS C WHERE O.CUSTOMER_ID = C.CUSTOMER_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 3042513348

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1376K|    45M|  5060   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |           |  1376K|    45M|  5060   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 46023 |   584K|   214   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| ORDERS    |  1353K|    28M|  4839   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

Note
-----
   - this is an adaptive plan

SQL>

###############  Sort-merge Join #############

SQL> SELECT ENAME , DNAME FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO;

Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   879 | 28128 |     8  (13)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |   879 | 28128 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     7 |    91 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |     7 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |   879 | 16701 |     6  (17)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |   879 | 16701 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
       filter("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

SQL>

PT - Table Access Examples while executing queries

Table Access Examples  (plus trace already enabled)

1. FULL TABLE  SCAN
2. INDEX UNIQUE SCAN
3. INDEX RANGE SCAN
4. INDEX RANGE SCAN DESCENDING
5. INDEX FULL SCAN
6. INDEX FAST FULL SCAN

C:\Users\varunyadav>sqlplus soe/soe@smrplm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 30 20:02:44 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Mar 30 2020 18:20:13 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho user
USER is "SOE"
SQL> SET LINESIZE 180
SQL> SET AUTOT TRACE EXP  (Already enabled plustrace)


###################### FULL TABLE  SCAN #########################

SQL> SELECT * FROM WAREHOUSES;

Execution Plan
----------------------------------------------------------
Plan hash value: 3585052864

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1000 | 27000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| WAREHOUSES |  1000 | 27000 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL>

###################### INDEX UNIQUE SCAN #########################

SQL> SELECT * FROM WAREHOUSES WHERE WAREHOUSE_ID=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2784191864

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WAREHOUSES    |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | WAREHOUSES_PK |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("WAREHOUSE_ID"=10)

SQL>


################### INDEX RANGE SCAN ##############################

SQL> SELECT * FROM WAREHOUSES WHERE WAREHOUSE_ID>180;

Execution Plan
----------------------------------------------------------
Plan hash value: 3585052864

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   821 | 22167 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WAREHOUSES |   821 | 22167 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("WAREHOUSE_ID">180)

SQL> SELECT * FROM WAREHOUSES WHERE LOCATION_ID=7244;

Execution Plan
----------------------------------------------------------
Plan hash value: 3486536519

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| WAREHOUSES      |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | WHS_LOCATION_IX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LOCATION_ID"=7244)

SQL>

################# "INDEX RANGE SCAN DESCENDING" ######################

SQL> SELECT * FROM WAREHOUSES WHERE WAREHOUSE_ID>180 ORDER BY WAREHOUSE_ID DESC;

Execution Plan
----------------------------------------------------------
Plan hash value: 2083944140

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   821 | 22167 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY     |            |   821 | 22167 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| WAREHOUSES |   821 | 22167 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("WAREHOUSE_ID">180)

SQL>

################## "INDEX FULL SCAN" ###############################

SQL> SELECT DEPT_NO, DNAME FROM DEPT ORDER BY DEPT_NO;

Execution Plan
----------------------------------------------------------
Plan hash value: 1726130703

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     7 |    91 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     7 |    91 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | DEPT_PK |     7 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL>

################## INDEX FAST FULL SCAN ##################################

SQL> SELECT WAREHOUSE_ID FROM WAREHOUSES WHERE WAREHOUSE_ID>180;

Execution Plan

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

Plan hash value: 2133609682

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

| Id  | Operation               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT        |               |   821 |  3284 |     2   (0)| 00:00:01 |        |   |       |

|   1 |  PX COORDINATOR         |               |       |       |            |          |        |   |       |

|   2 |   PX SEND QC (RANDOM)   | :TQ10000      |   821 |  3284 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR    |               |   821 |  3284 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |

|*  4 |     INDEX FAST FULL SCAN| WAREHOUSES_PK |   821 |  3284 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |

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

Predicate Information (identified by operation id):

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

   4 - filter("WAREHOUSE_ID">180)

SQL>

PT - SQL Operators and joins

                         SQL Operators: Access Paths and Joins

• Access path: a technique to retrieve rows from a row source
• Full Table Scan 
• Table Access by ROWID 
• Sample Table Scan 
• Index Unique Scan 
• Index Range Scan 
• Index Full Scan 
• Index Fast Full Scan
• Index skip Scan
• Index Join Scan
• Bitmap Index Join Value
• Bitmap Index Range Scan
• Bitmap Merge
• Cluster Scan
• Hash Scan

• JOINS :how the row sources of two steps are joined
• Join Methods: Nested loop, Hash join, Sort merge, Cartesian 
• Join Order: defines which table should be accessed first (outertable or driving table). The other table is the driven-in or inner table. 
• Join Types: InnerJoins, Outer Joins, Semijoins, Antijoins

MS SQL Fill backup and validation check

• 
  
• 
  
• Create Backup database

--Create a test database--

Use master

go

Create database BackupDatabase

go

•  Create table on backup database

use BackupDatabase 

go

--Create table

Create table Products

(ProductID int IDENTITY (1,1) Primary Key,

ProductName varchar (100),

Brand varchar (100))

go

--Insert data into table

insert into Products values ('Bike','Genesis')

insert into Products values ('Hat','Nike')

insert into Products values ('Shoe','Payless')

insert into Products values ('Phone','Apple')

insert into Products values ('Book','Green')

insert into Products values ('Cup','Large')

--View data

select * from Products

• --Take full database backup of backupdatabase

• --Verify that the database backup is valid (not that the data within is valid)

declare @backupSetId as int

select @backupSetId = position 

from msdb..backupset 

where database_name=N'BackupDatabase' 

and backup_set_id=(select max(backup_set_id) 

from msdb..backupset where database_name=N'BackupDatabase' )

if @backupSetId is null 

begin 

raiserror(N'Verify failed. Backup information for database ''BackupDatabase'' not found.', 16, 1) 

end

RESTORE VERIFYONLY 

FROM  

DISK = N'c:\fullbackups\BackupDatabase.bak' 

WITH  FILE = @backupSetId

GO

• 
  

Use msdb

go

SELECT logical_name,physical_name,file_number,backup_size,file_type, * 

FROM dbo.backupfile

ORDER BY 1  -- Contains one row for each data or log file that is backed up 

--SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set 

--SELECT * FROM dbo.backupset  -- Contains a row for each backup set 

--SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup 

• --SELECT * FROM dbo.backupmediafamily  

• --Extented backup script wtih options

BACKUP DATABASE [smrmindev] 

TO  DISK = N'C:\FullBackups\smrmindev.bak' 

WITH 

--NOFORMAT, 

INIT,   --<< Specifies that all backup sets should be overwritten 

NAME = N'smrmindev-Full Database Backup', 

--SKIP, 

--NOREWIND, 

--NOUNLOAD, 

COMPRESSION,        --<< Compresses the database

STATS = 10, CHECKSUM  --<< Specifies that the backup operation will verify each page for checksum and torn page

GO

• --Verifies that the database backup is valid

declare @backupSetId as int

select @backupSetId = position from msdb..backupset

 where database_name=N'smrmindev' and backup_set_id=(select max(backup_set_id) 

 from msdb..backupset 

 where database_name=N'smrmindev' )

if @backupSetId is null 

begin 

raiserror(N'Verify failed. Backup information for database ''smrmindev'' not found.', 16, 1) 

end

RESTORE VERIFYONLY 

FROM  DISK = N'C:\FullBackups\smrmindev.bak' 

WITH  FILE = @backupSetId,  

NOUNLOAD,  

NOREWIND

GO

MSSQL - Deattach and attach manually by commands

• sp_helpdb smrtest

• --Set the database to a single user mode from multiuser mode

USE [master]

GO

ALTER DATABASE [smrtest] 

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

• 
  

--Detach the database using the sprocs

• 
  

USE [master]
GO
CREATE DATABASE [smrtest1] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\smrtest.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\smrtest_log.ldf' )
 FOR ATTACH --<<use for attach to attach the sales database
GO

MSSQL - Detach and Attach database using GUI

1



2



3



4



5



6





############ ATTACH #######################

1



2



3



4

MSSQL - Change tempdb mdf and ldf file location

sp_helpdb tempdb




USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\tempdb_mssql_8.ndf');
GO
USE master;
GO

###########################ALSO  ##########################################

We cant change recovery mode from simple to full in temp db





USE [master]
GO
ALTER DATABASE [tempdb] SET RECOVERY FULL
GO

--Sizing the data and log files and autogrowth property

USE [master]

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 20480KB )--<< pre sizing data, auto growth

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'templog', SIZE = 20480KB , FILEGROWTH = 20480KB )--<< pre sizing log, auto growth

GO

expdp Master Table error ORA-31633:

ERROR :

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.FULL_EXPDP_SMRTESTENV"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object

oracle@s08:~> sh /u02/scripts/expdp_SMRTESTENV_full.sh

Export: Release 12.1.0.2.0 - Production on Mon Mar 23 11:48:20 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.FULL_EXPDP_SMRTESTENV"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;  2    3    4

OWNER_NAME                               JOB_NAME                                 OPERATION                      JOB_MODE             STATE                          ATTACHED_SESSIONS
---------------------------------------- ---------------------------------------- ------------------------------ -------------------- ------------------------------ -----------------
SYS                                      FULL_EXPDP_SMRTESTENV                    EXPORT                         FULL                 NOT RUNNING                           0



SQL> drop table sys.FULL_EXPDP_SMRTESTENV;

Table dropped.

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;  2    3    4

no rows selected

SQL>


oracle@s08:/u01/app/oracle/diag/rdbms/smrtestenv/SMRTESTENV/trace> sh /u02/scripts/expdp_SMRTESTENV_full.sh

Export: Release 12.1.0.2.0 - Production on Mon Mar 23 12:09:13 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."FULL_EXPDP_SMRTESTENV":  "sys/********@SMRTESTENV AS SYSDBA" dumpfile=expdpSMRTESTENV-2020-03-23_12-09-13.dmp directory=EXP_SMRTESTENV logfile=expdpSMRTESTENV-2020-03-23_12-09-13.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_SMRTESTENV parallel=2
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.858 GB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Database installation using DBCA Silent installation with custom Template on 12C

oracle@s8:/u01/app/oracle/product/12.1.0/dbhome_1/bin> ./dbca -silent -createDatabase -templateName Teamcenter_Oracle.dbt -gdbName SMRTESTENV -sid SMRTESTENV -sysPassword system123 -systemPassword system123 -emConfiguration NONE -characterSet AL32UTF8
Creating and starting Oracle instance
2% complete
3% complete
4% complete
11% complete
Creating database files
12% complete
13% complete
14% complete
22% complete
Creating data dictionary views
25% complete
32% complete
33% complete
34% complete
35% complete
36% complete
37% complete
38% complete
39% complete
40% complete
41% complete
42% complete
43% complete
44% complete
45% complete
52% complete
55% complete
56% complete
58% complete
Completing Database Creation
62% complete
65% complete
68% complete
69% complete
70% complete
80% complete
90% complete
92% complete
Running Custom Scripts
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SMRTESTENV/SMRTESTE.log" for further details.
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/SMRTESTENV.
Database Information:
Global Database Name:SMRTESTENV
System Identifier(SID):SMRTESTENV

oracle@sgdcpl08:/u01/app/oracle/product/12.1.0/dbhome_1/bin>

Check Template Teamcenter_Oracle.dbt on link

Drop Oracle database Manually

oracle@8:~> . oraenv

ORACLE_SID = [] ? zabbixdr

The Oracle base remains unchanged with value /u01/app/oracle

oracle@8:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 19 08:49:16 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2932632 bytes

Variable Size             536871016 bytes

Database Buffers         3741319168 bytes

Redo Buffers               13844480 bytes

Database mounted.

Database opened.

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRKORID  zabbixdr        zabbixdr                      READ WRITE           PRIMARY          NO                 0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2932632 bytes

Variable Size             536871016 bytes

Database Buffers         3741319168 bytes

Redo Buffers               13844480 bytes

Database mounted.

SQL> !cat d.sql

set lines 200

select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance;

SQL>

SQL>  @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRKORID  zabbixdr        zabbixdr                      MOUNTED              PRIMARY          NO                 0

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

alert.log file

drop database

Mon Mar 08 16:55:36 2021

Deleted file /u01/app/oracle/oradata/zabbixdr/system01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/sysaux01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/undotbs01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdbseed/system01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/users01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdbseed/sysaux01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdb1/system01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdb1/sysaux01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdb1/pdb1_users01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/redo01.log

Deleted file /u01/app/oracle/oradata/zabbixdr/redo02.log

Deleted file /u01/app/oracle/oradata/zabbixdr/redo03.log

Deleted file /u01/app/oracle/oradata/zabbixdr/redo04.log

Deleted file /u01/app/oracle/oradata/zabbixdr/redo05.log

Deleted file /u01/app/oracle/oradata/zabbixdr/redo06.log

Deleted file /u01/app/oracle/oradata/zabbixdr/redo07.log

Deleted file /u01/app/oracle/oradata/zabbixdr/temp01.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdbseed/pdbseed_temp012021-02-16_05-38-06-PM.dbf

Deleted file /u01/app/oracle/oradata/zabbixdr/pdb1/temp012021-02-16_05-38-06-PM.dbf

Stopping background process RVWR

Starting background process RSM0

Mon Mar 08 16:55:36 2021

RSM0 started with pid=29, OS id=4553

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2t9qj0p_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2t9qncv_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2tly5jo_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2vh1307_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2w44w63_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2z99v3c_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2z9bsf4_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2z9clx4_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j2zt0wq6_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j31xrxfl_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j33knsy7_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j34oh724_.flb

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ZABBIXDR/flashback/o1_mf_j35ozjhl_.flb

Flashback Database Disabled

Deleted file /u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_zabbixdr.f

Shutting down archive processes

Archiving is disabled

Mon Mar 08 16:55:37 2021

ARCH shutting down

ARC3: Archival stopped

Mon Mar 08 16:55:37 2021

ARCH shutting down

ARC2: Archival stopped

Mon Mar 08 16:55:37 2021

ARCH shutting down

ARC1: Archival stopped

Mon Mar 08 16:55:37 2021

ARCH shutting down

ARC0: Archival stopped

ALTER DATABASE MOUNT

ORA-750 signalled during: ALTER DATABASE MOUNT...

------------------- Same as primary single instance database  ------------------------------

oracle@test08:~> . oraenv

ORACLE_SID = [oracle] ? NEWSMRUPGR10

The Oracle base remains unchanged with value /u01/app/oracle

oracle@test08:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 24 08:29:13 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size            1426065528 bytes

Database Buffers          704643072 bytes

Redo Buffers               13062144 bytes

Database mounted.

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

NEWSMRUP  NEWSMRUPGR10     NEWSMRUPGR10                   MOUNTED              PRIMARY          NO                 0

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

alert.log

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Tue Aug 24 08:30:56 2021

drop database

Tue Aug 24 08:30:56 2021

Deleted file /u04/db_backup/Flash_recovery_area/NEWSMRUPGR10/datafile1/u01/app/oracle/oradata/NEWSMRUPGR10/system01.dbf

Deleted file /u04/db_backup/Flash_recovery_area/NEWSMRUPGR10/datafile1/u01/app/oracle/oradata/NEWSMRUPGR10/sysaux01.dbf

Deleted file /u04/db_backup/Flash_recovery_area/NEWSMRUPGR10/datafile1/u01/app/oracle/oradata/NEWSMRUPGR10/undotbs01.dbf

Deleted file /u04/db_backup/Flash_recovery_area/NEWSMRUPGR10/datafile1/u01/app/oracle/oradata/NEWSMRUPGR10/idata01.dbf

Deleted file /u04/db_backup/Flash_recovery_area/NEWSMRUPGR10/datafile1/u01/app/oracle/oradata/NEWSMRUPGR10/ilog01.dbf

Deleted file /u04/db_backup/Flash_recovery_area/NEWSMRUPGR10/datafile1/u01/app/oracle/oradata/NEWSMRUPGR10/indx01.dbf

Deleted file /u01/app/oracle/oradata/NEWSMRUPGR10/redo01.log

Deleted file /u01/app/oracle/oradata/NEWSMRUPGR10/redo02.log

Deleted file /u01/app/oracle/oradata/NEWSMRUPGR10/redo03.log

Deleted file /u01/app/oracle/oradata/NEWSMRUPGR10/temp01.dbf

Deleted file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_NEWSMRUPGR10.f

Shutting down archive processes

Archiving is disabled

database and os level scripts

script - Mount point details on mail with attachements

window bat scripts

Datababase alert on mail with html output format

Example Script to delete old dump files or archive log based on time and minutes

Crontab - Check database Instance status on mail

Crontab - Stats gathering schema status on mail

Crontab - Oracle Database Health Check report on email

Crontab - Tablespace threshold Alert om mail example

Crontab - Daily Tablespace Check output on mail

Crontab - Physical standby Archive sync details on mail

Crontab -Daily Sar output Example in mail alert system

Oracle Database Crontab script on linux system and Email Alert on mail

LDAPBackup scripts

Example Script to delete old dump files or archive log based on time and minutes

 ---------------  steps for removing old alert log files ---------------


1. Go to alert log file directory
 cd /u01/app/oracle/diag/rdbms/smrprodb_dr/PRODB/trace

mkdir /u01/app/oracle/diag/rdbms/smrprodb_dr/PRODB/trace/alert_backup_logs

2. create housekeeping_alert.sh files

cd /u04/DB/scripts/

vi housekeeping_alert.sh

oracle@s1:/u04/DB/scripts> cat housekeeping_alert.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin

#location of alert log files
cd /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace
#Moving log files
mv /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_RODB*.log /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_backup_logs

#Granting permissions for Dump files
chmod -R 775 /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_backup_logs/

#Compressing files

gzip /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_backup_logs/*.log*

#Removing 60 days old dump files
find /u01/app/oracle/diag/rdbms/smrprodb_dr/SMRPRODB/trace/alert_backup_logs/ -mtime +60 -exec rm {} \;

#Removing 1440 minute old archive log files ####################

#find /u01/app/oracle/fast_recovery_area/RAC/archivelog/*.arc -mmin +1440 -exec rm {} \;

3.  chmod 777 housekeeping_alert.sh

oracle@s:/u04/MDB/scripts>

4. schedule it on crontab it will run on every month (next at 2017-10-01 00:00:00)

crontab -e

0 0 1 * *  /u04/MDB/scripts/housekeeping_alert.sh

$$$$$$$$$$$$$$ Archive log delete output Example ###########################

find /u01/app/oracle/fast_recovery_area/RAC/archivelog/*.arc -mmin +1440 -exec rm {} \;

oracle@s08:~> cd /u01/app/oracle/fast_recovery_area/RAC/archivelog/

oracle@s08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> ls -ltr

total 4382064

-rw-r----- 1 oracle oinstall     70144 Mar 12 07:18 arch_2_843_1020157972.arc

-rw-r----- 1 oracle oinstall 247096320 Mar 12 07:18 arch_1_1190_1020157972.arc

-rw-r----- 1 oracle oinstall 244623360 Mar 12 07:18 arch_1_1191_1020157972.arc

-rw-r----- 1 oracle oinstall 244738048 Mar 12 07:18 arch_1_1192_1020157972.arc

-rw-r----- 1 oracle oinstall 176008704 Mar 12 07:18 arch_2_844_1020157972.arc

-rw-r----- 1 oracle oinstall 245496320 Mar 12 07:32 arch_1_1193_1020157972.arc

-rw-r----- 1 oracle oinstall 244393472 Mar 12 07:32 arch_1_1194_1020157972.arc

-rw-r----- 1 oracle oinstall 244472320 Mar 12 07:32 arch_1_1195_1020157972.arc

-rw-r----- 1 oracle oinstall  84999680 Mar 12 07:32 arch_2_845_1020157972.arc

-rw-r----- 1 oracle oinstall 244384256 Mar 12 07:32 arch_1_1196_1020157972.arc

-rw-r----- 1 oracle oinstall  27237376 Mar 12 07:32 arch_2_846_1020157972.arc

-rw-r----- 1 oracle oinstall 255912448 Mar 12 07:52 arch_1_1197_1020157972.arc

-rw-r----- 1 oracle oinstall  72956928 Mar 12 07:52 arch_1_1198_1020157972.arc

-rw-r----- 1 oracle oinstall     45056 Mar 12 07:52 arch_1_1199_1020157972.arc

-rw-r----- 1 oracle oinstall     96256 Mar 12 07:52 arch_1_1200_1020157972.arc

-rw-r----- 1 oracle oinstall 175212544 Mar 12 07:52 arch_2_847_1020157972.arc

-rw-r----- 1 oracle oinstall     13312 Mar 12 07:52 arch_2_849_1020157972.arc

-rw-r----- 1 oracle oinstall     45568 Mar 12 07:52 arch_2_848_1020157972.arc

-rw-r----- 1 oracle oinstall 244402688 Mar 13 07:51 arch_1_1201_1020157972.arc

-rw-r----- 1 oracle oinstall 244753920 Mar 13 07:51 arch_1_1202_1020157972.arc

-rw-r----- 1 oracle oinstall 244624384 Mar 13 07:51 arch_1_1203_1020157972.arc

-rw-r----- 1 oracle oinstall 177154560 Mar 13 07:52 arch_2_850_1020157972.arc

-rw-r----- 1 oracle oinstall 244860928 Mar 13 07:52 arch_1_1204_1020157972.arc

-rw-r----- 1 oracle oinstall 245518848 Mar 13 07:52 arch_1_1205_1020157972.arc

-rw-r----- 1 oracle oinstall 246188032 Mar 13 07:52 arch_1_1206_1020157972.arc

-rw-r----- 1 oracle oinstall  87497216 Mar 13 07:52 arch_2_851_1020157972.arc

-rw-r----- 1 oracle oinstall 244379648 Mar 13 07:52 arch_1_1207_1020157972.arc

oracle@sgdcpl08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> find /u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/*.arc -mtime +1 -exec rm {} \;

oracle@sgdcpl08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> ll

total 4382064

-rw-r----- 1 oracle oinstall 247096320 Mar 12 07:18 arch_1_1190_1020157972.arc

-rw-r----- 1 oracle oinstall 244623360 Mar 12 07:18 arch_1_1191_1020157972.arc

-rw-r----- 1 oracle oinstall 244738048 Mar 12 07:18 arch_1_1192_1020157972.arc

-rw-r----- 1 oracle oinstall 245496320 Mar 12 07:32 arch_1_1193_1020157972.arc

-rw-r----- 1 oracle oinstall 244393472 Mar 12 07:32 arch_1_1194_1020157972.arc

-rw-r----- 1 oracle oinstall 244472320 Mar 12 07:32 arch_1_1195_1020157972.arc

-rw-r----- 1 oracle oinstall 244384256 Mar 12 07:32 arch_1_1196_1020157972.arc

-rw-r----- 1 oracle oinstall 255912448 Mar 12 07:52 arch_1_1197_1020157972.arc

-rw-r----- 1 oracle oinstall  72956928 Mar 12 07:52 arch_1_1198_1020157972.arc

-rw-r----- 1 oracle oinstall     45056 Mar 12 07:52 arch_1_1199_1020157972.arc

-rw-r----- 1 oracle oinstall     96256 Mar 12 07:52 arch_1_1200_1020157972.arc

-rw-r----- 1 oracle oinstall 244402688 Mar 13 07:51 arch_1_1201_1020157972.arc

-rw-r----- 1 oracle oinstall 244753920 Mar 13 07:51 arch_1_1202_1020157972.arc

-rw-r----- 1 oracle oinstall 244624384 Mar 13 07:51 arch_1_1203_1020157972.arc

-rw-r----- 1 oracle oinstall 244860928 Mar 13 07:52 arch_1_1204_1020157972.arc

-rw-r----- 1 oracle oinstall 245518848 Mar 13 07:52 arch_1_1205_1020157972.arc

-rw-r----- 1 oracle oinstall 246188032 Mar 13 07:52 arch_1_1206_1020157972.arc

-rw-r----- 1 oracle oinstall 244379648 Mar 13 07:52 arch_1_1207_1020157972.arc

-rw-r----- 1 oracle oinstall     70144 Mar 12 07:18 arch_2_843_1020157972.arc

-rw-r----- 1 oracle oinstall 176008704 Mar 12 07:18 arch_2_844_1020157972.arc

-rw-r----- 1 oracle oinstall  84999680 Mar 12 07:32 arch_2_845_1020157972.arc

-rw-r----- 1 oracle oinstall  27237376 Mar 12 07:32 arch_2_846_1020157972.arc

-rw-r----- 1 oracle oinstall 175212544 Mar 12 07:52 arch_2_847_1020157972.arc

-rw-r----- 1 oracle oinstall     45568 Mar 12 07:52 arch_2_848_1020157972.arc

-rw-r----- 1 oracle oinstall     13312 Mar 12 07:52 arch_2_849_1020157972.arc

-rw-r----- 1 oracle oinstall 177154560 Mar 13 07:52 arch_2_850_1020157972.arc

-rw-r----- 1 oracle oinstall  87497216 Mar 13 07:52 arch_2_851_1020157972.arc

oracle@s08:/u01/app/oracle/fast_recovery_area/RAC/archivelog> find /u01/app/oracle/fast_recovery_area/RAC/archivelog/*.arc -mmin +1440

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1190_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1191_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1192_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1193_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1194_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1195_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1196_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1197_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1198_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1199_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_1_1200_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_843_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_844_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_845_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_846_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_847_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_848_1020157972.arc

/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/arch_2_849_1020157972.arc

oracle@sgdcpl08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> find /u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/*.arc -mmin +1440 -exec rm {} \;

oracle@s08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> ls -ltr

total 1933004

-rw-r----- 1 oracle oinstall 244402688 Mar 13 07:51 arch_1_1201_1020157972.arc

-rw-r----- 1 oracle oinstall 244753920 Mar 13 07:51 arch_1_1202_1020157972.arc

-rw-r----- 1 oracle oinstall 244624384 Mar 13 07:51 arch_1_1203_1020157972.arc

-rw-r----- 1 oracle oinstall 177154560 Mar 13 07:52 arch_2_850_1020157972.arc

-rw-r----- 1 oracle oinstall 244860928 Mar 13 07:52 arch_1_1204_1020157972.arc

-rw-r----- 1 oracle oinstall 245518848 Mar 13 07:52 arch_1_1205_1020157972.arc

-rw-r----- 1 oracle oinstall 246188032 Mar 13 07:52 arch_1_1206_1020157972.arc

-rw-r----- 1 oracle oinstall  87497216 Mar 13 07:52 arch_2_851_1020157972.arc

-rw-r----- 1 oracle oinstall 244379648 Mar 13 07:52 arch_1_1207_1020157972.arc

oracle@08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog>

Database Creation 12.1.0.1.0 and above using template Script

Database Creation 12.1.0.1.0 using template Script and above


There are 3 basic scripts to create database using dbca

Please find details below list

Copy and paste below script  on location :
u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates

dbca




Select Templete

1. Template_Oracle.dbt
2. tc_create_user_ilog.sql
3. create_user.sql

#####################     Template_Oracle.dbt              ####################


<DatabaseTemplate name="tc" description="" version="12.1.0.1.0">

   <CommonAttributes>

      <option name="ISEARCH" value="false"/>

      <option name="OMS" value="false"/>

      <option name="JSERVER" value="false"/>

      <option name="SPATIAL" value="false"/>

      <option name="ODM" value="false">

         <tablespace id="SYSAUX"/>

      </option>

      <option name="IMEDIA" value="false"/>

      <option name="XDB_PROTOCOLS" value="false">

         <tablespace id="SYSAUX"/>

      </option>

      <option name="ORACLE_TEXT" value="false">

         <tablespace id="SYSAUX"/>

      </option>

      <option name="SAMPLE_SCHEMA" value="false"/>

      <option name="CWMLITE" value="false">

         <tablespace id="SYSAUX"/>

      </option>

      <option name="EM_REPOSITORY" value="true">

         <tablespace id="SYSAUX"/>

      </option>

   </CommonAttributes>

   <Variables/>

   <CustomScripts Execute="true">

      <CustomScript script="{ORACLE_HOME}/assistants/dbca/templates/tc_create_user_ilog.sql"/>

   </CustomScripts>

   <InitParamAttributes>

      <InitParams>

         <initParam name="control_files" value="(&quot;{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl&quot;, &quot;{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control02.ctl&quot;, &quot;{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control03.ctl&quot;)"/>

         <initParam name="db_domain" value=""/>

         <initParam name="db_file_multiblock_read_count" value="32"/>

         <initParam name="db_name" value="tc"/>

         <initParam name="instance_name" value="tc"/>

         <initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/flash_recovery_area"/>

         <initParam name="db_recovery_file_dest_size" value="4096" unit="MB"/>

         <initParam name="open_cursors" value="300"/>

         <initParam name="pga_aggregate_target" value="500" unit="MB"/>

         <initParam name="processes" value="150"/>

         <initParam name="undo_tablespace" value="UNDOTBS1"/>

         <initParam name="query_rewrite_integrity" value="TRUSTED"/>

         <initParam name="sga_max_size" value="2048" unit="MB"/>

         <initParam name="sga_target" value="2048" unit="MB"/>

         <initParam name="optimizer_index_caching" value="95"/>

         <initParam name="optimizer_index_cost_adj" value="10"/>

      </InitParams>

      <MiscParams>

         <databaseType>MULTIPURPOSE</databaseType>

         <maxUserConn>20</maxUserConn>

         <percentageMemTOSGA>40</percentageMemTOSGA>

         <customSGA>true</customSGA>

         <archiveLogMode>false</archiveLogMode>

         <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>

      </MiscParams>

      <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>

   </InitParamAttributes>

   <StorageAttributes>

      <ControlfileAttributes id="Controlfile">

         <maxDatafiles>100</maxDatafiles>

         <maxLogfiles>16</maxLogfiles>

         <maxLogMembers>3</maxLogMembers>

         <maxLogHistory>1</maxLogHistory>

         <maxInstances>8</maxInstances>

         <image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>

         <image name="control02.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>

         <image name="control03.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>

      </ControlfileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/idata01.dbf">

         <tablespace>IDATA</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">500</size>

         <reuse>false</reuse>

         <autoExtend>true</autoExtend>

         <increment unit="KB">10240</increment>

         <maxSize unit="KB">-1</maxSize>

      </DatafileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/ilog01.dbf">

         <tablespace>ILOG</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">50</size>

         <reuse>false</reuse>

         <autoExtend>true</autoExtend>

         <increment unit="KB">10240</increment>

         <maxSize unit="KB">-1</maxSize>

      </DatafileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/indx01.dbf">

         <tablespace>INDX</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">50</size>

         <reuse>false</reuse>

         <autoExtend>true</autoExtend>

         <increment unit="KB">10240</increment>

         <maxSize unit="KB">-1</maxSize>

      </DatafileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf">

         <tablespace>SYSAUX</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">120</size>

         <reuse>true</reuse>

         <autoExtend>true</autoExtend>

         <increment unit="KB">10240</increment>

         <maxSize unit="MB">-1</maxSize>

      </DatafileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf">

         <tablespace>SYSTEM</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">400</size>

         <reuse>true</reuse>

         <autoExtend>true</autoExtend>

         <increment unit="KB">10240</increment>

         <maxSize unit="MB">-1</maxSize>

      </DatafileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf">

         <tablespace>TEMP</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">50</size>

         <reuse>true</reuse>

         <autoExtend>false</autoExtend>

         <increment unit="KB">640</increment>

         <maxSize unit="MB">-1</maxSize>

      </DatafileAttributes>

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf">

         <tablespace>UNDOTBS1</tablespace>

         <temporary>false</temporary>

         <online>true</online>

         <status>0</status>

         <size unit="MB">500</size>

         <reuse>true</reuse>

         <autoExtend>true</autoExtend>

         <increment unit="KB">10240</increment>

         <maxSize unit="MB">-1</maxSize>

      </DatafileAttributes>

      <TablespaceAttributes id="IDATA">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>false</temporary>

         <defaultTemp>false</defaultTemp>

         <undo>false</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>1</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">-1</initSize>

         <increment unit="KB">-1</increment>

         <incrementPercent>1</incrementPercent>

         <minExtends>-1</minExtends>

         <maxExtends>-2</maxExtends>

         <minExtendsSize unit="KB">-1</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/idata01.dbf">

               <id>1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <TablespaceAttributes id="ILOG">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>false</temporary>

         <defaultTemp>false</defaultTemp>

         <undo>false</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>1</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">-1</initSize>

         <increment unit="KB">-1</increment>

         <incrementPercent>1</incrementPercent>

         <minExtends>-1</minExtends>

         <maxExtends>-2</maxExtends>

         <minExtendsSize unit="KB">-1</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/ilog01.dbf">

               <id>1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <TablespaceAttributes id="INDX">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>false</temporary>

         <defaultTemp>false</defaultTemp>

         <undo>false</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>1</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">-1</initSize>

         <increment unit="KB">-1</increment>

         <incrementPercent>1</incrementPercent>

         <minExtends>-1</minExtends>

         <maxExtends>-2</maxExtends>

         <minExtendsSize unit="KB">-1</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/indx01.dbf">

               <id>1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <TablespaceAttributes id="SYSAUX">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>false</temporary>

         <defaultTemp>false</defaultTemp>

         <undo>false</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>1</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">64</initSize>

         <increment unit="KB">64</increment>

         <incrementPercent>50</incrementPercent>

         <minExtends>1</minExtends>

         <maxExtends>4096</maxExtends>

         <minExtendsSize unit="KB">64</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf">

               <id>-1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <TablespaceAttributes id="SYSTEM">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>false</temporary>

         <defaultTemp>false</defaultTemp>

         <undo>false</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>3</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">64</initSize>

         <increment unit="KB">64</increment>

         <incrementPercent>50</incrementPercent>

         <minExtends>1</minExtends>

         <maxExtends>-1</maxExtends>

         <minExtendsSize unit="KB">64</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf">

               <id>-1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <TablespaceAttributes id="TEMP">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>true</temporary>

         <defaultTemp>true</defaultTemp>

         <undo>false</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>1</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">64</initSize>

         <increment unit="KB">64</increment>

         <incrementPercent>0</incrementPercent>

         <minExtends>1</minExtends>

         <maxExtends>0</maxExtends>

         <minExtendsSize unit="KB">64</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf">

               <id>-1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <TablespaceAttributes id="UNDOTBS1">

         <online>true</online>

         <offlineMode>1</offlineMode>

         <readOnly>false</readOnly>

         <temporary>false</temporary>

         <defaultTemp>false</defaultTemp>

         <undo>true</undo>

         <local>true</local>

         <blockSize>-1</blockSize>

         <allocation>1</allocation>

         <uniAllocSize unit="KB">-1</uniAllocSize>

         <initSize unit="KB">512</initSize>

         <increment unit="KB">512</increment>

         <incrementPercent>50</incrementPercent>

         <minExtends>8</minExtends>

         <maxExtends>4096</maxExtends>

         <minExtendsSize unit="KB">512</minExtendsSize>

         <logging>true</logging>

         <recoverable>false</recoverable>

         <maxFreeSpace>0</maxFreeSpace>

         <autoSegmentMgmt>true</autoSegmentMgmt>

         <datafilesList>

            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf">

               <id>-1</id>

            </TablespaceDatafileAttributes>

         </datafilesList>

      </TablespaceAttributes>

      <RedoLogGroupAttributes id="1">

         <reuse>false</reuse>

         <fileSize unit="KB">102400</fileSize>

         <Thread>1</Thread>

         <member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>

      </RedoLogGroupAttributes>

      <RedoLogGroupAttributes id="2">

         <reuse>false</reuse>

         <fileSize unit="KB">102400</fileSize>

         <Thread>1</Thread>

         <member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>

      </RedoLogGroupAttributes>

      <RedoLogGroupAttributes id="3">

         <reuse>false</reuse>

         <fileSize unit="KB">102400</fileSize>

         <Thread>1</Thread>

         <member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>

      </RedoLogGroupAttributes>

   </StorageAttributes>

</DatabaseTemplate>

#################          tc_create_user_ilog.sql               ##################

REM EDS PLM
REM
REM File: tc_create_user_ilog.sql
REM
REM ****************************************************************************
REM Revision    History:
REM Date        Author         Comment
REM =========   ======         =================================================

REM
REM ****************************************************************************

REM This script:
REM 1) creates the Tc test user account
REM 2) creates the Tc test logging tables/indexes

spool tc_create_user_ilog.lst

connect / as sysdba

REM create the Tc test account
@@create_user.sql


#################           create_user.sql                 #######################

REM EDS PLM
REM
REM File: create_user.sql
REM
REM ****************************************************************************
REM Revision    History:
REM Date        Author         Comment
REM =========   ======         =================================================
REM 12-Mar-03      Initial.
REM 14-Jul-05        Remove dba privilege;only grant those required.
REM 11-Jan-06       Give test imp_full_database role.
REM
REM ****************************************************************************

REM This script creates the TcEng test account.
REM This script expects 2 existing tablespaces: "idata" and "temp".

prompt Creating test account and granting privileges.
grant Connect, Create table, Create procedure, Create view, Select_catalog_role, alter session to test identified by test;


prompt Setting default tablespaces for the test account.
alter user test default tablespace idata temporary tablespace temp;
ALTER USER "TEST" QUOTA UNLIMITED ON "IDATA" QUOTA UNLIMITED ON "ILOG" QUOTA UNLIMITED ON "INDX";


spool off

exit