1. Automatic Storage Management (ASM).

ASM means Automatic Storage Management to simplify the Storage of data files, control files and Redo log files.ASM is the extension of OMF (oracle managed files).ASM functionality is controlled by ASM instance. It is not full database instance it’s just a memory structures.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are known as ASM files

ASM provides the following the functionalities

Ø Manages groups of disks, called disk groups.

Ø Manages disk redundancy within a disk group

Ø Supports large files.

Initialization Parameters and ASM Instance Creation

Ø The initialization parameters that are of specific interest for an ASM instance are:

Ø INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.

Ø DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

Ø ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

Ø ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.

Ø ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

2. Automatic Shared Memory Management (ASMM).

Oracle database 10g’s New features. By default sga_target is enabled. But always sga_max_size is greater than or equal to sga_target. So once sga_target is enabled means, no need to set the auto tunned parameters like db_cache_size, shared_pool_size, large_pool_size, java_pool_size.So ASMM automatically readjusts the sizes of the main pools.

3. Automatic Database Diagnostic Monitor (ADDM).

Addm means automatic database diagnostic monitor and is a oracle database 10g’s new features

ADDM does analysis of the database, identifies problems and their potential causes, and comes up with recommendations for fixing the problems. It can call all other advisors also.

ADDM stores the snapshot in SYSAUX tablespace

The main features of the ADDM are as follows:

ADDM runs automatically in the background process MMON whenever a snapshot of in-memory statistics is taken. ADDM does analysis of the statistics collected between two snapshots.
ADDM analysis results are written back to the workload repository for further use.
ADDM uses the new wait and time statistics model, where activities with high time consumption are analyzed on a priority basis. This is where the big impact lies.
ADDM can also be invoked manually

The Automatic Database Diagnostic Monitor (ADDM) is a new diagnosis tool that runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and saves recommendations for improving performance in SYSAUX.

ADDM also gives the recommendations for

Sql tuning advisor

Sql access advisor

Segment advisor

Undo advisor

Redo log file size advisor

4. Automatic Workload Repository (AWR).

AWR is the oracle database 10g’s new features.Oracle8i introduced the Stats pack functionality which Oracle9i extended. In Oracle 10g stats pack has evolved into the Automatic Workload Repository (AWR). In your database some repository tables are created automatically when database is created. Each and every seconds AWR collects issues and activities in the database.

The repository is a source of information for several other Oracle 10g features

· Automatic Database Diagnostic Monitor

· SQL Tuning Advisor

· Undo Advisor

· Segment Advisor

To check the AWR status, Show parameter statistics level. If you want to enable the AWR report set statistics_ level=Typical. If you want to disable the AWR report statistics _level=normal.

When you enable the AWR report the MMON (Manageability Monitor) background process will active. It will write issues in the repository tables. By default every one hour AWR will generate a snapshot in SYSAUX tablespace and the default retention period is 7 days. If you want to take snapshot every 15 minutes and retention period 10 days means

BEGIN

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (

Retention => 14400, -- Minutes (= 30 Days). Current value retained if NULL.

Interval => 15); -- Minutes. Current value retained if NULL.

END;

5. Flashback Technologies.

Flashback technologies are the oracle database 10g’s new features.

Types of Flashback recoveries

There are six basic types of Flashback recovery, discussed below in detail:

Flashback Query
Flashback Version Query
Flashback Transaction Query
Flashback Table
Flashback Drop (Recycle Bin)
Flashback Database

How to configure the flashback

In mount stage only we can enable the flashback technologies and disable

SQL>alter database flashback on; -----Enable

SQL>alter database flashback off; --------Disable

Flashback Query

You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.

This example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.

The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:

SQL> SELECT * FROM employee AS OF TIMESTAMP

TO_TIMESTAMP ('2006-04-04 09:30:00', 'YYYY-MM-DD HH: MI: SS’)

WHERE name = 'JON';

This update then restores John's information to the employee table:

SQL> INSERT INTO employee

(SELECT * FROM employee AS OF TIMESTAMP

TO_TIMESTAMP ('2006-04-04 09:30:00', 'YYYY-MM-DD HH: MI: SS’)

WHERE name = 'JON');

Flashback Version Query

Not only can the DBA run a manual analysis, but this is a powerful tool for the application's developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions. Various elements for this are shown below:

VERSIONS_XID - The transaction id that created this version of the row
VERSIONS_OPERATION - The action that created this version of the row (such as delete, insert, and update)
VERSIONS_STARTSCN - The SCN in which this row version first occurred
VERSIONS_ENDSCN - The SCN in which this row version was changed.

For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates:

SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,

dept

from Dept versions between timestamp minvalue and maxvalue

order by VERSIONS_STARTTIME

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V Dept

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

01-DEC-06 03.57.12 PM 01-DEC-06 03.57.30 PM 0002002800000C61 I 10

01-DEC-06 03.57.30 PM 01-DEC-06 03.57.39 PM 000A000A00000029 U 11

01-DEC-06 03.57.39 PM 01-DEC-06 03.57.55 PM 000A000B00000029 U 12

We can automatically generate the undo statement using:

SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '000A000A00000029';

- output: update "SCOTT"."Dept" set "Dept" = '12' where ROWID = 'AAAMicAAEAAAAA/AAA';

Flashback Transaction Query

A Flashback Transaction Query is a query on a view called FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction. It uses the XID from above and shows the complete transaction steps for that XID. For example:

SQL> SELECT xid, start_scn START, commit_scn COMMIT,

operation OP, logon_user USER, undo_sql FROM flashback_transaction_query

WHERE xid = HEXTORAW('000200030000002D');

XID START COMMIT OP USER UNDO_SQL

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

000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"

("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"

where ROWID = 'AAAKD4AABAAAJ3BAAB';

000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"

set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';

000200030000002D 195243 113565 BEGIN HR

4 rows selected.

Flashback Table

Just as the flashback query helps to retrieve rows of a table, FLASHBACK TABLE helps to restore the state of a table to a certain point in time - even if a table structure change has occurred since then. The following simple command will take us to the table state at the specified timestamp:

SQL> FLASHBACK TABLE Employee TO

TIMESTAMP ('13-SEP-06 8:50:58','DD-MON-YY HH24: MI: SS');

Flashback Drop

The accidental dropping of objects has always been a problem for users and DBAs alike. Users soon realize their mistake, but by then it's too late - and until now there has been no easy way to recover those dropped tables, indexes, constraints, triggers, etc.

Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

The Recycle Bin

The Recycle Bin is a virtual container for all dropped objects. Under its lid, the objects occupy the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the
USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependent objects are not moved; they are simply renamed with the prefix BIN$$. You can continue to access the data in a dropped table, or even use Flashback Query on it.

SQL> FLASHBACK TABLE dept_test TO
BEFORE DROP;

Flashback Database

We have been talking about object recovery. But what if something happens to the whole database? This recovery quickly rewinds the complete database to a previous time, to correct any logical data corruption. This can be used with both RMAN & SQL*Plus.
Some of the options are:

FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);

FLASHBACK DATABASE TO TIMESTAMP my_date;

FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;

FLASHBACK DATABASE TO SCN my_scn

When the system comes back with FLASHBACK COMPLETE, open the database with Resetlogs.

For example:

-- Create a dummy table.

CREATE TABLE flashback_database_test (id NUMBER(10)

--Flashback 5 Minutes

CONN sys/password AS SYSDBA

SHUTDOWN IMMEDIATE

STARTUP MOUNT EXCLUSIVE

FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);

ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.

DESC flashback_database_test

6.Datapump (EXPDP/IMPDP) replaces Traditional EXP/IMP.

Datapump is a new features in oracle database 10g.That enables very high speed data and meta data loading and unloading of oracle databases.Noramally traditional EXP/IMP is a command line execution.But datapump is a job.whenever we execute the datapump job it creates a master control table and master control process will active.

When compare to exp/imp, datapump export process 3-5 times faster and import process 20-50 times faster.

If you want to more speed up the datapump process, you can specify parrellism is more than one. By default parellism is 1.

Database Level export and import using datapump

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

$ expdp sys/sys dumpfile=full.dmp logfile=full.log full=y

$ impdp sys/sys full=Y dumpfile=full.dmp logfile=full.log

Schema Level Export and import using datapump

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.

expdp sys/sys schemas=SCOTT dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp sys/sys schemas=SCOTT dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Table Level export and import using datapump

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

expdp sys/sys tables=EMP,DEPT dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp sys/sys tables=EMP,DEPT dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

7.Automatic Checkpoint Tunning(FAST_START_MTTR_TARGET).

Oracle 9i introduced the fast_start_mttr_target parameter.Oracle database 10g supports automatic checkpoint tunnig.It is enabled if fast_start_mttr_target is explicitly set to non zero values,It is disabled fast_start_mttr_target=0 and the value must be in 0-3600

Check-pointing is an important Oracle activity which records the highest system change number (SCN,) so that all data blocks less than or equal to the SCN are known to be written out to the data files

There are two parameters we are using parameter

1.Instance recovery within the given seconds

2.Redo log file size advisor

8.Automatic Undo Retention Tunning.

Oracle database 10g automatically tunes undo retention by collecting database statistics such as the longest running query on the undo generation rate. The automatic tunnig can be done by using undo_retention parameter.

The default value for this parameter is 900 seconds . If you don’t specify a value or if you specify zero then the oracle database 10g automatically tunes undo_retention for the undo tablespace using 900 as the minimum value.

As long as you always have enough undo space. This feature reduces the occurance of snapshot too old errors for your long runing queries.

9.Introduced Default permanent Tablespace (USERS).

In Oracle database 10g USERS tablespace is the default permanent tablespace.

If you create a user without specifying any tablespace in oracle database 10g, the oracle server itself assigns User’s default tablespace is USERS. We can bring the tablespace offline while the database is open. USERS tablespace recovery is possible while the database is in open mode.

10.Introduced SYSAUX tablespace.

SYSAUX tablespace is an AUXILLIARY tablespace to the SYSTEM tablespace.If the sysaux tablespace is unavailable some database features might fail, however core database fnctionallities will remain operational.

SYSAUX tablespace is the Oracle database 10g ‘s New features. By default other than SYS users created tabless are stored in SYSAUX.We can bring the tablespace offline while the database is open. SYSAUX tablespace recovery is possible while the database is in open mode.

Logminer occupant are also stored in sysaux tablespace.

Benefits

Reduces no of tablespace

Load reduction on system

Easier RAC management

11.Streams Technology(STREAMS POOL).

Streams pool is the oracle database 10g’s New features.This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle. To configure the Streams pool explicitly, specify the size of the pool in bytes using the streams_pool_size initialization parameter.

If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

Replication database environment we must configure the STREAMS pool.The concept of streams is extracting the new data from redo log buffer and propogate to the destination databases.For example ONLINE RAILWAY TICKETING SYSTEM

12.Introduced Bigfile Tablespace Option and Rename Tablespace Command.

In oracle database 10g supports bigfile tablespace option when we creating tablespaces.

Bigfile tablespace having only one datafile can have very large size. Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

It allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.

It simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.

BLOCK SIZE MAXIMUM DATA FILE SIZE MAXIMUM DATABASE SIZE

32 128 GB 8,388,608 GB

16 64 GB 4,194,304 GB

8 32 GB 2,097,152 GB

4 16 GB 1,048,579 GB

2 8 GB 524,288 GB

13.Cloning Database using DBCA(Database Configuration Assistant).

http://shaharear.blogspot.com/2008/04/oracle-database-cloning-using-dbca.html

14.Automatic SQL Tunning.

Automatic sql tuning is new features in oracle database 10g.SQL Tuning Advisor, newly introduced in 10g,It is primarily designed to replace the manual tuning of SQL statements and speed up the overall SQL tuning process.

SQL tuning is one of the challenging tasks faced by DBAs and developers. It is an interesting and creative, but at the same time, daunting task. Manual tuning of SQL statements requires a high level of expertise and experience to understand and design suitable access paths to yield better response times.

Optimizer mode

Normal Mode

In Normal mode optimizer compiles the sql and generates the execution plan.The Normal mode of the optimizer generates a reasionable execution plan for vast sql statements.

Tuning Mode

The optimizer performs additional analysis to check if the execution plan is generated in normal mode can be further improved. When called under the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. The tuning performed by the Automatic Tuning Optimizer is called Automatic SQL Tuning.

Sql Tuning advisor is used to access the tuning Mode and the tunning mode should be used for high load sql statements.

SQL dbms Packages

1.Create_tunnig_task

2.execute_tuning_task

3.report_tunnig_task

4.reset_tuning_task

5.delete_tuning_task

For example we take the below query as the longest runnig query

Select * from emp where eno=101;

Exec dbms_sqltune.create_tuning_task(

Sql_text =>’select * from emp where eno=101’,username=>’scott’,task_name=>’emp_tune’,

Scope=>’COMPREHENSIVE’,time_limit=>30;

If scope =limited means it will give limitted solutions,COMPREHENSIVE means it will analyse fully and provide solutions

15.Temporary Tablespace Group and Default Temporary Tablespace.

In Oracle database 10g supports Temporary tablespace group and default temporary tablespace for newly created user without specifing default temporary tablespace.

Syntax

Create temporary tablespace tablespace_name tempfile ‘/opt/oracle/temp01.dbf’ size 100m

Tablespace group group_name;

Create a temporary tablespace and implicitly add it to a temporary tablespace group.

CREATE TEMPORARY TABLESPACE temp01

TEMPFILE ‘/u02/oradata/temp01.dbs’ SIZE 500M

TABLESPACE GROUP tempgroup_a;

Add a temporary tablespace to a temporary tablespace group.

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Remove a temporary tablespace from a temporary tablespace group.

This statement will remove temporary tablespace temp04 from its original temporary tablespace group:

ALTER TABLESPACE temp04 TABLESPACE GROUP group_name;

Assign a user to a default temporary tablespace group.

In this case, user Scott will have multiple default temporary tablespaces (see Figure 3.1). A single SQL operation by Scott can use more than one temporary tablespace for sorting.

ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;

16.Recovery Manager Enhancements(RMAN).

In Oracle 9i RMAN default storage is $ORACLE_HOME/dbs.If you configure the flashback means in Oracle database default storage is $ORACLE_HOME/flash_recovery_area.

In Oracle database 10g supports fast incremental backups by enabling block change tracking.

The change tracking file has an initial size of 10Mb.we can increase upto 20Mb

The below command used to enable the block change tracking

SQL>alter database enable block change tracking; ----If you enabled the db_create_file_dest

Otherwise you have to specify the block change tracking file destination.

SQL>alter database enable block change tracking using file ‘/opt/oracle/blockct.txt’;

------ If you disable the db_create_file_dest

Drop database command introduced in oracle database 10g.

RMAN>drop database database_name including backups;

Compressed backupset

The

AS
COMPRESSED BACKUPSET

option of the BACKUP command allows RMAN to perform binary compression of backupsets. The resulting backupsets do not need to be uncompressed during recovery. It is most useful in the following circumstances:

You are performing disk-based backup with limited disk space.

You are performing backups across a network where network bandwidth is limiting.

You are performing backups to tape, CD or DVD where hardware compression is not available.

The following examples assume that some persistent parameters are configured in a similar manner to those listed below:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/MYSID/%d_DB_%u_%s_%p';

The

AS
COMPRESSED BACKUPSET

option can be used explicitly in the backup command:

# Whole database and archivelogs.

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

# Datafiles 1 and 5 only.

BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;

Alternatively the option can be defined using the CONFIGURE command:

# Configure compression.

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

# Whole database and archivelogs.

BACKUP DATABASE PLUS ARCHIVELOG;

Compression requires additional CPU cycles which may affect the performance of the database. For this reason it should not be used for tape backups where hardware compression is available.

Cross-Platform Tablespace Conversion

The

CONVERT
TABLESPACE

allows tablespaces to be transported between platforms with different byte orders. The mechanism for transporting a tablespaces is unchanged, this command merely converts the tablespace to allow the transport to work.

The platform of the source and destination platforms can be identified using the V$TRANSPORTABLE_PLATFORM view. The platform of the local server is not listed as no conversion in necessary for a matching platform.

SQL> SELECT platform_name FROM v$transportable_platform;

PLATFORM_NAME

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

Solaris[tm] OE (32-bit)

...

Microsoft Windows 64-bit for AMD

15 rows selected.

The tablespace conversion can take place on either the source or the destination server. The following examples show how the command is used in each case:

# Conversion on a Solaris source host to a Linux destincation file.

CONVERT TABLESPACE my_tablespace

TO PLATFORM 'Linux IA (32-bit)'

FORMAT='/tmp/transport_linux/%U';

# Conversion on a Linux destination host from a Solaris source file.

CONVERT DATAFILE=

'/tmp/transport_solaris/my_ts_file01.dbf',

'/tmp/transport_solaris/my_ts_file02.dbf'

FROM PLATFORM 'Solaris[tm] OE (32-bit)'

DB_FILE_NAME_CONVERT

'/tmp/transport_solaris','/u01/oradata/MYDB';

In the first example the converted files are placed in the directory specified by the FORMAT clause. In the second example the specified datafiles are converted to the local servers platform and placed in the correct directory specified by the DB_FILE_NAME_CONVERT clause.

17.DBMS Sheduler Packages and DBMS File Transfer Packages.

for exmple Creating a sheduler job

Begin

Dbms_sheduler.create_job(

Job_name=>’Incremental backup’

Job_type=>’Executables’

Job_action=>’/opt/oracle/rman/incre_backup.sh’

Start_date=>’Trunc(sysdate+1)+23/24’

Repeat_interval=>’Trunc(sysdate+1)+23/24’

/*night at 11PM*/

Comments=>’Nightly incremental backup’)

End;

There are three procedures within the dbms_file_transfer package:

COPY_FILE: This is useful for copying files locally on the database server.

GET_FILE: This is useful when a file on a remote database is to be transferred to a local file system through the local database connection.

PUT_FILE: Reads a local file and contacts a remote database to create a copy of the file in the remote file system

Assume that the tablespace containing a data file with excessive IO was created as follows:

CREATE TABLESPACE ts_hotspot LOGGING
DATAFILE '/usr/oracle/databases/mid101so/datafiles/hotspot.dbf' SIZE 5120K REUSE
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

The following SQL demonstrates how he can move the datafile from a hotspot to a different location without executing an OS command. As the DBA user, execute the following command.

CREATE DIRECTORY DEST_DIR AS '/tmp';
CREATE DIRECTORY SOURCE_DIR AS '/usr/oracle/databases/mid101so/datafiles';

As user SYS, create the necessary grants.

GRANT EXECUTE ON DBMS_FILE_TRANSFER to DBA
GRANT READ ON DIRECTORY SOURCE_DIR to DBA
GRANT WRITE ON DIRECTORY DEST_DIR to DBA

.As the DBA user, execute the following command.

ALTER TABLESPACE ts_hotspot READ ONLY;
BEGIN
dbms_file_transfer.copy_file( source_directory_object => 'SOURCE_DIR',
source_file_name => 'hotspot.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'hotspot.dbf');
END;
/
ALTER TABLESPACE ts_hotspot OFFLINE;
ALTER TABLESPACE ts_hotspot RENAME datafile '/usr/oracle/databases/mid101so/datafiles/hotspot.dbf' TO '/tmp/hotspot.dbf';
ALTER TABLESPACE ts_hotspot ONLINE;
ALTER TABLESPACE ts_hotspot READ WRITE;

******************************************************************************

Oracle 10g has many features compare to 9i like
asm,awr,addm,flaskback recovery,data pump.
Generating snap shot is much more flexible in 10 g compare to 9i.

10 G supports grid computing, ASM (Automatic storage management) and Memory management.

Oralce 10g is higher version of 9i , Oracle 10g has added a follwoing features

Transparent Data Encryption
Async commits
CONNECT ROLE can not only connect
New asmcmd utility for managing ASM storage
Grid computing - an extension of the clustering feature (Real Application Clusters)
Manageability improvements (self-tuning features)
Performance and scalability improvements
Automated Storage Management (ASM)
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Flashback operations available on row, transaction, table or database level
Ability to UNDROP a table from a recycle bin
Ability to rename tablespaces
Ability to transport tablespaces across machine types (E.g Windows to Unix)
New 'drop database' statement
New database scheduler - DBMS_SCHEDULER
DBMS_FILE_TRANSFER Package
Support for bigfile tablespaces that is up to 8 Exabytes in size
Data Pump - faster data movement with expdp and impdp.

In Oracle 9i after drop we can't rollback but we can do it in 10g.
Moreover, 10g has additional 149 features than 9i.

Passwords for DB Links are encrypted

Transparent Data Encryption
Async commits

1.Architecural component level changes

STREAMS POOL is the new componenet in oracle 10g.

When we configure in Replication database server environment like online transaction processing database,if we take online railway ticketing system is the best example

streams_pool_size – This specifies (in bytes) the size of the Streams pool, from which memory is allocated for Streams. If this parameter is not specified or is set to 0, then up to 10 percent of the shared pool is allocated for Streams.

What is Stream Replication?

Oracle streams extracts changes from transaction (redo) logs to propagate them to the destination database. It relies on methodlogy of redo log files for the database changes. The db transactions are recorded in the redo logs files, which are exracted by the log transport service. It can act as a DR solution. Since it is a pure data solution, data can be maintained as either a complete replica of the source database or as a subset of the data. It can be sent to multiple destinations. With the help of apply handler, you can have a variety of data transformations.

What is Stream pool in oracle 10g?

In a single database, you can specify that Streams memory be allocated from a new pool in the SGA called the Streams pool. To configure the Streams pool, specify the size of the pool in bytes using the STREAMS_POOL_SIZE initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams pool is zero, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

2.Database level changes

a.RMAN>drop database including backups;

and if we want drop the database,we can use DBCA its also new features in oracle 10g

b.flashback database including database level,tablespace level,table level,even row level

flashback table table_name to before drop;

desc flashback_version_query

table_name,table_owner,start_scn,commit_scn,start_timestamp,commit_timestamp;

flashback database to timestamp to_timesstamp(

c.datapump replaces the traditional export and import

datapump export 3 to 4 time faster than exp

database import 15 to 20 times faster than imp

datapump is a job so it maintained by a dedicated background process

master control background process will activate.it allocatessome worker process

we can also allocate some worker process in order to mention by parrellism=4

anytime we can start and stop

d.recyclebin feature dba_recyclebin

e.database cloning using DBCA(database configuration assistant)

f.DBMS file transfer package

ALTER TABLESPACE ts_hotspot READ ONLY;
BEGIN
dbms_file_transfer.copy_file( source_directory_object => 'SOURCE_DIR',
source_file_name => 'hotspot.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'hotspot.dbf');
END;
/
ALTER TABLESPACE ts_hotspot OFFLINE;
ALTER TABLESPACE ts_hotspot RENAME datafile '/usr/oracle/databases/mid101so/datafiles/hotspot.dbf' TO '/tmp/hotspot.dbf';
ALTER TABLESPACE ts_hotspot ONLINE;
ALTER TABLESPACE ts_hotspot READ WRITE;

3.Tablespace level changes

There are two additional tablespace is there in oracle 10g

A.USERS tablespace is the default permanent tablespace in oracle users

b.SYSAUX tablespace is the other then sys user created tables,function,procedure,tiggers are stored

in sysaux tablespace and LOGMINER UTILITY ALSO STORED IN SYSAUX TABLESPACE

c.rename tablespace command

d.bigfile tablespace option

e.default temporary tablespace

f.temporary tablespace group

g.alter system set undo_tablespaces=UNDO2 scope=both;but in 9i we cannot switch the undo

tablespace while the database is in open or read write mode

4.Memory Management

a.Automatic storage management(ASM).

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

INSTANCE_TYPE=ASM

Next, using SQL*Plus connect to the ide instance.

export ORACLE_SID=+ASM

sqlplus / as sysdba

Create an spfile using the contents of the init+ASM.ora file.

SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';

File created.

Finally, start the instance with the NOMOUNT option.

SQL> startup nomount

ASM instance started

Total System Global Area  125829120 bytes

Fixed Size                  1301456 bytes

Variable Size             124527664 bytes

Database Buffers                  0 bytes

Redo Buffers                      0 bytes

SQL>

The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.

SQL> shutdown

ASM instance shutdown

SQL>

Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:

DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST

Startup and Shutdown of ASM Instances

ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:

FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT - Starts the ASM instance without mounting any disk groups.
OPEN - This is not a valid option for an ASM instance.

The options for the SHUTDOWN command are:

NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.

Administering ASM Disk Groups

Disks

Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:

NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.

In addition failure groups and preferred names for disks can be defined. If the NAME clause is omitted the disks are given a system generated name like "disk_group_1_0001". The FORCE option can be used to move a disk from another disk group into this one.

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY

  FAILGROUP failure_group_1 DISK

    '/devices/diska1' NAME diska1,

    '/devices/diska2' NAME diska2

  FAILGROUP failure_group_2 DISK

    '/devices/diskb1' NAME diskb1,

    '/devices/diskb2' NAME diskb2;

Disk groups can be deleted using the DROP DISKGROUP statement.

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Disks can be added or removed from disk groups using the ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.

-- Add disks.

ALTER DISKGROUP disk_group_1 ADD DISK

  '/devices/disk*3',

  '/devices/disk*4';

-- Drop a disk.

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The statement can be used to resize individual disks, all disks in a failure group or all disks in the disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned by the OS.

-- Resize a specific disk.

ALTER DISKGROUP disk_group_1

  RESIZE DISK diska1 SIZE 100G;

-- Resize all disks in a failure group.

ALTER DISKGROUP disk_group_1

  RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

-- Resize all disks in a disk group.

ALTER DISKGROUP disk_group_1

  RESIZE ALL SIZE 100G;

The

UNDROP
DISKS

clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.

ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.

ALTER DISKGROUP ALL DISMOUNT;

ALTER DISKGROUP ALL MOUNT;

ALTER DISKGROUP disk_group_1 DISMOUNT;

ALTER DISKGROUP disk_group_1 MOUNT;

Templates

Templates are named groups of attributes that can be applied to the files within a disk group. The following example show how templates can be created, altered and dropped.

-- Create a new template.

ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.

ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.

ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;

How is datafile stored in ASM?
ASM uses disk groups to store data files.

What is an ASM disk group?
An ASM disk group is a group of disks that ASM manages as an unit.Within the
diskgroups an file system interface maintains the Oracle database files. ASM simplifies
database storage by consolidating disks into disk groups.This reduces I/O overhead.

Will the SGA(System Global Area) of ASM is comparable in size to an Oracle database instance?

No.The SGA size of an ASM is smaller than that of an Oracle database instance,as an ASM performs fewer tasks than a database.

Can ASM instance mount a database?

No.An ASM instance can mount a disk group that can be used by a database instance.

What is ASM metadata and where is it present?

ASM metadata is the information that ASM uses to control the disk group.It is present within a disk group.

Ainistrator to reference disk groups rather than individual disks and files, which are managed by ASM. b.Automatic shared memory management(ASMM)

Sga_target

5.Tuning

a.optimizer mode CBO(cost based optimizer) and there no RBO(rule based optimizer)

b.Automatic database diagonastic monitor(ADDM)

this is ultimate solution for oracle database tunning.The addm automatically identifies the bottlenecks within the oracle server abd its aditionally makes the recommendations of the option available to fix them.it is a top down analysis.

1.sql tuning advisor

2.sql access advisor

3.pga advisor

4.sga advisor

5.undo advisor

c.Automatic workload repository(AWR) replaces statspack report

d.Automatic undo_retention tunnig

e.undo tunnig advisor

f.atomatic checkpoint tuning (fast_start_mttr_target set to higher value means the features set,if set 0 means it is disabled).

g.sql tunnig advisor DBMS package like create_tunnig_task,execute_tunnig_task,reset_tunnig_task,delete_tunnig_task,

report_tunnig_task

h.DBMS job sheduler Package

automatic task with sheduler

for exmple Creating a sheduler job

Begin

Dbms_sheduler.create_job(

Job_name=>’Incremental backup’

Job_type=>’Executables’

Job_action=>’/opt/oracle/rman/incre_backup.sh’

Start_date=>’Trunc(sysdate+1)+23/24’

Repeat_interval=>’Trunc(sysdate+1)+23/24’

/*night at 11PM*/

Comments=>’Nightly incremental backup’)

End;

DBMS_FILE_TRANSFER

dbms_file_transfer:

Miscellanious features

In Oracle 10g, the tablespace disk space utilization is proactively managed by the database. The Server Alert Mechanism monitors Tablespace disk space utilization.

Information gathered into the AWR is also used to do the growth trend analysis and capacity planning of the database. The background process (MMON) verifies tablespace thresholds. The threshold is reached if the allocated space in the tablespace has reached a certain percentage of the total size of the tablespace (if the threshold is configured as a percentage), or when the total allocated space has reached a certain value that is set by you. An alert is triggered when the threshold has been reached.

Another important and useful feature introduced in 10g is the facility to shrink the segment. In the previous releases of the Oracle database, moving or redefining the segment was the only way to free space once allocated below the segment's HWM.

In Oracle 10g, you can now shrink segments. When a segment is shrunk, its data is compacted, its HWM is pushed down, and unused space is released back to the tablespace containing the segment. This is possible for the segments in Automatic Segment Space Managed (ASSM) tablespaces only. For example, to shrink the table sales_items, use the statement:

ALTER TABLE sales_items SHRINK SPACE CASCADE;

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 9, 2013

Concepts Oracle