SIE Implementation <Document Title> Puerto Rico Department of Education
Puerto Rico
Department of Education
Refresh Data of TMAX1O Database from Production
Scripts to Refresh Data of TMAX1O Database
17/NOV/2012
2 / Business Consulting ServicesStudent Information System Practice
SIE Implementation Project –Refresh Data of TMAX1O Database -Puerto Rico Department of Education
Refresh Data of TMAX1O Database ScriptsProject / Refresh Data of TMAX1O Database from Production
Version Information
Version number / 1.0.0Draft/Final as of / 17/November/2012
Printed on / 27 November 2012
Author / Ricardo Cortés Huertas
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 / 17/Nov/2012 / First version / YES
Reviews
This document has been reviewed internally by the following people.
Name / Date of Review /Vicente Graulau Rosario/PRDE / 17/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.
Name / Function / Date of Approval / Signature /Distribution
This document has been distributed to:
Name / Function /Vicente Graulau Rosario / PRDE SIE Administrator
Ricardo Cortés Huertas / Intellutions Consultant
http://intraedu/osiatd/sie / Sharepoint site
Table of Contents
Page
1 Introduction 4
1.1 Objectives 4
2 Import Scripts Section 4
2.1 SCRIPTS vg_PRDE_TMAXIO1.sh 6
2.2 SCRIPTS vg_PRDE_TMAXIO3.sh 7
3 Log Files Section 8
3.1 File vg_PRDE_TMAXIO1.log 8
3.2 File vg_PRDE_TMAXIO3.log 13
4 Output File Section 17
4.1 File 06Nov2012.log 17
Refresh Data of TMAX1O Database Scripts
1 Introduction
The TMAX1O database environments were created in order to keep copies of production database for testing. The purpose of the scripts related with the refreshes in the TMAX1O environments is to have copies of the production database for testing.
1.1 Objectives
The objective of this document is to provide scripts documentation to be used in order to do an import of the schemas PROD and PROD_RMAX from the SIE system into test environments. Those schemas are the principals in order to have a copy of the production database. The purpose is to have copies of the production database in a test environment for testing.
The processes involved are already mounted in a LINUX operating system crontab scheduler. The frequency is upon request for each one. Is necessary to modify the crontab in order to establish an specific date and time for the execution depending of which copy is needed
2 Import Scripts Section
There are two scripts involved in order to refresh the test environment. One was created for the tmaxio1 and another for the tmaxio3 environments. The vg_PRDE_TMAXIO1.sh script was created for the TMAX1O1 database, this script is for the refresh of PRDE_TMAXIO1 and PRDE_TMAXIO1_RMAX with the corresponding PROD and PROD_RMAX schemas in the production environment. The vg_PRDE_TMAXIO3.sh script was created for the TMAX1O1 database, this script is for the refresh of PRDE_TMAXIO3 and PRDE_TMAXIO3_RMAX with the corresponding PROD and PROD_RMAX schemas in the production environment.
Is very important and necessary to have an export done from the production environment as a file: /DE-PR-CUSTOMER/prde_prod01.dmp. This is the name of the file that the import processes use for input.
If a schedule is established for a process, is necessary to monitor the job frequently during the execution until is finished to be sure that was successful.
A copy of the entries in the crontab scheduler for each script are the following:
00 08 * * 6 sh /DE-PR-CUSTOMER/vg_PRDE_TMAXIO3.sh > /DE-PR-CUSTOMER/vg_PRDE_TMAXIO3.log
00 08 * * 2 sh /DE-PR-CUSTOMER/vg_PRDE_TMAXIO1.sh > /DE-PR-CUSTOMER/vg_PRDE_TMAXIO1.log
Al of the scripts are commented in the crontab with the symbol # at the beginning of the entry as a comment in order to execute only when are ready.
The following section describes how each process is executed. A description of how to execute the corresponding process manually is explained. The process consists of an import of the schemas PROD and PROD_RMAX from the Schoolmax System into the PRDE_TMAXIO1 and PRDE_TMAXIO2_RMAX or into the PRDE_TMAXIO3 and PRDE_TMAXIO3 schemas, depending on the script been executed. . Is necessary to connect to the server auohsmaxi72 with the ip 140.85.100.100. That server has the scripts necessaries for each 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.
Log files are generated during the process. To execute the process is necessary 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. Is necessary to have the file prde_prod01.dmp under the path /DE-PR-CUSTOMER as an input.
B. Enter the user and password for the operating system to the server 140.85.100.100 using SSH Secure Shell or Putty tools.
C. Switch to the user orpmax1o and enter the following command in the command prompt:
(TMAX1O1@auohsmaxi72) /home/c_rcorte>
pbrun ortmax1o
D. To change to the directotory or path DE-PR-CUSTOMER, enter the following command in the command prompt:
(PMAX1O1@auohsmaxi76)/pmax1o/oracle
cd /DE-PR-CUSTOMER
E. Execute the shell script, enter the following command:
./vg_PRDE_TMAXIO1.sh and click enter or
./vg_PRDE_TMAXIO3.sh and click enter
Depending on the script for the environment to be executed.
F. Once the process finished, the prompt appears again.
(TMAX1O1@auohsmaxi72)/DE-PR-CUSTOMER>
G. The log file generated is found in the path /DE-PR-CUSTOMER with the name vg_PRDE_TMAXIO1.log or vg_PRDE_TMAXIO3.log depending on the process been executed. The entries generated are added to the log files in order to have a history of every process executed.
2.1 SCRIPTS vg_PRDE_TMAXIO1.sh
# SCRIPT FOR THE TMAX1O REFRESH
# 29-SEP-2012
# CREADO POR RICARDO CORTES
#!/bin/bash
export ORACLE_HOME=/tmax1o/oracle/product/112
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=TMAX1O1
export SCHEMA_NAME=PRDE_TMAXIO1
export WRK_DIR="/DE-PR-CUSTOMER"
datef=`date +%d%b%G`
export LOG_NAME="$SCHEMA_NAME_$datef.log"
DMP_FILE="prde_prod01.dmp"
echo "---Begin to drop the schema and import the dump file";
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/recreate_user_tmax1o1.sql PRDE_TMAXIO1 PRDE_TMAXIO1_RMAX "/DE-PR-CUSTOMER"
$ORACLE_HOME/bin/impdp ladba/schoolmax@$ORACLE_SID remap_schema=PROD:PRDE_TMAXIO1 remap_schema=PROD_RMAX:PRDE_TMAXIO1_RMAX directory=PROD_DUMP dumpfile=prde_prod01.dmp logfile=$LOG_NAME exclude=statistics
echo "";
echo "";
echo "---unlock the user account!"
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/unlock_user.sql PRDE_TMAXIO1
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/unlock_user.sql PRDE_TMAXIO1_RMAX
echo "";
echo "";
echo "---sending the email....."
uuencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O1' uencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O1' encode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O1' uencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O1' #uuencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O1' encode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O1'
echo "---Done !!!";
2.2 SCRIPTS vg_PRDE_TMAXIO3.sh
# SCRIPT FOR THE TMAX1O REFRESH
# 27-SEP-2012
# CREADO POR RICARDO CORTES
#!/bin/bash
export ORACLE_HOME=/tmax1o/oracle/product/112
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=TMAX1O1
export SCHEMA_NAME=PRDE_TMAXIO3
export WRK_DIR="/DE-PR-CUSTOMER"
datef=`date +%d%b%G`
export LOG_NAME="$SCHEMA_NAME_$datef.log"
DMP_FILE="prde_prod01.dmp"
echo "---Begin to drop the schema and import the dump file";
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/recreate_user_tmax1o3.sql PRDE_TMAXIO3 PRDE_TMAXIO3_RMAX "/DE-PR-CUSTOMER"
$ORACLE_HOME/bin/impdp ladba/schoolmax@$ORACLE_SID remap_schema=PROD:PRDE_TMAXIO3 remap_schema=PROD_RMAX:PRDE_TMAXIO3_RMAX directory=PROD_DUMP dumpfile=prde_prod01.dmp logfile=$LOG_NAME exclude=statistics
echo "";
echo "";
echo "---unlock the user account!"
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/unlock_user.sql PRDE_TMAXIO3
$ORACLE_HOME/bin/sqlplus ladba/schoolmax@$ORACLE_SID @/DE-PR-CUSTOMER/unlock_user.sql PRDE_TMAXIO3_RMAX
echo "";
echo "";
echo "---sending the email....."
uuencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O3' uencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O3' encode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O3' uencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O3' #uuencode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O3' encode $WRK_DIR/$LOG_NAME $WRK_DIR/$LOG_NAME | cat $WRK_DIR/$LOG_NAME - | mailx -s 'Refresh de hoy TMAX1O3'
echo "---Done !!!";
3 Log Files Section
The following section shows the entries generated in the log file in the /DE-PR-CUSTOMER path during the process:
3.1 File vg_PRDE_TMAXIO1.log
---Begin to drop the schema and import the dump file
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 6 08:00:01 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
This system is restricted to Oracle authorized users. Unauthorized access may result in disciplinary action and/or civil or criminal penalties.
To the extent permitted by law, use of this system may be monitored in accordance with the terms of Oracle's Acceptable Use Policy.
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
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
------
alter system kill session '194,23663';
alter system kill session '258,16103';
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
------
alter system kill session '168,39617';
User altered.
User altered.
SP2-0734: unknown command beginning "'ALTERSYST..." - rest of line ignored.
System altered.
System altered.
SP2-0734: unknown command beginning "'ALTERSYST..." - rest of line ignored.
System altered.
User dropped.
User dropped.
old 1: CREATE USER &1
new 1: CREATE USER PRDE_TMAXIO1
old 2: IDENTIFIED BY &1
new 2: IDENTIFIED BY PRDE_TMAXIO1
User created.
old 1: GRANT CONNECT TO &1
new 1: GRANT CONNECT TO PRDE_TMAXIO1
Grant succeeded.
old 1: GRANT RESOURCE TO &1
new 1: GRANT RESOURCE TO PRDE_TMAXIO1
Grant succeeded.
old 1: ALTER USER &1 DEFAULT ROLE ALL
new 1: ALTER USER PRDE_TMAXIO1 DEFAULT ROLE ALL
User altered.
old 1: GRANT CREATE SEQUENCE TO &1
new 1: GRANT CREATE SEQUENCE TO PRDE_TMAXIO1
Grant succeeded.
old 1: GRANT CREATE VIEW TO &1
new 1: GRANT CREATE VIEW TO PRDE_TMAXIO1
Grant succeeded.
old 1: GRANT UNLIMITED TABLESPACE TO &1
new 1: GRANT UNLIMITED TABLESPACE TO PRDE_TMAXIO1
Grant succeeded.
old 1: CREATE USER &2
new 1: CREATE USER PRDE_TMAXIO1_RMAX
old 2: IDENTIFIED BY &2
new 2: IDENTIFIED BY PRDE_TMAXIO1_RMAX
User created.
old 1: GRANT CONNECT TO &2
new 1: GRANT CONNECT TO PRDE_TMAXIO1_RMAX
Grant succeeded.
old 1: GRANT RESOURCE TO &2
new 1: GRANT RESOURCE TO PRDE_TMAXIO1_RMAX
Grant succeeded.
old 1: ALTER USER &2 DEFAULT ROLE ALL
new 1: ALTER USER PRDE_TMAXIO1_RMAX DEFAULT ROLE ALL
User altered.
old 1: GRANT CREATE SEQUENCE TO &2
new 1: GRANT CREATE SEQUENCE TO PRDE_TMAXIO1_RMAX
Grant succeeded.
old 1: GRANT CREATE VIEW TO &2
new 1: GRANT CREATE VIEW TO PRDE_TMAXIO1_RMAX
Grant succeeded.
old 1: GRANT UNLIMITED TABLESPACE TO &2
new 1: GRANT UNLIMITED TABLESPACE TO PRDE_TMAXIO1_RMAX
Grant succeeded.
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
---unlock the user account!
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 6 17:58:32 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
This system is restricted to Oracle authorized users. Unauthorized access may result in disciplinary action and/or civil or criminal penalties.
To the extent permitted by law, use of this system may be monitored in accordance with the terms of Oracle's Acceptable Use Policy.
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
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
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 6 17:58:32 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
This system is restricted to Oracle authorized users. Unauthorized access may result in disciplinary action and/or civil or criminal penalties.
To the extent permitted by law, use of this system may be monitored in accordance with the terms of Oracle's Acceptable Use Policy.
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
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
---sending the email.....
---Done !!!
3.2 File vg_PRDE_TMAXIO3.log
---Begin to drop the schema and import the dump file
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 5 08:35:01 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
This system is restricted to Oracle authorized users. Unauthorized access may result in disciplinary action and/or civil or criminal penalties.
To the extent permitted by law, use of this system may be monitored in accordance with the terms of Oracle's Acceptable Use Policy.
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
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
------
alter system kill session '61,32347';
alter system kill session '114,6275';
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
------
alter system kill session '72,22539';
User altered.
User altered.
SP2-0734: unknown command beginning "'ALTERSYST..." - rest of line ignored.
System altered.
System altered.
SP2-0734: unknown command beginning "'ALTERSYST..." - rest of line ignored.
System altered.
User dropped.
User dropped.
old 1: CREATE USER &1
new 1: CREATE USER PRDE_TMAXIO3
old 2: IDENTIFIED BY &1
new 2: IDENTIFIED BY PRDE_TMAXIO3
User created.
old 1: GRANT CONNECT TO &1
new 1: GRANT CONNECT TO PRDE_TMAXIO3
Grant succeeded.
old 1: GRANT RESOURCE TO &1
new 1: GRANT RESOURCE TO PRDE_TMAXIO3
Grant succeeded.
old 1: ALTER USER &1 DEFAULT ROLE ALL
new 1: ALTER USER PRDE_TMAXIO3 DEFAULT ROLE ALL
User altered.
old 1: GRANT CREATE SEQUENCE TO &1
new 1: GRANT CREATE SEQUENCE TO PRDE_TMAXIO3
Grant succeeded.
old 1: GRANT CREATE VIEW TO &1
new 1: GRANT CREATE VIEW TO PRDE_TMAXIO3
Grant succeeded.