changing the instance name for single instance

To change the instance name:

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             310379748 bytes

Database Buffers          100663296 bytes

Redo Buffers                6066176 bytes

Database mounted.

Database opened.

SQL> spool C:\Users\jayva\Desktop\work\oracle_sid_change\sid.txt

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      target

SQL> show parameter instance_name

NAME                                 TYPE        VALUE

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

instance_name                        string      target

SQL> create pfile='C:\Users\jayva\Desktop\work\oracle_sid_change\pfile.ora' from

 spfile;

File created.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Alter the instance_name parameter in newly created pfile and also creating the new service (using oradim utility).

C:\Windows\system32>oradim -new -sid target1

Instance created.

C:\Users\jayva>set ORACLE_SID=target1

Now starting the database using altered pfile.

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 26 19:56:11 2012

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

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile=C:\Users\jayva\Desktop\work\oracle_sid_change\pfile.ora

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             281019620 bytes

Database Buffers          130023424 bytes

Redo Buffers                6066176 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      target

SQL> show parameter instance_name

NAME                                 TYPE        VALUE

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

instance_name                        string      target1

SQL>

Here we go, instance name is change to target1 and database name is target. 

Install 11.2.0.2 from patch set.. no base version required

Helllo DBA's. wanna share one great news. 


From 11.2.0.2, you can directly download patchset and install (I just installed for freshing server). 


No need to install the base version..


Oracle id: 10098816

RDA test run

“rda.sh -T hcve” is the script to testrun for RDA.

thunder:(infoddp)/home/oracle/rda/rda > rda.sh -T hcve

Processing HCVE tests ...

Available Pre-Installation Rule Sets:

   1. Oracle Database 10g R1 (10.1.0) Preinstall (HP-UX)

   2. Oracle Database 10g R2 (10.2.0) Preinstall (HPUX)

   3. Oracle Database 11g R1 (11.1.0) Preinstall (HPUX)

   4. Oracle Database 11g R2 (11.2.0) Preinstall (HPUX)

   5. Oracle Application Server 10g (9.0.4) Preinstall (HP-UX)

   6. Oracle Application Server 10g R2 (10.1.2) Preinstall (HP-UX)

   7. Oracle Fusion Middleware 11g R1 (11.1.1) Preinstall (HP-UX)

   8. Oracle Portal Preinstall (Generic)

   9. Oracle Identity Management 10g (10.1.4) Preinstall (HP-UX)

  10. Oracle E-Business Suite Release 12 (12.1.1) Preinstall (HP-UX)

Available Post-Installation Rule Sets:

  11. Oracle Portal Postinstall (generic)

  12. Data Guard Postinstall (Generic)

Enter the HCVE rule set number

Hit 'Return' to accept the default (1)

> 

Enter value for < Planned ORACLE_HOME location >

Hit 'Return' to accept the default (/u01/infoddp/product/11.2.0/db)

> 

Test "Oracle Database 10g (10.1.0) Preinstall(HP-UX)" executed at 16-Apr-2012 10:06:45

Test Results

~~~~~~~~~~~~

ID     NAME                 RESULT  VALUE

====== ==================== ======= ==========================================

A00010 OS Certified?        PASSED  HP-UX Itanium 11.31 64 bit is certifi..>

A00020 User in ``/etc/passw PASSED  userOK

A00030 Group in ``/etc/grou PASSED  GroupOK

A00040 Enter ORACLE_HOME    RECORD  /u01/infoddp/product/11.2.0/db

A00050 ORACLE_HOME Valid?   PASSED  OHexists

A00060 O_H Permissions OK?  PASSED  CorrectPerms

A00070 Umask Set to 022?    PASSED  UmaskOK

A00080 LDLIBRARYPATH Unset? FAILED  IsSet

A00090 SHLIB_PATH Unset?    PASSED  UnSet

A00100 Other O_Hs in PATH?  PASSED  NoneFound

A00110 oraInventory Permiss PASSED  oraInventoryOK

A00120 /tmp Adequate?       PASSED  TempSpaceOK

A00130 Swap (in MB)         RECORD  95072

A00140 RAM (in MB)          FAILED  0

A00150 SwapToRam OK?        FAILED  RamNotDetermined

A00160 Disk Space OK?       PASSED  DiskSpaceOK

A00170 Kernel Parameters OK FAILED  [SEMVMX=32767] too small

A00180 Got ld,nm,ar,make?   PASSED  ld_nm_ar_make_found

A00190 ulimits OK?          FAILED  CoreTooSmall

A00200 OS Packages?         SKIPPED NotChecked

A00210 OS Patches?          PASSED  OSpatchesFound

A00220 Other OUI Up?        PASSED  NoOtherOUI

Result file: /home/oracle/rda/rda/output/RDA_HCVE_A200DB10R1_hp_res.htm

thunder:(infoddp)/home/oracle/rda/rda >

Dynamics views for SGA

V$SGA ---- Displays summary information about the system global area (SGA).

V$SGAINFO ---- Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.

V$SGASTAT ---- Displays detailed information about the SGA.

V$SGA_DYNAMIC_COMPONENTS --- Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.

V$SGA_DYNAMIC_FREE_MEMORY -----Displays information about the amount of SGA memory available for future dynamic SGA resize operations.

V$SGA_RESIZE_OPS ---- Displays information about the last 400 completed SGA resize operations.

V$SGA_CURRENT_RESIZE_OPS --- Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.

V$SGA_TARGET_ADVICE ---- Displays information that helps you tune SGA_TARGET. For more information, see Oracle Database Performance Tuning Guide.

set lin 180

col component format A50

select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME

from v$sga_resize_ops where status='ERROR'

order by start_time, component

col current_size for 999,999,999,999 head "Current Size"

col min_size for 999,999,999,999 head "Min Size"

col max_size for 999,999,999,999 head "Max Size"

select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,USER_SPECIFIED_SIZE from V$SGA_DYNAMIC_COMPONENTS order by COMPONENT;

using md_backup & md_restore

Oracle backup utility RMAN only provide the backup for oracle data file but it won’t back up the ASM meta data. Thus in loss asm disk group, rman won’t help to restore the asm disk group. 

Oracle has provided mb_backup and md_restore commands to backup and restore the asm meta data.

We will practice how it works.

So now, connecting with the asm instance and creating new disk group.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

SQL> CREATE DISKGROUP DATA_REPORTING NORMAL REDUNDANCY DISK

  2   '/dev/raw/raw7' name disk5 ,

  3  '/dev/raw/raw8' name disk6 force;

Diskgroup created.

SQL>

SQL> select NAME, TOTAL_MB, FREE_MB from V$ASM_DISKGROUP;

NAME                                    TOTAL_MB    FREE_MB

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

DATA                                          4000               1068

INDX                                             0                     0

DATA_REPORTING                     2000               1898

Now connecting with the database instance, we will create tablespace on this newly created disk group and also create new table on this tablespace.

SQL> create tablespace data_reporting datafile '+DATA_REPORTING';

Tablespace created.

SQL> select NAME from v$datafile;

NAME

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

+DATA/oraw/datafile/system.256.744936879

+DATA/oraw/datafile/sysaux.257.744936887

+DATA/oraw/datafile/undotbs1.258.744936889

+DATA/oraw/datafile/users.259.744936891

+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855

SQL> create table reporting(ID NUMBER , REP_DATE DATE) TABLESPACE DATA_REPORTING;

Table created.

SQL> insert into reporting values (1, sysdate);

1 row created.

SQL> insert into reporting values (2, sysdate);

1 row created.

SQL> commit;

Commit complete.

Elapsed: 00:00:00.57

SQL> select * from reporting;

        ID REP_DATE

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

         1 29-03-2012 18:55:04

         2 29-03-2012 18:56:22

Elapsed: 00:00:00.36

SQL>

Now connecting with the asmcmd (it is the command line utility to manage asm), we will do backup for this newly created diskgroup.

ASMCMD>

ASMCMD>  md_backup -b /tmp/asmbkp_data_reporting -G 'DATA_REPORTING'

WARNING:option 'b' is deprecated for 'md_backup'

Disk group metadata to be backed up: DATA_REPORTING

Current alias directory path: ORAW/DATAFILE

Current alias directory path: ORAW

ASMCMD>

Also to perform complete recovery, we are taking full rman backup.

[oracle@wissem ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 5 19:13:05 2012

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

RMAN> connect target sys

target database Password:

connected to target database: ORAW (DBID=3426823299)

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Starting backup at 05-MAR-11

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=744953697

channel ORA_DISK_1: starting piece 1 at 29-MAR-12

channel ORA_DISK_1: finished piece 1 at 29-MAR-12

piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_annnn_TAG20110305T033502_6q38brxq_.bkp tag=TAG20110305T033502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16

Finished backup at 29-MAR-12

Starting backup at 29-MAR-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/oraw/datafile/system.256.744936879

input datafile file number=00002 name=+DATA/oraw/datafile/sysaux.257.744936887

input datafile file number=00005 name=+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855

input datafile file number=00003 name=+DATA/oraw/datafile/undotbs1.258.744936889

input datafile file number=00004 name=+DATA/oraw/datafile/users.259.744936891

channel ORA_DISK_1: starting piece 1 at 29-MAR-12

channel ORA_DISK_1: finished piece 1 at 29-MAR-12

piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp tag=TAG20110305T033522 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:07:54

Finished backup at 29-MAR-12

Starting backup at 29-MAR-12

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=4 RECID=2 STAMP=744954215

channel ORA_DISK_1: starting piece 1 at 29-MAR-12

channel ORA_DISK_1: finished piece 1 at 29-MAR-12

piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_annnn_TAG20110305T034337_6q38tto8_.bkp tag=TAG20110305T034337 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 29-MAR-12

Starting Control File and SPFILE Autobackup at 29-MAR-12

piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/autobackup/2011_03_05/o1_mf_s_744954221_6q38v2xk_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 29-MAR-12

RMAN>

Now, we have disk group metadata backup (md_backup) and database backup (rman). So now connecting with the asm instance, we will drop this disk group.

SQL> alter diskgroup DATA_REPORTING DISMOUNT FORCE;

Diskgroup altered.

SQL> drop diskgroup DATA_REPORTING FORCE INCLUDING CONTENTS;

Diskgroup dropped.

Dropping if the disk group, crash the instance as it contains the datafile for tablespace data_reporting.

SQL>

Database instance:

SQL> select * from reporting;

select * from reporting

              *

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL>

So now, starting the recovery phase we will restore the diskgroup metadata using md_restore command.

[oracle@wissem ~]$ asmcmd

ASMCMD> md_restore /tmp/asmbkp_data_reporting -t full -g DATA_REPORTING

WARNING:option 'g' is deprecated for 'md_restore'

please use 'G'

WARNING:option 't' is deprecated for 'md_restore'

please use 'full|nodg|newdg'

Current Diskgroup metadata being restored: DATA_REPORTING

Diskgroup DATA_REPORTING created!

System template ONLINELOG modified!

System template AUTOBACKUP modified!

System template ASMPARAMETERFILE modified!

System template OCRFILE modified!

System template ASM_STALE modified!

System template OCRBACKUP modified!

System template PARAMETERFILE modified!

System template ASMPARAMETERBAKFILE modified!

System template FLASHFILE modified!

System template XTRANSPORT modified!

System template DATAGUARDCONFIG modified!

System template TEMPFILE modified!

System template ARCHIVELOG modified!

System template CONTROLFILE modified!

System template DUMPSET modified!

System template BACKUPSET modified!

System template FLASHBACK modified!

System template DATAFILE modified!

System template CHANGETRACKING modified!

Directory +DATA_REPORTING/ORAW re-created!

Directory +DATA_REPORTING/ORAW/DATAFILE re-created!

ASMCMD>

Confirming the restoration.

SQL> select NAME, TOTAL_MB, FREE_MB from V$ASM_DISKGROUP;

NAME                             TOTAL_MB    FREE_MB

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

DATA                                 4000             1068

INDX                                      0                   0

DATA_REPORTING            2000             1898

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Now since disk group is restore, we can use RMAN to restore the datafile.

[oracle@wissem ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mar 29 19:29:31 2012

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

RMAN> connect target sys

target database Password:

connected to target database: ORAW (DBID=3426823299, not open)

RMAN> restore database;

Starting restore at 29-MAR-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA/oraw/datafile/system.256.744936879

channel ORA_DISK_1: restoring datafile 00002 to +DATA/oraw/datafile/sysaux.257.744936887

channel ORA_DISK_1: restoring datafile 00003 to +DATA/oraw/datafile/undotbs1.258.744936889

channel ORA_DISK_1: restoring datafile 00004 to +DATA/oraw/datafile/users.259.744936891

channel ORA_DISK_1: restoring datafile 00005 to +DATA/oraw/datafile/data_reporting.266.744955249

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp tag=TAG20110305T033522

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:07:42

Finished restore at 29-MAR-12

RMAN> recover database;

Starting recover at 29-MAR-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:29

Finished recover at 29-MAR-12

From above, note that RMAN restored the DATA_REORTING datafie to +DATA diskgroup.

But, we would like to restore the DATA_REORTING datafie to +DATA_REPORTING diskgroup as it was before the failure.

Also note that the restored datafile is in offline state:

SQL> SELECT FILE_NAME, TABLESPACE_NAME, ONLINE_STATUS from DBa_DATA_FILES;

FILE_NAME

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

TABLESPACE_NAME                ONLINE_

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

+DATA/oraw/datafile/users.259.744936891

USERS                          ONLINE

+DATA/oraw/datafile/undotbs1.258.744936889

UNDOTBS1                       ONLINE

+DATA/oraw/datafile/sysaux.257.744936887

SYSAUX                         ONLINE

FILE_NAME

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

TABLESPACE_NAME                ONLINE_

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

+DATA/oraw/datafile/system.256.744936879

SYSTEM                         SYSTEM

+DATA/oraw/datafile/data_reporting.266.744955249

DATA_REPORTING                 OFFLINE

SQL> ALTER DATABASE OPEN;

Database altered.

Confirming for the data restoration by querying the newly created table.

SQL> select * from reporting;

        ID REP_DATE

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

         1 29-MAR-12

         2 29-MAR-12

SQL>

Regards,
Jay Vardhan

jayvardhancs@gmail.com

I/O information for datafiles

extract how much I/O is performed on datafiles.

set lines 180

col name format A50

select   NAME,

  PHYRDS "Physical Reads",

  round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",

  PHYWRTS "Physical Writes",

  round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",

  fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"

from (

  select   sum(PHYRDS) PHYS_READS,

    sum(PHYWRTS) PHYS_WRTS

  from    v$filestat

  ) pd,

  v$datafile df,

  v$filestat fs

where   df.FILE# = fs.FILE#

order   by fs.PHYBLKRD+fs.PHYBLKWRT desc;

Sample output:

NAME                                               Physical Reads     Read % Physical Writes    Write % Total Block I/O's

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

/p07/oradata/padwsdpr/SGDATA_PART_DATA_03.dbf            46817222        4.2          526942        .66         242239138

/p07/oradata/padwsdpr/SGDATA_PART_DATA_01.dbf            45504990       4.09          570122        .72         227943429

/p03/oradata/padwsdpr/SGDATA_PART_DATA_02.dbf            48710514       4.37          547700        .69         226105668

/p03/oradata/padwsdpr/SGDATA_PART_DATA_08.dbf            39217397       3.52          503078        .63         208712510

/p07/oradata/padwsdpr/SGDATA_PART_DATA_09.dbf            40487221       3.64          487099        .61         204213299

Recovery Process Flow of Physical Standby

The Actual Recovery Process Flow of Physical Standby (10gR2 & 11G)

Story is to present the step by step process how recovery is performed at standby side.

Let’s start the journey with important background process:

From Primary side :-

ORL: Online redo log file. Any changes happen, it is captured and store in redo log file that is very frequently also called ORL.

LGWR: The log writer process flushes the data from log buffer (SGA) to ORL.

LNS: The Logwritter Network Services which read the redo being flushed from the log buffer and send over the network to standby site.

ARCH: The Archiver process the archive the ORL.

From Standby Side:-

RFS: Remote file server process and its main objective is to receive the data send by LNS process and write into the Standby redo logfile.

MRP: The managed recovery process and it work for the media recovery management.

PROx: Works as recovery process.

Objective is to focus on how data is received and it is applied to the standby. I will try to cover the step by when replicated data is written in datafiles for standby database.

The actual Recovery process done in three phases:

As data is collected by the LNS process and send over the network. Where it is recoved by the RFS process and written down to standby redo logfile. So upto now, the replicated data or better say changes are present in standby redo logfile.

Log red phase: The background process MRP will keep reading the SRL asynchronously. Many times it also read from archived log files when recovery falls behind the real time apply. The blocks that require the redo apply (changes) and parsed and placed into the memory.

Redo Apply phase: The MRP process transfer redo data to the recovery slaves process using the parallel query process. the Parallel media recovery (PMR) causes the required data block to be read into the buffer cache and apply redo changes to the buffer in buffer cache.

Checkpoint phase: This is the last step performed in recovery phase. It involves the flushing the recently modified data to the disk and also update the datafile header to record checkpoint completion.

Real Time Apply: So in brief redo is received by RFS on the standby system, The RFS process writes the redo to SRL and thus changes where directly captured and applied. This is also called as Real Time Apply in terms of standby database.

Following command is needed to start RTA (Real Time Apply) that is to be executed at sql prompt.

alter database recover managed standby database using current logfile disconnect;

Below can be used to check where RTA is started that is to be executed at Primary side.

select recovery_mode from v$archive_dest_status where dest_id=2;