Skip to content

Archive for

New RAC Book By Mr.Syed Jaffar Hussain and Others.

This Book will release on April 2010. I hope this book will be useful for all DBA guys and me also…

Oracle 11g R1 / R2 Real Application Clusters Handbook

In Detail

RAC or Real Application Clusters is a grid computing solution that allows multiple nodes (servers) in a clustered system to mount and open a single database that resides on shared disk storage. Should a single system (node) fail, the database service will still be available on the remaining nodes. RAC is an integral part of the Oracle database setup: one database, multiple users accessing it, in real time. This book will enable DBAs to get their finger on the pulse of the Oracle 11g RAC environment quickly and easily.

This practical handbook documents how to administer a complex Oracle 11g RAC environment. It covers all areas of the Oracle 11g R1 RAC environment, with bonus R2 information included, and is indispensable if you are an Oracle DBA charged with configuring and implementing Oracle11g. It presents a complete method for the design, installation, and configuration of Oracle 11g RAC, ultimately enabling rapid administration of Oracle 11g RAC environments.

Packed with real-world examples, expert tips, and troubleshooting advice, the book begins by introducing the concept of RAC and High Availability. It then dives deep into the world of RAC design, installation, and configuration, enabling you to support complex RAC environments for real-world deployments. Chapters cover RAC and High Availability, Oracle 11g RAC Architecture, Oracle 11g RAC Installation, Automatic Storage Management, Troubleshooting, Workload Management, and much more.

By following the practical examples in the book, you will learn every concept of the RAC environment and how to successfully support complex Oracle 11g R1 and R2 RAC environments for various deployments in real-world situations.

What you will learn from this book

  • Administer, implement, and manage Oracle 11g RAC environments for real-world deployments
  • Understand the high availability concepts and solutions that are available for Oracle 11g RAC
  • Discover the key architectural design and installation techniques required to successfully deploy Oracle 11g RAC
  • Add functionality to your RAC environment by incorporating new RAC features such as Automatic Storage Management
  • Effectively manage the complex 11g Clusterware, using key troubleshooting tips and techniques.
  • Successfully implement database creation methods, manage the RAC database, and handle workload in your RAC environment efficiently
  • Plan your backup and recovery strategy appropriately
  • Know when and how to upgrade your RAC environment effectively
  • Deploy Oracle 11g RAC with complex standard-off-the-shelf systems like Oracle EBS
  • Understand key new features for 11g R1/R2 RAC and ASM
Advertisements

Managing Recyclebin

Recycle Bin :

  1. When a tablespace is completely filled up with recycle bin data such
    that the datafiles have to extend to make room for more data, the tablespace is said to be under “space pressure”.
  2. Objects are automatically purged from the recycle bin in a first in  first out manner.
  3. the dependent objects(such as indexes) are removed before a table is removed.
  4. Space pressure can occur with user quotes as defined for a particular tablespace.
  5. The tablespace may ahve enough free space, but the user may be running out of his or her allotted portion of it.
  6. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.
  7. If your datafiles have the AUTOEXTEND attribute enabled, Oracle will not in fact autoextend  the datafiles until all space occupied by dropped objects has been reassigned.it will overwrite the recycle bin in preference to increasing the datafile size.

Example :-

  1. A 1MB tablesapce,called SMALL, has beeen completely filled by one table,called LARGE.
  2. The space usage alerts will have fired, and querying DBA_FREE_SPACE reports no space available. Then the table is dropped.
  3. The alert will clear itself and DBA_FREE_SPACE will report that the whole tablespace is empty ,but
    querying the recycle bin , or indeed DBA_SEGMENTS will tell the truth.

Sql>select sum(bytes) from dba_free_space where tablespace_name=’SMALL’;
sum(bytes)
———-

sql>select segment_name,bytes from dba_segments where tablespace_name=’SMALL’;
SEGMENT_NAME             BYTES
————-           ——
LARGE                   983040

sql>drop table large;
Table dropped.

sql>select sum(bytes) from dba_free_space where tablespace_name=’SMALL’;
sum(bytes)
———-
983040

sql>select segment_name,bytes from dba_segments where tablespace_name=’SMALL’;
SEGMENT_NAME             BYTES
————-           ——
BIN$                    983040

  1. This apparently contradictory state is resolved by Oracle reusing space as it needs it.
  2. If space is required in the tablespace for a new segment, then it will be taken and it will no longer be possible to retain the dropped table.
  3. If there are many deleted objects in the recycle bin, Oracle will overwrite the object that had been in there for the longest time.THIS FIFO or first in first out algorithm assumes that  ojects dropped recently are most likely candidates for a flashback.

—-

Each user has his own recycle bin

SQL> conn scott/tiger;
Connected.
SQL> show user;
USER is “SCOTT”
SQL> create table trial as select * from dept;

Table created.

SQL> drop table trial;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1vHeLSXYTROOaynkYZGpCg==$0 TABLE        2010-01-25:11:29:00

SQL> desc dba_recyclebin;
ERROR:
ORA-04043: object “SYS”.”DBA_RECYCLEBIN” does not exist

( Sys user only can access this above view).

SQL> select original_name,type,ts_name,droptime,can_undrop,space from user_recyc
lebin;

ORIGINAL_NAME                    TYPE
——————————– ————————-
TS_NAME                        DROPTIME            CAN      SPACE
—————————— ——————- — ———-
TRIAL                            TABLE
USERS                          2010-01-25:11:29:00 YES          8
SQL>

Drop the table and do not move it to the recycle bin

SQL> create table test as select * from dept;

Table created.

SQL> drop table test purge;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1vHeLSXYTROOaynkYZGpCg==$0 TABLE        2010-01-25:11:29:00

Remove the table from the recycle bin.If there are several objects with the same Original name, the oldest is removed.

SQL> drop table test;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TEST             BIN$a2qVloT+Q5q1roJlQz+ZKw==$0 TABLE        2010-01-25:11:37:21

TRIAL            BIN$1r9D5xVUQtu0IBhxh8q6kA==$0 TABLE        2010-01-25:11:36:44

SQL> purge table test;

Table purged.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1r9D5xVUQtu0IBhxh8q6kA==$0 TABLE        2010-01-25:11:36:44

Remove an index from the recycle bin

sql>purge index <index_name>;

Remove all dropped objects from the tablespace

SQL> purge tablespace users;

Tablespace purged.

SQL> show recyclebin;

Remove all dropped objects belonging to one user from the tablespace

sql>purge tablespace <tablespace_name> user <user_name>;

Remove all your dropped objects

sql>purge user_recyclebin

Remove all dropped objects.You will need DBA privileges to execte this.

sql>purge dba_recyclebin

How to Access EM in windows 7 Outside of VMware(Database installed on solaris in vmware)

Step 1: Configure Bridge network on vmware.

Step 2: Login as root user .

Step 3:Login as Oracle user. Check Emctl status whether Enterprise Manager console is running or not, if its not running ,start dbconsole(emctl start dbconsole).You must check listner is running or not.

Lsnrctl>status  ‘If its not start ,start listner.

Step 4: Note your solaris Ip address.

Step 5: Comeout from vmware and ping solaris ip address on windows command prompt.

Step 6 : After ping , open browser and type Enterprise manager URL in url address bar. Now you can easily access EM on windows 7.

Flashback DROP

Flashback DROP 

  • The Flashback drop command applies only to tables, but all associated objects will also be recovered,except for foreign key constraints.
  • In earlier releases of the Oracle Database, when a table was dropped all references to it were removed from the data dictionary.
  • If it were possible to see the source code for the old DROP TaBLE command, you would see that it was actually a series of DELETE commands against the various tables in the SYS schema that define a table and its space usage, followed by commit. 
  • There was no actual clearing of data from disk, but the space used by a dropped table was flagged as being unused and thus available for reuse.
  • Even though the blocks of the table were still there, there was no possible way of getting to them because the data dictionary would have no record of which blocks were part of the dropped table.the only way  to recover a dropped table was to do a point in time recovery, restoring a version of the database from before the drop when the data dictionary still knew about the table.
  • In release 10g of the Oracle database , the implementation of the DROP TABLE command has been completely changed.
  • tables are no longer dropped at all, they are renamed.
  • You can see that a table, EMP occupies one extent of 64KB, which starts in the 25the of file 4.After the rename to NEW_NAME, the storage is exactly the same; therefore the table is the same,
    Querying the view DBA_OBJECTS would show that the table’s object number had not changed either.
  • The release 10g implementation of the DROP TABLE command has been mapped internally onto a RENAME command, which affects the table all its associated indexes, triggers and constaints with the exception
    of foreign key constraints, which are dropped.
  • If they were maintained, even with a different name, then DML on the non dropped parent table would be constrained by the contents of a dropped table, which would be absurd.
  • The dropped objects can be queried by looking at the recycle bin to obtain their names.
  • There is a recycle bin for each other, visible in the USER_RECYCLEBIN data dictionary view or DBA_RECYCLEBIN.
  • The space occupied by the recycle bin objects will be reused automatically when a tablespace comes under space pressure(after whic
    time the objects cannot be recovered), or you can manually force Oracle to really drop the objects with the PURGE command.

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4                  25      65536
 

SQL> alter table EMP rename to EMPLOYEE;
alter table EMP rename to EMPLOYEE
*
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> conn scott/tiger
Connected.
SQL> alter table EMP rename to EMPLOYEE;
 

Table altered.

SQL> conn sys/sys@riyadh as sysdba
Connected.
SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

no rows selected
 

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMPLOYEE’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4               25             65536
 

Flashback DROP Example :
 

Step 1
 

Create user
 

SQL> show user;
USER is “SYS”
SQL> create user dropper identified by dropper;
 

User created.
 

SQL> grant connect,resource to dropper;
 

Grant succeeded.
 

Step 2
Create a table, with an index and a constraint and insert a row.
 

SQL> connect dropper/dropper@riyadh;
Connected.
SQL> create table test(name varchar2(10));  

Table created.  

SQL> create index test_idx on test(name);  

Index created.  

SQL> alter table test add(constraint name_u unique(name));  

Table altered. 
SQL> insert into test values(‘Azar’);
 

1 row created.
 

SQL> commit;
 

Commit complete.
 

SQL> select * from test;
 

NAME
———-
Azar
 

Step 3
 
Confirm the contents of your schema
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

TEST_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

Step 4 

Drop the table
 

SQL> drop table test;
 

Table dropped.
 

Step 5
 
Re-run the queries from step 3.Note that the objects and the constaints do still exist but that they now
have system generated names, all prefixed with BIN$.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
BIN$Zl4wALngQtKfJL+9mgzJgg==$0
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U BIN$Zl4wALngQtKfJL+9mgzJgg==$0
 

Step 6 

Query your recycle bin to see the mapping of the original name to the recyclebin names.
 

SQL> select object_name,original_name,type from user_recyclebin;
 

OBJECT_NAME                    ORIGINAL_NAME
—————————— ——————————–
TYPE
————————-
BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0 TEST_IDX
INDEX
 

BIN$Zl4wALngQtKfJL+9mgzJgg==$0 TEST
TABLE
 

Step 7
 

Query the recycle bin but that you cannot do DML against it.
 

SQL> select * from “BIN$Zl4wALngQtKfJL+9mgzJgg==$0”;
 

NAME
———-
Azar
 

SQL> insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’);
insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
 

Step 8
 

Recover the table with Flashback Drop 
SQL> flashback table test to before drop;
 

Flashback complete.
 

Step 9
 

Rerun the step 3.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U TEST
 

Step 10
Rename the index and constraint to the original names.

SQL> alter index “BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0” rename to name_idx;
 

Index altered.
 

SQL> alter table test rename constraint “BIN$BBKVasdjRsqyYqne8rM8VQ==$0” to name_u;
 

Table altered.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

NAME_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

SQL> select * from test;
 

NAME
———-
Azar

Configuring Flashback Database

C:\Documents and Settings\mazar>set oracle_sid=riyadh

C:\Documents and Settings\mazar>sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 18 11:17:37 2010

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

Enter user-name: sys/sys as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

Step 1

Ensure that the database in archivelog mode.

Archivelog mode is a prerewuistite for enabling flashback database.

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

Step 2

Set up a flash recovery area.

The flash recovery area is the location for the flashback logs. YOu have no control over them other than setting the flah recovery area directory and limiting its size. It is controlled with two instance paramenters: DB_RECOVERY_FILE_DEST specifies the destination directory;
DB_RECOVERY_FILE_DEST_SIZE restricts that the flash recovery area is used for purposes other than flashback logs and it will need to be sized appropriately.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      D:\flashrecovery
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

Step 3

Set the lifespan for the flashback retention target.

This setting is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which specifies a time in minutes (the default is one day).

The flashback log space is reused in a circular fashion, older data being overwritten by newer data.This parameter instructs Oracle to keep flashback data for a certan cumber of minutes before overwriting it

SQL> alter system set db_flashback_retention_target=240;

System altered.

It is only a target(four hour in this example), and if the flash recovery area is undersized, Oracle may not be able to keep it.but is principle, you should be able to flash back to any time within this target.

Step 4

Cleanly Shutdown the database and mount

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  247463936 bytes
Fixed Size                  1248332 bytes
Variable Size             104858548 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.

Step 5

Enable flashback logging, while in mount mode.
SQL> alter database flashback on;

Database altered.

This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.

Step 6

Open the database.

SQL> alter database open;

Database altered.

Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.

Step 7

Check Whether Flashback On or Off.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES