Method 1:Through SQL command prompt.
SQL> select dbid from v$database;
DBID
----------
3764293269
Thursday, December 27, 2012
Different ways to get the DBID of Database.
Simulating Oracle Tape Backups.
As per Oracle Note:312737.1 The Oracle DiskSbt API is designed for TESTING purposes ONLY and can NOT be used for any database backup. The Oracle.Disksbt can be used to simulate tape backups on disk using sbt_tape channel.
Oracle Database links.
Primary database name = dell
Secondary Database Name = dup
On Secondary
Create TNS Entry for primary database.
[oracle@newyork admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
Tuesday, December 11, 2012
Oracle 11gR2 RAC Node Deletion.
NodeName which has to be removed from the cluster - rac3 Backup OCR from rac1 [root@rac1 ~]# /u01/app/11.2.0/grid/bin/ocrconfig -manualbackup
Monday, December 10, 2012
RAC common wait events.
1) Global cache blocks lost- This statistic shows block losses during network transfers. High values of this statistic indicate network problems. The use of an unreliable IPC protocol, such as UDP, may result in the value for lost global cache blocks being non-zero. High values for this statistics indicates that there is some problem with the IPC, network or hardware. 2) Global cache blocks corrupt - This statistic shows if there is any block corruption during interconnect transfers. High value for this statistic indicates that there is a IPC,network or hardware problem.
Tuesday, December 4, 2012
Oracle 11gR2 RAC Node Addition.
Following are the IP Addresses which i am using for the node. Public IP Address: 192.168.1.170 Private IP Address: 192.168.2.170 Virtual IP Address: 192.168.1.250 OS installed - RHEL 5.4
Wednesday, November 14, 2012
Oracle Goldengate 11g Initial Load Using Direct Load Method.
This article focuses on initial synchronization of source and target databases by using Golden Gate initial direct load method for the purpose of zero downtime DML replication. Prerequisites: 1) Target Tables should be empty. Otherwise you can get duplicate row errors. 2) Disable Foreign Key and Check Constraints. They can be enabled later on when the initial load is complete. 3) (Optional) Remove indexes from target tables as they may slow down the initial loading process. 4) To use handlecollisions function, each target table must have a primary or unique key. If that's not possible use the KEYCOLS option of the TABLE and MAP parameters. Even if that is not possible then quiesce your database for the duration of intial load.
Sunday, November 11, 2012
Delete or Remove Extract & Replicat Processes (Golden Gate 11g).
1)Stop extract/replicat. kill extract group_name kill replicat group_name 2)Delete extracat / replicat. delete extract group_name delete replicat group_name 3) Cleanup. cleanup extract group_name cleanup replicat group_name
Tuesday, October 30, 2012
How to Tune Shared Pool Cache ?
The one basic rule for tuning the shared pool is to avoid or minimize parses of any kind, whether they be soft parses or hard parses. Parses can only be avoided by your developers by efficiently coding the application. As a DBA you can only minimize parses.
Friday, October 5, 2012
Oracle 11gR2 RAC Enable Archive log Mode.
In the cluster log in from anyone of the instances. In my case it is DELL1 [oracle@rac1 ~]$ export ORACLE_SID=dell1 [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 4 16:38:15 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Monday, September 24, 2012
Sunday, September 23, 2012
Oracle RAC 11gR2 Client Side TAF.
TYPE: TAF supports three types of failover types 1.SESSION failover - If a user's connection is lost, SESSION failover establishes a new session
automatically created for the user on the backup node. This type of failover does not attempt
to recover selects. This failover is ideal for OLTP (online transaction processing) systems,
where transactions are small. 2.SELECT failover – If the connection is lost, Oracle Net establishes a connection to another
node and re-executes the SELECT statements with cursor positioned on the row on which it was
positioned prior to the failover. This mode involves overhead on the client side and Oracle
NET keeps track of SELECT statements. This approach is best for data warehouse systems,
where the transactions are big and complex 3.NONE: This setting is the default and no failover functionality is provided. Use this setting to
prevent failover.
Saturday, September 22, 2012
Oracle RAC 11gR2 ACFS Configuration & Snapshots.
On Oracle Version 11.2.0.1 ACFS is only supported on the following OS Platforms. 1) OL5 and RHEL5 2) Windows 2003 R1 (64-bit) 3) Windows 2003 R2 (64-bit) Oracle ACFS Disk Space Usage. 1) Upto 64 million files in a file system. 2) 63 snapshots. 3) 64 Mounts on 32-bit systems. 4) 256 Mounts on 64-bit systems.
Oracle RAC 11gR2 ASMCMD Commands.
Saving your ASM Spfile. Check spfile location. [root@rac1 bin]# su - grid [grid@rac1 ~]$ echo $ORACLE_SID +ASM1 [grid@rac1 ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 22 00:46:33 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Oracle RAC 11gR2 Voting Disks & OCR Backups.
Voting Disks In 11g release 2 you no longer have to take voting disks backup. In fact according to Oracle documentation restoration of voting disks that were copied using the "dd" or "cp" command may prevent your clusterware from starting up.
Monday, September 17, 2012
Oracle RAC 11gR2 Clusterware and Database Administration.
Prior to 11gR2 "crs_stat -t" was used for checking the status of all the resources But now "crsctl status resource -t" can be used in it's place. I have a test two node RAC system on which i am going to demonstrate some of the database and clusterware administration commands. Here are some important notes from Oracle Docs on "SRVCTL" & "CRSCTL" utilities which are going to be used in this article.
Friday, September 7, 2012
How to configure VNC Server in RHEL5 ?
Install Vnc Server [root@test ~]# yum install *vnc* [root@test ~]# vncserver :1 You will require a password to access your desktops. Password: Verify:
How to configure VNC Server in RHEL6 ?
Disable nm controlled network management. vi /etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE="eth0" NM_CONTROLLED="no" ONBOOT="no" HWADDR=00:0C:29:AA:10:3D TYPE=Ethernet BOOTPROTO=none IPADDR=192.168.1.30 PREFIX=24 GATEWAY=192.168.1.1 DEFROUTE=yes IPV4_FAILURE_FATAL=yes IPV6INIT=no
Thursday, August 30, 2012
Oracle 11gR2 RAC On Vmware with Grid Infrastructure & Scan.
Warning before proceeding make sure you have at least 6Gb or more ram otherwise your
installation will not work.
Virtual Machine 1 configuration
Guest OS - RHEL 5.4
Name - rac1
Public I/P eth0 - 192.168.1.100
Interconnect I/P eth1 - 192.168.2.100
memory - 2GB
Disk space - 30GB
Location - C:\11gRAC\rac1\
Saturday, May 5, 2012
How To Read An AWR Report ?
Prerequisites for awrreport.
Statistics level should be set to typical.
Timed statistics should also be enabled.
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
statistics_level string TYPICAL
Thursday, April 26, 2012
Convert Oracle Database From Non-ASM to ASM.
Modify parameters SQL> alter database disable block change tracking; SQL> alter system set db_create_file_dest='+DATA' scope=spfile; System altered. SQL> alter system set db_recovery_file_dest='+FLASH' scope=spfile; System altered.
Wednesday, April 25, 2012
Single Instance To RAC Conversion Using RCONFIG.
Nodes Available. vmlinux1,vmlinux2 Single instance running at vmlinux1 ORACLE_HOME=/u05/app/oracle/product/10.2.0/db_1 SID=racorcl
Saturday, April 21, 2012
How to Delete ASM ?
Log in to the asm instance as Oracle User $ export ORACLE_SID=+ASM $ sqlplus / as sysdba Enter the following command to determine whether any Oracle database instance is using the ASM instance:
How To Clear Oracle Clusterware Installation ?
There are two methods to do it. Automatic Run the following scripts as root user. cd $ORA_CRS_HOME/install/
Monday, April 16, 2012
Single Instance Conversion To RAC Using DBCA.
This tutorial shows how to convert a single instance database to a RAC one by using DBCA. Below is the configuration of my single instance Database and RAC Nodes. Single Instance Database. OS: RHEL4 Oracle Software Version: 10.2.0.4 SID: orascott
Drop or Delete a RAC Database Using DBCA 10g.
This example shows how to drop a RAC database using DBCA.
Create Database Template Using DBCA.
In this example i am creating a template of an existing database named "ORASCOTT" by using DBCA.
Wednesday, March 28, 2012
Oracle GoldenGate DDL Replication.
This post is built upon the previous post on Oracle GoldenGate DML replication. Points to consider before implementing DDl replication. 1) Recyclebin feature should be turned off(Oracle 10g onwards).
Monday, March 26, 2012
Explain Plan Using Statement ID.
SQL>explain plan set statement_id = 'TEST' FOR select * from scott.EMP;
Explained.
SQL>@explain TEST
PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP
--------------------- ------------ ------------ -------- -------- -------------------- --------------
Select Statement 1218 3
1.1 Table Access (Full) EMP TABLE 1218 3
Gathering Oracle Database Statistics Using DBMS_STATS.
Gathering Table Statistics.
Check the table for when it was last analyzed.
SQL> select last_analyzed, sample_size from dba_tables where owner='SCOTT' and
table_name='EMP';
LAST_ANAL SAMPLE_SIZE
--------- -----------
19-MAR-12 14
Friday, March 16, 2012
Oracle GoldenGate Basic DML Replication.
Schema to be replicated "SCOTT".
SOURCE DATABASE.
OS - Red Hat Enterprise Linux Server release 5.4
Hostname - canada
IP Address - 192.168.1.100
Database - Oracle 11g Release 2.
SID - orcl
GoldenGate Version - 11.1.1.1.2
Sample "/etc/hosts" file.
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
192.168.1.100 canada canada
192.168.1.200 london london
Oracle GoldenGate Basic Architecture.
Source Database - GoldenGate extracts the information from the source database. The source database may be from any of the following vendors. - Oracle Database - TimesTen - MySQL - IBM DB2 - Microsoft SQL Server - Teradata - Sybase - Enscribe - SQL/MX
Monday, March 12, 2012
Tools & commands for analyzing server performance.
To check for disk space.
[root@london ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 26G 16G 8.3G 66% /
/dev/sda1 99M 12M 83M 12% /boot
tmpfs 742M 0 742M 0% /dev/shm
Thursday, March 8, 2012
Oracle RAC Instance Recovery.
1. All nodes available. 2. One or more RAC instances fail. 3. Node failure is detected by any one of the remaining instances. 4. Global Resource Directory(GRD) is reconfigured and distributed among surviving nodes.
How to kill sessions in oracle database.
Execute the following script to list all sessions running in your database.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
Oracle Database 11g Release 2 (11.2) Installation On RedHat Enterprise Linux 5.
This post assumes that you have already installed RHEL5.(Cento OS 5 or Oracle Linux 5 can also be used for this purpose.) This installation is done with 2 Gb of swap space. SELINUX and Firewall should be disabled on the server. Server IP Address : 192.168.1.100
Friday, March 2, 2012
Split Brain Syndrome and I/O Fencing (RAC).
Split brain syndrome occurs when the instances in a RAC fails to connect or ping to each other via the private interconnect, Although the servers are physically up and running and the database instances on these servers is also running. The individual nodes are running fine and can accept user connections and work independently.
Oracle Clusterware Components.
Oracle clusterware is a software which facilitates multiple nodes to communicate with each other and make them behave as a single Server. Oracle clusterware is run by Cluster Ready Services(CRS) which in turn is dependent on two components. a) Oracle Cluster Registry(OCR) b) Voting Disk
RAC Background Processes.
Lock Monitor(LMON) Process. LMON is responsible for monitoring all instances in a cluster for the detection of failed instances. Once a failed Instance is detected it facilitates in the recovery of global locks held by that instance. It is also responsible for reconfiguration of locks and other resources when instances leave or are added to the cluster. This dynamic reconfiguration is done in realtime.
Cache Fusion Scenarios (RAC).
Block Resource Modes.
Resource Mode Identifier Description
Null N Nodes which are holding the blocks
at this level do not have any access
rights to the block.
Shared S This level indicates that the block
at this level is in shared mode or
read mode, Multiple instances have
read access to this block but cannot
modify it.
Exclusive X In this level the block is held in Exclusive
mode and other instances cannot write to it.
Monday, February 27, 2012
Oracle Software Cloning.
On Source $ emctl stop dbconsole $ lsnrctl stop $ cd/u01/app/oracle/product
Friday, February 17, 2012
Essential RMAN Commands.
CHANGE Options: 1) AVAILABLE - Changes the status of backup or copy to "available" in the repository. 2) UNAVAILABLE - Changes the status of backup or copy in the repository. 3) KeepOption - Overrides the retention policy.
RMAN Catalog Creation.
Target Database: Name:orcl IP Address:192.168.1.55 Catalog Database: Name:recovery IP Address:192.168.1.200
Oracle Database Cloning Using User Managed Backup.
Assumption: 1) Hot backups of the production server are there.
Production Database Name: orcl Cloned Database Name: pub --Create pfile from the production servers spfile.
Saturday, February 11, 2012
Oracle Database Best Practices.
1) Maintain atleast three controlfiles.
2) Set CONTROL_FILE_RECORD_KEEP_TIME Large Enough.
(a) The CONTROL_FILE_RECORD_KEEP_TIME specifies the number of days
that records are kept within the controlfile before being reused.
Friday, February 10, 2012
Commonly Configured OEM Alerts and Their Solutions.
Archiver Hung Alert Log Error Cause: Possible cause is that the destination LOG_ARCHIVE_DEST_n is full. Action: Check alert log file and archiver for additional information. ORA-01578:ORACLE data block corrupted
Thursday, February 9, 2012
Oracle 11g GoldenGate Installation.
Download the GoldenGate software from Oracle Edelivery. Select a Product Pack: Oracle Fusion Middleware Platform: Linux X86 Download the Oracle GoldenGate media pack.
Oracle 11g Manual Database Creation.
Create necessary directories. $ mkdir -p /u01/app/oracle/admin/pub/adump/ $ mkdir -p /u01/app/oracle/oradata/pub/ $ mkdir -p /u01/app/oracle/flash_recovery_area/pub/
Monday, February 6, 2012
Oracle Enterprise Manager Cloud Control 12c Release 1 Installation on Oracle Linux 5.6
Requirements: 1) Oracle Linux 5.6 or RHEL 5.6 or higher 2) Oracle Database 11gR2 3) Enterprise Manager Cloud Control 12c Release 1 Package Requirements for Grid Control on 32-bit:
Saturday, February 4, 2012
Oracle 11g Invisible Indexes.
With Oracle 11g you can turn any index invisible with this feature. When you turn a index invisible the optimizer cannot see it and therefore the executions plans are generated without the use of index in the plans. This feature is helpful when you want to check whether a table is better without its index. Instead of dropping the index just make it invisible and check for performance benifits. If you want the index back just make it visible.
Monday, January 30, 2012
Oracle 11g RMAN Validate command.
The validate command can be used to detect corrupt blocks and missing files, also to determine whether backup set can be restored. The validate command can check for physical and logical corruptions in database files. The following command will validate and check all datafiles along with archivelogs, current controlfile and spfile for any kind of corruption.
Oracle Database 11g Workload Replay.
Database replay feature can be used to capture workload from the production system, and replay it on a test system, with exact timing and concurrency. Its like you can emulate the transaction workload on the test system as it was happening on the production system. Main benefit of this is that you can test new settings on the test system before implementing them on the production system.
Oracle 11g Health Checks.
Oracle 11g now has a feature called health monitor which can be used
to run diagnostic checks on the database.
Health check can be run in two ways.
1) Automatic - The fault diagnosable infrastructure runs the checks
when a critical error is reported.
2) Manual - Can be done manually either through enterprise manager
or through DBMS_HM PL/SQL package.
Oracle 11g Data Recovery Advisor.
The data recovery advisor automatically detects corruption or loss of data on disk and also provides advisory and execution functions for repair or recovery of lost data. Example: I lost my Users Tablespace datafile for some reason.
Tuesday, January 24, 2012
Oracle 10g Using RMAN to Roll Forward a Physical Standby Database.
On Standby
SQL>alter database recover managed standby database cancel;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
485739
Oracle 11gR2 Table level compression.
Earlier in oracle 10g we had compression at the table level but it was only for bulk load operations. But with Oracle 11gR2 compression is also available at the OLTP level.
Oracle Segment Advisor 10g.
Segment advisor which is available from oracle 10g onwards helps in detecting segments that have space issues. For example if a table had lots of rows earlier but now it has few rows then it may be hording up space because of its high water mark. Segment advisor helps in finding following types of segments.
Oracle Database Row Chaining Solution.
Move the affected table 1) alter table hr.employees move; Rebuild the indexes on that table because they will become invalid because of move operation.
Oracle 10g Free up Unused Table Space.
The following operations will shrink the table and reset the high water mark.
Excessive Redo Generation During Begin Backup Mode.
Excessive Redo Generation During Hot/Online backup. During hot backup there is no excessive redo generation, only some additional information is generated. The process is something like following. 1) Before backup, tablespace or database is put into begin backup mode.
Oracle 10g Enable Read Write On Physical Standby Database.
On Standby Enable Flashback Database. SQL> show parameter db_recovery NAME TYPE VALUE ---------------------- ----------- ----------------------------------- db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G
Oracle Database Manual Backup and Recovery.
COLD BACKUP AND RECOVERY SCENARIOS. Cold backup of Database in Noarchivelog Mode. 1) analyze the space requirements. SQL> select sum(sum_bytes)/1024/11024 m_bytes 2 from ( 3 select sum(bytes) sum_bytes from v$datafile 4 union 5 select sum(bytes) sum_bytes from v$tempfile 6 union 7 select (sum(bytes) * members) sum_bytes from v$log 8 group by members);
Monday, January 23, 2012
Wednesday, January 18, 2012
Oracle Database User Management.
USER CREATION AND DELETION. SQL> create user tom identified by jerry 2 default tablespace users 3 temporary tablespace temp 4 quota 20m on users; User created.
Oracle Fuzzy Bits.
If there are FUZZY BITS in the datafile header then that means there may have been writes on the datafile after the last checkpoint. So, whenever a datafile is opened it is checked for fuzzy bits. If it is fuzzy then the database checks the checkpoint_change$ column to search the SCN from which the redo logs are to be applied.
Opening a Standby Database For Read Only Access.
Open a standby database for read-only access when
it is currently performing redo apply. SQL> alter database recover managed standby database cancel; Database altered.
Gap Detection on Standby Database.
If the primary and standby databases are configured with
FAL_SERVER and FAL_CLIENT
parameters then gaps should not occur.
But still if the gaps occur do the following.
On the standby database.
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
------- -------------- ---------------
1 30 40
Oracle 10g Dataguard Failover.
On standby database. Defer the remote archival destination, since we are not going to use it. SQL> alter system set log_archive_dest_state_2 = 'DEFER'; System altered. Before performing going any furthur transfer all the unapplied redo data from the primary to standby to reduce data loss.
Oracle 10g Dataguard Switchover.
This article is based on the dataguard tutorial written earlier. Configure the follwoing parameters on both the databases. SQL> show parameter fal_server
Friday, January 13, 2012
Oracle Database Online Redo Log Files Management.
Lets check the online redo log configuration.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
Thursday, January 12, 2012
A Brief Introduction to Materialized Views.
The Following tutorial is prepared on Oracle Database 11gR2. Materialized views facilitate you to execute a SQl query and save its results either locally or in a remote database. After the Materialized view is initially populated it can be refreshed later on to store fresh results into the underlying table.
Wednesday, January 11, 2012
Oracle Database Undo Tablespace Mangement.
Creating undo tablespace.
SQL> create undo tablespace undotbs2
2 datafile '/u01/app/oracle/oradata/orcl
/undotbs201.dbf' size 200m
3 autoextend on
4 extent management local;
Oracle Database Temporary Tablespace Management.
To check for all the default tablespaces in your database execute the following query. SQL> column property_name format a30 SQL> column property_value format a30 SQL> column description format a50
Tuesday, January 10, 2012
Oracle 11g Feature Read Only Tables.
In oracle database 11g you can alter individual tables to be read only. When in read-only mode, you can’t issue any DML statements against the table. SQL>alter table emp read only; Table altered.
Sunday, January 8, 2012
Oracle 10g Dataguard Physical Standby.
This tutorial is for configuring physical standby database. The dataguard type which we are configuring here is "Maximum Performance Mode". Primary Machine IP: 192.168.1.160 Database name:orcl
Monday, January 2, 2012
Oracle 11gR2 Compression feature.
In Oracle 10g we had basic compression in RMAN. But Oracle 11gR2 has added some more compression features. There are basically four compression levels. HIGH = unmodified BZIP2 MEDIUM = ZLIB LOW = LZO BASIC = BZIP2
Oracle 11g Feature Multisection Backup.
With this new feature RMAN backups can be split into multiple pieces. Suppose you want to backup your system tablespace and spread the backups to three different backup locations. You can do so through the following query.
Subscribe to:
Posts (Atom)