SIE - Departamento de Educación de Puerto Rico
Puerto Rico
Department of Education
[pic]
Calculate Database Size of PMAX1O
Script to Calculate the Database Size of PMAX1O
28/NOV/2012
|Calculate Database Size of PMAX1O Database Script |
| | |
|Project |Calculate Database Size of PMAX1O |
Version Information
|Version number |1.0.0 |
|Draft/Final as of |28/November/2012 |
|Printed on |29 November 2012 |
|Author |Ricardo Cortés Huertas |
| |rcortes@ |
|Owner |Vicente Graulau Rosario |
|Last Update By |Ricardo Cortés Huertas |
Document Information
Document source
This document is maintained as an online document. Contact the author for the latest version.
Revision history
|Version Number |Date |Summary of Changes |Revision Marks |
|1.0 |28/Nov/2012 |First version |YES |
| | | | |
Reviews
This document has been reviewed internally by the following people.
|Name |Date of Review |
|Vicente Graulau Rosario/PRDE |28/Nov/2012 |
|Ricardo Cortés Huertas/Intelutions | |
Approvals
This document has been approved by the following people. The signed approval forms are filed in the Project Control Book.
| |Function |Date of Approval |Signature |
| | | | |
| | | | |
| | | | |
| | | | |
|Name | | | |
| | | | |
Distribution
This document has been distributed to:
|Name |Function |
|Vicente Graulau Rosario |PRDE SIE Administrator |
|Ricardo Cortés Huertas |Intellutions Consultant |
| |Sharepoint site |
| | |
Table of Contents
Page
1 Introduction 4
1.1 Objectives 4
2 Export Script Section 4
2.1 SCRIPT calculate_database_size.sh 5
2.2 SCRIPT calculate_database_size.sql 5
2.3 Log files generated during the process. 6
3 Table Creation Script Section 7
3.1 SCRIPT for DATABASE_SIZE_STATISTICS Creation. 7
Calculate Database Size of PMAX1O Script
Introduction
The process of Caculation of the Database Size was created in order to keep a history in the table PRDE_STAT.DATABASE_SIZE_STATISTICS for future statistics purposes. It keeps the sizes at the moment the process is executed.
The purpose of the Calculate Database Size of PMAX1O Database Script Project is to provide a script for the calculation of the databaser size of the SIE (Sistema de Información Estudiantil) to be maintained in a table for future statistics.
1 Objectives
The objective of this document is to provide a script documentation to be used in order calculate the database size of the SIE system to be saved into a table. The purpose is to have the database sizes in a table for future statistics use.
The process is already mounted in a LINUX operating system crontab scheduler. The frequency established is every Sunday at 7:30 a.m.
Script Section
The entry for the crontab scheduler in the operating system for the process of Calculate Database Size is:
30 07 * * 7 /DE-PR-CUSTOMER/calculate_database_size.sh >> /DE-PR-CUSTOMER/logs/calculate_database_size.log
The following section describe how the process is executed. A description of how to execute the process manually is explained. Is necessary to connect to the server auohsmaxi76 with the ip 140.85.100.111. That server has the script necessary for the process. Once is connected into the corresponding server the process can be proceed. Is a requirement to be in the directory /DE-PR-CUSTOMER in the server.
A log file is generated during the process. To execute the process is needed to move to the path /DE-PR-CUSTOMER in the server specified above and to change the user to orpmax1o. The entries should be:
A. Enter the user and password for the operating system to the server 140.85.,100.111 using SSH Secure Shell or Putty tools.
B. Switch to the user orpmax1o and enter the following command in the command prompt:
PMAX1O1@auohsmaxi76)/home/c_rcorte>
pbrun orpmax1o
C. Change to the directotory or path DE-PR-CUSTOMER and enter the following command in the command prompt:
(PMAX1O1@auohsmaxi76)/pmax1o/oracle
cd /DE-PR-CUSTOMER
D. Execute the shell script, enter the following command:
./calculate_database_size.sh >> /DE-PR-CUSTOMER/logs/calculate_database_size.log and click enter.
E. Once the process finished, the prompt appears again.
(PMAX1O1@auohsmaxi76)/DE-PR-CUSTOMER>
F. The log file generated is found in the path /DE-PR-CUSTOMER/logs with the name calculate_database_size.log. The entries generated are added to the log files in order to have a history of every process executed.
1 SCRIPT calculate_database_size.sh
#
# Creado por Ricardo Cortes Huertas
#08/20/2012
#!/bin/bash
export ORACLE_HOME=/pmax1o/oracle/product/112
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=PMAX1O
export SCHEMA_NAME=PROD
datef=`date +%d%b%G`
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/calculate_database_size.sql
echo "---Done !!!";
2 SCRIPT calculate_database_size.sql
INSERT INTO PRDE_STAT.database_size_statistics SELECT sysdate, ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) "Database Size GB"
, ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) -
ROUND(FREE.P / 1024 / 1024 / 1024) "Used space GB"
, ROUND(FREE.P / 1024 / 1024 / 1024) "Free space GB"
FROM (SELECT BYTES
FROM V$DATAFILE
UNION ALL
SELECT BYTES
FROM V$TEMPFILE
UNION ALL
SELECT BYTES
FROM V$LOG) USED
, (SELECT SUM(BYTES) AS P
FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
commit;
exit;
~
3 Log files generated during the process.
The following section shows the entries generated in the log file /DE-PR-CUSTOMER/logs/ calculate_database_size.log during the process:
SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 25 07:30:01 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
1 row created.
Commit complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
---Done !!!
Table Creation Script Section
As part of the process a table is updated during the execution. The table is under the schema PRDE_STAT that was created for statistics purposes. Is necessary to connecto to the schema PRDE_STAT for the creation of the table at the moment of the execution of the script. The table is already creation and necessary for the process.
1 SCRIPT for DATABASE_SIZE_STATISTICS Creation.
CREATE TABLE DATABASE_SIZE_STATISTICS
(
DATE_STATISTICS DATE,
DATABASE_SIZE_STAT NUMBER(10),
DATABASE_USED NUMBER(10),
DATABASE_FREE NUMBER(10)
)
TABLESPACE TBL_PRDE_STAT
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
GRANT DELETE, INSERT, SELECT, UPDATE ON DATABASE_SIZE_STATISTICS TO LADBA;
GRANT SELECT ON DATABASE_SIZE_STATISTICS TO PROD;
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- departamento de policia federal
- departamento de justicia pr
- departamento de policia miami
- departamento de policia puerto rico
- comida tradicional de puerto rico
- banco popular de puerto rico
- banco santander de puerto rico
- gobierno de puerto rico certificados
- gobierno de puerto rico certificaciones
- gofio de puerto rico
- significado escudo de puerto rico
- colegio de medicos de puerto rico