MOHAMMED ABDUL HAMEED: April 2017

Tuesday, April 25, 2017

Oracle Database Upgrade from 11.2.0.1 to 11.2.0.4 Step by Step

Posted by MOHAMMED ABDUL HAMEED on 1:47 PM

** Find the date of the particular day **
[root@india ~]# date
Tue Apr 25 15:39:24 EDT 2017

** Find the release no **
[root@india ~]# cat /etc/oracle-release
Oracle Linux Server release 5.8

** Find the Kernal Parameter **
[root@india ~]# uname -r
2.6.32-300.10.1.el5uek

** Find the 32 or 64 bit **
[root@india ~]# uname -p
x86_64

** Find the no of Database in one partiuclar Server **

[oracle@india ~]$ cat /etc/oratab
#

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
Stage:/u01/app/oracle/oracle11g/product/11.2.0/dbhome_1:N

** Find the Oracle Inventory location and group **

[oracle@india ~]$ cat /u01/app/oracle/oracle11g/product/11.2.0/dbhome_1/oraInst.loc

inventory_loc=/u01/app/oraInventory

inst_group=oinstall

** Find the all ORACLE_HOME Locations **

[oracle@india ~]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml

12.1.0.2.0

2.1.0.6.0

** To Check which instance is wokring **

[oracle@india ~]$ ps -ef | grep -i smon

oracle 12566 1 0 08:23 ? 00:00:04 ora_smon_HYD

oracle 16275 1 0 09:56 ? 00:00:03 ora_smon_PROD

oracle 25916 1 0 14:04 ? 00:00:15 ora_smon_Stage

oracle 30710 30125 0 15:56 pts/10 00:00:00 grep -i smon

** To Connect to Stage database **

[oracle@india ~]$ . oraenv

ORACLE_SID = [oracle] ? Stage

The Oracle base for ORACLE_HOME=/u01/app/oracle/oracle11g/product/11.2.0/dbhome_1 is /u01/app/oracle

** Check the version **

SQL> select instance_name,host_name,version,status from v$instance;

INSTANCE_NAME HOST_NAME VERSION STATUS

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

Stage india 11.2.0.1.0 OPEN

SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

OWB 11.2.0.1.0 VALID

Oracle Application Express 3.2.1.00.10 VALID

Oracle Enterprise Manager 11.2.0.1.0 VALID

OLAP Catalog 11.2.0.1.0 VALID

Spatial 11.2.0.1.0 VALID

Oracle Multimedia 11.2.0.1.0 VALID

Oracle XML Database 11.2.0.1.0 VALID

Oracle Text 11.2.0.1.0 VALID

Oracle Expression Filter 11.2.0.1.0 VALID

Oracle Rules Manager 11.2.0.1.0 VALID

Oracle Workspace Manager 11.2.0.1.0 VALID

COMP_NAME VERSION STATUS

Oracle Database Catalog Views 11.2.0.1.0 VALID

Oracle Database Packages and Types 11.2.0.1.0 VALID

JServer JAVA Virtual Machine 11.2.0.1.0 VALID

Oracle XDK 11.2.0.1.0 VALID

Oracle Database Java Packages 11.2.0.1.0 VALID

OLAP Analytic Workspace 11.2.0.1.0 VALID

Oracle OLAP API 11.2.0.1.0 VALID

18 rows selected.

** Check the 11.2.0.4 software binaries **

[oracle@india neupgrade]$ ls

p13390677_112040_LINUX_1of7 (1).zip p13390677_112040_LINUX_2of7.zip

p13390677_112040_LINUX_1of7.zip

[oracle@india neupgrade]$ pwd

/mnt/hgfs/neupgrade

[oracle@india neupgrade]$ unzip /mnt/hgfs/neupgrade/p13390677_112040_LINUX_1of7 -d /u01/app/oracle/oracle11g/product/11.2.0.4/dbhome_2/

[oracle@india neupgrade]$ unzip /mnt/hgfs/neupgrade/p13390677_112040_LINUX_2of7 -d /u01/app/oracle/oracle11g/product/11.2.0.4/dbhome_2/

** Now Export the new Oracle Home **

[oracle@india database]$ export ORACLE_HOME=/u01/app/oracle/oracle11g/product/11.2.0.4/dbhome_2

** Install the Binaries of 11.2.0.4 **

[oracle@india database]$ ./runInstaller

Starting Oracle Universal Installer...

@90 % it will prompt to run the root.sh script

select Y for all to replace the current files with the new files.

** Now run the DBUA from 11.2.0.4 **

[oracle@india ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 3 14:32:18 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 500;
SQL> select instance_name,host_name,version,status from v$instance;

INSTANCE_NAME HOST_NAME VERSION STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------
Stage india 11.2.0.4.0 OPEN

SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -----------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 VALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID

COMP_NAME VERSION STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 VALID
Oracle OLAP API 11.2.0.4.0 VALID

18 rows selected.

Create,Run,Stop,Drop DBMS_SCHEDULER Jobs

Posted by MOHAMMED ABDUL HAMEED on 12:26 AM

Create the job :

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(job_name => `SYS.XXXXXXX`,
program_name => `SYS.XXXXXXX`,
schedule_name => `SYS.XXXXXXX`);
END;
/

Run the job :

BEGIN
SYS.DBMS_SCHEDULER.RUN_JOB
(job_name => 'SYS.XXXXXXX');
END;
/

Stop the job :

BEGIN
SYS.DBMS_SCHEDULER.STOP_JOB
(job_name => 'SYS.XXXXXXX');
END;
/

Drop the job :

BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'SYS.XXXXXXX');
END;
/

Sunday, April 23, 2017

How to kill interrupted Oracle impdp/expdp job.

Posted by MOHAMMED ABDUL HAMEED on 2:59 PM

Hi Folks ,

Whenever we need to cancel the export or import jobs , we need to follow the below mention procedure :

[oracle@india ~]$ impdp full=Y directory=DATA_PUMP_DIR dumpfile=fullexport24042017.dmp logfile=fullexport24042017.log

Import: Release 11.2.0.1.0 - Production on Sun Apr 23 17:19:43 2017

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

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** full=Y directory=DATA_PUMP_DIR dumpfile=fullexport24042017.dmp logfile=fullexport24042017.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
ORA-31684: Object type USER:"APEX_030200" already exists
ORA-31684: Object type USER:"OWBSYS" already exists
ORA-31684: Object type USER:"OWBSYS_AUDIT" already exists
ORA-31684: Object type USER:"SCOTT" already exists
ORA-31684: Object type USER:"HAMEED" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DBFS_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"ADM_PARALLEL_EXECUTE_TASK" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_SELECT_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_EXECUTE_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JMXSERVER" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
ORA-31684: Object type ROLE:"ORDADMIN" already exists
ORA-31684: Object type ROLE:"OLAPI_TRACE_USER" already exists
ORA-31684: Object type ROLE:"OLAP_XS_ADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"CWM_USER" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists
ORA-31684: Object type ROLE:"SPATIAL_WFS_ADMIN" already exists
ORA-31684: Object type ROLE:"WFS_USR_ROLE" already exists
ORA-31684: Object type ROLE:"SPATIAL_CSW_ADMIN" already exists
ORA-31684: Object type ROLE:"CSW_USR_ROLE" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
ORA-31684: Object type ROLE:"APEX_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"OWB$CLIENT" already exists
ORA-31684: Object type ROLE:"OWB_DESIGNCENTER_VIEW" already exists
ORA-31684: Object type ROLE:"OWB_USER" already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
^C

** We need to stop this import
( ctrl + c )
Import> ^C

Import> kill_job ( kill_job - > Use this Keyword )
Are you sure you wish to stop this job ([yes]/no): yes

Note : The most common error which DBA make is , when they press CTRL + C to terminate the job of export/import and think that it will eventually stop. But the fact of matter is it will be running untill we kill the job.

Query to check the job status :

SQL> select *from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

SYSTEM SYS_IMPORT_FULL_01 IMPORT FULL EXECUTING 1

1 3

To kill the import/export job :

Ctrl + c

Import> ^C

Import> kill_job

Are you sure you wish to stop this job ([yes]/no): yes