Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 16, 2017

Deploy a website on windows-7 via IIS

My friend had requested me to deploy a website on windows-7 via IIS on his VM for some load balancer testing.


Here is the code for the webpage and create a file named index.html on the VM/windows machine preferably in C:/ and I created in C:/shaik-site folder.

Give the above path on the IIS while
index.html
<!DOCTYPE html>
<html lang="en-us">
<head>
<style>
body  {
    background-image: url("paper.gif");
    background-color: #cccccc;
}
h1   {color: Black;}
p    {color: Green;}
</style>
</head>
<body>

<h1> TESTING NETSCALE</h1>
<p>vm-machine-1</p>
</body>
</html>




Give the path  C:/shaik-site on the IIS while creating the site and deploy it. 







Tuesday, August 22, 2017

Install and upgrade to Oracle 12c - Part-3

In the Part-3  we will upgrade the database from 11.2.0.2 to 12.1.0.2. In the subsequent series.
Part-4 : We will convert a non - CDB 12.1.0.2 database into a CDB and create a PDB.
Part-5 : We will upgrade the 12.1.0.2 database to 12.2.0.1



12c upgrade:


Minimum version of the database that can be directly upgraded to Oracle 12c Release 1 (12.1)
Source Database
Target Database
10.2.0.5
12.1.x
11.1.0.7
12.1.x
11.2.0.2 or higher
12.1.x

  • Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.
  • Ensure that you do not have duplicate objects in the SYS and SYSTEM schema.
  •  Disable any custom triggers that would fire before/after DDL and enable them after the upgrade is complete.
  •  In 11g, Creation of ACLs with timestamp with timezone datatype does not fail but migrating the same in 12c it fails with "ORA-01830: date format picture ends before converting entire input string" error 
    Please refer Note 1958876.1 Upgrade to 12.1 fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION"
  • Patch requirements BEFORE upgrade ,to avoid the ORA-7455 error while running the  datapump after the upgrade . Please see  Note 2017572.1 ORA-7445 [qcsIsColInFro] Querying After Upgrade to 12c
·          Before starting the manual upgrade  it is required change the preference for 'concurrent statistics gathering' on the current release if the current setting is not set to 'FALSE'
  •  
SQL>  SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
FALSE
When 'concurrent statistics gathering' is not not set to 'FALSE', change the value to 'FALSE before the upgrade. 

BEGIN 
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE'); 
END; 
/


In both 11.2 as in 12.1 concurrency is disabled by default for both manual and automatic statistics gathering. If the database requires changing this value back to the original setting, do this after the upgrade.

Step 3 : Check for the integrity of the source database.

Check for the integrity of the source database prior to starting the upgrade by downloading and running the dbupgdiag.sql script from the My Oracle Support article below:
Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)


The script needs to be run in SQL*Plus both before the upgrade on the source database and after the upgrade on the upgraded database as SYS user.

This will help to determine the status of the database before and after upgrade.

Download and save the script as dbupgdiag.sql. The script needs no additional configuration.

Connect as sysdba and execute the script
cd <location of the script>
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit


[shaikdb1]: /home/oradm/sshaik/upgrade> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 21 14:50:41 2017

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "SYS"
SQL> @"dbupgdiag.sql"


SQL> show user
USER is "SYS"
SQL> @"dbupgdiag.sql"

Enter location for Spooled output:

Enter value for 1: /tmp

21_Aug_2017_0251          .log

SHAIIK11_



                          *** Start of LogFile ***

  Oracle Database Upgrade Diagnostic Utility       08-21-2017 14:51:33

===============
Hostname
===============

shaikdb1

===============
Database Name
===============

SHAIIK11

===============
Database Uptime
===============

14:41 21-AUG-17

=================
Database Wordsize
=================

This is a 64-bit database

================
Software Version
================

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

=============
Compatibility
=============

Compatibility is set as 11.2.0.0.0

================
Archive Log Mode
================

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u0/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     17
Current log sequence           19

================
Auditing Check
================


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u0/admin/SHAIIK11G/adump
                                                 audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

================
Cluster Check
================

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>########################################################
DOC>





Step 4 : Pre-Upgrade Steps

  • Run the Pre-Upgrade Information Tool from the environment of the database that will be upgraded.
 @$ORACLE_HOME/rdbms/admin/preupgrd.sql

SQL> show user
USER is "SYS"
SQL> @preupgrd.sql






Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in SHAIIK11...
***************************************************************************


      ************************************************************

                 ====>> ERRORS FOUND for SHAIIK11 <<====

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.

           You MUST resolve the above errors prior to upgrade

      ************************************************************

      ************************************************************

             ====>> PRE-UPGRADE RESULTS for SHAIIK11 <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /u0/oracle/product/11.2.0/dbhome_1/cfgtoollogs/SHAIIK11G/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /u0/oracle/product/11.2.0/dbhome_1/cfgtoollogs/SHAIIK11G/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /u0/oracle/product/11.2.0/dbhome_1/cfgtoollogs/SHAIIK11G/preupgrade/postupgrade_fixups.sql

      ************************************************************

***************************************************************************
Pre-Upgrade Checks in SHAIIK11 Completed.
***************************************************************************

***************************************************************************






emctl stop dbconsole

Remove Enterprise Manager  Database Control repository :

Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express .
     Therefore no repository is needed anymore .
     Remove  Enterprise Manager  Database Control  repository MANUALLY using the following command
     Note : You will get the emremove.sql script in the Oracle 12c home .
    Copy the emremove.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORACLE_HOME/rdbms/admin and then execute on the source database prior to upgrade.

 

4.1 Deprecated CONNECT Role

Update Access Control Lists and Network Utility Packages 

Database Links with Passwords from Earlier Releases

Create a script for DBLINKs (in case the database has to be downgraded again).

During the upgrade to Oracle Database 12c any passwords in database links are encrypted.
To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade.
Consequently, the database links do not exist in the downgraded database.
If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.

Save Database Control Files and Data with the emdwgrd Utility 

After upgrading to Oracle Database 12c , if you want to downgrade Oracle Enterprise Manager Database Control you must save your Database Control files and data before upgrading your database.
The emdwgrd utility can be used to keep a copy of your database control files and data before upgrading your database.
The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 12c home.

1. Set ORACLE_HOME to your old Oracle Home
2. Set ORACLE_SID to the SID of the database being upgraded.
3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
4. Change directory to Oracle Database 12c  home.
5. Run the emdwgrd command.
   a. Run the following command for single instance database:
$ emdwgrd -save -sid old_SID -path save_directory


Ensure That No Files Need Media Recovery Before Upgrading 

Make  ensure that there are no files requiring media recovery by executing the following query .
SQL> SELECT * FROM v$recover_file;
SELECT * FROM v$recover_file;

no rows selected

 Disable all batch and cron jobs


Run olspreupgrade.sql  :



if you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE during the upgrade and set it back to true after the upgrade. 

  •  Stop the listener for the database.

Create a new listener and spfile for Oracle Database 12c Release 1 (12.1) database

cp /u0/oracle/product/11.2.0/dbhome_1/dbs/spfileSHAIIK11G.ora /u0/oracle/product/12.1.0/dbhome_1/dbs/


Take full backup before upgrade:



[shaikdb1]: /u0/oracle/product/12.1.0/dbhome_1/install> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 21 14:41:22 2017

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2219168 bytes
Variable Size             771752800 bytes
Database Buffers          452984832 bytes
Redo Buffers                9003008 bytes
Database mounted.
SQL> exit


[shaikdb1]: /u0/oracle/product/12.1.0/dbhome_1/install> rman

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Aug 21 14:41:42 2017

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

RMAN> connect target /

connected to target database: SHAIIK11 (DBID=4235168011, not open)

RMAN> run { allocate channel ch01 type disk;
2> backup database format '/u0/oracle/backups/SHAIIK11G_%U.bak' TAG before_upgrade;
3> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=156 device type=DISK

Starting backup at 21-AUG-17
channel ch01: starting full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00001 name=/u0/oracle/oradata/SHAIIK11G/system01.dbf
input datafile file number=00002 name=/u0/oracle/oradata/SHAIIK11G/sysaux01.dbf
input datafile file number=00003 name=/u0/oracle/oradata/SHAIIK11G/undotbs01.dbf
input datafile file number=00004 name=/u0/oracle/oradata/SHAIIK11G/users01.dbf
channel ch01: starting piece 1 at 21-AUG-17
channel ch01: finished piece 1 at 21-AUG-17
piece handle=/u0/oracle/backups/SHAIIK11G_04scfe1p_1_1.bak tag=BEFORE_UPGRADE comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:07
channel ch01: starting full datafile backup set
channel ch01: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch01: starting piece 1 at 21-AUG-17
channel ch01: finished piece 1 at 21-AUG-17
piece handle=/u0/oracle/backups/SHAIIK11G_05scfe20_1_1.bak tag=BEFORE_UPGRADE comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-AUG-17
released channel: ch01


start the DB in upgrade mode:


[shaikdb1]: /home/oradm> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 21 15:26:45 2017

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  2997440 bytes
Variable Size             922747712 bytes
Database Buffers          301989888 bytes
Redo Buffers               13778944 bytes
Database mounted.
Database opened.
SQL>

Run catupgrade.sql

[shaikdb1]: /u0/oracle/product/12.1.0/dbhome_1/rdbms/admin> ls catctl*
catctl.pl





[shaikdb1]: /u0/oracle/product/12.1.0/dbhome_1/rdbms/admin> ls catupgrd.sql
catupgrd.sql
[shaikdb1]: /u0/oracle/product/12.1.0/dbhome_1/rdbms/admin> $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count     n = 6
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = /u0/oracle/product/12.1.0/dbhome_1/diagnostics
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Home           = /u0/oracle/product/12.1.0/dbhome_1

Analyzing file catupgrd.sql
Log files in /u0/oracle/product/12.1.0/dbhome_1/diagnostics
catcon: ALL catcon-related output will be written to /u0/oracle/product/12.1.0/dbhome_1/diagnostics/catupgrd_catcon_33226900.lst
catcon: See /u0/oracle/product/12.1.0/dbhome_1/diagnostics/catupgrd*.log files for output generated by scripts
catcon: See /u0/oracle/product/12.1.0/dbhome_1/diagnostics/catupgrd_*.lst files for spool files, if any
Number of Cpus        = 8
SQL Process Count     = 6

------------------------------------------------------
Phases [0-73]
Serial   Phase #: 0 Files: 1

returned from sqlpatch
    Time: 35s
Serial   Phase #:70 Files: 1     Time: 27s
Serial   Phase #:71 Files: 1     Time: 1s
Serial   Phase #:72 Files: 1     Time: 0s
Serial   Phase #:73 Files: 1     Time: 17s

Grand Total Time: 4619s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
/u0/oracle/product/12.1.0/dbhome_1/cfgtoollogs/SHAIIK11G/upgrade/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:16m:59s]



[shaikdb1]: /u0/oracle/product/12.1.0/dbhome_1/rdbms/admin> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 22 09:43:38 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  2997440 bytes
Variable Size            1157628736 bytes
Database Buffers           67108864 bytes
Redo Buffers               13778944 bytes
Database mounted.
Database opened.
SQL>  @utlu121s.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.





CATCTL REPORT = /u0/oracle/product/12.1.0/dbhome_1/cfgtoollogs/SHAIIK11G/upgrade/upg_summary.log

PL/SQL procedure successfully completed.






Oracle Database 12.1 Post-Upgrade Status Tool           08-22-2017 09:44:23



               


Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
Oracle Server                          UPGRADED      12.1.0.2.0  00:10:39
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:06:55
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:01
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:17
Oracle OLAP API                           VALID      12.1.0.2.0  00:00:25
Oracle XDK                                VALID      12.1.0.2.0  00:00:44
Oracle Text                               VALID      12.1.0.2.0  00:01:07
Oracle XML Database                       VALID      12.1.0.2.0  00:08:32
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:14
Oracle Multimedia                         VALID      12.1.0.2.0  00:02:28
Spatial                                UPGRADED      12.1.0.2.0  00:15:19
Oracle Application Express                VALID     4.2.5.00.08  00:23:48
Final Actions                                                    00:02:24
Post Upgrade                                                     00:00:07

Total Upgrade Time: 01:15:06

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Update Summary Table with con_name and endtime.
SQL> --
SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
  2                                  con_name = SYS_CONTEXT('USERENV','CON_NAME'),
  3                                  endtime  = SYSDATE
  4         WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
 
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql




COMPATIBLE Initialization Parameter

The COMPATIBLE initialization parameter controls the compatibility level of your database.
When you are certain that you no longer need the ability to downgrade your database to its original release
ALTER SYSTEM SET COMPATIBLE = '12.0.0' SCOPE=SPFILE;

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  2997440 bytes
Variable Size             889193280 bytes
Database Buffers          335544320 bytes
Redo Buffers               13778944 bytes
Database mounted.
Database opened.

 Identify Invalid Objects With the utluiobj Script



SQL> @utluiobj
.
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 08-22-2017 11:06:55
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner                     Object Name                     Object Type
.

PL/SQL procedure successfully completed.



Upgrade DST Timezone:


Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)

Download & unzip : DBMS_DST_scriptsV1.9.zip
shaikdb1]: /home/oradm/sshaik/upgrade/DBMS_DST_scriptsV1.9> ls -lrt
total 136
-rw-r--r--    1 oradm    oradm         31010 Aug 22 2014  upg_tzv_check.sql
-rw-r--r--    1 oradm    oradm         19502 Aug 22 2014  upg_tzv_apply.sql
-rw-r--r--    1 oradm    oradm          6294 Jan 08 2015  countstarTSTZ.sql
-rw-r--r--    1 oradm    oradm          6909 Jan 08 2015  countstatsTSTZ.sql


SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.1.0.2 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv18 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.


SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv18 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  2997440 bytes
Variable Size             905970496 bytes
Database Buffers          318767104 bytes
Redo Buffers               13778944 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv18 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.