Monday, November 26, 2012

DBA AUDIT TRAIL

col USERNAME for a10
col OS_USERNAME for a10
col USERHOST for a15
col ACTION_NAME for a11
set pages 200 lines 100
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
select USERNAME,OS_USERNAME,USERHOST,TIMESTAMP,ACTION_NAME,LOGOFF_TIME from dba_audit_trail where username='&username' order by 3;


Temp Usage

Temp Usage
----------

select TABLESPACE_NAME,total_blocks*to_number(value)/1024/1024 total_MB
        ,used_blocks*to_number(value)/1024/1024 used_MB
        ,free_blocks*to_number(value)/1024/1024 free_MB
        ,max_used_blocks*to_number(value)/1024/1024 max_used_MB
        ,max_blocks*to_number(value)/1024/1024 max_MB
        ,max_sort_blocks*to_number(value)/1024/1024 max_Indv_sort_MB
from v$sort_segment,v$parameter where name='db_block_size';

----------------------------------------------------------
v$temp_space_header instead of dba_free_space;

SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS bytes_used_mb,
SUM(bytes_free)/1024/1024 as bytes_free_mb FROM   V$temp_space_header GROUP  BY
tablespace_name;

select * from v$temp_space_header;

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

To report true free space within the used portion of the TEMPFILE:

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;



How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.

All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.

If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT

From Oracle 8, one can just query SYS.v$sort_usage. Look at these examples:

select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from   sys.v_$session s, sys.v_$sort_usage u
where  s.saddr = u.session_addr
/

select s.osuser, s.process, s.username, s.serial#,
       sum(u.blocks)*vp.value/1024 sort_size
from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where  s.saddr = u.session_addr
  and  vp.name = 'db_block_size'
  and  s.osuser like '&1'
group  by s.osuser, s.process, s.username, s.serial#, vp.value
/


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

select CURRENT_USERS,TOTAL_BLOCKS,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,
MAX_USED_SIZE,MAX_USED_BLOCKS from  v$sort_segment;


_-------------------------------------------------------------------------------------

First, you need to find out if your applications need all the space which they are using for the temp, and tune as appropriate.

Second, you may have falled into the trap of using OEM or some other such tool which does not show the 'true' space available in the temp tablespace. Such tools only show the maximum space ever allocated, not the current space allocated (or rather segments). A tool such as DB Console or Grid will show at any point in time the 'true' or current segments allocated. You can also use the scripts below:

SELECT SUM (u.blocks * blk.block_size) / 1024 / 1024 "Mb. in sort segments"
, (hwm.MAX * blk.block_size) / 1024 / 1024 "Mb. High Water Mark"
FROM v$sort_usage u
, (SELECT block_size
FROM DBA_TABLESPACES
WHERE CONTENTS = 'TEMPORARY') blk
, (SELECT segblk# + blocks MAX
FROM v$sort_usage
WHERE segblk# = (SELECT MAX (segblk#)
FROM v$sort_usage)) hwm
GROUP BY hwm.MAX * blk.block_size / 1024 / 1024;

Temp_Status
-----------

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off

COLUMN tablespace_name       FORMAT a18               HEAD 'Tablespace Name'
COLUMN tablespace_status     FORMAT a9                HEAD 'Status'
COLUMN tablespace_size       FORMAT 999,999,999,999   HEAD 'Size'
COLUMN used                  FORMAT 999,999,999,999   HEAD 'Used'
COLUMN used_pct              FORMAT 999               HEAD 'Pct. Used'
COLUMN current_users         FORMAT 9,999             HEAD 'Current Users'

SELECT
    d.tablespace_name                      tablespace_name
  , d.status                               tablespace_status
  , NVL(a.bytes, 0)                        tablespace_size
  , NVL(t.bytes, 0)                        used
  , TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct
  , NVL(s.current_users, 0)                current_users
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
  , v$sort_segment  s
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = s.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
/

Saturday, October 20, 2012

Steps - Blackout OS level

--.>To know blockout status:
. oemagent  (connecting to oem env file)
>emctl status blackout

---> complete server blackout
. oemagent
>emctl start blackout "maintenance" -nodelevel    ----> To start blackout
-- emctl stop blackout "maintenance"   ----> To stop blackout
maintenance --- Blackout name
-->To know the perticular db is configured or not in OEM
. oemagent
>emctl config agent listtargets

[xxxx, oracle_database]
....
....

-->To start perticular database blackout
>emctl start blackout "<name>" xxxx:oracle_database  ### to start blackout
>emctl status blackout "<name>"

--> To stop blackout
> emctl stop blackout "<name>"

Steps - OEM Blackout


OEM Blackouts from the GUI and Command Line

Oracle Enterprise Manager provides you with the ability to monitor your environments and alert you once specified thresholds have been reached.  Blackouts allow you to suspend monitoring so you do not get notified.   This is useful for scheduled maintenance windows, such as cold backups, where the application and/or database may not be available.

As well, blackouts also suspends data collection for the given targets.  This means that certain metrics such as availability will not be affected.

To create a blackout from the GUI, login to Enterprise Manager, navigate to the target you would like to blackout and at the bottom of the page under Related Links you will see a Blackouts link.  You will be brought to the following page:



clip_image002

On the screen above you can view any blackouts that may currently be in effect as well as create new ones.  To create a new blackout click on the create button.

clip_image002[8]

On this page you can create a name for the blackout, with the default being “Blackout-<timestamp>”.  You can also select the targets you wish to set the blackout for.   As you can see from the screenshot I am going to set a blackout for an infrastructure application server (infra10g).

You can also provide a reason for the blackout by click on the Reason drop down list.  Quite a few are available, from Server Bounce to Security Patch.    Jobs can be disabled by deselecting the Run jobs during the blackout checkbox.  If your applying a security patch then you may not want a scheduled backup to run as it will either error or cause problems.

clip_image002[10]

The next screen allows you to select which components within the target will be blacked out.  I selected a full blackout but you can select certain members if the outage will only affect specific components.

clip_image002[12]

This screen allows you to schedule the blackout.  It can either start immediately or you can choose a date along with a duration.  Blackouts can be repeating as well, so you only have to create one for that monthly maintenance window for example.

clip_image002[14]

The last screen provides a summary and once you have finished reviewing click on the Finish button.

You can also set blackouts from the command line, which is useful if you have some maintenance scripts which are not executed from Enterprise Managers job system.   I’ve only tested this on linux but it should be the same for windows.

To set a blackout for a list of targets:

emctl start blackout <Blackoutname> [<Target_name>[:<Target_Type>]]…. [-d Duration]

To set a blackout for all targets on a host:

emctl start blackout <Blackoutname> [-nodeLevel] [-d <Duration>]

-nodeLevel tells the agent to stop monitoring all targets on the server.

-d Duration allows you to set a duration in the format of [days] hh:mm.   ex. 1 02:05 means the blackout will last for 1 day, 2 hours and 5 minutes.

For example, to use this in a script in which all targets will be unavailable you would start a blackout at the beginning of the script and stop it at the end:

cd $AGENT_HOME/bin
./emctl start blackout alltargets-myserver –nodeLevel
<Perform Maintenance Tasks>
cd $AGENT_HOME/bin
./emctl stop blackout alltargets-myserver


Troubleshooting

In case you hit issues with blackouts take a look at the following notes:

Subject:     Agent Blackout Initiated By Emctl Command Not Ending      Doc ID:     559577.1

Subject:     EMDiagkit Download and Master Index   Doc ID:     421053.1

Subject:     How to Troubleshoot the EM 10gR1 Blackout Sub-system      Doc ID:     284024.1

Subject:     Troubleshooting Blackouts in EM 10g Grid Control using EMDiag Kit      Doc ID:     300671.1

They provide alot of information and solutions to different scenarios.   I hit an issue over the weekend in which the blackout didn’t end properly.  When I tried to stop it from the command line:

[oracle@myserver ~]$ /u01/app/oracle/product/agent10g/bin/emctl stop blackout alltargets-myserver
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Blackout stop Error : Blackout name alltargets-myserver is invalid


When trying to end the blackout via Enterprise Manager:

Error stopping the blackout on "infra10g": ORA-20710: Agent-side blackouts cannot be edited or stopped ORA-06512: at "SYSMAN.MGMT_BLACKOUT_ENGINE", line 501 ORA-06512: at "SYSMAN.MGMT_BLACKOUT_ENGINE", line 3262 ORA-06512: at "SYSMAN.MGMT_BLACKOUT", line 74 ORA-06512: at "SYSMAN.MGMT_BLACKOUT_UI", line 1167 ORA-06512: at line 1 .

To fix this problem I performed the following:
1. Shutdown the agent on the target server myserver
2. Removed the blackouts.xml file under $AGENT_HOME/sysman/emd
3. Used note 421053.1 to install the EMDiag kit
4. Logged in as sysman on the Enterprise Managers repository database and executed the following query:

select blackout_guid, blackout_name
from mgmt_blackouts;


BLACKOUT_GUID                    BLACKOUT_NAME
-------------------------------- ----------------------
30E2956CA329F0E59FBDF50951F2578E alltargets-myserver


5. Then executed:

exec mgmt_diag.KillBlackout(HEXTORAW(‘30E2956CA329F0E59FBDF50951F2578E’));

6. Restarted the agent on myserver and when I looked in Enterprise manager the blackout had cleared.

I have seen the command used above for some other scenarios but not this one specifically.   Before executing any commands in your environment please test first.

Server reboot steps - Data guard

Server reboot steps :
--------------------
-->If its DG setep or normal db, following steps mandatroy just take backup of follwoing three steps and store it in separate notepad. once server reboot
   activity completed croos check once which was taken before server reboot.
##########################################################
step 1:  ps -ef|grep pmon 
            ps -ef|grep pmon|wc -l
step 2 : ps -ef|grep inh (OR) ps -ef|grep tns
            ps -ef|grep inh|wc -l
step 3 : df -h 
#############################################################
server reboot time database side need to  check the below commands:
(Before server reboot and after serevr boot, if its is DG database)
-------------------------------------------------------------------
SQL> select name,db_unique_name,database_role,controlfile_type,CREATED from v$database;
SQL >select sequence#,first_time,next_time,completion_time,applied from v$archived_log where applied <> 'YES' and DEST_ID !=0 and status!='D'and completion_time <(sysdate-1/48) Order By 1;
no rows selected----->in sync
SQL >select process, status ,sequence# from v$managed_standby;
MRP0---->process should reflect
===============================
Data Guard Db Startup Procedure
===============================
sqlplus “/ as sysdba”
startup nomount;
alter database mount standby database;
recover managed standby database disconnect from session; ----------->To put it in MRM MOD
exit;
=================================
Data Guard Db  Shutdown Procedure
=================================
Login as oracle
source the environment
sqlplus “/ as sysdba”
alter database recover managed standby database cancel; ------> to cancel MRM mode
shutdown immediate;
exit

Tuesday, October 9, 2012

Backup - PACKAGE



set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE NAME.pkb
select text from dba_source where name='<PACKAGE NAME>' and type='PACKAGE BODY';
spool off

set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE NAME.pks
select text from dba_source where name='<PACKAGE NAME>' and type='PACKAGE';
spool off

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

RMAN COLD BACKUP script:
========================
sql> shutdown immediate;
sql> startup mount
$ rman target /
rman> RUN
 {
 ALLOCATE CHANNEL CH1 DEVICE TYPE DISK
 FORMAT '/s029/so/backup/oracle/gisq1_cold_bkp/%d_COLD_%M%D%Y_%p_%s'
 ;
 ALLOCATE CHANNEL CH2 DEVICE TYPE DISK
 FORMAT '/s029/so/backup/oracle/gisq1_cold_bkp/%d_COLD_%M%D%Y_%p_%s'
 ;
 ALLOCATE CHANNEL CH3 DEVICE TYPE DISK
 FORMAT '/s029/so/backup/oracle/gisq1_cold_bkp/%d_COLD_%M%D%Y_%p_%s'
 ;
 ALLOCATE CHANNEL CH4 DEVICE TYPE DISK
 FORMAT '/s029/so/backup/oracle/gisq1_cold_bkp/%d_COLD_%M%D%Y_%p_%s'
 ;
 BACKUP
 AS COMPRESSED BACKUPSET
 FULL
 DATABASE
 TAG ent1_COLD_0511_1039
 ;
 BACKUP FORMAT '/s029/so/backup/oracle/gisq1_cold_bkp/%d_%M_%D_%Y_%t.ctl'
 CURRENT CONTROLFILE
 TAG gisq1_CONTROLFILE_0511_1039
 ;
 }
> sql 'alter database open';
RMAN HOT BACKUP SCRIPT:
=======================
rman msglog /atpwecq1/dbdata/data1/workorders/rman/rman_backup.log <<EOF
connect target /
RUN
{
  ALLOCATE CHANNEL ch00 DEVICE TYPE DISK FORMAT '/atpwecq1/dbdata/data1/workorders/rman/%d_db_u%u_s%s_p%p_t%t';
  ALLOCATE CHANNEL ch01 DEVICE TYPE DISK FORMAT '/atpwecq1/dbdata/data1/workorders/rman/%d_db_u%u_s%s_p%p_t%t';
  backup as compressed backupset full database TAG 'evnmond1';
  RELEASE CHANNEL ch00;
  RELEASE CHANNEL ch01;
  sql "alter system archive log current";
  backup as compressed backupset archivelog all FORMAT '/atpwecq1/dbdata/data1/workorders/rman/arch-s%s-p%p-t%t'  TAG 'evnmond1';
BACKUP FORMAT '/atpwecq1/dbdata/data1/workorders/rman/bk_u%u_s%s_p%p_t%t.ctl' CURRENT CONTROLFILE;
}
exit;
EOF

Thursday, August 2, 2012

BLOGS I FOLLOW


http://taliphakanozturken.wordpress.com/step-by-step-oracle/
http://www.dba-oracle.com/
http://kamranagayev.com/
http://oracledba.org/ 
http://dbakevin.blogspot.mx/
 
http://www.oracle.com/technetwork/database/features/availability/298772-132349.pdf
http://kb.dbatoolz.com/tp/1174.how_to_drop_a_lost_tempfile_after_failed_drive.html
http://www.dbvisit.com/forums/showthread.php?t=331
http://www.gavinsoorma.com/2009/06/monitor-space-used-in-asm-disk-groups/
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTOC69/Default.aspx
http://fatihacar.com/blog/delete-archivelog-using-rman-in-oracle/
http://web.njit.edu/info/oracle/Doc/backup.102/b14191/rcmbackp009.htm
https://blogs.oracle.com/sysdba/entry/rman_backup_error_what_to_do_i
http://cs.felk.cvut.cz/10gr2/server.102/b14196/asm004.htm#CIAFIGDB
http://dba-expert.blogspot.hk/2011/05/rman-script-backing-up-all-archivelog.html
http://docs.oracle.com/cd/B19306_01/backup.102/b14192/maint003.htm
http://andunix.net/info/oracle/rman/
http://www.oraclelabspace.blogspot.hk/2012/06/opatch-error-opatch-failed-with-error.html
http://oracle-database-tips.com/kill_oracle_processes.html
http://www.runningoracle.com/product_info.php?products

http://dbaregistry.blogspot.hk/2010/02/how-to-change-table-name-during-import.html

ASM

How can the ASM instance start when the spfile is located on a diskgroup
http://oracletechlovers.blogspot.hk/2010/09/how-can-asm-instance-start-when-spfile.html

RAC

11gR2 RAC Architecture Picture - Theory
http://uhesse.com/2011/11/18/11gr2-rac-architecture-picture/

DATAGUARD

How to recover Standby database from a missing archivelog
http://dbaregistry.blogspot.hk/2010/01/how-to-recover-standby-database-from.html


Data Guard failover with flashback
http://dbaregistry.blogspot.hk/2010/01/datagurad-failover-with-flashback.html

Oracle Database 11g: New Features in DataGuard
http://dbaregistry.blogspot.hk/2010/01/oracle-database-11g-new-features-in_26.html

RMAN

Delete Archivelog files without using RMAN
http://www.oracleracexpert.com/2009/08/delete-archivelog-files-without-using.html

Delete archive logs using RMAN.
http://www.oracleracexpert.com/2009/08/delete-archive-logs-using-rman.html

Restore validate using RMAN
http://www.oracleracexpert.com/2009/08/restore-validate-using-rman.html

How to validate RMAN backup
http://www.oracleracexpert.com/2009/08/how-to-validate-rman-backup.html

Block Recovery Using RMAN
http://www.oracleracexpert.com/2009/08/block-recovery-using-rman.html

Block Corruption and Recovery
http://www.oracleracexpert.com/2009/08/block-corruption-and-recovery.html


RMAN - RESTOREReorganizing Tables in Oracle – is it worth the effort?
http://uhesse.com/2010/08/23/reorganizing-tables-in-oracle-is-it-worth-the-effort/

Restore controlfile from RMAN Controlfile auto backup
http://oracleinstance.blogspot.hk/2010/08/restore-lost-control-file-using-rman.html

Restore the server parameter file(spfile) from RMAN backup
http://oracleinstance.blogspot.hk/2010/08/restore-server-parameter-file-spfile.html


Redo log corruption and Recovery
http://www.oracleracexpert.com/2009/08/redo-log-corruption-and-recovery.html


SCRIPTS

http://ashok-linux-tips.blogspot.com/feeds/_id=265/posts/default



 

"Hope for the best, prepare for the worst"
-Dharmendra

Saturday, July 14, 2012

THEORY - UPGRADING


Start the instance by issuing the following command:
SQL> STARTUP UPGRADE
Note:
The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBAsessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.
You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.

What is the difference between startup Upgrade and Migrate ?

startup migrate:
---------------
Used to upgrade a database till 9i.

Startup Upgrade
---------------
From 10G  we are using startup upgrade to upgrade database.

What happens internally when you use startup upgrade/migrate?
It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothely.
in other way..it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues.

REFERENCE:

Oracle® Database Upgrade Guide 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm


THEORY - DATA GUARD

Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.


DG Broker
“it is the management and monitoring tool”
Oracle dataguard broker is a distributed management framework that automates and centralizes the creation , maintenance and monitoring of DG configuration.
All management operations can be performed either through OEM, which uses the broker (or)  broker specified command-line tool interface “DGMGRL”


Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other.


Dataguard Architecture
The Oracle 9i Data Guard architecture incorporates the following items:
• Primary Database - A production database that is used to create standby databases. The archive logs from the primary database are transfered and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
• Standby Database - A replica of the primary database.
• Log Transport Services - Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.
• Network Configuration - The primary database is connected to one or more standby databases using      Oracle Net.
• Log Apply Services - Apply the archived redo logs to the standby database. The Managed Recovery      Process (MRP) actually does the work of maintaining and applying the archived redo logs.
• Role Management Services - Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.
• Data Guard Broker - Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.


Primary Database:
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.

Standby Database:
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
The types of standby databases are as follows:

Physical standby database:
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

Logical standby database:
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.


The services required on the primary database are:
• Log Writer Process (LGWR) - Collects redo information and updates the online redo logs. It can also      create local archived redo logs and transmit online redo to standby databases.
• Archiver Process (ARCn) - One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
• Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .


The services required on the standby database are:
• Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
• Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.
• Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).
• Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.


11g  Active Data Guard
Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production database.
Oracle Active Data Guard also enables the use of fast incremental backups when offloading backups to a standby database, and can provide additional benefits of high availability and disaster protection against planned or unplanned outages at the production site.

11g Snapshot Standby Database
Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database.
We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production database and then finally revert it to  it’s earlier state as a physical standby database.
While the snapshot standby database is open in read-write mode, redo is being received from the primary database, but is not applied.
After converting it back to a physical standby database, it is resynchronized with the primary by applying the accumalated redo data which was earlier shipped from the primary database but not applied.
Using a snapshot standby, we are able to do real time application testing using near real time production data. Very often we are required to do production clones for the purpose of testing. But using snapshot standby databases we can meet the same requirement sparing the effort,time,resources and disk space.
REFERENCE:
http://gavinsoorma.com/2009/07/11g-snapshot-standby-database/




Snapshot Standby Database (UPDATEABLE SNAPSHOT FOR TESTING)
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.
Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.

REFERENCE:
http://docs.oracle.com/cd/B28359_01/server.111/b28294/title.htm


DG Vs. STANDBY database
http://neeraj-dba.blogspot.in/2011/06/difference-between-dataguard-and.html
Dataguard  :
Dataguard is mechanism/tool to maintain standby database.
The dataguard is set up between primary and standby instance .
Data Guard is only available on Enterprise Edition.

Standby Database :
Physical standby database provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis.
Standby capability is available on Standard Edition.


Data Guard Protection Modes
This section describes the Data Guard protection modes.
In these descriptions, a synchronized standby database is meant to be one that meets the minimum requirements of the configured data protection mode and that does not have a redo gap. Redo gaps are discussed in Section 6.3.3.

Maximum Availability
This protectionmode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance
This protectionmode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.
This is the default protection mode.

Maximum Protection
This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.
Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.


How to delay the application of logs to a physical standby?
A standby database automatically applies redo logs when they arrive from the primary database. But in some cases, we want to create a time lag between the archiving of a redo log at the primary site, and the application of the log at the standby site.

Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database.

Example: For 60min Delay:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby_srvc DELAY=60';
The DELAY attribute is expressed in minutes.
The archived redo logs are still automatically copied from the primary site to the standby site, but the logs are not immediately applied to the standby database. The logs are applied when the specified time interval expires.



STEPS - UPGRADING


UPGRADING

Manual upgrade which involves the following steps:

1.Backup the database.
2.In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
3.Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script.
4.Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
5.Recompile invalid objects.
6.Restart the database.
7.Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
8.Troubleshoot any issues or abort the upgrade.


STEPS - PATCH DOWNLOAD & APPLY


Patch Download
==============
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on Patches and Updates tab
- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.

REFERENCES:
http://docs.oracle.com/cd/E11857_01/em.111/e12255/e_oui_appendix.htm
Oracle® Universal Installer and OPatch User's Guide
11g Release 2 (11.2) for Windows and UNIX
Part Number E12255-11

Patch Applying
==============
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.

Patching Oracle Software with OPatch
opatch napply <patch_location> -skip_subset -skip_duplicate
OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).

STEPS - OPATCH Utility


OPATCH Utility (Oracle RDBMS Patching)

1. Download the required Patch from Metalink based on OS Bit Version and DB Version.
2. Need to down the database before applying patch.
3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .
4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id  <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .
5. Patch file format will be like, “p<patch no.>_<db version>_<os>.zip”
6. We can check the opatch version using “opatch -version” command.
7. Generally, takes 2 minutes to apply a patch.
8. To get latest Opatch version download “patch 6880880 - latest opatch tool”, it contains OPatch directory.
9. Contents of downloaded patches will be like “etc,files directories and a README file”
10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.
12. Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).
13. Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).
14.Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

REFERENCES:
OPATCH Utility (Oracle RDBMS Patching)
http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/

How to apply Database Patches
http://rafioracledba.blogspot.in/search/label/Database%20Patches

Critical Patch Updates, Security Alerts and Third Party Bulletin
http://www.oracle.com/technetwork/topics/security/alerts-086861.html

Oracle: Quick Guide to Opatch - (Oracle Database Patching utility)
http://www.dbalifeline.com/content/oracle-quick-guide-opatch-oracle-database-patching-utility

How to Design an Effective Patch Management Process
http://www.computing.net/howtos/show/how-to-design-an-effective-patch-management-process/744.html

Oracle Database 11.2.0.2 Patch Set (English)
http://www.dbacomp.com.br/blog/?p=69

Apply Oracle CPUApr2010 – 9352191 for Oracle10.2.0.4 in Aix5L
http://hendrydasan.com/2010/05/21/apply-oracle-cpuapr2010-9352191-for-oracle10-2-0-4-in-aix5l/

Saturday, June 30, 2012

QUERY - BACKUP


QUERY - BACKUP



Oracle Home and Inventory Backup


tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz



Note: 
tar -cvf <destination_location> <source_location>


ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC)
cd /u01/before_patch_backup
tar -cvf oracle_base_jun30.tar $ORACLE_BASE
tar -cvf oracle_home_jun30.tar $ORACLE_HOME
tar -cvf oracle_crs_home_jun30.tar $ORACLE_CRS_HOME




All Users passwords backup


spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users;  ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off


Database links backup


All DBlinks backup:
set pages 0
set long 90000
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;


For a specific DBlink backup:
set pages 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('DB_LINK','DB_LINK_NAME','USER_NAME') FROM dba_db_links;


Controlfile Backup


alter database backup controlfile to trace; 


show parameter user_dump_dest
(go to udump dest and make the note of controlfile trace)



"Hope for the best, prepare for the worst"
-Dharmendra

Wednesday, June 27, 2012

QUERY - DDL OF PACKAGE

PACKAGE DDL


spool packagebackup.sql
select text from dba_source where owner = 'OWNERNAME' and name = 'PACKAGENAME' and type = 'PACKAGE BODY' order by line;
spool 0ff
(OR)
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 50000
select dbms_metadata.get_ddl('PACKAGE','PACKAGENAME') from dual ;


PACKAGE DROP


col object_name for a30
col object_type for a25
col owner for a25
spool packagedrop.sql
select object_name,object_type,owner from dba_objects where object_name='objectname';
drop package <owner>.<package_name>;
spool 0ff



"Hope for the best, prepare for the worst"
-Dharmendra

Monday, June 25, 2012

Query - DBA views



Query - DBA views

DBA Views Queries
---------------------------          
select * from dict;
select * from cat;

TABLE COLUMNS
---------------------------
select * from dba_tab_columns where table_name = 'table_name';

CONSTRAINTS
-----------------------
select table_name, constraint_name, constraint_type, status from dba_constraints where owner in('', '')

select constraint_name, r_constraint_name, constraint_type from dba_constraints where constraint_name = ''; 
select constraint_name, constraint_type from dba_constraints where r_constraint_name = '';

DATAFILES
-----------------
select substr(file_name,1,70) from dba_data_files order by file_name;

DEPENDENCIES
------------------------
select * from user_dependencies where referenced_name = ''

INDEXES
--------------
select index_name, column_name from dba_ind_columns where table_name = '' order by index_name, column_position
select index_name from dba_indexes where table_name = '';

JOBS
--------
select job, this_date, this_sec, next_date,  next_sec, interval, what,  failures, broken from dba_jobs;

OBJECTS
--------------
select object_name from dba_objects where status <> 'VALID';

TABLE PRIVILEGES
------------------------------
desc dba_tab_privs 
select * from dba_tab_privs where grantee = '';
select * from dba_tab_privs where table_name = '';

ROLE PRIVILEGES
-----------------------------
select * from dba_role_privs where grantee='';

SYSTEM PRIVILEGES
--------------------------------
select * from dba_sys_privs where grantee='';

OTHERS
-------------
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS

SEGMENTS
-----------------
select segment_name, count(*), sum(bytes) from dba_extents where segment_name = '' group by segment_name ;
select * from dba_segments where segment_name = '';
select * from dba_segments@p1aqcis where segment_name = '';
select segment_name, count(*), sum(bytes) from dba_extents group by segment_name having count(*) > 1;
select * from dba_tables where table_name = '';

select segment_name, segment_type, bytes from dba_segments where segment_name in ('','','','','');

select sum(bytes) from dba_segments where segment_name in ('','','','','');

SELECT substr(segment_name, 1, 30) "OBJECT", max(extents) FROM dba_segments group by segment_name having max(extents) > &max_extents_greater_than;
  
SOURCE CODE
-----------------------
select owner,name,type,text from dba_source where upper(text) like '%%';
select name,type,text from dba_source where upper(text) like '%%';
select name,type,text from dba_source where upper(text) like '%%';
select substr(line,1,4) line, text from dba_source where type = 'PACKAGE BODY' and name = ''
select name,type,text from dba_source where upper(text) like '%%';

TABLES
------------
space allocation for tables using computed statistics
select table_name, num_rows, avg_row_len, (num_rows * avg_row_len) from dba_tables where owner in ('','');

TABLESPACES
----------------------
select tablespace_name, bytes, file_name from dba_data_files order by tablespace_name;

USERS
----------
set pagesize 0
set echo off
select 'ROLE '||GRANTED_ROLE from dba_role_privs where grantee='';
select 'SYSTEM PRIVILEGE '||privilege from dba_sys_privs where grantee='';
select privilege||' ON '||owner||'.'||table_name from dba_tab_privs where grantee='';
set echo on

select * from dba_users where username = '';

VIEWS
----------
select view_name from dba_views where view_name like '%%';

"Hope for the best, prepare for the worst"
-Dharmendra

COMMAND - DELETING FILES AT OS LEVEL


DELETING FILES AT OS LEVEL


$ df -h .
$ find . -name "*.trc" -mtime +7 -exec ls -ltr {} \;
$ find . -name "*.trc" -mtime +7 -exec rm {} \;


df -h .
$ find . -name "*.trm" -mtime +7 -exec ls -ltr {} \;
$ find . -name "*.trm" -mtime +7 -exec rm {} \;


$  df -h .
$ find . -name "*.gz" -mtime +7 -exec ls {} \;
$ find . -name "*.gz" -mtime +7 -exec rm -rf {} \;


NOTE
find . -name "*.trc" -mtime +7 -exec gzip  {} \;



"Hope for the best, prepare for the worst"
-Dharmendra

Steps - Adding Space to Temporary Tablespace


STEPS - Adding Space to Temporary Tablespace


Set Environment
---------------
export ORACLE_SID=
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin:.


To Check Database
-----------------
set pages 200 lines 170
col HOST_NAME for a15
select instance_name,name db_name,host_name,version db_version from gv$instance,v$database;


TEMP TABLESPACE REPORT
----------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off


COLUMN tablespace_name       FORMAT a18               HEAD 'Tablespace Name'
COLUMN tablespace_status     FORMAT a9                HEAD 'Status'
COLUMN tablespace_size       FORMAT 999,999,999,999   HEAD 'Size'
COLUMN used                  FORMAT 999,999,999,999   HEAD 'Used'
COLUMN used_pct              FORMAT 999               HEAD 'Pct. Used'
COLUMN current_users         FORMAT 9,999             HEAD 'Current Users'


SELECT
    d.tablespace_name                      tablespace_name
  , d.status                               tablespace_status
  , NVL(a.bytes, 0)                        tablespace_size
  , NVL(t.bytes, 0)                        used
  , TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct
  , NVL(s.current_users, 0)                current_users
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
  , v$sort_segment  s
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = s.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
/


To Find the free space in Temp:
-------------------------------
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


Temp Free Space:
----------------
SELECT tablespace_name, SUM(bytes_used)/1024/1024/1024 "used(GB)", SUM(bytes_free)/1024/1024/1024 "free(GB)" FROM V$temp_space_header GROUP BY tablespace_name;


Tempfile location:
------------------
col tablespace_name for a15
col file_name for a35
select TABLESPACE_NAME,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_temp_files where tablespace_name='TABLESPACENAME' order by 1,2;


Filesystem
----------
$ df -h /tempfile/path/
or
$ df -g /tempfile/path/


Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on


set pages 200 lines 160
spool ./resize.log
set echo on feed on time on term on


To resize a datafile:-
----------------------
alter database tempfile '/tempfile/path/tempfilename.dbf' resize 4096m;
To resize a datafile:-(ASM)
----------------------
ALTER DATABASE TEMPFILE '&FILE_NAME' RESIZE 4096M;


spool off


BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE
---------------------------------------------------------------
SQL> Select file_name from dba_temp_files where file_name like '%&tempdatafilename%';


set pages 200 lines 160
spool ./add.log
set echo on feed on time on term on


Adding space in temp tablespace:
--------------------------------
ALTER TABLESPACE <temptablespacename> ADD TEMPFILE '/tempfile/path/tempfilename.dbf' SIZE 4096M;


Adding space in temp tablespace:(ASM)
--------------------------------
ALTER TABLESPACE <temptablespacename> ADD TEMPFILE '+DATA' SIZE 64G;


spool off


Take Table Space Report.


"Hope for the best, prepare for the worst"
-Dharmendra