Oracle Dataguard - Quick Learn

iDBA , An iphone App for Oracle DBA's DownLoad To Day !!!

Oracle Dataguard - Quick Learn

A Data Guard configuration contains a primary database and up to nine associated standby databases.

What is a Standby database ?

A standby database is a transactionally consistent copy of an Oracle production

database that is initially created from a backup copy of the primary database. Once the

standby database is created and configured, Data Guard automatically maintains the

standby database by transmitting primary database redo data to the standby system,

where the redo data is applied to the standby database.

What are the types of standby databases?

A standby database can be one of these types: a physical standby database, a logical standby database, or a snapshot standby database. A Data Guard configuration can include any combination of these types of standby databases.

What is the use of a standby database?

Standby database can assume the role of the primary database and take over

production processing.

What is a Physical standby database and mention its benefits?

A physical standby database is an exact, block-for-block copy of a primary database.A physical standby is maintained as an exact copy through a process called Redo Apply,in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.A physical standby database provides the following benefits:

1) Disaster recovery and high availability

2) Data protection

3) Reduction in primary database workload

4) Performance(as redo apply bypass all SQL level code layers

What is a Logical standby database?

Data Guard applies information from the archived redo log file or standby redo log file to the logical standby database by transforming the data in the log files into SQL statements and then executing the SQL statements on the logical standby database. Because the logical standby database is updated using SQL statements, it must remain open. Although the logical standby database is opened in read/write mode, its target tables for the regenerated SQL are available only for read-only operations. While those tables are being updated, they can be used simultaneously for other tasks such as reporting, summations, and queries. Tthese tasks can be optimized by creating additional indexes and materialized views on the maintained tables.

What are the benefits of a Logical standby database?

A logical standby database is ideal for high availability (HA) compared to physical standby database and provides following benefits:

1) Logical standby analyzes the redo and reconstructs logical changes to the database, it can detect and protect against certain kinds of hardware failure on the primary that could potentially be replicated through block level changes.

2)A logical standby database is open read/write while changes on the primary are being replicated and hence can be used to run reporting workloads, test new software releases and some kinds of applications on a complete and accurate copy of the primary’s data.It can host other applications and additional schemas while protecting data replicated from the primary against local changes.It can be used to assess the impact of certain kinds of physical restructuring (for example, changes to partitioning schemes). Because a logical standby identifies

user transactions and replicates only those changes while filtering out background system changes, it can efficiently replicate only transactions of interest.

3) Logical standby provides a simple solution for creating up-to-the-minute,consistent replicas of a primary database that can be used for workload distribution. As the reporting workload increases, additional logical standbys can be created with transparent load distribution without affecting the transactional throughput of the primary server.

4) A key benefit of logical standby is that significant auxiliary structures can be created to optimize the reporting workload; structures that could have a prohibitive impact on the primary’s transactional response time. A logical standby can have its data physically reorganized into a different storage type with different partitioning, have many different indexes, have on-demand refresh materialized views created and maintained, and it can be used to drive the creation of data cubes and other OLAP data views.

5) Logical standby can be used to greatly reduce downtime associated with applying patchsets and new software releases. A logical standby can be upgraded to the new release and then switched over to become the active primary. This allows full availability while the old primary is converted to a logical standby and the patchset is applied.

What is a snapshot standby database?

A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot

standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

What are the benefits of a snapshot standby database?

1)A snapshot standby database is a fully updatable standby database that provides disaster recovery and data protection benefits that are similar to those of a physical standby database

2)It provides an exact replica of a production database for development and testing purposes, while maintaining data protection at all times

3) It can be easily refreshed to contain current production data by converting to a physical standby and resynchronizing

http://oraclesource.weebly.com

Oracle Database Auditing in 10g and 9i - Part 1

iDBA , An iphone App for Oracle DBA's

DOWNLOAD

Oracle Database Auditing in 10g and 9i - Part 1

Auditing required features installed default when you install a database . You need to enable it when you want to make use of it . The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command.

SQL> SHOW PARAMETER AUDIT

NAME TYPE VALUE

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

audit_file_dest string /vivekapp1p/admin/vivek1/adump

audit_sys_operations boolean FALSE

audit_trail string NONE

SQL>

By Default Auditing is disabled, but can be enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

none or false - Auditing is disabled.

db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).

db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.

xml- Auditing is enabled, with all audit records stored as XML format OS files.

xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

AUDIT_SYS_OPERATIONS: static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

AUDIT_FILE_DEST: parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

To enable auditing and direct audit records to the database audit trail, we would do the following.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248600 bytes

Variable Size 71303848 bytes

Database Buffers 213909504 bytes

Redo Buffers 2945024 bytes

Database mounted.

Database opened.

SQL>

Demonstration of setting Audit option for an Oracle User

OK, Our auditing has been set so straight we can proceed to enable it for particular user , which should be monitored under that .

SQL> sho parameter audit

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/acme/adu

mp

audit_sys_operations boolean TRUE

audit_syslog_level string

audit_trail string DB

SQL>

SQL>

SQL>

Now create a new for Auditing

SQL> create user audi identified by "xxxxxxx"

2 ;

User created.

SQL> grant connect ,resource to audi;

Grant succeeded.

SQL> grant dba to audi;

Grant succeeded.

Next we audit all operations by the AUDIT_TEST user.

SQL> AUDIT ALL BY audi BY ACCESS;

Audit succeeded.

SQL>

______________________________________________________________________________

Now login from different session to perform some activity as AUDI user .

poracle@acmord3p> sqlplus audi

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Mar 14 23:29:37 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

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

SQL> create table b (name int);

Table created.

SQL> insert into a values (1234);

1 row created.

SQL> select * from a;

NAME

----------

1234

1234

SQL> insert into b values (1234);

1 row created.

SQL> select * from b;

NAME

----------

1234

SQL> exit

_______________________________________________________________________________

Login as SYS and check if above activity have been audited for user AUDI .

SQL> COLUMN username FORMAT A10

SQL> COLUMN owner FORMAT A10

SQL> COLUMN obj_name FORMAT A10

SQL> COLUMN extended_timestamp FORMAT A35

SQL>

SQL> SELECT username,

2 extended_timestamp,

3 owner,

4 obj_name,

5 action_name

FROM dba_audit_trail

6 WHERE owner = 'AUDI'

7 8 ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME

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

ACTION_NAME

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

AUDI 14-MAR-10 11.27.00.967650 PM +08:00 AUDI A

CREATE TABLE

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY AUDI BY ACCESS;

Audit succeeded.

SQL> COLUMN username FORMAT A10

SQL> COLUMN owner FORMAT A10

SQL> COLUMN obj_name FORMAT A10

SQL> COLUMN extended_timestamp FORMAT A35

SQL>

SQL> SELECT username,

2 extended_timestamp,

3 owner,

4 obj_name,

5 action_name

6 FROM dba_audit_trail

7 WHERE owner = 'AUDI'

8 ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME

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

AUDI 14-MAR-10 11.27.00.967650 PM +08:00 AUDI A CREATE TABLE

AUDI 14-MAR-10 11.29.49.480341 PM +08:00 AUDI B CREATE TABLE

AUDI 14-MAR-10 11.29.52.804367 PM +08:00 AUDI A INSERT

AUDI 14-MAR-10 11.29.57.708012 PM +08:00 AUDI A SELECT

AUDI 14-MAR-10 11.30.15.240634 PM +08:00 AUDI B INSERT

AUDI 14-MAR-10 11.30.21.371140 PM +08:00 AUDI B SELECT

6 rows selected.

To check about enabled audit options in database :

SELECT * FROM DBA_PRIV_AUDIT_OPTS where USER_NAME='AUDI';

USER_NAME AUDIT_OPTION SUCCESS FAILURE

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

AUDI SELECT TABLE BY ACCESS BY ACCESS

AUDI ALTER SYSTEM BY ACCESS BY ACCESS

AUDI SYSTEM AUDIT BY ACCESS BY ACCESS

AUDI CREATE SESSION BY ACCESS BY ACCESS

AUDI TABLE BY ACCESS BY ACCESS

AUDI CLUSTER BY ACCESS BY ACCESS

AUDI TABLESPACE BY ACCESS BY ACCESS

AUDI USER BY ACCESS BY ACCESS

AUDI ROLLBACK SEGMENT BY ACCESS BY ACCESS

AUDI TYPE BY ACCESS BY ACCESS

AUDI INDEX BY ACCESS BY ACCESS

USER_NAME AUDIT_OPTION SUCCESS FAILURE

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

AUDI SYNONYM BY ACCESS BY ACCESS

AUDI PUBLIC SYNONYM BY ACCESS BY ACCESS

AUDI VIEW BY ACCESS BY ACCESS

AUDI SEQUENCE BY ACCESS BY ACCESS

AUDI DATABASE LINK BY ACCESS BY ACCESS

AUDI PUBLIC DATABASE LINK BY ACCESS BY ACCESS

AUDI ROLE BY ACCESS BY ACCESS

AUDI DIMENSION BY ACCESS BY ACCESS

AUDI PROCEDURE BY ACCESS BY ACCESS

AUDI TRIGGER BY ACCESS BY ACCESS

AUDI PROFILE BY ACCESS BY ACCESS

USER_NAME AUDIT_OPTION SUCCESS FAILURE

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

AUDI DIRECTORY BY ACCESS BY ACCESS

AUDI MATERIALIZED VIEW BY ACCESS BY ACCESS

AUDI NOT EXISTS BY ACCESS BY ACCESS

AUDI SYSTEM GRANT BY ACCESS BY ACCESS

AUDI CONTEXT BY ACCESS BY ACCESS

AUDI INSERT TABLE BY ACCESS BY ACCESS

AUDI UPDATE TABLE BY ACCESS BY ACCESS

AUDI DELETE TABLE BY ACCESS BY ACCESS

SGGUVI CREATE SESSION BY ACCESS BY ACCESS

CAUTION when AUDIT is set to DB :

As the table AUD$ which contains all the auditing data is created in the system tablespace,

so as the auditing information grows the size of the system tablespace also increases,

so it is advisable to move this particular table AUD$ to some another tablespace.

THE COMMAND TO MOVE THE TABLE IS:-

SQL>create table AUDX tablespace as select * from AUD$;

SQL>rename AUD$ to AUD$$;

SQL>rename AUDX to AUD$;

TO check whether AUD$ table has shifted to the new tablespace write the following query in the SQL prompt

SQL>select table_name,tablespace_name from dba_tables where table_name=’AUD$’;

to be continued..

AIX-Oracle : Symbol resolution Failed For ..

If you are an Oracle DBA & Own an iPhone , Here we have must to download app for you . Available World Wide in App store !!

iDBA DownLoad

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

Its possible sometime you prefer to copy the Oracle Binaries instead of installing gracefully as you don't have time to go thru time consuming process .

Don't be surprised if you are hitting below error .

[uoracle:/u01/app/oracle/product/9.2.0/bin]/>./sqlplus

exec(): 0509-036 Cannot load program ./sqlplus because of the following errors:

0509-130 Symbol resolution failed for /usr/lib/libc.a[aio_64.o] because:

0509-136 Symbol kaio_rdwr64 (number 0) is not exported from

dependent module /unix.

0509-136 Symbol listio64 (number 1) is not exported from

dependent module /unix.

0509-136 Symbol acancel64 (number 2) is not exported from

dependent module /unix.

0509-136 Symbol iosuspend64 (number 3) is not exported from

dependent module /unix.

0509-136 Symbol aio_nwait (number 4) is not exported from

dependent module /unix.

0509-150 Dependent module libc.a(aio_64.o) could not be loaded.

0509-026 System error: Cannot run a file that does not have a valid format.

0509-192 Examine .loader section symbols with the

'dump -Tv' command.

[uoracle:/u01/app/oracle/product/9.2.0/bin]/>

Then to resolve it , You will do below :

[uoracle:/u01/app/oracle/product/9.2.0/bin]/>relink all

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

Even After still getting below Error , Let's have a look .

In General we may encounter below linkage problem during invoacation of sqlplus

[uoracle:/home/uoracle]/>sqlplus

exec(): 0509-036 Cannot load program sqlplus because of the following errors:

0509-130 Symbol resolution failed for /usr/lib/libc.a[aio_64.o] because:

0509-136 Symbol kaio_rdwr64 (number 0) is not exported from

dependent module /unix.

0509-136 Symbol listio64 (number 1) is not exported from

dependent module /unix.

0509-136 Symbol acancel64 (number 2) is not exported from

dependent module /unix.

0509-136 Symbol iosuspend64 (number 3) is not exported from

dependent module /unix.

0509-136 Symbol aio_nwait (number 4) is not exported from

dependent module /unix.

0509-150 Dependent module libc.a(aio_64.o) could not be loaded.

0509-026 System error: Cannot run a file that does not have a valid format.

0509-192 Examine .loader section symbols with the

'dump -Tv' command.

Caused By : Problem may caused by AIX aio . Which has to be changed explicitly for Oracle .

[uoracle:/home/uoracle]/>exit

Resolution : Run rootpre.sh avilalabe within Oracle installer sfotware for AIX (using root user) .

apd2t:root:/u01/app/oracle/product>./rootpre.sh

Configuring Asynchronous I/O...

aio0 Available

aio0 changed

apd2t:root:/u01/app/oracle/product>

Problem Solved :

wapd2t:root:/u01/app/oracle/product>su - uoracle

[uoracle:/home/uoracle]/>

[uoracle:/home/uoracle]/>sqlplus

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Mar 9 21:49:27 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name:

[uoracle:/home/uoracle]/>

iDBA An iPhone App Just for DBA

iDBA iPhone App for Oracle DBA's

Here is best time to download your copy now !!

Description :

Oracle database administration is a complex and stressful
job still highly paid and challenging. In a series of easy-to-use checklists, this concise small application summarizes the enormous number of tasks you must be handling or learning as an Oracle DBA. Each topics takes a simple and small approach to presenting DBA quickRef application
Most suitable in order to go thru important topics at anywhere .
Quick reference of some mostly used important commands, Either related to basic database administration or to higher level RAC administration .
iDBA is small effort to give a brief conceptual information on various topics of Oracle database administration .

Features :

• All important Oracle Database administration areas covered conceptually .

• Good For quick reference, Very helpful specially when preparing for surprise quiz,test,interview .

• Important RAC management commands .

• Quick SQL reference for DBAs.

• Important Unix commands for DBAs

iDBA - AVAILABLE in Apps Store - DownLoad Now !!

iDBA - An iPhone app Just for Oracle DBAs

Here is a good news for techies , those are playing around iPhone .

Recently " iDBA" , An application for iphone has been released in app store .

iDBA is a small effort to give a brief conceptual information on various topics of Oracle database administration .

Features :

All important Oracle Database administration areas covered conceptually .
Good For quick reference, Very helpful specially when preparing for surprise quiz,test,interview .
Important RAC management commands .
Quick SQL reference for DBAs.
Important Unix commands for DBAs . Please click on below image to download the app .

Updates on iPhone App

ORACLE INDEX REBUILD SCRIPT

REM:********************************************************************************************** REM: SCRIPT FOR Database Informations : Index Rebuild REM: Author: Kumar Menon REM: Date Submitted: 10.07.2009 REM:FileName: indexreb.sql REM: REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE. REM: Author will not be responsible for any damage that may be cause by this script. **************************************************************************************************** SET HEADING OFF SET FEEDBACK OFF SET LINESIZE 200 SET PAGESIZE 0 SPOOL rebtemp.sql SELECT 'SELECT to_char(SYSDATE,''YYYY/MM/DD HH24:MI:SS'') FROM dual;' FROM dual; SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' ONLINE;' FROM dba_indexes WHERE owner IN ('&ownername','&ownername') ORDER BY owner, index_name; SELECT 'SELECT to_char(SYSDATE,''YYYY/MM/DD HH24:MI:SS'') FROM dual;' FROM dual; SPOOL OFF SET ECHO ON SET FEEDBACK ON SPOOL Al_Indexes.log @rebtemp.sql spool off

TIME AND DATE SYNCHRONIZATION AMONG THE NODES IN ORACLE RAC

We may face date and time difference among various nodes in our Oracle RAC setup .

Some application are really time critical , such issues may lead to big business loss if ignored .

Shutdown your database on each node . Shutdown crs servervices as well to ensure all services are down .

We will be using ntpdate command to reset time in our server .

The ntpdate command sets the local date and time by polling the NTP servers specified to determine the correct time. It obtains a number of samples from each server specified and applies the standard NTP clock filter and selection algorithms to select the best of the samples.

RUN BELOW COMMAND FROM ROOT USER ON EACH NODE

#############################################

ora*****:root:/u01/app/oracle/product/10.2/crs/bin>ntpdate 10.111.35.21

29 Sep 18:32:36 ntpdate[462942]: step time server 10.111.35.21 offset 31.980096