manual Upgrade from 10gR2 to 11gR1

Pre-Requisite:

You should have enough MEMORY, server space and SWAP SPACE on Oracle database 10g, which you want to migrate.

Also here we are upgrading 10g to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

$  cp   utlu111i.sql   /tmp

For more about utlu111i.sql :

 http://oracleflash.com/38/Oracle-11g-Release-1-Pre-Upgrade-tool-utlu111i-sql.html

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select version from v$timezone_file;

          

          version

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

                   2  

here oracle 10.2.0.1 has timezone file version of 2

Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file version.

SQL >  select * from sys.sys_tzuv2_temptab ;

           no rows selected

here we see no rows but if we see any rows then we need to take that data backup and restore after timezone file version upgrade

Step 4) Run Pre-Upgrade Utility again

Run utlrp.sql to validate the objects if there is any invalidate objects.

SQL> @?/rdbms/admin/utlrp.sql

Check for any INVALID Objects in the database before upgrade starts:

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

SQL> purge DBA_RECYCLEBIN

Gather dictionary stats to run UPGRADE process fast,

SQL> exec dbms_stats.gather_dictionary_stats;

Create pfile from spfile 

Shutdown  à shut down the database in oracle 10g.

Copy the pfile of database from ORACLE_HOME/dbs(10g) to ORACLE_HOME/dbs(11g) using cp command.

In that make changes shown below,

 1.     Remove BDUMP, CDUMP, UDUMP and their locations

 2.     Add Diagnostic_dest=/’$ORACLE_BASE/diag’-àlocation for diagnostic dest

 3.     Change audit_file_dest location

 4.     Change compatibility to 11g and save it.

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings. If everything looks fine, shut down the database from 10g Oracle Home.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export PATH=$ORACLE_HOME/bin:$PATH[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Once the upgrade finishes, It will shut down the database automatically.

Now export New oracle HOME and startup the database

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export TNS_ADMIN=$ORACLE_HOME/network/admin

Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

SQL> startup 

SQL> select substr(comp_id,1,10) comp_id, substr(comp_name,1,25) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

All the components should be VALID state

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display/verify the results of the upgrade:

SQL> @?/rdbms/admin/utlu111s.sql

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> @?/rdbms/admin/catuppst.sql

Run utlrp.sql to recompile invalid objects after upgrade

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

Run utluiobj.sql after the upgrade to identify any new invalid objects

Edit /etc/oratab file with new 11g Oracle home and comment out 10g oracle home

Manual and Clean Uninstall Oracle for Windows

Manual and Clean Uninstall Oracle for Windows

If you facing problems trying to uninstall Oracle from your Windows workstation, or unable to uninstall Oracle installations cleanly and properly, the following steps may be used to uninstall all Oracle products currently install on the workstation:

1. Uninstall all Oracle components using the Oracle Universal Installer (OUI).
2. Delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key which contains registry entries for all Oracle products by using regedit.
3. Delete any references to Oracle services/components in the following registry location: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/. Looks for key entries that starts with “Ora” which are obviously related to Oracle.
4. Reboot the workstation.
5. Delete the ORACLE_BASE directory where you installed.
6. Delete the C:\Program Files\Oracle directory.

Warning: Editing registry may cause problems to your PC.

DATA PUMP

1)   Import 11g data dump into 10g

You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version

So, If your database is 11g and you want to export 11g and import into 10g

from 11g db ,

$  expdp  Test_schema_name/passs directory=datapump  schemas=Test_schema_name Version=10.2.0.4.0.

Once the export is done, you do the regular import from 10g server.



2)   Import multiple dump files

If the size of the dump file is large, usually they will be split into smaller chunks for easier ftp upload/download.

If you are trying to import a dump file that is split into many chunks, then you need to modify the DUMPFILE paratermeter to include %U

Ex:  If the dump files are named EXP_PROD_1.dmp, EXP_PROD_2.dmp etc ,    then  DUMPFILE=EXP_PROD_%U.DMP



3)   How to kill data pump jobs

When you import or export using data pump impdp or expdp commands, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too

Following sql will give you the list of data pump jobs

select * from dba_datapump_jobs
If you want to kill your impdp or expdp

1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
If by mistake, you closed the window and your import/export job is still running,

1) Get the name of the job using
select * from dba_datapump_jobs

2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job

3) Once you are attached to job, Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs


4) REUSE_DUMPFILE :( Overwrite existing dumpfile)

This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.

          $ expdp   scott/tiger   directory=exp_dir     dumpfile = x.dmp     table s= example    reuse_dumpfiles = y

DBA SCRIPTS

1) DROP USER OBJECTS:

conn username/password --->>> MANDATORY to connect as a user

declare
cursor fkc is  select  table_name,
constraint_name
from  user_constraints
where constraint_type ='R';
tname user_constraints.table_name%TYPE;
cname user_constraints.constraint_name%TYPE;
begin
open fkc;
loop
fetch fkc into tname, cname;
exit when fkc%NOTFOUND;
dbms_output.put_line('alter table '||tname||' drop constraint '||cname);
execute immediate 'alter table '||tname||' drop constraint '||cname;
end loop;
close fkc;

end;
/

declare
cursor fkc is  select  object_name,
object_type
from  user_objects
where object_name not in
('INDEX','PACKAGE BODY');
obj_name user_objects.object_name%TYPE;
obj_type user_objects.object_type%TYPE;
begin
open fkc;
loop
fetch fkc into obj_name, obj_type;
exit when fkc%NOTFOUND;
dbms_output.put_line('Drop '||obj_type||' '||obj_name);
begin
  execute immediate 'Drop '||obj_type||' '||obj_name;
exception
  when others then null;
end;
end loop;
close fkc;
end;
/

PURGE RECYCLEBIN;

DECLARE

the_job user_jobs.job%TYPE;
cursor c1 is select job from user_jobs;
BEGIN
open c1;
loop
fetch c1 into the_job;
exit when c1%NOTFOUND;
dbms_job.remove(the_job);
end loop;
close c1;
END;
/

VERIFY :

     set heading off
SQL >   select 'Objects left in schema : ' from dual;
SQL>    select object_name,object_type from user_objects;

SQL>   select 'Jobs left in schema: ' from dual;
SQL>   select job,what from user_jobs;

2) User privileges & Grantee :

SELECT grantee, privilege, admin_option
FROM sys.dba_sys_privs
WHERE (privilege LIKE '% ANY %'
OR privilege IN ('BECOME USER', 'UNLIMITED TABLESPACE')
OR admin_option = 'YES')
AND grantee NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')



3) Free Space :
//** This script lists all Tablespaces and its datafiles with their free and used space **//

SET SERVEROUTPUT ON

SET PAGESIZE 1000

SET LINESIZE 255

SET FEEDBACK OFF

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",

       Substr(df.file_name,1,40) "File Name",

       Round(df.bytes/1024/1024,2) "Size (M)",

       Round(e.used_bytes/1024/1024,2) "Used (M)",

       Round(f.free_bytes/1024/1024,2) "Free (M)",

       Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"

FROM   DBA_DATA_FILES  df,

       (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes

           FROM dba_extents  GROUP by file_id)  e,

       (SELECT Max(bytes) free_bytes, file_id

           FROM dba_free_space   GROUP BY file_id) f

WHERE  e.file_id (+) = df.file_id

AND    df.file_id  = f.file_id (+)

ORDER BY df.tablespace_name, df.file_name;

4) Tablespaces :

//** This script lists all Tablespaces with their Sizes **// 

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

5) Locked_objects :

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,

       NVL(b.oracle_username, '(oracle)') AS username,

       a.owner AS object_owner,

       a.object_name,

       Decode(b.locked_mode, 0, 'None',

                             1, 'Null (NULL)',

                             2, 'Row-S (SS)',

                             3, 'Row-X (SX)',

                             4, 'Share (S)',

                             5, 'S/Row-X (SSX)',

                             6, 'Exclusive (X)',

                             b.locked_mode) locked_mode,

       b.os_user_name

FROM   dba_objects a,

       v$locked_object b

WHERE  a.object_id = b.object_id

ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14

SET VERIFY ON

6) Data Pump Monitoring Script : 

select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode

from gv$session_longops sl, gv$datapump_job dp

where sl.opname = dp.job_name and sofar != totalwork;

7) RMAN Job Monitoring Script :

SQL >  SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

        FROM V$SESSION_LONGOPS

        WHERE OPNAME LIKE 'RMAN%'  AND OPNAME NOT LIKE '%aggregate%' 

              AND TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

To see the Rman status and SID:

SQL >  SELECT s.SID, p.SPID, s.CLIENT_INFO FROM V$PROCESS p, V$SESSION s

       WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE 'rman%';

8) To see what USERS are Running :
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address;

**** To see for a particular USER, what he is running *******

SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address and a.username = '<username>';


9) Find FREE/USED/TOTAL size of oracle database : 

(used space):

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

SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments;

(free space):

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

SQL> select sum(bytes)/1024/1024/1024 GB from dba_free_space;

(Total database size):

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

SQL> select sum(bytes)/1024/1024/1024 GB from dba_data_files;

  +

(Temp size):

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

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header;

(or)

SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"
   FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space  FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space  FROM v$log) a, (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
     FROM dba_temp_files) c;

10) RMAN Cold Backup :

Rman target / nocatalog

Rman > configure controlfile autobackup on;

Rman >  run {

2>  allocate channel d1 type disk;

3> Backup full tag full_offline_bkup

4> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’

5> Database plus archivelog;

6> Release channel d1;

}

Find more scripts here : http://www.oracle-base.com/dba/Scripts.php