Oracle Database Upgrade 10g to 11g

Oracle Database Upgrade 10g to 11g

At this article I'll present Step by step upgrading Oracle 10g database to 11g.During my testing to upgrade i have tried DBUA(database upgrade assistant) and Manual upgrade,however i would like to share Manually upgrading.

Before starting upgrade we should know that database binary,files,listeners,tnsname,spfile and passwordfile had backed.

1. Stop isqlplus  

[oracle@localhost ]$ isqlplusctl stop 

iSQL*Plus 10.2.0.4.0 Copyright (c) 2003, 2005, Oracle. All rights reserved.

 Stopping iSQL*Plus

 iSQL*Plus stopped.

2.Stop listener 

[oracle@localhost ]$lsnrctl stop Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))The command completed successfully

3.Stop Enterprise manager

 [oracle@localhost ]$ emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.

 Now Installing Oracle 11g Software 

The Oracle 10g  had installed on /u01/app/oracle/oracle/product/10.2.0/db_1

The Oracle 11g may install on  /u01/app/oracle/oracle/product/11.2.0/db_1

1. Go to /etc/sysctl.conf  Put the following setting in.

fs.file-max = 65536
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

2. Run /sbin/sysctl -p 

3. Now Create New directory for new version Oracle 11g as above

 [oracle@localhost ]$ echo $ORACLE_BASE
/u01/app/oracle
 [oracle@localhost ]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
 [oracle@localhost ]$ cd /u01/app/oracle/product
 [oracle@localhost ]$ mkdir -p 11.1.0/db_1
 [oracle@localhost ]$ mkdir -p 11.1.0/oraInventory

4   4. Now we should change the Bash_profile 

      [oracle@localhost ]$ vi .bash_profile

      # User specific environment and start up programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Oracle 11g Home settings
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl11g; export ORACLE_SID

# Oracle 10g Home settings
#ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
#ORACLE_SID=orcl10g; export ORACLE_SID
#ORACLE_TERM=xterm; export ORACLE_TERM
#PATH=/usr/sbin:$PATH; export PATH
#PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; 
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; 
export CLASSPATH

if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
            ulimit -p 16384
            ulimit -n 65536
      else
            ulimit -u 16384 -n 65536
      fi
fi

So its time to install Oracle 11g  

Go to Oracle 11g database file and 

[oracle@localhost ]$ ./runinstaller

2.Pre-Upgrade Information Tools

After installation Oracle 11g we should analyze our database. 

This is checking the database for upgrading and replay a report for us
$cp /u01/app/oracle/oracle/product/product/11.2.0/db_1/rdbms/admin/utlu112i.sql /tmp   

Now Lets start. Log in as Oracle 10g Binary
$/u01/app/oracle/oracle/product/product/10.2.0/db_1/bin/sqlplus / as sysdba 

Use the Spool for a report. SQL> SPOOL upgrade_check_veysi.log 

Run the script to check database
SQL> @/tmp/utlu112i.sql

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

--> Oracle Catalog Views         [upgrade]  VALID

--> Oracle Packages and Types    [upgrade]  VALID

--> JServer JAVA Virtual Machine [upgrade]  VALID

--> Oracle XDK for Java          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> OLAP Analytic Workspace      [upgrade]  VALID

--> OLAP Catalog                 [upgrade]  VALID

--> EM Repository                [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Java Packages         [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

--> Spatial                      [upgrade]  VALID

--> Data Mining                  [upgrade]  VALID

--> Expression Filter            [upgrade]  VALID

--> Rule Manager                 [upgrade]  VALID

--> Oracle OLAP API              [upgrade]  VALID

.

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

So put down the important information, As you see the all should  Valid, otherwise we have to make valid manually the invalid tools.

Now close spool.
SQL> SPOOL OFF
SQL> exit
If we want to looking at the check log,

$vi upgrade_check_veysi.log

Now remove dump_dest parameters from spfile.


SQL> alter system reset background_dump_dest scope=spfile sid='*';

System altered.

SQL> alter system reset core_dump_dest scope=spfile sid='*';

System altered.

SQL> alter system reset user_dump_dest scope=spfile sid='*';

System altered.

 Shutdown the database and create pfile from spfile.

SQL> shutdown immediate

SQL> create pfile from spfile;

File created.

SQL> exit

Now Copy the init.ora and oraclepassword under Oracle 11g binary

cp initora10g.ora orapwora10g /u01/app/oracle/product/11.1.0/db_1/dbs/

$cd $ORACLE_HOME/rdbms/admin
$sqlplus / as sysdba

Open the database upgrade mode

SQL> STARTUP UPGRADE; 

Use spool again for report
SQL> SPOOL upgrade.log 

Now run the upgrade after it finished we should check upgrade log if we got any error we would fix it.
SQL> @catupgrd.sql 

Close the database normally

SQL> SHUTDOWN IMMEDIATE; 

and startup normally,
SQL> STARTUP;

And now again check the upgrade summary if there is any invalid we would set it valid.
SQL> @utlu112s.sql 

Run the 
SQL> @catuppst.sql 

Compile the invalid objects
SQL> @utlrp.sql

Now check your object.
SQL> SELECT count(*) FROM dba_invalid_objects; 

Now change the listener to Oracle 11g

# listener.ora Network Configuration:

 /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = Orcl11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = xxxxxxxxx))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl11g.oracle.com)(PORT = 1521))
    )
  )

And Start Listener

[oracle@localhost ]$ Start lsnrctl

I hope you will successful on this important issue... Have a nice oracle day :)

Finding Oracle Alert Log

The Location of Alert log

At this article i will present the alert log's location. which is very important for DBAs.

First we are getting connect the Sqlplus.

SQL> sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 01 18:49:29 2014

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

Connected to an idle instance.

Looking our version...

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Getting our Location

SQL> show parameter background

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orcl/orcl/trace

And finding our ALERT log

SQL> !ls /u01/app/oracle/diag/rdbms/orcl/orcl/trace/a*

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Buffer Busy Wait

Buffer Busy Wait 

 This article presents Buffer Busy Wait 

Buffer busy waits could indicate contention in redo, rollback or data blocks. In this action all we need to check the v$waitstat view.

That Buffer busy wait condition happens when:

The block is being read into buffer by another session, And Another session has the  buffer block locked in a mode that is in compatible with the waiting session’s request

The action and resolving this problem

P1: File#  (The waiting data block of datafile numbers. We can see at DBA_DATA_FILES view and  FILE_ID )

P2: Block# (At DBA_EXTEND view block_id)

P3: Class#

So now find the buffer waiting session

SELECT tablespace_name, file_name

    FROM dba_data_files

   WHERE file_id = &v$session.p1;

If you may not see P1 at dba_data_files Then check the Temp files. May e the size of action is big thats why it under temp file

SELECT tablespace_name, file_name

    FROM dba_temp_files f, v$parameter p

   WHERE p.name='db_files'

     AND f.file_id+p.value = &v$session.p1;

Or we can check by Segments

SELECT  owner , segment_name , segment_type

    FROM  dba_extents

   WHERE  file_id = &v$session.p1

     AND  &v$session.p2 BETWEEN block_id AND block_id + blocks -1

Also we can see the block by following select

SELECT *

  FROM v$waitstat

 WHERE COUNT > 0 order by 2 desc;

                   

              So, by following command we can figure out that event.   

SELECT 'Segment Header' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_segments a, v$session_wait b

 WHERE a.header_file = b.p1

   AND a.header_block = b.p2

   AND b.event = 'buffer busy waits'

UNION ALL

SELECT 'Freelist Groups' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_segments a, v$session_wait b

 WHERE b.p2 BETWEEN a.header_block + 1

                AND (a.header_block + a.freelist_groups)

   AND a.header_file = b.p1

   AND a.freelist_groups > 1

   AND b.event = 'buffer busy waits'

UNION ALL

SELECT a.segment_type || ' block' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_extents a, v$session_wait b

 WHERE b.p2 BETWEEN a.block_id AND a.block_id + a.blocks - 1

   AND a.file_id = b.p1

   AND b.event = 'buffer busy waits'

   AND NOT EXISTS

          (SELECT 1

             FROM dba_segments

            WHERE header_file = b.p1 AND header_block = b.p2);

Oracle Flashback Data Archive

Oracle Flashback Data Archive 

At this Article I would like to share new feature of Oracle 11g version Flashback Data Archive/ Total Recall

Today I tested this to keep transaction, I think at oracle 10g version we used to Log Miner or Developed Trigger to keep the datas. After I used FBDA I saw incredibly to made datas in the compressed file.

My user have to have  FLASHBACK ARCHIVE ADMINISTER privilege.

So, let me start

Frist I give a privilege to my user

SQL> grant flashback archive administer to veysi;

Grant succeeded.

Created a tablespace for testing Flasback data archive

SQL> CREATE TABLESPACE tblspce_arch DATAFILE ‘/u01/app/oradata/orcl/flshbck_arch.dbf’ size 10m;

Tablespace created.

Lets tested it by creating table and using transactions

SQL> create table vey_flsh_arch (id number, name varchar2(15));

Table created.

SQL> insert into vey_flsh_arch values(1,’Veysi’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vey_flsh_arch;

        ID NAME

———- ——————–

         1 Veysi

Create A flashback archive on this tablespace and give for your retention time.

SQL> create flashback archive FLSHBACK_ARCH tablespace tblspce_arch retention 5 year;

Flashback archive created.

Now we can use the flashback query through 5 years.

So register flashback archive on apllication

SQL> begin

     dbms_flashback_archive.register_application

     (application_name=>'veyAp',

     flashback_archive_name=>' FLSHBACK_ARCH);

     end;

     /  

PL/SQL procedure successfully completed.

Now Add our table on it

SQL> begin

     dbms_flashback_archive.add_table_to_application

     (application_name=>'veyAp',

     table_name=>'vey_flsh_arch',

     schema_name=>'VEYSI');

     end;

     /  

PL/SQL procedure successfully completed.

Or use the following command.

SQL> alter table vey_flsh_arch flashback archive FLSHBACK_ARCH;

Table altered.

So, let look at our table

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

Upps! No rows, it means we have to enabled our table on flashback archive tables.

SQL> select table_name,status from user_flashback_archive_tables;

SQL> exec dbms_flashback_archive.enable_application(application_name=>'veyAp')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

TABLE_NAME STATUS

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

VEY_FLSH_ARCH         ENABLED

Also we can lock down our table no one can change any transactions

SQL> exec dbms_flashback_archive.lock_down_application(application_name=>'veyAp')

PL/SQL procedure successfully completed.

SQL> delete from vey_flsh_arch;

delete from vey_flsh_arch

            *

ERROR at line 1:

ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

Unlock by using following command

SQL> exec dbms_flashback_archive.unlock_application(application_name=>'veyAp')

PL/SQL procedure successfully completed.

SQL>

Now it will be on the Flashback archive and every transaction we can use it on it.

SQL> set time on;

SQL> insert into vey_flsh_arch values (2,’Gunay’)

1 row inserted.

SQL>delete from vey_flsh_arch;

2 row deleted.

SQL> commit;

Commit complete.

SQL> select * from vey_flsh_arch;

no rows selected

SQL> select * from tbl_fl_archive as of timestamp to_timestamp(’08082013 15:08:30′,’ddmmyyyy hh24:mi:ss’);

        ID NAME

———- ——————–

         1 Veysi

ORA-01078: failure in processing system parameters

Today, I got a issue during my experience
 ORA-01078: Failure in processing system parameters,
 LRM-00109: could not open parameter file '/app/oracle/product/11.2.0/db_1/dbs/init.ora
 Its first time I'm on this issue
 So, i realized that Failure caused during processing of INIT.ORA parameters during system startup.

My action was like the following and it had fixed, May be we can see easy/different way on the internet,However let me fix it :) 

[oracle@localhost ]$ sqlplus as sysdba 

SQL>startup 

ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/app/oracle/product/11.2.0/db_1/dbs/init.ora' 

SQL> exit

Disconnected

Open database nomount mode by pfile

SQL> startup nomount pfile=/app/oracle/admin/orcl/pfile/init.ora

ORACLE instance started.

Create pfile again

SQL> create spfile from pfile

2 ;

create spfile from pfile

*

ERROR at line 1:

ORA-01078: failure in processing systemparameters

LRM-00109: could not open parameter file

'/app/oracle/product/11.2.0/db_1/dbs/init.ora'

 Create like that

SQL> create spfile from pfile='/app/oracle/admin/orcl/pfile/init.ora';

File created.

NOW create pfile 

SQL> create pfile from spfile;

File created.

SQL> exit

I got init.ora

 [oracle@localhost pfile]$ ls

init.ora

 [oracle@localhost pfile]$sqlplus

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and RealApplication Testing options

I thought i resolved my issue but after this select 

SQL> select name from v$database;

select name from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

Close database

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

Open it should work i guess :)

SQL> startup

ORACLE instance started.

Total System Global Area 224760096 bytes

Fixed Size 1235143 bytes

Variable Size 218627450 bytes

Database Buffers 87557892 bytes

Redo Buffers 5427622 bytes

Database mounted.

Database opened.

Finnaly

SQL> select name from v$database;

NAME

---------

orcl

RMAN Commands List Oracle 11g

RMAN Commands List (Oracle 11g)

This article presents more useful  commands list Of RMAN Oracle 11g.

During my experience i used all of these commands for many reasons and i decided to share with you.

SHOW Commands :

1.shows all the current configuration settings

RMAN>show all;

2.shows the current retention policy.

RMAN> show retention policy;

3.shows the default device type configured for backups.

RMAN>show default device type;

4.shows the number of backup copies available in the target database.

RMAN> show datafile backup copies;

Configuration..

1.To how many days the backup copies need to be retained. 

RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY2;

2.Retention policy to the default value of 1 day

RMAN> CONFIGURE RETENTION POLICY CLEAR;

3.Optimization option to the default value.

RMAN>CONFIGURE BACKUP OPTIMIZATION CLEAR;

4.identical files are NOT backed up to the device specified.

RMAN>CONFIGURE BACKUP OPTIMIZATION ON;

BACKUP COMMAND

1.backs up the database (target).

RMAN> backup database;

2.backs up the target database and the current control file and archive log files.

RMAN> backup database include current controlfile plus archive log;

1.reports which backups can be deleted.

RMAN> report obsolete;

1.deletes all the backups that are older based on the retention policy setup.

RMAN> delete obsolete;

LIST COMMAND

1. First of all we may LIST our Rman backup sets by following command. 

RMAN> list backup;

   2.If we want to list backup of individul database we use

RMAN> list backup of database;

 

  3.List only backup set proxy copies

RMAN> list backupset;

 

4.List image copies and archivelog

RMAN>list copy;

 5.lists out all the files that are in the backup that belong to the tablespace ‘system’.

RMAN> list backup of tablespace system;

6.If backup deleted/moved by OS we can use

RMAN>list expired backup;

7.to list by file or some specific(control) file

RMAN>list backup by file;

RMAN>list backup of control file;

8.We can list summary of copies, proxy and sets

RMAN>list backup summary;

9.To list incarnation backup

RMAN>list incarnation;

10.Also to list all database

RMAN>list backup of database;