Oracle Database

Oracle database fragmentation step by step explaining how to sort out

kods.net — Wed, 03 Feb 2010 18:54:46+0000

Oracle as a large database, widely used in finance, telecommunications, electric power, civil aviation data throughput of large, widespread adoption of computer networks, an important sector. For the system administrator is concerned, how to ensure stable operation of the network, how to improve database performance, to make it more safe and effective, it is very important. Affect database performance, as one of the major factors - the database fragments, DBA should arouse enough attention to discover and organize the fragments Naishi DBA a fundamental maintain the content.

1, debris is how to generate the

When a database is generated, it will be divided into as table space (Tablespace) multiple logical segment (Segment), such as the system (System) table space, the Provisional (Temporary) table space. A table space can contain multiple data range (Extent) and one or more free range blocks, namely, free-space (Free Space).

Table space, segment, range, the logic of the relationship between free-space is as follows:

When the table space to generate a paragraph, the effective free space from the table space for this segment in the initial scope of allocated space. In the full scope of these initial data, the section would be a request to add another range. This expansion process will continue indefinitely, until it reaches the maximum range of values, or the space in the table has no free space for the next range. Ideal state is a segment of the data can be stored in a single range. In this way, all data is stored near the paragraph when the other data, and search data can be a bit less pointers. However, a number of areas which segment contains a large number exist, no measures can guarantee that these areas are adjacent to storage, when a space to meet requirements, the database is no longer adjacent to the freedom of the scope of consolidation (unless no choice) , but look for the table space, the largest free range to use. This will gradually form a more and more discrete, separate, smaller free space, or debris. For example:

2, the impact of debris on the system

Over time, database-based application system widely used, the resulting fragments will be more and more of the database will have the following two main effects:

1) result in reduced system performance.

As mentioned above, when a space to meet the requirements, the database will be the first to find the current largest free range, while the "maximum" free range became smaller and smaller, to find a large enough scope of freedom has become increasingly difficult, leading to the table space, the speed of obstacles, so that the database space allocation increasingly away from the ideal state;

2) wasting a lot of table space.

Although some of the scope of freedom (such as table space pctincrease non-0) will be SMON (system monitor) background process periodically merge, but there is always a part of the scope of freedom can not be automatically merged, wasting a lot of table space.

3, calculation of the scope of freedom of debris

As the free space debris is composed of several parts, such as the range of volume, the largest range of sizes and so on, we can FSFI - Free Space Fragmentation Index (free space debris index) values to visual expression:

FSFI = 100 * SQRT (max (extent) / sum (extents)) * 1/SQRT (SQRT (count (extents)))

As can be seen, FSFI the maximum possible value is 100 (an ideal single-file table space). With the increase in the scope, FSFI value decreased slowly and, with the greatest range of size reduction, FSFI values will decline rapidly.

The following script can be used to calculate the FSFI value:

rem FSFI Value Compute 
rem fsfi.sql 
column FSFI format 999,99 
select tablespace_name,sqrt(max(blocks)/sum(blocks))* 
(100/sqrt(sqrt(count(blocks)))) FSFI 
from dba_free_space 
group by tablespace_name order by 1; 
spool fsfi.rep; 
/ 
spool off;

For example, in a database, run the script fsfi.sql, get the following FSFI values:

TABLESPACE_NAME FSFI 

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

RBS 74.06 

SYSTEM 100.00 

TEMP 22.82 

TOOLS 75.79 

USERS 100.00 

USER_TOOLS 100.00 

YDCX_DATA 47.34 

YDCX_IDX 57.19 

YDJF_DATA 33.80 

YDJF_IDX 75.55

The statistics of the database FSFI values, you can use it as a comparable argument. In a country with effective enough free space, and the FSFI value of more than 30 of the table space, rarely meet the effective free space. When a space will be near the comparable parameters, we need to do a defragmentation.

4, free range defrag

1) Table space pctincrease the value of non-0.

Can be the default tablespace storage parameters pctincrease to non-0. Normally be set to 1, such as:

alter tablespace temp 

default storage(pctincrease 1);

This will be free range SMON automatically merged. The scope of freedom can also be merged manually: alter tablespace temp coalesce.

5, paragraph defragmentation

We know that the segment formed by the range. In some cases, it is necessary to sort out the debris section. To view the segment information, you can view the data dictionary dba_segments, the scope of information can view the data dictionary dba_extents. If the segment fragments too much of its data compression to a range of the most simple way is to use the correct storage parameters for the reconstruction of this segment, and then the old data in the table inserted into a new table, and delete the old table. This process can use Import / Export (input / output) tool to complete.

Export () command has a (compressed) flag, this flag will be raised at the reading table Export table to determine the amount of physical space allocated, it will dump file is written to the output of the initialization of a new storage parameters - equal to all the the allocated space. If this table is off, then use the Import () tools to regenerate. In this way, its data will be placed in a new, larger initial segment. For example:

exp user/password file=exp.dmp compress=Y grants=Y indexes=Y 

tables=(table1,table2);

If the output of success, from the database to delete the output table, and then enter the dump file from the output table:

imp user / password file = exp.dmp commit = Y buffer = 64000 full = Y

This method can be used for the entire database.

Oracle database, for more than a simple analysis of debris, calculation method and collate for reference only. Database performance optimization is a high technological content, while the need for adequate patient, careful and meticulous work. A bit of debris on the database,

Here is how the automatic processing of a table space debris code, want to be useful at all to see above

Coalesce Tablespace Automatically 
This technique comes from Sandeep
 Naik, a database administrator 
for GSXXI, Inc. in New York City, New York 
Here is a handy script which can be 
scheduled to automatically run 
and coalesces the tablespaces. 
This script is designed to run in NT 
but can be run in any operating system 
by slight modifications in the path where the file spools 
from the SQLPLUS environment. 
It assumes that the user who runs the script 
has priviledges to view the data dictionary. 
Start of code 
-------------------------------------- 
sqlplus / 
prompt this script will coalesce the 
tablespace automatically 
set verify off; 
set termout off; 
set head off; 
spool c: empcoalesce.log 
select alter tablespace 
||TABLESPACE_NAME|| coalesce ; 
from DBA_FREE_SPACE_COALESCED where 
PERCENT_EXTENTS_COALESCED <100 
or PERCENT_BLOCKS_COALESCED<100 ; 
spool off; 
@ c: empcoalesce.log 
set head on; 
set termout on; 
set verify on; 
prompt Tablespaces are coalesced successfully

SQL optimization (3) - four index 2009-03-28 13:10:06
SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
An example of the use of TKPROF 2009-02-26 01:38:19

How to quickly identify lock waiting for Oracle Database

kods.net — Wed, 03 Feb 2010 13:30:49+0000

In a large database systems, in order to ensure data consistency, in the right data in the database to operate, the system will be conducted on the data corresponding lock.

---- These lock in the "read lock", "exclusive lock", "shared exclusive lock" and many other types, and each type has "row-level locking" (one lock a record), "page-level lock" (a locked one, that is stored in the database can be recorded in the smallest allocation unit), "table-level lock" (lock the entire table).

---- If it is "row-level exclusive lock", then, subject to being locked in the bank, the other rows in the table may be modified by other users (Update) or remove (delete) operations, if it is " table-level exclusive lock ", then all other users can only query the table (select) operation, which can not be right to modify or delete any record. When the program modifications made to submit (commit), or after the rollback (rollback), the locked resources would be released, allowing other users to operate.

---- But, sometimes, because of the reasons for locking resources, not long after the submission of their work; or because of the user's reasons, such as a need to modify the transfer of data, not timely revised and submitted, but is placed on one side; or because of the way client-server client a "crash" while the server is not detected, resulting in the release of locked resources in a timely manner not affecting the operation of other users.

---- So, how to quickly diagnose and resolve user locking of resources is the database administrator to lock a challenge.

---- As the database application system more complex, once the resources are not due to the timely release of the lock case, it would create the same table to operate on a large number of users can not operate, thus affecting the use of the system. At this point, DBA should try to quickly solve the problem. However, due to the implementation of the Oracle 8.0.x "Get the user is waiting for the lock resource name" query

select a.username, a.sid, a.serial#, b.id1
  from v$session a, v$lock b
  where a.lockwait = b.kaddr

---- very slow (in Oracle 7.3.4 in the implementation of the fast), and the implementation of the "Find blocking other user's process "query

    select a.username, a.sid, a.serial#, b.id1
      from v$session a, v$lock b
     where b.id1 in
           (select distinct e.id1
              from v$session d, v$lock e
             where d.lockwait = e.kaddr)
       and a.sid = b.sid
       and b.request = 0

---- implementation was also very slow. Therefore, often forced by v $ session in the status of "inactive" (inactive) and the last for hours of operation to the current more than 20 minutes (last_call_et> 20 * 60 seconds) of the user process clear, then that makes the problem is solution.

---- However, this method is actually "throwing the baby out with the dirty water, together." Because some users of the process, although also "inactive", and also has been a long time without activity, but it is because they are in lock wait state.

---- So, I have come up with a solution. The problem occurs through the v $ lock, v $ session view the related record-keeping in their own table set up, and then query the table, then the higher speed, you can quickly find the problem. The actual use, the effect was very good. After receiving the user to reflect a few seconds to lock up resources to identify the impact due to the process of other users, and make the appropriate treatment.

---- First, dba capacity (not necessarily for the system) log into the database, create three basic tables: my_session, my_lock, my_sqltext, and the column will be carried out inquiries to establish the corresponding index. Statement is as follows: rem from v $ session view, out of concern to the field, creating my_session table and fields in the query to use to create indexes to speed up the query speed

drop table my_session;
create table my_session
as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
  from v$session a
 where 1=2 ;

create unique index my_session_u1 on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);

---- rem from v $ lock view out the field, create my_lock table and fields in the query to use to create indexes to speed up the query speed

drop table my_lock;
create table my_lock
as
select id1, kaddr, sid, request,type
  from v$lock
 where 1=2;

create index my_lock_n1 on my_lock(sid);
create index my_lock_n2 on my_lock(kaddr);

---- rem from v $ sqltext view, remove the field, create my_sqltext table, and in the query to use to field to create an index to speed up the query speed

drop table my_sqltext;
create table my_sqltext
as
select hash_value , sql_text
  from v$sqltext
 where 1=2;

create index my_sqltext_n1 on my_sqltext ( hash_value);

---- Then, create a SQL script file, so that the need arises directly from the SQL * Plus calls. Among them, first using truncate table table command will remove records in the table. The reason to use truncate command, rather than using delete command, because the delete command, it will produce a repeat record, slow, and the amount of space the index has not really released, if done repeatedly insert and delete, then the index of the accounting for the space will continue to increase query speed will be slow. The truncate command does not produce a repeat records, delete faster than the speed of implementation, and the index space is accordingly released. Remove the record, and then three view records into their own to create the three tables. Finally, its query, because the index, and because the conditions in the insertion After filtration, the number of records there are relatively few, so check very quickly as soon as you can see the results.

----At this point, if it found that the process of blocking the process of other users is a normal operation, you can notify the user to submit them to achieve the purpose of releasing the lock of resources; if it is not normal operation, that is, its status is "inactive", and its last_call_et have more time, you can execute the following statement to clear the process, the system will automatically roll back them to release the locked resources.

   alter system kill session 'sid, serial#';

---- SQL script is as follows:

set echo off
set feedback off
prompt '删除旧记录.....'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;

prompt '获取数据.....'
insert into my_session
select a.username, a.sid, a.serial#,
       a.lockwait, a.machine,a.status,
       a.last_call_et,a.sql_hash_value,a.program
  from v$session a
 where nvl(a.username,'NULL')< >'NULL;

insert into my_lock
select id1, kaddr, sid, request,type
  from v$lock;

insert into my_sqltext
select hash_value , sql_text
  from v$sqltext s, my_session m
 where s.hash_value=m.sql_hash_value;

column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999

prompt "正在等待别人的用户"
select a.sid, a.serial#, 
a.machine,a.last_call_et, a.username, b.id1
  from my_session a, my_lock b
 where a.lockwait = b.kaddr;

prompt "被等待的用户"
select a.sid, a.serial#, 
a.	machine,  a.last_call_et,a.username,
b.	 b.type,a.status,b.id1
  from my_session a, my_lock b
 where b.id1 in
      (select distinct e.id1
         from my_session d, my_lock e
        where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and b.request=0;

prompt "查出其  sql "
select a.username, a.sid, a.serial#,
 b.id1, b.type, c.sql_text
  from my_session a, my_lock b, my_sqltext c
 where b.id1 in
       (select distinct e.id1
          from my_session d, my_lock e
         where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and b.request=0
   and c.hash_value =a.sql_hash_value;

---- the above ideas can also be used in other large database systems such as Informix, Sybase, DB2 in. By using this script, you can greatly improve the acquisition system that is currently waiting for the lock, and hence timely resolution of database application system in the lock waiting for problems. Moreover, since the program has actually removed his name and the corresponding sql statement, it can later be recorded and handed over to the developers for analysis and fundamentally solved.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database, the operation method of the partition table

kods.net — Wed, 03 Feb 2010 08:36:07+0000

Abstract: In a large number of business data processing projects, consider using the partition table to improve the performance of application systems and to facilitate data management, This paper describes the use of the partition table.

In the large enterprise application or enterprise-level database applications, to handle the amount of data can often be the order of tens to hundreds of GB, and some even go to TB level. While the storage media and data processing technology is also fast, but still can not meet the needs of users, in order to enable users of the large amount of data in the read and write operations and queries faster, Oracle provides a table and index partitioning technology, in order to improve the performance of large-scale application systems.

Advantages of using the partition:

* Enhanced usability: If the table a partition fails, the table in other divisions of the data is still available;

* Easy to maintain: If the table a partition fails, the data need to be repaired, only you can repair the partition;

* Balanced I / O: can be mapped to different partitions in order to balance the disk I / O, improving the overall system performance;

* To improve the query performance: The partition object queries can only search your area of concern to improve the retrieval speed.

Oracle database table or index partition in three ways:

* Range partitioning

· Hash partition (hash partition)

* Composite Partitioning

The following examples will be these three kinds of partitioning methods, respectively, to illustrate the use of the partition table. To test convenience, let us build three table spaces.

create tablespace dinya_space01
datafile '/ test/demo/oracle/demodata/dinya01.dnf' size 50M
create tablespace dinya_space01
datafile '/ test/demo/oracle/demodata/dinya02.dnf' size 50M
create tablespace dinya_space01
datafile '/ test/demo/oracle/demodata/dinya03.dnf' size 50M

1.1. Partition table to create

1.1.1. Range partitioning

Range partitioning is a data table, the value of the scope of a district, according to a certain range of values, decided that the data is stored in which partition. If under the serial number area, according to business records such as the creation date of partition.

Requirements Description: There is a material transaction table, the table name: material_transactions. The table in the future there may be 10 million data records. Required to build the table when the use of the partition table. At this time we can use the serial number partition three zones, each zone is expected to store 30 million of data, you can also use the date of partition, such as every five years, the data is stored in a partition.

According to the serial number of transactions to build partition table:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number (8) not null,
5 item_description varchar2 (300),
6 transaction_date date not null
7)
8 partition by range (transaction_id)
9 (
10 partition part_01 values less than (30000000) tablespace dinya_space01,
11 partition part_02 values less than (60000000) tablespace dinya_space02,
12 partition part_03 values less than (maxvalue) tablespace dinya_space03
13);
Table created.

The successful construction of the table, according to the transaction number, transaction ID in the 30 million below the record will be stored in a table space dinya_space01, the district is named: par_01, in between 30000000-60000000 record is stored in the second table Space:

dinya_space02, the district is named: par_02, the transaction ID in more than 60 million records stored in a third tablespace dinya_space03, the district is named par_03.

According to date of the transaction to build partition table:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number (8) not null,

5 item_description varchar2 (300),
6 transaction_date date not null
7)
8 partition by range (transaction_date)
9 (
10 partition part_01 values less than (to_date ('2006-01-01 ',' yyyy-mm-dd '))
tablespace dinya_space01,
11 partition part_02 values less than (to_date ('2010-01-01 ',' yyyy-mm-dd '))
tablespace dinya_space02,
12 partition part_03 values less than (maxvalue) tablespace dinya_space03
13);
Table created.

So that we were built, transaction number and transaction date to partition the partition table. Insert the data for each time, the system will be based on the value of the specified field to automatically record store to the development of a partition (table space).

Of course, we can also demand the use of two fields in the scope of the distribution to partition, such as the partition by range (transaction_id, transaction_date), regional conditions, the value also changes accordingly, the reader is self-testing.

1.1.2. Hash partition (hash partition)

Hash partition by specifying a uniform distribution of the data partition numbers to a partition type, because through the I / O devices on a hash partition, so that these partitions the same size. If the material transaction data in the table to the hash of the transaction ID stored in the designated space of three tables:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number (8) not null,
5 item_description varchar2 (300),
6 transaction_date date
7)
8 partition by hash (transaction_id)
9 (
10 partition part_01 tablespace dinya_space01,
11 partition part_02 tablespace dinya_space02,
12 partition part_03 tablespace dinya_space03
13);
Table created.

The successful construction of the table, this time inserting the data, the system will record the hash will be inserted transaction_id three partitions, here is three different table space.

1.1.3. Composite Partitioning

Sometimes we need to range partitioning, each sub-region re-hash of data distributed among several tables space, so we will use the composite partition. Composite partition is the first use of partitions, and then re-use in each sub-region of a hash partition partition methods, such as the material record of the transaction by the time partition, and then the data in each partition is divided into three sub-partitions, using the data, scattered listed in the table is stored in the three designated space:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number (8) not null,
5 item_description varchar2 (300),
6 transaction_date date
7)
8 partition by range (transaction_date) subpartition by hash (transaction_id)
9 subpartitions 3 store in (dinya_space01, dinya_space02, dinya_space03)
10 (
11 partition part_01 values less than (to_date ('2006-01-01 ',' yyyy-mm-dd ')),
12 partition part_02 values less than (to_date ('2010-01-01 ',' yyyy-mm-dd ')),
13 partition part_03 values less than (maxvalue)
14);
Table created.

This example, the first trading date range under the partition, and then record the transaction ID will be stored in three hash table space.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database character set conversion law of a comprehensive analysis of

kods.net — Fri, 29 Jan 2010 10:35:02+0000

Oracle database character set conversion law of a comprehensive analysis of
rn

rn

rn
A good article in this chapter, specific and comprehensive.
rn

rn
rn
As an Oracle database users, for the Export and Import two commands will not feel strange, because this is exactly what we both frequently used for data backup and recovery tool. However, in using these two commands have taken place during the Oracle character set issues, often to a number of Oracle users to unnecessary trouble and unnecessary data loss. This will Export and Import process Oracle character set conversion laws and the use of these two commands will make a summary note. rn

The reasons for character set conversion

rn
Export, Import process, as shown above can be seen from this diagram there are four related to the character set, which is four character sets is precisely the cause of the inconsistency Oracle character set conversion for reasons. rn

* Source database character set; rn

* Export the course of a user session character set; rn

* Import the course of a user session character set; rn

* Target database character set. rn

In the Export and Import process, if there is character set conversion affect the four factors that are inconsistent, then the Oracle character set conversion can occur, namely: rn

In the Export process, if the source database character sets and Export user session character set inconsistent, character set conversion will occur, and in the exported binary format Dmp files stored in the head a few bytes of Export user session character set ID. In this conversion process may occur in data loss. rn

Example 1: If the source database to use ZHS16GBK, while the Export user session character set to use US7ASCII, because ZHS16GBK are 8-bit character set, while the US7ASCII is 7-bit character set, this conversion process, the Chinese characters in the US7ASCII not be able to find, etc. characters, so all Chinese characters will be lost and turned into "??" form, that is generated after such a conversion Dmp file data loss has occurred. rn

Example 2: If the source database to use ZHS16GBK, while the Export user session character set to use ZHS16CGB231280, but ZHS16GBK character set is a superset of character set ZHS16CGB231280, this process can correct the majority of character transformation, only a few character sets beyond ZHS16CGB231280 characters become "??" form. If the source database using ZHS16CGB231280 character set, while the Export the user's session using ZHS16GBK character set, then the conversion process can be completely successful conversion. rn

In the Import to the target database during the conversion process, its character set conversion is exactly the opposite happened during Export contrary, there shall not repeat. rn

Dmp exported in the Export file, containing the Export user session character set. In the Import process, the first place is the Dmp document character set (ie, Export user session character set) to the Import user session character set conversion. If this conversion process can not be completed properly, Import to the target database, the import process also can not be completed. rn

Carried out the correct character set conversion rn

Under normal circumstances, we use Oracle's Export and Import process, do not want to place characters in the conversion, but sometimes such a conversion is necessary. If we install the Oracle database, select ZHS16CGB231280 character set, because of this character set is a small Chinese character set, for some characters can not be correctly said that this needs to be resolved using the ZHS16GBK character set, this time will be carried out character set conversion. rn

In order to ensure that the Export, Import process, Oracle character set conversion or incorrect conversion does not occur, we recommend the best course of this process before, check the source database character sets and Export user session character set is consistent, the source database and target database character set characters sets are the same target database character with the Import user session character sets are the same. If you can guarantee that this four character sets are identical, then Export, Import process, Oracle character set conversion would not have happened. rn

Using the following way to check the database character set: rn

By InitXXXX.ora file view; rn

With SQL statements see: SELECT NAME, VALUE $ FROM SYS.PROPS $ WHERE NAME = 'NLS_CHARACTERSET'. rn

For Export, Import user session character set, in the Windows system registry can also be in the NLS_LANG to view or modify, for Unix System provides the user's environment variable by setting the NLS_LANG to view or modify. rn

In particular, note that, Oracle database character set is usually determined at the time created, once the stored user data should not be modified after, because its data is stored using the character set, and change the other character sets, the original data not be able to correctly expressed. But if it does want to change the character set, you can be achieved through the following steps: rn

Backup the database to delete the original data (can be physical backup, such as the use of Export, please note that to ensure the character set conversion or data does not occur without loss); rn

Use Internal user updates sys.props $ table character set: rn

rn rn rn rn

Update sys.props$ set name=‘Dest.CharSet’ rnWhere name=‘NLS_CHARACTERSET’; COMMIT;

Restart the database; rn

Restore the data. rn

The following conversion between character sets is feasible: rn

Character set collections sets the parent to the character set conversion is feasible, such as ZHS16CGB231280 to ZHS16GBK conversion; and character set the parent class to the character set conversion collected works, it will lose some of the data. rn

Contains only English characters of the double-byte character set data can also be converted to single-byte character sets, such as ZHS16GBK (English Only) can US7ASCII converted properly. rn

The scope of the same single-byte coded character set conversion between each other can often be. rn

Please note that the data loss is not mentioned here, refers to a character set A character set into another, after B, B can be further from the character set into the correct character set A or B character set A character set that can correctly converted over data.

rn
Character set of the program rn

According to how many bytes a character need to be expressed in the character set can be divided into single-byte character sets and multi-byte character sets. Among them, single-byte character set is divided into 7-bit character set and the 8-bit character sets. Single-byte 7-bit coded character set are US7ASC Ⅱ, single-byte 8-bit coded character set are in line with ISO 8859-1 standard specifies the WE8ISO8859P1 so. Multi-byte code is divided into fixed-length (length greater than or equal to 2) encoding mode and non-fixed length encoding mode. Multi-byte coded character set of the ZHS16GBK, ZHS16CGB231280, JA16SJIS such as is used to represent a character two-byte character set, also known as double-byte character sets. rn

An alphabet is a character, a Chinese characters are a few characters do? We know that a Chinese character is a double-byte characters, but it has several characters related to its database character set. If the database character set to use single-byte US7ASCII, then a Chinese characters are the two characters; If the database character set to use double-byte character set ZHS16GBK, is a Chinese character is a character. On this point you can use the Oracle function Substr been proven. rn

When using the US7ASC Ⅱ character sets: rn

Select substr ( 'Northeastern University', 1,2) from dual; rn

Statement to the implementation of the results returned 'the East'. rn

ZHS16GBK character set to use when: rn

Select substr ( 'Northeastern University', 1,2) from dual; rn

Statement to the implementation of the results returned 'the Northeast'. rn

Select the appropriate database character set rn

Select the database character set should consider the following items: rn

1. Database need to support what language rn

Choice for the database character set, often they will find several character sets are suitable for your current language requirements, such as Simplified Chinese, there ZHS16GBK and ZHSCGB231280 other character sets to choose from, which should be selected? In selecting character set should be considered when the future system requirements to the database. If you know the future, the database should be extended to support different languages, choose a wider range of characters that assembly is a better idea. rn

2. System resources and application of the interaction between the nature of rn

Select the database character set should ensure that the operating system and applications seamless connection between. If you select the operating system character set is not a valid character set, the system will need to do between these two character conversion. In such a character during the conversion process, there may occur the phenomenon of a number of characters is lost. From one to another character set A character set B during the conversion process, A the characters must be equivalent in B can be found in the character, otherwise the "?" Instead. In this sense, if the scope of two kinds of character set encoding is the same, you can convert each other. rn

Character set conversion process can affect system performance, therefore, should ensure that the client and server-side have the same character set in order to avoid character set conversion, but also can improve a certain degree of system performance. rn

3. The system performance requirements rn

Different database character set for the performance of the database have a certain effect. To get the best database performance, choose the database character set should avoid character conversion, and to select the desired language for the most efficient coding efficiency. Typically, single-byte character set than the multi-byte character sets have better performance in smaller space requirements are also more. rn

4. Other limitations rn

In a suitable choice for the database character set should refer to the corresponding versions of Oracle documentation, check the Oracle character set for a number of limitations. Such as the Oracle 8.1.5 version, the following character sets can not be used: JA16EUCFIXED, ZHS16GBKFIXED, JA16DBCSFIXED, KO16DBCSFIXED, ZHS16DBCSFIXED, JA16SJISFIXED, ZHT32TRISFIXED. rn

In summary, the correct understanding of Oracle character set conversion process will enable us to avoid unnecessary trouble and data loss. The rational use of Oracle character set conversion process can also help us to correctly convert from one character set to another character set to meet our demand for a variety of applications.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle Database Backup and Recovery Summary -exp/imp

kods.net — Wed, 27 Jan 2010 00:41:21+0000

1.1 Basic commands

1. Get Help

$ Exp help = y

$ Imp help = y

2. Three kinds of work

(1) an interactive way

$ Exp / / Then prompted to enter the parameters needed for

(2) command line mode

$ Exp user / pwd @ dbname file = / oracle / test.dmp full = y / / Enter the required command line parameters

(3) parameters of papers

$ Exp parfile = username.par / / In the parameter file, enter the required parameters

Parameter file username.par content userid = username / userpassword buffer = 8192000

compress = n grants = y

file = / oracle / test.dmp full = y

3. Three models

(1) Table means the table will specify the data export / import.

Export:

Export one or a few table:

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log tables = table1, table2

Export of a portion of the data tables

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log tables = table1 query = \ "where col1 = \ '... \'

and col2 \ <... \ "

Import:

Import one or a few tables

$ imp user / pwd file = / dir / xxx.dmp log = xxx.log tables = table1, table2 fromuser = dbuser touser = dbuser2 commit = y ignore = y

(2) user mode, all objects in the specified user and data export / import.

Export:

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log owner = (xx, yy)

Export only the data objects, non-export data (rows = n)

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log owner = user rows = n

Import:

$ imp user / pwd file = / dir / xxx.dmp log = xxx.log fromuser = dbuser touser = dbuser2

commit = y ignore = y

(3) Full library way, all objects in the database export / import

Export:

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log full = ycommit = y ignore = y

Import:

$ Imp user / pwd file = / dir / xxx.dmp log = xxx.log fromuser = dbuser touser = dbuser2

1.2 Advanced Options

1. Split into multiple files

Of a number of fixed-size papers Export: This is usually a larger amount of data used in the table, a single dump file may be

Would exceed the limit of the file system

$ Exp user / pwd file = 1.dmp, 2.dmp, 3.dmp, ... filesize = 1000m log = xxx.log full = y

Of a number of fixed-size papers into

$ imp user / pwd file = 1.dmp, 2.dmp, 3.dmp, ... filesize = 1000m tables = xxx fromuser = dbuser touser = dbuser2 commit = y ignore = y

2. Incremental Export / Import

/ / Oracle 9i After exp Is no longer supported inctype

Must SYS Or SYSTEM Before implementation of the incremental export import

Incremental Export: Consists of three types:

(1) "full" incremental export (Complete) / / Backup the entire database

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log inctype = complete

(2) "incremental" incremental export Export changed after the last backup of data.

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log inctype = incremental

(3) "Cumulative type" incremental export (Cumulative) to export only since the last "full" Export changed after the database information.

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log inctype = cumulative

Incremental Import:

$ Imp usr / pwd FULL = y inctype = system / restore / inct ype

Of which:

SYSTEM: Importing System Objects

RESTORE: Import all user objects

3. To SYSDBA for Export / Import

1. Technical support for Oracle

2. For the table space transmission

Example:

$ imp \ 'usr / pwd @ instance as sysdba \ ' tablespaces = xx transport_tablespace = y file = xxx.dmp datafiles = xxx.dbf

$ imp file = expdat.dmp userid = "" "sys / password as sysdba "" " transport_tablespace = y

"Datafile = (c: tempapp_data, c: tempapp_index)"

4. Tablespace transfer (Fast)

Table space transmission is 8i A quick additional data in the database as a way to move is to a database format data file attached to another database, rather than to export the data into the dmp Document, which is sometimes very useful, because the space of mobile data transmission tables as fast as copying the files.

1. With regard to transfer the table space in a number of rules (10g ago):

? The source database and target database must be running on the same hardware platform.

? Source database and target database must use the same character set.

? Source database and target database must have the same size of data blocks

? Target database can not have and migration of the same name as the table space tablespace

? SYS objects can not migrate

? Be transmitted self-contained set of objects

? Some objects, such as materialized views, function-based index can not be transferred, etc.

(The same byte order the file cross-platform data files can replace the header of the method)

(10g Support cross-platform tablespace transport, as long as the operating system, the same byte order, you can transfer the table space. Need to use the RMAN convert the file format, abbreviated)

2. Detection of a table space is in line with the transmission standard method:

SQL> exec sys.dbms_tts.transport_set_check ( 'tablespace_name', true); SQL> select * from sys.transport_set_violations;

If no row selection, indicating that the table space contains only the table data, and is a self-contained. For some non-self-packets

Containing the table space, such as data table space and index table spaces, and you can transfer.

3. Briefly the use of steps:

If you want to refer in detail to use, you can also refer to ORACLE online help.

1. Set tablespace read-only (assuming the table space name is APP_Data And APP_Index)

SQL> alter tablespace app_data read only; SQL> alter tablespace app_index read only;

2. To issue EXP command

SQL> host exp userid = "" "sys / password as sysdba" "" transport_tablespace = y tablespaces = (app_data, app_index)

Note that the above

In order to execute SQL * EXP, USERID must be three quotes, in UNIX, must also be taken to avoid "/" Use of

In 816 and beyond must use sysdba in order to operate

* This command in the SQL must be placed in the line (in this case because the display problem on two lines)

3. Copy. Dbf data files (and. Dmp files) to another location, that is the target database

Can be cp (unix) or cop y (windows) or by ftp to transfer files (must be in the bin method)

4. To the local tablespace set to read and write

$ Alter tablespace app_data read write;

$ Alter tablespace app_index read write;

5. In the target database, attach the data file (Either directly specify the data file name)

(Table space can not exist, we must establish the appropriate user name or use fromuser / touser)

$ Imp file = expdat.dmp userid = "" "sys / password as sysdba" "" transport_tablespace = y datafiles = ( "c: \ app_data.dbf, c: \ app_index.dbf") tablespaces = app_data, app_index tts_owners = hr, oe

6. Set target database table space for reading and writing

$ Alter tablespace app_data read write;

$ Alter tablespace app_index read write;

1.3 Optimization

1. Accelerate the pace of exp

Increase large_pool_size, can improve the speed of exp using direct path method (direct = y), the data did not require the integration of memory and checks. To set a larger buffer, if the exporting large objects, small buffer will fail.

export file is not used on the drive ORACLE not export to the NFS file system

UNIX Environment: pipe model directly into lead out to improve imp / exp performance

2. Accelerate the pace of imp

The establishment of a indexfile, after the completion of the data import indexing

The import file on a different drive to increase DB_BLOCK_BUFFERS

Increase LOG_BUFFER

Archiving run a non-ORACLE: ALTER DATABASE NOARCHIVELOG; the establishment of a large table space and rollback segments, OFFLINE other rollback segments, rollback segment size of the largest table 1 / 2 to use COMMIT = N

Use ANALYZE = N

Single-user mode to import

UNIX Environment: pipe model directly into lead out to improve imp / exp performance

3. Through the unix / Linux PIPE pipeline to speed up exp / imp speed

Through pipelines to export data:

1. Through establishment of pipe mknod-p

$ Mknod / home / exppipe p / / In the directory / home to build a pipeline under the exppipe attention parameters p

2. By exp, and gzip export data to build the pipeline and compression

$ Exp test / test file = / home / exppipe & gzip exp.dmp.gz

$ Exp test / test tables = bitmap file = / home / newsys / test.pipe &

gzip bitmap.dmp.gz

3. Export to delete the establishment of the successful completion of the pipeline

$ Rm -rf / home / exppipe

Export script:

# # # UNIX pipes under the ORACLE database, back up through the PIPE

###### Using "export" and "tar" command to bakup oracle datebase #######

trap "" 1 # nohup

LOGFILE = / opt / bakup / log / bakup_ora.log

export LOGFILE

DUMPDIR = / archlog_node1

export DUMPDIR

exec> $ LOGFILE 2> & 1

echo

echo 'Begin at' `date`

echo

# clear old result file

cd $ DUMPDIR

if [-f exp.dmp.Z]

then

echo "clear old result file"

rm exp.dmp.Z

fi

# make pipe

mkfifo exp.pipe

chmod a + rw exp.pipe

# gain the dmp.Z file

compress exp.dmp.Z &

su-u oracle-c "exp userid = ll / ll file = $ DUMPDIR / exp.pipe full = y buffer = 20000000"

echo

echo ' exp end at ' `date`

echo

# Rm pipe

rm exp.pipe

# tar the dmp.Z file to tape

mt-f / dev/rmt/0 rew

tar cvf / dev/rmt/0 exp.dmp.Z

echo

echo ' tar end at ' `date`

echo

Through pipelines to import generated file:

1. Through establishment of pipe mknod-p

$ Mknod / home / exppipe p

2. To import the generated compressed file

$ Imp test / test file = / home / exppipe fromuser = test touser = macro &

gunzip / home / exppipe

3. Remove the pipe

$ Rm-fr / home / exppipe

4. The general steps to import the whole library

Note: In the export, you need through toad or any other tool to extract a source database to create the primary key and indexes in the script

1. The first full-Cuga rows = n the structure of lead into the

$ Imp system / manager file = exp.dmp log = imp.log full = y rows = n indexes = n

2. So that business users trigger failure / Remove the primary key and unique index

spool drop_pk_u.sql

select 'alter table' | | table_name | | 'drop constraint' | | constraint_name ||';'

from user_constraints

where constraint_type in ( 'P', 'U');

/

spool off

spool disable_trigger.sql

select 'alter trigger' | | trigger_name | | 'disable;'

from user_triggers;

/

spool off

@ drop_pk_u.sql

@ disable_trigger.sql

3. To ignore = y full database import

$ Imp system / manager file = exp.dmp log = imp.log full = y ignore = y

4. By toad or any other tool to extract a source database to create the primary key and indexes of scripts in the target database to create the primary key

And indexes. To trigger the entry into force.

1.3 Optimization

1. Accelerate the pace of exp

Increase large_pool_size, can improve the speed of exp using direct path method (direct = y), the data did not require the integration of memory and checks. To set a larger buffer, if the exporting large objects, small buffer will fail.

export file is not used on the drive ORACLE not export to the NFS file system

UNIX Environment: pipe model directly into lead out to improve imp / exp performance

2. Accelerate the pace of imp

The establishment of a indexfile, after the completion of the data import indexing

The import file on a different drive to increase DB_BLOCK_BUFFERS

Increase LOG_BUFFER

Archiving run a non-ORACLE: ALTER DATABASE NOARCHIVELOG; the establishment of a large table space and rollback segments, OFFLINE other rollback segments, rollback segment size of the largest table 1 / 2 to use COMMIT = N

Use ANALYZE = N

Single-user mode to import

UNIX Environment: pipe model directly into lead out to improve imp / exp performance

3. Through the unix / Linux PIPE pipeline to speed up exp / imp speed

Through pipelines to export data:

1. Through establishment of pipe mknod-p

$ Mknod / home / exppipe p / / In the directory / home to build a pipeline under the exppipe attention parameters p

2. By exp, and gzip export data to build the pipeline and compression

$ Exp test / test file = / home / exppipe & gzip exp.dmp.gz

$ Exp test / test tables = bitmap file = / home / newsys / test.pipe &

gzip bitmap.dmp.gz

3. Export to delete the establishment of the successful completion of the pipeline

$ Rm -rf / home / exppipe

Export script:

# # # UNIX pipes under the ORACLE database, back up through the PIPE

###### Using "export" and "tar" command to bakup oracle datebase #######

trap "" 1 # nohup

LOGFILE = / opt / bakup / log / bakup_ora.log

export LOGFILE

DUMPDIR = / archlog_node1

export DUMPDIR

exec> $ LOGFILE 2> & 1

echo

echo 'Begin at' `date`

echo

# clear old result file

cd $ DUMPDIR

if [-f exp.dmp.Z]

then

echo "clear old result file"

rm exp.dmp.Z

fi

# make pipe

mkfifo exp.pipe

chmod a + rw exp.pipe

# gain the dmp.Z file

compress exp.dmp.Z &

su-u oracle-c "exp userid = ll / ll file = $ DUMPDIR / exp.pipe full = y buffer = 20000000"

echo

echo ' exp end at ' `date`

echo

# rm pipe

rm exp.pipe

# tar the dmp.Z file to tape

mt-f / dev/rmt/0 rew

tar cvf / dev/rmt/0 exp.dmp.Z

echo

echo ' tar end at ' `date`

echo

Through pipelines to import generated file:

1. Through establishment of pipe mknod-p

$ Mknod / home / exppipe p

2. To import the generated compressed file

$ Imp test / test file = / home / exppipe fromuser = test touser = macro &

gunzip / home / exppipe

3. Remove the pipe

$ Rm-fr / home / exppipe

4. The general steps to import the whole library

Note: In the export, you need through toad or any other tool to extract a source database to create the primary key and indexes in the script

1. The first full-Cuga rows = n the structure of lead into the

$ Imp system / manager file = exp.dmp log = imp.log full = y rows = n indexes = n

2. So that business users trigger failure / Remove the primary key and unique index

spool drop_pk_u.sql

select 'alter table' | | table_name | | 'drop constraint' | | constraint_name ||';'

from user_constraints

where constraint_type in ( 'P', 'U');

/

spool off

spool disable_trigger.sql

select 'alter trigger' | | trigger_name | | 'disable;'

from user_triggers;

/

spool off

@ drop_pk_u.sql

@ disable_trigger.sql

3. To ignore = y full database import

$ Imp system / manager file = exp.dmp log = imp.log full = y ignore = y

4. By toad or any other tool to extract a source database to create the primary key and indexes of scripts in the target database to create the primary key

And indexes. To trigger the entry into force.

1.4 Frequently Asked Questions

1. Problems With Character Sets

ORACLE Multi-language setting is to support the world's languages and character sets, the general language tips, currency format, sorting methods and CHAR, VARCHAR2, C LOB, LONG Field displays the data efficiently. ORACLE The multi-language settings of the two most important features is the national language settings and character sets, national language setting determines the language used interface or prompt type the database character set determines the character set the data preservation and

(Eg, text) when the encoding rules.

ORACLE character set settings, is divided into the database character set and client character set environment settings. In the database side,

Character set when creating a database set up, and stored in a database props $ table.

In the client's character set is relatively simple, the main is the environment variables or registry entries NLS_ LANG, note that the priority level of NLS_LANG as follows: parameter file "registry" environment variable session. If the client character set and server-side character set is different, and the character set conversion is not compatible, then the client's data show that with the export / import character set with all the relevant data will be garbled.

Use a little skill, you can export / import in a different database character set conversion data. Here

The need for a two binary file editing tool can be, such as uedit32. Open the exported with the editing dmp file, be

Take 2, 3 Bytes of content, such as 00 01, first it is converted to 10 Binary number, in order to 1, using the function

NLS_CHARSET_NAME you can get the character set: SQL> select nls_charset_name (1) from dual; NLS_CHARSET_NAME (1)

------------------- US7ASCII

Dmp file to know the character set for the US7ASCII, this dmp file if you need to change the character set

Cheng ZHS16GBK, you need to get the character set to use NLS_CHARSET_ID number: SQL> select nls_charset_id ( 'zhs16gbk') from dual; NLS_CHARSET_ID ( 'ZHS16GBK')

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

The 852 replaced by 16 hexadecimal number, for the 354, the 2,3-byte 00 01 replaced by 03 54, which completed the character set to the dmp file from us7ascii to zhs16gbk conversion, so that then the dmp file into the database character set zhs16gbk on it.

2. Version of the problem

Exp / Imp In many cases, can be used across versions, such as between version 7 and version 8 export import data, but it must choose the correct version, the rules as follows:

* Always use IMP's version of the matching version of the database, if you want to import into the 816, use 816 of the Import Tool.

* Always use the version of EXP match the two databases that version of the low, such as between 815 and 816 each guide, use 815 of the EXP tool.

imp and exp can not be upward compatible with versions: imp can import a low version of the exp-generated documents, Can not import the high version of the exp generated files.

Original Address http://blog.sina.com.cn/s/blog_525394060100gbnx.html Published on: 2010-01-27, modified on: 2010-01-27 12:26 has been here 12 times there have been comments 0 recommend Complaints

User Reviews

Comments

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database ASM Answer Center 1

kods.net — Tue, 19 Jan 2010 21:07:56+0000

1 What is the ASM
ASM called the Automated Storage Management, the automatic storage management, it is this version of Oracle since the Oracle10g new features. This is the Oracle provides a volume manager for the replacement operation of the operating system provided by the LVM, which not only supports single-instance configuration, also supports multiple instances of configurations such as RAC. Oracle database administrator will bring great convenience, ASM can automatically manage the disk groups, and to provide data redundancy and optimization. Especially for large-scale enterprise-wide database administrator who can enable administrators to manage hundreds of data files from the trivial day to day affairs of these off from relief in order to deal with other more important matters up.
Prior to this version in Oracle 10g to manage a large database of hundreds of data files to the database administrator is neither a technological content and is very boring work, which requires the database administrator to be familiar with some of the system related to LVM knowledge, good regulation of the disk, LV bands and other related aspects related to operation of the system. The use of automatic storage management will greatly reduce the workload in this area, database administrators need to manage only a small number of disk groups can be. An ASM disk group is the management of a logical unit, formed by a group of disk devices. We can define a default disk group as the database disk group, Oracle will automatically manage storage, including creating, deleting the data files. Oracle will automatically these files with an appropriate database objects associated with doing so that we in the management of these objects when you only need to provide the name of the object, as in the past without having to provide a detailed file name.

ASM provides many useful storage technologies, such as RAID and LVM (logical volume management). The same as these technologies, ASM allows you to a group of independent disks to create a separate disk group. This group achieved a single disk I / O balance. At the same time also achieved ASM striping (Striping) and disk mirroring (Mirroring) to enhance the I / O performance and data reliability. With RAID or LVM difference is that, ASM is implemented in the file-level striping and mirroring, so that to the user with a way to achieve a great degree of choice, we can in the same disk group files for different configurations of different storage properties, to achieve a different storage methods.

2 11g what are the new features of ASM in
2.1 Rapid re-synchronization (ASM Fast Mirror Resync)
A short path to the disk problem, the resumption of ASM disk group (DISK GROUP) of the Yun I is time-consuming, especially in such recovery operations need to re-layout of the entire disk group situations. ASM Fast Disk to re-synchronize this new feature can significantly reduce the disk to re-synchronize a bad time for such a situation, when you replace a bad disk, ASM can quickly sync ASM disk extent.

Any party disk group temporarily unavailable are considered the issue a temporary failure, which is quick to re-synchronize the new features of ASM can be restored. Disk-path failure, such as the interface line problems, the host adapter problems, disk controller problem, or a disk problem, these are all caused by the instantaneous power failure. The default case, when a disk offline, ASM will be immediately removed from the disk. ASM fast enough to record offline and then sync the disk in the disk on the area during the off-line all the changes, when the disk has been repaired or re-line, the extent of changes during this period can be quickly re-synchronize to the earlier failure of these disks .

You can set this attribute to DISK_REPAIR_TIME failed disk has been repaired and re-line this time to re-organize this operation does not occur. This time can be in minutes (m or M) or hours (h or H) as a unit, if you do not specify the time units, the default time units of hours. If the DISK_REPAIR_TIME this attribute is not set, its default value is 3.6 hours. Note that this default value is applied to the disk is set to offline mode operating statement DROP AFTER clause is not such a situation. Most part, the environment, 3.6 hours of this DISK_REPAIR_TIME default attribute values should all be appropriate.

Note:
Use this new feature, ASM disk groups need to set the compatibility to 11.1 or higher.

Example:
CREATE DISKGROUP asmdskgrp1 DISK '/ dev / raw / *'
SET ATTRIBUTE 'compatible.rdbms' = '11 .1 ',' compatible.asm '= '11 .1';

Only when a disk that contains offline group mounted again, elapsed time (since the disk is set to offline mode) is increased, V $ ASM_DISK the REPAIR_TIME This column shows the disk offline before it was deleted the remaining time (unit: seconds), when the specified time arrives, ASM remove the disk can be used with the DROP AFTER the ALTER DISKGROUP DISK OFFLINE statement to cover this property.

Note:
DROP AFTER is 11g's new features.

If an ALTER DISKGROUP SET ATTRIBUTE DISK_REPAIR_TIME operation of the disk group containing the disk offline, this property is only those non-offline mode on the current disk is in force.

When a disk is offline, the second implementation of the off-line operation, elapsed time will be reset and re-counted. If the disk was again another time this piece performed DROP AFTER operation, on a value will be overwritten and the new values take effect. Can not use ALTER DISKGROUP DROP DISK statement to delete the offline disk, this operation will complain. If a certain time, for example, the disk can not be repaired, you need to remove the disk DISK_REPAIR_TIME before arrival, you can re-implement with the DROP AFTER clause OFFLINE statement, DROP AFTER designated 0H or 0M, said repealed immediately.

You can use the ALTER DISKGROUP to set the disk group DISK_REPAIR_TIME properties, which can be minutes or it can be hours, for example, 4.5 hours or 270 minutes, for example:

ALTER DISKGROUP dg01 SET ATTRIBUTE 'disk_repair_time' = '4 .5 h '
ALTER DISKGROUP dg01 SET ATTRIBUTE 'disk_repair_time' = '270m '

After you repair the disk, run the ALTER DISKGROUP DISK ONLINE This SQL statement can make the disk group back to the online state, the new read and write operations can be carried out correctly, this statement is also triggered to change the period of the disk repair from the disk extent group of redundant data re-synchronization to the earlier failure of these disks.

2.2 ASM rolling upgrade
in ORACLE11g and after versions, you can set the ASM cluster is "rolling upgrade" model, charging slightly different versions of the ASM nodes to work together. Rolling upgrade "model in each node can independently upgrade or patch, but does not affect the use of the database, because some of its greatly improved the database uptime. Note that you can only right ORACLE11g and later version "rolling upgrade", in other words, you can not use this function to ORACLE10g database upgrade to 11G's.
During rolling upgrade, you must do a certain environment prepared. For example, if you are using ORACLE Clusterware software, such as making you open rolling upgrade, Clusterware also must complete to meet the requirements to upgrade to the next version. Of course, do Clusterware rolling upgrades should also be used the way, more to ensure a high stability and maximum uptime.

In response to a node ASM software patch or upgrade, the cluster must be set for the ASM rolling upgrade mode, which allowed to begin to upgrade and operation of your environment in a number of software versions of the model, the statement is as follows:
ALTER SYSTEM START ROLLING MIGRATION TO number;

number is the version number, release number, update number, port number and port release updated numbers in these parts, and the middle of a comma-separated, for example, 11.2.0.0.0.
Instance, when running this statement will check the number you specify with the current version of the software installed is not compatible. When the upgrade after the start, ASM instance only the following number of operations is permissible charge:
 disk group mount and unloaded
 database file open, close, re-set the size and remove
 restrict access to ORACLE's own views and packages, all of the global view is expired

After the start of a rolling upgrade, you can put off any one ASM instance to carry out a software upgrade, upgrade end of the ASM instance starts automatically re-enter the ASM cluster. When the cluster in all instances have been completed to upgrade to the latest version of the software, you can be the end of a rolling upgrade mode.

ASM instance, if a disk is in a rolling upgrade is offline, then the knot-speed piece of the disk until the upgrade will remain off-line state, but return to normal mode until the ASM cluster is triggered to delete the disk timer is stopped.

If you upgrade over a problem class, you can use the same process node software rollback to the previous version. Any cluster where there is a data reorganization operation, upgrade will fail, so such data must be re-rolling operation is complete before they can begin to upgrade. In addition, as long as there is a cluster node is active, a rolling upgrade state is retained.

If a cluster rolling upgrade in progress of a new ASM instance add to the mix, the new instance will be told that the cluster is in rolling upgrade mode, you can use the following SQL statement to query the status of ASM cluster environment:
SELECT SYS_CONTEXT ( 'sys_cluster_properties', 'cluster_state') FROM DUAL;

If the cluster ASM instances are stopped for all, then when any one ASM instance restart, this example will be out of rolling upgrade mode. To restart the instance even after the upgrade, you must restart the rolling upgrade operation.
When the rolling upgrade is complete, run the following SQL:
ALTER SYSTEM STOP ROLLING MIGRATION;

After issuing this statement, ORACLE made the following some of the following:
 Check all members of the ASM cluster version of the software is not the same, if one or several instances running on different software versions, this statement will complain, cluster in rolling upgrade mode to continue.
 so that all instances of the cluster out of rolling upgrade mode, the cluster began to work full-function
 If you set ASM_POWER_LIMIT parameters allow data re-finishing, rolling upgrade is blocked because of the data re-finishing operations will be re-started.

2.3 for the ASM administrator added SYSASM operating system permissions and user groups OSASM
In ORACLE10g this version, ORACLE did not appropriate the role of ASM administrators to customize, ASM administrator to SYSDBA roles management, in practical work, ASM administrators and database administrator may be different for two or a few people to complete, and the relative permissions defined for the .11 g is not clear that the introduction of new features of this new authority SYSASM aim is to clear ASM administrator and database administrator interface, to prevent unauthorized operations occur, so that ASM to ASM administrators to better management.
This new feature at the same time in the operating system for ASM added OSASM user group, OSASM this group is designed specifically for ASM, you can license the operating system has been authorized by the members of this group has a local connection SYSASM permissions can SYSASM the role of ASM-wide permissions management. Initially, only the ASM installation the user is a member of this group, in the follow-up work, you can add new users to the OSASM this user group so that new users have full access to ASM management.

Note that in ORACLE11g Release1 this version, the system OSDBA members of the group, and even into the database, according to a SYSDBA permissions, so users can still connect and manage ASM instances, it is believed that in subsequent versions have SYSDBA privileges users are not authorized to have ASM instances of administrative privileges.

2.4 ASM scalability and performance enhancements
ASM file area management in 11g has improved, reflecting improvements in performance and significant reduction in the SGA area used to store files on memory. When the ASM's file size increases, the size of each district will automatically increase, therefore, would need very little description of the file pointer to point area. When the visit to the size of 20GB to 128TB of ASM file this new feature of 11g will improve performance. Of course, such a document is usually very large database (VLDBs) used.

In addition, when you create a new disk group, you now have more than one allocation unit size options, such as 1, 2, 4, 8,16, 32, and 64 (MB). Based on the database load and the type of storage systems, the choice of the distribution of units may get significant performance gains.

ASM disk group stored contents of the file is composed of N a data area, data area is stored in a separate disk. Area contains one or more allocation units (AU). In order to meet the gradually increasing large files, ASM-use change the size of the district.

Changes in the size of areas capable of supporting a larger ASM files to reduce the large database of the SGA memory use, and upgrade the file to create and open operations performance. An ASM file beginning of a zone is formed by an assigned unit. When the file size increases, if the size exceeds a pre-defined values, the new size will be increased to eight distribution units, and then the new size to the allocation of 64 units. For the newly created file, this feature self-executing.

The size distribution of the number of units set up in the disk group, when Hou determined for 1,2,4,8,16,32 and 64MB, when the ASM file size ranging from 1 to 20,000 districts in this order of magnitude, the The size of each district the size and distribution of units equal; ASM when the file size exceeds the 20,000 area, to the 20,001 to 40,000 districts in this range, the new area the size of the sub-automatic allocation unit size to 8 ; again when the ASM when the file size exceeds the 40,000 area, the new area the size of the sub-automatic allocation unit size to 64.

Figure 1 represents the block containing the disk from the disk group, the file 1AU from each district to change the situation 8AU, in this configuration, ASM did not do the file mirror.

Figure 1 ASM disk group, the file allocation map area

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database ASM Answer Center 2

kods.net — Tue, 19 Jan 2010 11:44:43+0000

2.5 The new ASM command-line (ASMCMD) commands and options.
ASMCMD have the following four new commands: lsdsk, md_backup, md_restore and remap. In addition, you can use with the new option of ls and lsdg commands. The following describe these four new ASM command:
lsdsk - whether or not there is a real column ASM is running, this command can be listed in ASM disk information. When the system administrators or storage administrators want to look at the ASM instance, what disk to use this command is very useful.

md_backup and md_restore-These two commands enable to use the same disk path, disk name, failure groups, attributes, templates and alias directory structure to re-establish the existing disk group. You can use md_backup backup disk group environment, when problems with the mk_restore Hou to restore the appropriate disk group.
Remap-You can use this command to re-mapping or respond to normal and high redundancy disk group, ASM model of the bad blocks, ASM to read a good copy of ASM in the corresponding image blocks, and re-write these pieces back to the disk group, an alternative to position.

2.6 The new disk group compatibility attributes
2.6.1 Summary of the disk group compatibility, a number of ASM to use the new features, you can use two disk group compatibility attributes, COMPATIBLE.RDBMS and COMPATIBLE.ASM. These attributes specify the database and ASM disk groups to use their own minimum required version of the software. By default, these two properties are set to 10.1, if you want to take advantage of new features must promote these attributes.

Compatibility property is set to release a version of the earlier allows clients to access higher version of the disk group, for example, Oracle 10g client can access Oracle11g the ASM disk group.

ASM and Oracle Database Disk Group Compatibility property is set to determine the system can be used in the smallest version of the ASM, and Oracle database software. For example, if the ASM compatibility is 11.1, and the Oracle database compatibility is 10.1, then the ASM software version must be at least 11.1, and the Oracle database client software version must be at least 10.1. 2 property setting is compatible.asm and compatible.rdbms.

ASM recently created a software version of the decision of the default disk group compatibility, when you order CREATE DISKGROUP SQL statements to create the disk group, you can ignore the disk group compatibility settings. In addition, you can use the ALTER DISKGROUP SQL statement for an existing disk group to change the compatibility settings. Disk group compatibility settings can only be gradual, you can not restore back to a lower compatibility setting.

2.6.2 COMPATIBLE.ASM and COMPATIBLE.RDBMS
Compatibility property is set through the completion of these two parameters. COMPATIBLE.ASM properties determine the use of either an example of an ASM disk group of the smallest version of the software. This setting and to determine the original data on disk ASM data structure format. Contents of the file format of the decision by the database instance. For the Oracle 11g version of the ASM, 10.1 is its default settings. In order to better the completion of the disk group compatibility settings, should be set before setting COMPATIBLE.ASM before COMPATIBLE.RDBMS properties.

The database used by all of the disk group COMPATIBLE.RDBMS attribute value must be less than or equal to the database COMPATIBLE parameter setting the initial value for the Oracle 11g version of the ASM, 10.1 is the default value of COMPATIBLE.RDBMS property. For example, if a database compatible initialization parameter is set to 11.1.0, then the COMPATIBLE.RDBMS can only be set to between 10.1 and 11.1 the value of any one.

2.6.3 set the disk group compatibility methods
may adopt CREATE DISKGROUP or ALTER DISKGROUP two statements set the disk group compatibility attributes.

Using the CREATE DISKGROUP established when the disk group compatibility attributes specify the following examples to build a normal redundancy disk disk group asmdskgrp1, ASM is compatible with property set to 10.2, with the database compatibility attributes take default values (assuming COMPATIBLE.RDBMS default is less than or equal to 10.2) :
CREATE DISKGROUP asmdskgrp1 DISK '/ dev / raw / *'
SET ATTRIBUTE 'compatible.asm' = '10 .2 ';
The following examples set the establishment of a normal redundancy disk group asmdskgrp2, ASM is compatible with property set to 11.1, with a database-compatible attributes take default values (assuming COMPATIBLE.RDBMS default is less than or equal to 11.1):
CREATE DISKGROUP asmdskgrp2 DISK '/ dev / raw / *'
SET ATTRIBUTE 'compatible.asm' = '11 .2 ';
The following examples set the establishment of a normal redundancy disk group asmdskgrp3, ASM is compatible with the database is compatible with attributes and properties are obtained 11.1:
CREATE DISKGROUP asmdskgrp3 DISK '/ dev / raw / *'
SET ATTRIBUTE 'compatible.rdbms' = '11 .1 ',' compatible.asm '= '11 .1';

Using the ALTER DISKGROUP SQL statement to change an existing disk group is compatible with properties in the disk group has been created, you can use the ALTER DISKGROUP SQL statement to change the compatibility properties. Use ALTER DISKGROUP SQL statement is submitted to change before the need to ensure that Oracle can improve the compatibility of the specified disk group. To further enhance the disk group ALTER DISKGROUP compatibility without reducing the database and file system functions, all the affected databases and file systems should be online.
For example:
ALTER DISKGROUP asmdskgrp4 SET ATTRIBUTE 'compatible.rdbms' = '11 .1';
ALTER DISKGROUP asmdskgrp5 SET ATTRIBUTE 'compatible.asm' = '11 .1 ';

2.7 ASM mirrors the priority to read
2.7.1 Summary of new features give priority to the mirror to read the extended cluster, if the remote node access is not balanced, this new feature to upgrade the performance is very useful. This has led to a better storage utilization and reduce network load. Oracle 10g,, ASM total mirror area reading a set of primary copy, and we know that this is aimed at distributed IO, improve performance. For a disk group of a disk, for a particular node is likely to be local disk, it may be a remote disk, if the read data from the master copy of this master copy is a remote disk, apparently there is no local disk and high efficiency. However, in Oracle10g is unable to complete the setup disk to read from a piece of data, in 11g this parameter is introduced ASM_PREFERRED_READ_FAILURE_GROUPS that we are talking about the priority image to read the new ASM features, you can accomplish this function, as further detail about this new feature.

When you configure ASM failure group, which allows nodes from the area closest to it to read data, which is probably the most efficient, even if that area is a secondary district. In other words, when the secondary zone from the junction close, you can configure the ASM reading from a secondary zone read, rather than from the main areas distant time. Use priority read failure group for the expansion of the cluster is very useful.

Using this feature, the expansion in comparison to the cluster node's local disk, Oracle recommends to configure at least one mirror copy of the district. However, in a RAC database, for a node is set priority group to read the failure of a node in terms of another is perhaps the election process, and this down do not worry, give priority to read the failure group for each instance of the parameters set can be separately specified.

Note:
By default, when you create a disk group, disk group each disk belongs to a failure group. Oracle does not recommend you to use the disk group configuration for each instance of a failure to read more than one priority group. If you have configured for each instance of a failure to read more than one priority group, Oracle alter the log is written to the warning message.

2.7.2 Configuration and management priorities in the read failure group you want to configure this new feature, ASM_PREFERRED_READ_FAILURE_GROUPS need to set this parameter, the parameter list for the failure of group 1, this parameter is usually the ASM instance in the cluster to be used, and also in the cluster ASM instance effectively, and each instance to set a different value, and its default value is NULL. Parameters of the following format:
For example:
diskgroup_name1.failure_group_name1, ...

Diskgroup_name which represents the disk group's name, failure_group_name said that the failure group name. If you set the parameters of the specified disk group does not exist in the corresponding failure group, ASM will ignore the failure of the corresponding group. You can be a comma-separated as ASM_PREFERRED_READ_FAILURE_GROUPS parameter setting multiple values.
For example:
ASM_PREFERRED_READ_FAILURE_GROUPS = = diskgroup_name.failure_group_name, diskgroup_name1.failure_group_name1 ...

2.8 ASM fast rebalance
rebalancing operation occurs when the disk group is in a restricted mode, it is also In addition to Oracle RAC consumer environment lock and unlock extent map information in the communications between the ASM instances, thus enhancing the overall rebalance throughput.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

To re-install the system, oracle database recovery

kods.net — Tue, 19 Jan 2010 02:30:39+0000

Sometimes not careful, the operating system can not enter, need to re-install the system. That may be of your oracle database files are placed in another dish, assuming that the original installation of the database (D: \ oracle \ oradata), re-named into another name, such as oradataxxx, and then re-install ORACLE, of course, when the installation require global database name as before. After the installation is complete, in the Control Panel, open the service, stop ORACLE services and monitoring. And then restore the original directory name oradataxxx to reconvert into a oradata, to prevent the case, the database backup of your original one, then open the ORACLE services and monitoring, in the OEM using sys / as dba access to the database, and then exit, if the database did not start, then activate the database, and then try to use the previous user access, this time you can find everything with the previous same. May be different environments, there are different situations.
I was under WINDOWS XP Professional Edition ORACLE9I tried using feasible.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database indexes created to do three appropriate

kods.net — Mon, 18 Jan 2010 07:54:59+0000

In the Oracle database, create an index is a relatively simple. However, to a reasonable index is relatively difficult to create. The author believes that when creating an index appropriate to do three, namely, in the appropriate table, the appropriate number of columns to create the appropriate indexes. While this can be summed up in one phrase index optimization of the basic principles, but to do that, then database administrators need to make great efforts. Specifically, do you need to have the following three due to several demands.

1, according to the size of the table to create the index.

While creating an index to the table can improve query efficiency. However, the database administrator should be noted that the index also requires a certain overhead. This is not to say to all of the tables to create an index, then you can improve database performance. This perception is wrong. On the contrary, if the just-do, to all of the tables are created the index, then the performance of the database but will have a negative impact. Because at this time the cost of misuse of the index may have been far greater than the resulting performance gains. So I believe that the database administrator first needs to be done, in order to fit the table to build an index, rather than indexing all of the tables.

In general, do not need to create an index on the table is relatively small. Such as an ERP system, database, department tables used to store information on the business sector. The part of the general business also more than a dozen up to no more than 100. Records for the 100 people who may be relatively more. But for the computer, it gave him sese teeth are not enough. So, for similar small table does not need to create an index. Because even if the index was established, its performance will not be greatly improved. In contrast the cost of indexing, such as the maintenance costs, etc., than this should be great. In other words, than get paid more than a clear violation of common sense.

Another is for large tables, not necessarily to build the index. Some of the table is a relatively large number of records very many. However, this time for this table indexing and a certain degree of fit. If the system has a table, which is mainly used to save some of the changes to the information in the database. This information is often only to database administrators. At this point for this table, then indexing, but inappropriate. Because this form rarely used only in trouble when it need to see. Second, even if the view of its need to check the record does not, there may be the recent week's update records and so on. For some large tables, indexing is sometimes and often can not achieve the expected results. But also in playing table indexing the cost of its index larger than the average over the table. So in the end it does to a large table indexing? In my view, the key is how the two aspects. First, the need to look at this big table, often need to query the number of records. Generally speaking, if you frequently need to query the data does not exceed 10% to 15%, then there is no need for indexing necessary. Because at this time may be greater than the cost of indexing to improve the performance of the multi-. This ratio is only an experience of data. If the database administrator will need to arrive at a more precise conclusion, then we need to conduct test analysis. Namely, a database administrator will need to test the full table scan of time, to see if the query than the index after the longer or shorter. If it is long, then illustrate the need for indexing. But if not, then the whole table scan or fast speed. At this point there is no need to establish an index of.

In short, in considering whether the indexing for the table, the generally small table does not need indexing. As for the playing table, then the need for practical analysis of the actual situation. To put it simply, it may, the ratio of general to determine. If you want to exact a little, you can full-table scan performance analysis, to determine the index as expected, after it really improved the database performance.

2, according to the characteristics set out to create the index.

The characteristics of the column is different from the effect of creating an index is also different. Database administrators need to know for which set out to create an index can play a less effective results. At the same time also need to know which columns to create an index for the play but the effect is less effective. This will help them understand how in the end to the kind of fields for indexing.

According to the author's experience, often for the following characteristics of the column to create an index can play a more significant results. If for some less duplicate content column, especially for those who define the unique constraint column. To create an index on these columns can often play a very good results. If null values for some columns and non-Null value column mixing, if users need to regularly check records of all non-Null value column, it is best to set the index. If you often need to multi-table join queries, in connection with the column and set the index can be achieved more with less.

Can be seen that the index is set up properly, not only with the framework of the database design, and even went to the enterprise's economic business-related. To this end, for some software packages, although a start has already been done database administrator the index optimization. However, with the subsequent increase in economic data, this index results will be increasingly compromised. This is mainly due to influence of the table records to the Index Tuning effect. Therefore, I propose you a database administrator, even with the big software company, packaged software, also need from time to time, such as one year, the database index optimization. The removable removed, the adjustment of the adjustment to improve database performance.

If there is a table in the database is used to store user information. Which field ID number, which is a unique field. In the database design, to create an index to this field. But when put into use this database, users enter the less the user's identity card number. But usually also basically do not follow the numbers to search. On months when the record for some time, the ID number on the index field not only failed to improve database query performance, has become a chicken ribs. There are many NULL values for these columns, but not always check the value of records of all non-NULL column, the database administrator must be determined, even if the removal of these columns on the index.

So the index optimization and adjustment is a dynamic process, not to say that after a good database design does not require an adjusted. Database administrators often need to record changes to make the appropriate changes. To improve the indexing effect.
Third, the number of indexes to create a table right?

Although, to create an index on the table no limit to the number, but never better. In other words, to create an index of the matter, 1 +1 "2 are not valid. Sometimes, to create the index the more it may get counter-productive. Then a table, in the end to create the index much better? This is not a clear standard. But requires the use of a database administrator and database based on the actual situation in the record to be judged.

Generally speaking, the more indexes the table, the sooner the pace of their inquiries. However, the table will be updated to reduce the speed. This is mainly because the table update (for example, insert a record into the table) speed, but as the index increases. This is mainly because, in the update records at the same time the need to update the relevant index information. To this end, in the end the number of indexes in the table to create the right, you need to update at this speed and query speed to achieve an equilibrium point between. As for some of the data warehouse or decision-making database systems, which are mainly used to search. Relevant records in the database initialization is often poured into the time. At this point, set a little more than an index, you can improve database query performance. At the same time because the records are not very updated, so the index more and more cases, it will not affect the update speed. Even in the beginning, when the need to import large amounts of data, this time the index can also be disabled first out. To wait until after the data import, and then enable the index. Can be indexed in this way to reduce the impact of the data update. On the contrary, if those tables often need to update records, such as some transactional applications, the data update operation is a routine matter. If a table at this time to create too many indexes, it will affect the update speed. Due to more frequent update operation, so their negative effects to enhance the query efficiency than larger multi-. At this point the need to limit the number of indexes, only the necessary fields to create an index.

I optimize the database in peacetime, often based on the use of these tables to set up an index for the column. Can query related to a dynamic view, take a look at the table for this operation is to update operations (including update, delete, insert, etc.) accounted for the proportion of large, or large proportion of query operation. When the index has been affected too much the speed of update operation, then the database administrator will need to disable some of the index in order to improve database performance.

In short, in the appropriate table, the appropriate columns on the establishment of an appropriate index. This sentence contains a lot of meaning, the above was simply a part of. As the saying goes, master, self-cultivation on their own. I refer here to be able to go beyond that. The index of the contents of a number of specific optimization still need you the reader to experience in their daily work and summary.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle Database scheduling problem by the Chinese alphabet, NLS_SORT set

kods.net — Thu, 14 Jan 2010 13:27:00+0000

Prior to Oracle9i, the Chinese is based on binary coding to sort. The oracle9i added in accordance with the phonetic, radical, stroke sorting.
1, set parameter values NLS_SORT
SCHINESE_RADICAL_M accordance with the radical (first order), stroke (second order) to sort
SCHINESE_STROKE_M accordance with the stroke (first order), radical (second order) to sort
SCHINESE_PINYIN_M sorted according to Pinyin
2, Session-level settings, modify the default ORACLE field Sort by:
According to Pinyin: alter session set nls_sort = SCHINESE_PINYIN_M;
According to stroke: alter session set nls_sort = SCHINESE_STROKE_M;
According to the radical: alter session set nls_sort = NLS_SORT = SCHINESE_RADICAL_M;
3, the statement level is set Sort by:
Oracle according to alphabetical order
Select * From [tablename] order By nlssort (colname, 'NLS_SORT = SCHINESE_STROKE_M');
Oracle accordance with the radical sort
Select * From [tablename] order By nlssort (colname, 'NLS_SORT = SCHINESE_RADICAL_M');
Oracle sorted according to Pinyin
Select * From [tablename] order By nlssort (colname, 'NLS_SORT = SCHINESE_PINYIN_M');
4, modify the system parameters (database where the operating system):
set NLS_SORT = SCHINESE_RADICAL_M; export NLS_SORT (sh)
setenv NLS_SORT SCHINESE_RADICAL_M (csh)
HKLC \ SOFTWARE \ ORACLE \ home0 \ NLS_SORT (win registry)

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Step by step explain the Oracle database administrator responsibilities 【1】

kods.net — Thu, 07 Jan 2010 09:22:24+0000

What are the responsibilities of Oracle database administrator

1, Oracle database administrator should be as follows on the Oracle database system for regular monitoring:
(1). On a daily basis running Oracle databases, log files, backups, database space usage, the use of system resources, it was discovered, and solve the problem.
(2). Weekly space for expansion of the database objects and data to monitor the growth of the database to do health checks, the state of the database objects for check-ups.
(3). On a monthly tables and indexes etc. Analyze, check the table of space debris, looking for a database performance tuning of the opportunity to conduct a database performance tuning, the next steps of space management plan. State of the Oracle database, conduct a comprehensive inspection.
2, the daily work of
(1). Make sure that all of the INSTANCE state of all the databases, or logged on to the normal routines, testing ORACLE background processes:
$ ps-ef | grep ora
(2). Check the file system used (the remaining space). If the file system free space is less than 20%, need to delete unused files to free up space.
$ df-k
(3). Check the log file and trace file record alert and trace errors in the file.
To connect to each system to be managed using the 'telnet'
For each database, cd to the bdump directory, usually $ ORACLE_BASE / / bdump
Using the Unix 'tail' command to view the file alert_.log If you find any new ORA-errors, record and resolve
(4). Check the validity of the database backup that day.
RMAN Backup on the way:
Check third-party backup tools, backup log to determine whether the success of the EXPORT backup of the backup mode:
Check exp log files to determine the success of the backup to other backup methods:
Check the appropriate log file
(5). Check the status of the record data file is not "online" data file and do recovery.
Select file_name from dba_data_files where status = 'OFFLINE'
(6). Check the table space is being used
SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char (100 * sum_free_m / sum_m, '99 .99 ') | |'% 'AS pct_free
FROM (SELECT tablespace_name, sum (bytes) / 1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
(SELECT tablespace_name AS fs_ts_name, max (bytes) / 1024/1024 AS max_m, count (blocks) AS count_blocks, sum (bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name)
WHERE tablespace_name = fs_ts_name
(7). Check the remainder of the table space
SELECT tablespace_name, sum (blocks) as free_blk,
trunc (sum (bytes) / (1024 * 1024)) as free_m,
max (bytes) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space GROUP BY tablespace_name;
(8). Monitor database performance running bstat / estat report generation system or use statspack to collect statistical data
(9). Check database performance, recording database cpu use, IO, buffer hit ratio, and so the use vmstat, iostat, glance, top and other commands
(10). Everyday problems with processing.
3, the weekly working
(1). Controlled space for expansion of the database objects according to the inspection day this week to find space for expansion of the database objects quickly and take appropriate measures
- Remove historical data
--- Extended table space
alter tablespace add datafile''size
--- Adjust the data object storage parameters
next extent
pct_increase
(2). Monitoring the growth of the amount of data this week, according to the inspection day to find records fast growth in the number of database objects, and to take appropriate measures
- Remove historical data
--- Extended table space
alter tablespace add datafile''size
(3). The system health check-check the following:
init.ora
controlfile
redo log file
archiving
sort area size
tablespace (system, temporary, tablespace fragment)
datafiles (autoextend, location)
object (number of extent, next extent, index)
rollback segment
logging & tracing (alert.log, max_dump_file_size, sqlnet)
(4). Check invalid database objects
SELECT owner, object_name, object_type FROM dba_objects
WHERE status = 'INVALID'.
(5). Check constraint does not work
SELECT owner, constraint_name, table_name,
constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLED' AND constraint_type = 'P'
(6). Check invalid trigger
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED'
4, the monthly work
(1). Analyze Tables / Indexes / Cluster
analyze table estimate statistics sample 50 percent;
(2). Check the table space debris, according to this month, the weekly inspection and analysis of database fragmentation to find the appropriate solution
(3). Looking for the opportunity to compare the database performance tuning database performance monitoring of daily reports to determine whether it is necessary to adjust the database performance
(4). Database Performance Tuning
If necessary, conduct performance tuning
(5). Make the next space management plan based on weekly monitoring, space management for improved methods proposed
5, Oracle DBA daily management of the
Purpose: This document has a very detailed information on a record of one or more of the ORACLE database, daily, monthly, and annual operating results and check the status of the results, In the appendix to the document you will see all the checks and revisions, the SQL and PL / SQL code.
Directory
1. Routine maintenance procedures
A. Checks all instances of the date of
B. Find some new warning log
C. Check DBSNMP is running
D. Check the database backup is correct
E. Check the backup to tape, the file is correct
F. Check whether the performance of the database proper and reasonable and whether there is sufficient space and resources
G. The document log copied to the backup database
H. DBA should be used to see a user's manual
2. Night maintenance procedures
A. The data collected VOLUMETRIC
3. Weekly maintenance
A. To find those who break the rules OBJECT
B. Check whether the issue of violation of security policy
C. See the error log parts of the SQL * NET
D. All of the alert log archive
E. Suppliers frequently visited the home page
4. Month maintenance program
A. View on the database will harm the growth rate of
B. Recalling its previous adjustment of the database to optimize performance
C. See I / O, screen neck problems
D. Review FRAGMENTATION
E. Implementation plan for the future
F. Point of view to adjust and maintain the
1. Day maintenance process
A. See all of the instances of the database to determine whether the effect is available to each instance and run the day to write the log report or run the test file. Of course, there are some operations we hope that it will run automatically.
Choose implementation: using ORACLE Manager 'PROBE' event to view
B. Log files to find new warning
1. Join an operation management system for each
2. Use 'TELNET' or a comparable program
3. For each managed instance, a regular implementation of the $ ORACLE_BASE / / bdump operations and enable them to fall back control of the database SID.
4. At the prompt, the use of UNIX in the 'TAIL' command to view the alert_.log, or otherwise check the file in the recent period the warning log
5. If you have previously there have been some of the ORA_ERRORS appeared, it would be recorded in the database recovery logs, and careful study of them, the database recovery log in the in the
C. View DBSNMP running checks each managed machine 'DBSNMP' process and recorded them to the log.
In UNIX, in the command line, type ps-ef | grep dbsnmp, will be back to see the two DBSNMP process running. If not, restart DBSNMP.
D. Check database backup is successful
E. Check the backup tapes document the success of
F. Check the performance of reasonable is whether there are sufficient resources to
1. Check the table space, there is no spare room for
for each an example, check whether there is space in the table there is space left to meet the anticipated needs of the day. When the data already in its database is stable, the data on the average growth also can be calculated, the smallest of the remaining space for at least a day to be able to meet the data growth.
A) Run 'FREE.SQL' to check the remaining space for the table space.
B) Run 'SPACE.SQL' to check the table space in the percentage of remaining space
2. Check the status of rollback segment
rollback is generally online, apart from some complex work to prepare for a special segment, which generally state is offline.
a. Each database has a list of rollback segment name.
b. You can use the V $ ROLLSTAT to look up online or offline rollback of the present state.
c. For all rollback segment storage parameters and name, can be used to query DBA_ROLLBACK_SEGS. But it is not as good as V $ ROLLSTAT accurate.
3. To identify excessive growth of
view the database, the growth rate in excess of resources or too large segments that are the storage parameters need to be adjusted.
a. gather information on the size of daily data, you can use 'ANALYZE5PCT.SQL'. If you collect the information every night, you can skip this step.
b. Check the current range of available 'NR.EXTENTS.SQL'.
c. Query the current size of the table information.
d. check the size of the current index information.
e. Look growth.
4. To determine the scope of space
if the range of spatial objects NEXT_EXTENT than the tablespace can offer the widest range bigger, then this will affect the database operation. If we find that goal, you can use 'ALTER TABLESPACE COALESCE' survey of its location, or add additional data files.
A) Run 'SPACEBOUND.SQL'. If you are normal, and will not return any rows.
5. Recalling the CPU, memory, networking, hardware resources, the process of argument
A) check the CPU utilization, and enter x: \ web \ phase2 \ default.htm => system
metrics => CPU utilization page, CPU's maximum of 400, when the CPU occupancy remained at more than 350 for some time, then we need to see and study the problems arose.
G. Copy the archive logs to the standby database, if there is a standby database, copy the appropriate archived logs to the standby database, the desired position, standby database, to save the most recent data.
H. regular access to DBA user's manual if possible, to a wide range of reading, including DBA manuals, trade journals, newsgroups or mailing lists.
二. Night maintenance procedures
most of the database products will benefit from night to determine the operation of the inspection process.
A. Data collection VOLUMETRIC
1. Analysis plan and data collection
more accurate analysis of the calculation and save the result.
a) If you do not make these words, with 'MK VOLFACT.SQL' to create a volumetric tables.
b) to collect data on the size of the information at night, with 'ANALYZE COMP.SQL'.
c) the collection of statistical results, the 'POP VOL.SQL'.
d) In the idle time of inspection data, if possible, weekly or monthly basis.
I was using MS EXCEL and the ODBC connection to examine the growth of data and charts
-------------------------------------------------- -----------
三. Weekly maintenance process
A. Find targets destroyed
1. For each object of a given table space is, NEXT_EXTENT size is the same, such as 12/14/98, the default NEXT_EXTENT of DATAHI for 1G, DATALO for 500MB, INDEXES to 256MB.
A) Check NEXT_EXTENT settings that can be used 'NEXTEXT. SQL '.
B) Check the existing EXTENTS, can be 'EXISTEXT. SQL '.
2. All tables should have a unique primary key
a. See that table does not have a primary key can be used 'NO_PK.SQL'.
b. identify the factors that play a role in the primary key is not, and can be used 'DIS_PK.SQL'.
c. All of the primary key for the index must be unique, and can be used 'NONUPK. SQL 'to check.
3. All the indexes have to put the index table space. Run 'MKREBUILD_IDX. SQL '
4. Different plans for the environment should be the same, especially the testing environment and finished plans for the environment should be the same.
a. Check the different operating environment in the two data types are the same, available 'DATATYPE.SQL'.
b. In two different instances of the object to look for a different point, can be 'OBJ_COORD.SQL'.
c. a better approach is to use a tool, such as program manager for the software as tools.
B. To see if there is harm to the security policy issues.
C. See the SQL * NET Baocuo log.
1. Client-side log.
2. Server-side log.
D. All of the alert log archive
E. Supplier home page
1. ORACLE Suppliers
http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://oracle.peixunzhaosheng.net/
2. Quest Software
http://www.quests.com
3. Sun Microsystems
http://www.sun.com
四. Month maintenance process
A. View on the database will harm the growth rate of
1. From the previous record or report reviewed the changes in the growth segment in order to determine the growth segment harm
B. Recalling its previous adjustment of the database to optimize performance
1. Recalling the ORACLE database to adjust the general point of comparison in previous reports to identify harmful trends.
C. See I / O, screen neck problems
1. View the activity of pre-database files, comparing the previous output to determine the problem may lead to cervical screen trend.
D. Review FRAGMENTATION
E. Program database in the future performance of
1. Comparison of ORACLE and the operating system CPU, memory, network, and disk utilization in order to determine in the near future there will be a number of trends in the scramble for resources
2. When the system will exceed the scope of the performance trend as necessary to service-level agreements of view
F. Complete the adjustment and maintenance work
1. To make changes to avoid the system resources to meet competing needs, This includes the addition of new resources or to make the expected downtime.

oracle database backup and restore 2009-04-03 18:35:52
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Turning optimization oracle article 2009-03-26 21:39:35
Block Oracle paragraph (reprint) 2009-03-26 17:41:14

The daily work of Oracle database administrator

kods.net — Thu, 07 Jan 2010 09:14:44+0000

Oracle Database administrator should be as follows on the Oracle database system for regular monitoring:
(1). On a daily basis running Oracle databases, log files, backups, database space usage, the use of system resources, it was discovered, and solve the problem.

(2). Weekly space for expansion of the database objects and data to monitor the growth of the database to do health checks, the state of the database objects for check-ups.

(3). On a monthly tables and indexes etc. Analyze, check the table of space debris, looking for a database performance tuning of the opportunity to conduct a database performance tuning, the next steps of space management plan. State of the Oracle database, conduct a comprehensive inspection.

Every day at work

(1). Make sure that all of the INSTANCE state of normal

Log on to all the databases or routine testing ORACLE background processes:

$ ps-ef | grep ora

(2). Check the file system used (the remaining space). If the file system free space is less than 20%, need to delete unused files to free up space.

$ df-k

(3). Check the log file and trace file record alert and trace errors in the file.

To connect to each system to be managed

Use 'telnet'

For each database, cd to the bdump directory, usually $ ORACLE_BASE / / bdump

Using the Unix 'tail' command to view the file alert_.log

If you find any new ORA-errors, record and resolve

(4). Check the validity of the database backup that day.

RMAN Backup on the way:

Check third-party backup tools, backup log to determine the success of the backup

On the EXPORT backup:

Check exp log files to determine the success of the backup

The other backup method:

Check the appropriate log file

(5). Check the status of the record data file is not "online" data file and do recovery.

Select file_name from dba_data_files where status = 'OFFLINE'

(6). Check the table space is being used

SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char (100 * sum_free_m / sum_m, '99 .99 ') | |'% 'AS pct_free

FROM (SELECT tablespace_name, sum (bytes) / 1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),

(SELECT tablespace_name AS fs_ts_name, max (bytes) / 1024/1024 AS max_m, count (blocks) AS count_blocks, sum (bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name)

WHERE tablespace_name = fs_ts_name

(7). Check the remainder of the table space

SELECT tablespace_name, sum (blocks) as free_blk,

trunc (sum (bytes) / (1024 * 1024)) as free_m,

max (bytes) / (1024) as big_chunk_k, count (*) as num_chunks

FROM dba_free_space GROUP BY tablespace_name;

(8). Monitor database performance

Run bstat / estat report generation system

Or use statspack to collect statistical data

(9). Check database performance, recording database cpu use, IO, buffer hit ratio, etc.

Use vmstat, iostat, glance, top and other commands

(10). Everyday problems with processing.

The working week

(1). Controlled the space extension of the database objects

According to the inspection day this week to find space for expansion of the database objects quickly and take appropriate measures

- Remove historical data

--- Extended table space

alter tablespace add datafile''size

--- Adjust the data object storage parameters

next extent

pct_increase

(2). Monitoring the growth of the amount of data

According to the inspection day this week to find records fast growth in the number of database objects, and to take appropriate measures

- Remove historical data

--- Extended table space

alter tablespace add datafile''size

(3). System health check

Check the following:

init.ora

controlfile

redo log file

archiving

sort area size

tablespace (system, temporary, tablespace fragment)

datafiles (autoextend, location)

object (number of extent, next extent, index)

rollback segment

logging & tracing (alert.log, max_dump_file_size, sqlnet)

(4). Check invalid database objects

SELECT owner, object_name, object_type FROM dba_objects

WHERE status = 'INVALID'.

(5). Check constraint does not work

SELECT owner, constraint_name, table_name,

constraint_type, status

FROM dba_constraints

WHERE status = 'DISABLED' AND constraint_type = 'P'

(6). Check invalid trigger

SELECT owner, trigger_name, table_name, status

FROM dba_triggers

WHERE status = 'DISABLED'

Monthly work

(1). Analyze Tables / Indexes / Cluster

analyze table estimate statistics sample 50 percent;

(2). Check the table space debris

According to this month, the weekly inspection and analysis of database fragmentation to find the appropriate solution

(3). Looking for a database performance tuning opportunities

Comparison of daily database performance monitoring and reporting, to determine whether it is necessary to adjust the database performance

(4). Database Performance Tuning

If necessary, conduct performance tuning

(5). Proposed management plan for the next space

Based on weekly monitoring, space management for improved methods proposed

The daily management of Oracle DBA

Purpose: This document has a very detailed information on a record of one or more of the ORACLE database, daily, monthly, and annual operating results and check the status of the results of the appendix in the document you will see All inspections, modify the SQL and PL / SQL code.

Directory

1. Routine maintenance procedures

A. Checks all instances of the date of

B. To find some new alert log

C. Check DBSNMP is running

D. Check the database backup is correct

E. Check the backup to tape, the files are correct

F. Check whether the performance of the database proper and reasonable and whether there is sufficient space and resources

G. The document copied to the backup log database

H. Should be used to see a DBA user's manual

2. Night maintenance procedures

A. To collect the data VOLUMETRIC

3. Weekly maintenance

A. To find those breaking the rules of the OBJECT

B. To find whether there are violations of security policy issues

C. See the error log where the SQL * NET

D. All the alert log archive

E. Frequently visited provider's home page

4. Month maintenance program

A. View of the database will harm the growth rate of

B. Recalling its previous adjustment of the database to optimize performance

C. See I / O, screen neck problems

D. Review FRAGMENTATION

E. The future implementation plan

F. See set point and maintain the

1. Day maintenance process

A. See whether it has played all of the instances of

Determine the database is available to each instance and run the day to write the log report or run the test file. Of course, there are some operations we hope that it will run automatically.

Choose implementation: using ORACLE Manager 'PROBE' event to view

B. To find new warning log file

1. Join an operation management system for each

2. Use 'TELNET' or a comparable program

3. For each managed instance, a regular implementation of the $ ORACLE_BASE / / bdump operations and enable them to fall back control of the database SID.

4. At the prompt, the use of UNIX in the 'TAIL' command to view the alert_.log, or otherwise check the file in the recent period the warning log

5. If you have previously there have been some of the ORA_ERRORS appeared, it would be recorded in the database recovery logs, and careful study of them, the database recovery log in the in the

C. See DBSNMP of operation

Check each managed machine 'DBSNMP' process and recorded them to the log.

In UNIX, in the command line, type ps-ef | grep dbsnmp, will be back to see the two DBSNMP process running. If not, restart DBSNMP.

D. Check database backup is successful

E. Check whether the backup tapes document the success of

F. Check for reasonable performance for the adequacy of resources for

1. Check the table space, there is no residual room for

For each instance, the check whether there is space in the table there is space left to meet the anticipated needs of the day. When the data already in its database is stable, the data on the average growth also can be calculated, the smallest of the remaining space for at least a day to be able to meet the data growth.

A) Run 'FREE.SQL' to check the remaining space for the table space.

B) Run 'SPACE.SQL' to check the table space in the percentage of remaining space

2. Check rollback

Rollback of the state is generally online, apart from some complex work to prepare for a special segment, which generally state is offline.

a. Each database has a list of rollback segment name.

b. You can use the V $ ROLLSTAT to look up online or offline rollback of the present state.

c. For all rollback segment storage parameters and name, can be used to query DBA_ROLLBACK_SEGS. But it is not as good as V $ ROLLSTAT accurate.

3. To identify excessive growth of

View the database in excess of the resources or growing too large segments that are the storage parameters need to be adjusted.

a. gather information on the size of daily data can be used

'ANALYZE5PCT.SQL'. If you collect the information every night, you can skip this step.

b. Check the current range of available 'NR.EXTENTS.SQL'.

c. Query the current size of the table information.

d. check the size of the current index information.

e. Look growth.

4. To determine the scope of space

If the range of spatial objects NEXT_EXTENT than the tablespace can offer the widest range bigger, then this will affect the database operation. If we find that goal, you can use 'ALTER TABLESPACE COALESCE' survey of its location, or add additional data files.

A) Run 'SPACEBOUND.SQL'. If you are normal, and will not return any rows.

5. Recalling the CPU, memory, networking, hardware resources, the process of argument

A) check the CPU utilization, and enter x: \ web \ phase2 \ default.htm => system

metrics => CPU utilization page, CPU's maximum of 400, when the CPU occupancy remained at more than 350 for some time, then we need to see and study the problems arose.

G. Will be archived logs copied to the standby database

If there is a standby database, copy the appropriate archived logs to the standby database, the desired position, standby database, to save the most recent data.

H. regular access to DBA User's Manual

If possible, to a wide range of reading, including DBA manuals, trade journals, newsgroups or mailing lists.

2. Night maintenance process

Most of the database products will benefit from night to determine the operation of the inspection process.

A. Data collection VOLUMETRIC

1. Analysis plan and data collection

A more accurate analysis of the calculation and save the result.

a) If you do not make these words, with 'MK VOLFACT.SQL' to create a volumetric tables.

b) to collect data on the size of the information at night, with 'ANALYZE COMP.SQL'.

c) the collection of statistical results, the 'POP VOL.SQL'.

d) In the idle time of inspection data, if possible, weekly or monthly basis.

I was using MS EXCEL and the ODBC connection to examine the growth of data and charts

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

3. A weekly maintenance procedures

A. To find the goal the destruction

1. For each object of a given table space is, NEXT_EXTENT size is the same, such as 12/14/98, the default NEXT_EXTENT of DATAHI for 1G, DATALO for 500MB, INDEXES to 256MB.

A) Check NEXT_EXTENT settings that can be used 'NEXTEXT. SQL '.

B) Check the existing EXTENTS, can be 'EXISTEXT. SQL '.

2. All tables should have a unique primary key

a. See that table does not have a primary key can be used 'NO_PK.SQL'.

b. identify the factors that play a role in the primary key is not, and can be used 'DIS_PK.SQL'.

c. All of the primary key for the index must be unique, and can be used 'NONUPK. SQL 'to check.

3. All the indexes have to put the index table space. Run 'MKREBUILD_IDX. SQL '

4. Different plans for the environment should be the same, especially the testing environment and finished plans for the environment should be the same.

a. Check the different operating environment in the two data types are the same, available 'DATATYPE.SQL'.

b. In two different instances of the object to look for a different point, can be 'OBJ_COORD.SQL'.

c. a better approach is to use a tool, such as program manager for the software as tools.

B. see if there is harm to the security policy issues.

C. See the SQL * NET Baocuo log.

1. Client-side log.

2. Server-side log.

D. All the alert log archive

E. Provider's home page

1. ORACLE Suppliers

http://www.oracle.com

http://technet.oracle.com

http://www.oracle.com/support

http://www.oramag.com

2. Quest Software

http://www.quests.com

3. Sun Microsystems

http://www. sun. com

4. Month maintenance process

A. View of the database will harm the growth rate of

1. From the previous record or report reviewed the changes in the growth segment in order to determine the growth segment harm

B. Recalling its previous adjustment of the database to optimize performance

1. Recalling the ORACLE database to adjust the general point of comparison in previous reports to identify harmful trends.

C. See I / O, screen neck problems

1. View the activity of pre-database files, comparing the previous output to determine the problem may lead to cervical screen trend.

D. Review FRAGMENTATION

E. Program database in the future performance of

1. Comparison of ORACLE and the operating system CPU, memory, network, and disk utilization in order to determine in the near future there will be a number of trends in the scramble for resources

2. When the system will exceed the scope of the performance trend as necessary to service-level agreements of view

F. The completion of the adjustment and maintenance work

1. To make changes to avoid the system resources to meet competing needs, This includes the addition of new resources or to make the expected downtime.

Oracle database used to maintain day-to-day command 2009-04-06 16:46:33
oracle database backup and restore 2009-04-03 18:35:52
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Turning optimization oracle article 2009-03-26 21:39:35
Block Oracle paragraph (reprint) 2009-03-26 17:41:14

Manually create the ORACLE database (10g) - Record only

kods.net — Wed, 06 Jan 2010 08:20:02+0000

Question:

Creating a database manually

Environment:

Operating System: Microsoft Windows XP [Version 5.1.2600]

Database: ORACLE 10.2

Source:

1 ORACLE DOC

Oracle? Database Administrator's Guide

2 Creating and Configuring an Oracle Database

Creating a Database with the CREATE DATABASE Statement

2 own blog in an introduction

http://blog.chinaunix.net/u1/53979/showart.php?id=428920

Experiment:

1 mkdir

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ admin \ magic \ adump

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ admin \ magic \ bdump

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ admin \ magic \ cdump

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ admin \ magic \ dpdump

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ admin \ magic \ pfile

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ admin \ magic \ udump

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ cfgtoollogs \ dbca \ magic

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ database

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ flash_recovery_area

mkdir D: \ WORK_SOFT \ app \ product \ 10.2.0 \ oradata \ magic

2 ORACLE_SID

ORADIM-NEW-SID MAGIC

3 PASSWORD FILE

ORAPWD FILE = D: \ WORK_SOFT \ app \ PRODUCT \ 10.2.0 \ DB_1 \ DATABASE \ PWDMAGIC.ORA PASSWORD = MAGIC

4 init file

5 startup nomount

create database

ALTER USER SYS TEMPORARY TABLESPACE TEMP

6 some scripts

@ D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ catalog.sql

@ D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ catproc.sql

@ D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ utlrp.sql

@ D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ scott.sql

Conclusion:

A simple experiment to create a database, did not include monitoring of the class, their experiments only.

Added:

Problems:

SQL> conn thinkaw / thinkaw

Access PRODUCT_USER_PROFILE Error

Warning: Product user profile does not load the file information!

You will need to PUPBLD.SQL running as SYSTEM

Is connected.

If you are prompted to perform the corresponding SQL, success.

- End

rn rn rn rn rn rn rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database in the Recycle Bin

kods.net — Sat, 02 Jan 2010 17:20:02+0000

A few days ago the database gave a number of useless delete a few tables, and later see what the current user, there are some table, actually featured a lot of garbled, and later checked the Internet know, these are I deleted The tables were put to the Recycle Bin in the database, which means you use the drop table XXX statement to delete obsolete table, there is no completely clear. It is just being put into the database of the Recycle Bin. As long as time is not very long also find them again. As long as you remember the table name, and then use the statement flashback table XXX to before drop can be seen. To completely remove the table when the drop in use was coupled with purge keywords, such as drop table XXX purge can be completely deleted.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Configure the Oracle database and monitoring systems with Linux since the launch of

kods.net — Sat, 26 Dec 2009 19:22:44+0000

In some cases, the need to provide a Linux operating system unattended on a random start Oracle functions, to perhaps just to help those who are not concerned about the details of Oracle's friends.
Of course, if possible, must not use this automated method, because there are too many uncertainties exist. The right path or the side of the hand to start the database, while monitoring alert log to identify problems the first time to solve the problem.

Anyway, with the Linux System Startup database still has its value, following children through this small text display for everyone to look into the automation process.

1. To ensure that "/ etc / oratab" the correctness of the document have then modify, if at creating "/ etc / oratab" file. The document function is to control the Oracle startup (dbstart) and close the script (dbshut) is available.
# Vi / etc / oratab
secooler: / oracle/app/oracle/product/10.2.0/db_1: Y

Only when the tail logo is a "Y", when, dbstart and dbshut scripts can use.
dbstart and a location for dbshut script is ORACLE_HOME's bin directory, you can use the following command to view the content of the script.
$ Cat $ ORACLE_HOME / bin / dbstart
$ Cat $ ORACLE_HOME / bin / dbshut

2. Modify access to oratab
# Chmod 755 / etc / oratab
# Chown oracle: root / etc / oratab

3. Preparation of automatic start and stop the script, the script called "oracle", the file needs to be saved in / etc / rc.d / init.d directory.
vi / etc / rc.d / init.d / oracle
#! / bin / bash
################################################## ############
# Script. Function: Auto start oracle instance and listener. #
# Script. Name: oracle #
# Author: Secooler Hou #
# Date: 2009-11-13 19:58:49 #
################################################## ############
case "$ 1" in
start)
echo "Starting Oracle Database ..."
su - oracle-c "dbstart"
echo "Done."
echo "Starting Oracle Listener ..."
su - oracle-c "lsnrctl start"
echo "Done."
touch / var / lock / subsys / oracle
;;
stop)
echo "Shutting down Oracle Listener ..."
su - oracle-c "lsnrctl stop">> / var / log / oracle
echo "Done."
rm-f / var / lock / subsys / oracle
echo "Shutting down Oracle Database ..."
su - oracle-c "dbshut"
echo "Done."
echo ""
;;
restart)
$ 0 stop
$ 0 start
;;
*)
exit 1
esac

4. Script need to explain the details of the two
1) / var / lock / subsys / oracle This file is used to identify oracle instance is already running. A simple lock to achieve a similar function;
2) To achieve the root user where the user sends to the oracle database startup and shutdown commands, need to use the "su" command "-c" option, more details, please use the man command to search.

5. Permissions to modify the oracle script
# Chmod 755 oracle

6. To ensure that the script in the Linux system startup process can be automatically called, so the need to establish the following connection file.
Of particular note one detail: to determine the script to start after the implementation of the card, otherwise Listener may not start (if the Listener configuration file using the IP address).
# Ln-s .. / init.d / oracle / etc/rc.d/rc2.d/S87oracle
# Ln-s .. / init.d / oracle / etc/rc.d/rc3.d/S87oracle
# Ln-s .. / init.d / oracle / etc/rc.d/rc5.d/S87oracle

7. At this point, after re-start the Linux system, Oracle instance and listener starts automatically.

8. Based on this configuration, you can also use the following easy way to stop, Kai, and restart the database to stop:
# Service oracle stop
Start:
# Service oracle start
Restart:
# Service oracle restart

9. Summary I admired automation, but here presented automated startup and shutdown the database approach also invited friends with caution.
Deep into the database the details of the startup and shutdown is the right way.

Good luck.

- The End --

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle database password file to use and maintenance of

kods.net — Mon, 21 Dec 2009 06:54:13+0000

Oracle database password file to use and maintenance of

By shijun »2009-11-03 11:01

Oracle, if the user wants to privileged user (INTERNAL / SYSDBA / SYSOPER) log Oracle, specifically, there are two authentication methods: (Source: linuxol.cn)

(1) The use of integrated authentication with the operating system

(2) using Oracle database password file authentication.

Therefore, to manage the password file for the control of authorized users to log on from a remote or local Oracle database system, the implementation of database management, are of particular importance.

Oracle database password file stored with super-user INTERNAL / SYS password and other privileges of the user's username / password, generally stored in the ORACLE_HOME \ DATABASE directory.

1, the password file creation:

Using the Oracle Instance Manager to create a database instance, when Hou, in the ORACLE_HOME \ DATABASE directory is also automatically creates a corresponding password file, the file name PWDSID.ORA, where SID represents the corresponding Oracle database system identifier. This password file is the initial database management basis for the work. After this, the administrator can also if necessary, use a tool ORAPWD.EXE manually create a password file, the command format is as follows: C: \> ORAPWD FILE = PASSWORD

= ENTRIES =

The meaning of the command parameters as follows:

FILENAME: password file name;

PASSWORD: Set INTERNAL / SYS account password;

MAX_USERS: password file can be stored in the largest number of users, corresponding to allow SYSDBA / SYSOPER permissions to log on the database the maximum number of users. Because of maintenance in the future, if the number of users exceeded this limit, you need to rebuild the password file, so this parameter can be set up too large a number as needed.

With the password file, the need to set initialization parameters to control the password file REMOTE_LOGIN_PASSWORDFILE the use of the state.

2, set the initialization parameters REMOTE_LOGIN_PASSWORDFILE:

In the Oracle database instance initialization parameter file, this parameter controls the use of the password file and its state. It has the following options:

NONE: direct Oracle system does not use a password file, the user's login privileges through operating system authentication;

EXCLUSIVE: indicates that only one database instance can use this password file. Only in this setting the password file can contain, inter INTERNAL / SYS other than the user information, which allows the system privileges SYSOPER / SYSDBA granted except INTERNAL / SYS other than the user.

SHARED: instruction may have multiple database instances can use this password file. In this setting only INTERNAL / SYS account password file can be identified, even if the file there are other users of information, but also allow them to SYSOPER / SYSDBA permissions to log on. This is set to default values.

The REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE, SHARED case, Oracle system password file search order as follows: in the system registry to find ORA_SID_PWFILE parameter values (which for the password file full path name); If you do not find, then search for ORA_PWFILE parameter value; If not found, use the default value is ORACLE_HOME \ DATABASE \ PWDSID.ORA; where SID represents the corresponding Oracle database system identifier.

Third, the password file to add, delete users:

REMOTE_LOGIN_PASSWORDFILE when the initialization parameter is set to EXCLUSIVE, the system allows addition INTERNAL / SYS other than the user as an administrator from a remote or local log on to the Oracle database system, the implementation of database management; these user names must exist in the password file in the , the system can identify them. Because whether you create the database instance is created automatically when the password file, or created manually using a tool ORAPWD.EXE password file only contains INTERNAL / SYS user information; To this end, in practice, you may need to add a password file or delete other user accounts.
Because only been granted SYSOPER / SYSDBA system privileges of the user only exists in the password file, so when a user to grant or withdraw SYSOPER / SYSDBA system privileges, their account will accordingly be added to the password file or from the password file removed. http://www.linuxol.cn As a result, the password file to add or delete a user, in fact it is right to grant or withdraw a user SYSOPER / SYSDBA system privileges. (Source: linuxol.cn)

To complete the authorization operation, need to use SYSDBA privileges (or INTERNAL account number) connected to the database, and the initialization parameters set REMOTE_LOGIN_PASSWORDFILE must be EXCLUSIVE. Concrete steps are as follows:

Create the appropriate password file;

Setting initialization parameters REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE;

Log in using the SYSDBA privileges:

CONNECT SYS / internal_user_passsword AS SYSDBA;

Start the database instance and open the database;

Create a corresponding user account, his mandate (including the SYSOPER and SYSDBA):

Grant permissions: GRANT SYSDBA TO user_name;

Withdraw permission: REVOKE SYSDBA FROM user_name;

Now these users can log on as an administrator of the database system;

Fourth, using the password file login:

With the password file, users can use the password file to SYSOPER / SYSDBA privileges log on Oracle database instance, and pay attention REMOTE_LOGIN_PASSWORDFILE initialization parameter should be set to EXCLUSIVE or SHARED. Any user with SYSOPER / SYSDBA permissions to log in, will be located in SYS Schema of users under the following two registry examples:

1. Log on to:

Suppose user scott has been granted SYSDBA privileges, he can use the following command log:

CONNECT scott / tiger AS SYSDBA

2. To INTERNAL log:

CONNECT INTERNAL / INTERNAL_PASSWORD

5, the password file maintenance:

1. View the password file members:

Can query the view V $ PWFILE_USERS to obtain ownership SYSOPER / SYSDBA system privileges of the user's information, the table SYSOPER / SYSDBA column values TRUE / FALSE means that the user has the appropriate permissions. These users is accordingly present in the password file members.

2. Expanding the number of users password file:

When the password file to add the account with more than create a password file, set limit (ie ORAPWD.EXE tools MAX_USERS parameters), the password file for the extension limits the number of users required to rebuild the password file, concrete steps are as follows:

(A) query the view V $ PWFILE_USERS, a record has SYSOPER / SYSDBA system privileges of the user information;

(B) close the database;

(C) delete the password file;

(D) a new one with the ORAPWD.EXE password file;

(E) obtained in step a user is added to the password file.

3. Modify the password file states:

Password file status information stored in the document, when it is created, its default state of SHARED. You can change the initialization parameter settings to change the password file REMOTE_LOGIN_PASSWORDFILE state. When you start the database instances when, Oracle systems from the initialization parameter file to read REMOTE_LOGIN_PASSWORDFILE parameter settings; when loading the database, the system password file this parameter and to compare the state, if different, then update the password file status. If the plan allows more than one client from the start the database instance, as each client machine must have the initialization parameter file, it should be to ensure that the client consistency of the initialization parameter file to avoid accidentally changed the password file status , resulting in a database login failure.

4. Modify the password file storage location:

The password file storage location can be moved as needed, but to make this modification, it should be amended accordingly the system registry pointing to the password file stored on the location parameter or environment variable settings.

5. Remove the password file:

Deleting the password file, should ensure that the currently running instance of the database initialization parameters REMOTE_LOGIN_PASSWORDFILE are set to NONE. After deleting the password file, if you want to enter the database administrator with the words, you must use operating system authentication way to log on.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Completely remove Oracle Database

kods.net — Sun, 20 Dec 2009 04:07:55+0000

:

A start - "Set -" Control Panel - "Administrative Tools -" Service

Stop all Oracle services.

2 to start - "process -> Oracle - OraHome81-> Oracle Installation Products->

Universal Installer

Uninstall all Oracle products, but the Universal Installer itself can not be removed

5, run regedit, select HKEY_LOCAL_MACHINESOFTWAREORACLE, press del key to delete this entry.

6, run regedit, select HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices, rolling

This list, remove all the Oracle portal.

7, run refedit,

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication,

Delete all Oracle portal.

8, beginning - "Set -" Control Panel - "System -" High - "Environment Variables

Remove the environment variable CLASSPATH and PATH settings relating to Oracle

9, from the desktop, STARTUP (startup) group, the program menu, delete all the Oracle group and icons

10, delete the Program Files \ Oracle directory

11, restart the computer, re-post to be completely removed from the directory where Oracle

2009-12-20

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Silent installation of Oracle Database Practice

kods.net — Sat, 19 Dec 2009 13:00:25+0000

Silent installation of Oracle Database Practice

Author: Qu Zhuo

April 21, 2006

Preface

This article is the author of Oracle database, the practice of silent installation process of recording and lessons learned. One of the silent installation of 10g on RHEL AS3U4 practice is carried out, 9i silent installation of the practice is in the Windows XP Professional (SP2) is conducted. 9i installed in three common CD-ROM, Oracle installing customized specifically for the embedded installation of 9i of a CD-ROM version of the 9204e, can download the version of the OPN site of the installation file. This article 9i install the version used in the practice shall be 9204e.

This understanding of Oracle databases through the silent installation of the main steps and processes may also be a silent installation of this as a concise guide for reference. If you need detailed instructions and parameters for silent installation process, please according to the actual operating system and database version of the reference to the corresponding OEIKit Kit documentation.

Back to Topic List

Silent install Oracle Database 10g chapter

The following is a silent installation on a Linux system, Oracle Database 10g course of practice, mainly divided into the following two steps:

Step 1. Silent install Oracle Database 10g Software

1.	Use OUI recorded response file, records the installation process Execute the following command, and then OUI prompts to install the database, in accordance with the operation of the software $. / runInstaller-record-destinationFile / tmp / install_database.rsp Note: The recording, select only the installation of the database software does not create a database When the installation interface reached the final step in the selection of cancel
2.	Playback response file silent install Oracle database software Execute the following command to install Oracle database software silently $. / runInstaller-silent-responseFile / tmp / install_database.rsp After the installation is complete, execute the following script #. $ ORACLE_BASE / oraInventory / orainstRoot.sh #. $ ORACLE_HOME / root.sh Optional parameters: Can use $. / RunInstaller-help see all the optional parameters OUI For example: $. / RunInstaller-silent-force-ignoreSysprereqs ORACLE_HOME = / oracle ORACLE_HOME_NAME = OHOME_1-responseFile / tmp / install_database.rsp
3.	Silent uninstall the Oracle Database Software Execute the following command to uninstall silently Oracle database software $. / runInstaller-silent-deinstall-removeallfiles-removeAllPatches "REMOVE_HOMES = ($ ORACLE_HOME)"-responseFile / tmp / install_database.rsp

Step 2. Silent installation of Oracle Database

1.	Use DBCA to create a seed database - ISV actually need to use the database (1) to create an initial database came time to select Custom Database template (2) recommend the use of File System Storage Mechanism (3) The data file location of the proposed options Use Oracle-Managed Files Database Area: (ORACLE_BASE) / oradata (4) memory recommend choosing Custom, specify the size of the SGA and PGA (5) character set based on the actual need to set up (6) database creation option inside the option Create Database (7) to create applications that require the table space and user, import the initial data
2.	According to seed the database with the DBCA to create a template (1) Start DBCA, select Manage Templates (2) Follow the prompts to create a template
3.	Oracle DB installation disc inside the response directory dbca.rsp copy to this machine
4.	Modify dbca.rsp file to the template name and the DB name to be added GDBNAME = SID = TEMPLATENAME = If you need to use Oracle Enterprise Manager, you also need to modify the following parameters are as follows: EMCONFIGURATION = "LOCAL" SYSMANPASSWORD = "password" DBSNMPPASSWORD = "password"
5.	Execute the following command to use DBCA to create a new database according to the template $. / dbca-silent-createdatabase-responseFile / home / oracle / embed / dbca.rsp
6.	Would have a direct write. Ora file configuration listener
7.	Configure listener using the method is as follows Netca (1) Oracle DB installation disc inside the response directory netca.rsp copy to this machine (2) modify the file netca.rsp INSTALL_TYPE = "" custom "" LISTENER_NAMES = ( "LISTENER_EMBED") LISTENER_PROTOCOLS = ( "TCP; 1521") LISTENER_START = "" LISTENER_EMBED "" (3) execute the following commands to configure silent Listener $. / dbca / silent / responseFile / home / oracle / embed / netca.rsp

Back to Topic List

Oracle Database 9i silent installation chapter

The following is a silent installation on a Windows system, Oracle database, 9204e course of practice, mainly divided into the following three steps:

Step 1. Oracle Database 9i silent installation software
Step 2. Silent installation of Oracle Database
Step 3. Silent Upgrade ODBC Driver (optional)

Step 1. Oracle Database 9i silent installation software

Obtain the response file

(1) Method 1: In the $ MountPoint / install directory, execute the following command to record a response file: setup.exe-record-destinationFile destinationFile.rsp

For example: D: \ Soft \ Oracle_product \ rdbms_9204e_win \ install> setup.exe-record-destinationFile D: \ Tmp \ install_oracle.rsp

Note:

(1) The need to add the recorded response file INSTALL_TYPE = "EE" to specify the installation of oracle Database Enterprise Edition (2) The need to respond to the recorded file, change the following parameters: ACCEPT_LICENSE_AGREEMENT = true

(2) Method 2: based on actual need to modify the $ MountPoint / stage / Response / oracle.server.EE.rsp:

Of which the following parameters are set according to the actual situation:

FROM_LOCATION = ".. \ stage \ products.xml"

FROM_LOCATION_CD_LABEL = "LABEL1"

ORACLE_HOME = ORACLE_HOME_NAME = "OHOME1"

Playback response file to install Oracle database software

Execute the following command to install Oracle database software silent setup.exe-silent-responseFile responseFile Name

Silent uninstall the Oracle Database Software

(1) perform the following SQLPlus script to close the database:
shutdown immediate
exit;
(2) Stop Oracle Services
(3) in the $ ORACLE_HOME / bin directory, execute the following command to remove the Oracle Database
oradim-delete-sid% MY_ORACLE_SID%
(4) OEIKit Toolkit removeService.exe where the directory execute the following command to remove the Oracle network services
removeService Oracle% ORACLE_HOME_NAME% TNSListener
(5) to perform the following command to uninstall silently Oracle database software
setup.exe-noconsole-silent-deinstall-waitforcompletion-monitorFile% monitorFile% ORACLE_HOME =% MY_ORACLE_HOME% ORACLE_HOME_NAME =% ORACLE_HOME_NAME% REMOVE_HOMES ="{"% MY_ORACLE_HOME% ")"-responseFile% responseFileDir% \ oracle9iserver_singlecd.rsp
For example:
D: \ Soft \ Oracle_product \ rdbms_9204e_win \ install> setup.exe-noconsole-silent-deinstall-waitforcompletion ORACLE_HOME = d: \ OraHome_2 ORACLE_HOME_NAME = OUIHome2 REMOVE_HOMES = "D: \ OraHome_2"-responseFile d: \ tmp \ install_test.rsp

Step 2. Silent installation of Oracle Database

1.	Use DBCA to create a seed database - ISV actually need to use the database (1) to create an initial database came time to select Custom Database template (2) character set based on the actual need to set up (3) database creation option inside the option Create Database (4) to create applications that require the table space and user, import the initial data
2.	2.2.2 DBCA to create a template according to seed the database (1) Start DBCA, select Manage Templates (2) Follow the prompts to create a template (3) will create a good template file (. Dbc and. Dfj) copied to the% ORACLE_HOME% \ assistants \ dbca \ templates \
3.	Execute the following command to use DBCA to create a new database according to the template dbca-silent-createDatabase-templateName templateName-gdbname gdbname-sid sidName-sysPassword sysPassword-systemPassword systemPassword For example: D: \ OraHome_2 \ BIN> dbca-silent-createDatabase-templateName test.dbc-gdbname "zesl.cn.oracle.com"-sid "zesl"-sysPassword "oracle"-systemPassword "oracle"
4.	Would have a direct write. Ora file configuration listener
5.	Configure listener using the method of Netca with 1.2.7

Step 3. Silent Upgrade ODBC Driver (optional)

Oracle DB 9204e Windows version of the installation is complete, ODBC Driver version is 9.02.00.00, in view of 9.02.00.02 version of some of the optimization, some users ODBC Driver needs to be upgraded to 9.02.00.02, the following is a silent upgrade process:

(1) Reference odbc.rsp to create a response file, which the following parameters set according to the actual situation: FROM_LOCATION, ORACLE_HOME, ORACLE_HOME_NAME;

(2) using the following commands to start OUI, specify the use of the response file to complete the silent update: setup.exe-silent-responseFile responseFile Name For example: D: \ Soft \ Oracle_product \ rdbms_9204e_win \ install> setup.exe-silent-responseFile D: \ Soft \ Oracle_product \ ODBC \ ora9202 \ odbc.rsp

(3) 9202ODBC driver download address http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/utilsoft.html

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

ORACLE database structure diagram

kods.net — Fri, 27 Nov 2009 07:19:12+0000

ORACLE database structure diagram:

500) this.width = 500; "border =" 0 ""

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle Database Optimization of the basic ideas

kods.net — Thu, 12 Nov 2009 17:17:48+0000

The purpose of a document

The company's current project, a database project using the oracle account for a very high ratio in various stages of the project life cycle, there is a demand for optimizing the database, this document used to guide the engineering and technical personnel quickly locate problems and seize database performance the main bottlenecks to standardize tuning work, improve work efficiency.

2 Scope

A certain database management and R & D-based technical staff, at the same time, this document describes only the principles and methods do not provide specific orders, nor can not replace the operating manual.

3 Terminology

PDCA: PDCA cycle is also called the Deming Wheel is the nation's experts in Deming quality management first proposed, it is a total quality management of scientific procedures to be followed. Total quality management activities of the whole process is the quality plan formulation and implementation process of the organization, this process is in accordance with PDCA cycle, without interruption to operation of the cycle.

Metalink: Oracle's official support site, the site need to use the customer service number to register (CSI), website provides a variety of Oracle's full range of products and technical data query, software patches and updates download, as well as a 24-hour engineer support services. At present, the site officially changed its name to My Oracle support.

4 points Description

4.1 "good performance" and "poor performance"

"Good performance" is defined in the absence of the time, rather than the definition in the event of the time. Can easily identify the performance of a business trip, while the "good performance" is usually defined as not simply a "poor performance." For the database, the engineering understanding, the "poor performance" can be reflected in the following areas:

The machine and the client remote access to the database, the connection response is not timely, or there is a long connection times out after trying to connect or not connect;

After the table in the database operations, including: implementation of the general increase, delete, change, check SQL, the results returned a long time delay, especially in some smaller operations, the waiting time is over; stored procedures and slow implementation of the package can also be considered to be "poor performance";

Application interface on the front-end operations, such as generate and view reports, add and modify a particular data, the system response is slow, poor user experience, resulting in disappointment and complained increase in the number and so on.

In contrast, if the situation does not exist, we believe that the database has a "good performance." As shown in Figure 4-1, we strive to maintain customer satisfaction performance, range, consume a lot of manpower and material resources to achieve the ultimate performance has not significantly improved user experience, the loss of their poor performance, customer satisfaction will quickly decrease.

Figure 4-1 the relationship between customer satisfaction and performance plans

4.2 Optimization goals

The ultimate goal is to optimize the database, database, user satisfaction, rather than the pursuit of the ultimate performance, more specific data are not reflected in the indicators. Moreover, with the adjustment of business to carry out the project life cycle, database optimization is an ongoing and long-term process, and any adjustment and optimization, as long as the performance, optimization is effective. Therefore, we advocate that the database should be optimized step by step in accordance with the laws of PDCA (see Figure 4-2), rather than aggressive behavior should be bold. Specifically, the performance problems, locate a good reason for the targeted optimization, the other with non-relevant factors, no adjustment.

Figure 4-2 Database Optimization of PDCA rules

4.3 optimized links and personnel

Good performance is that apart from security, stability, the database of the most should pay attention to one aspect. In the database design, application design stage, the implementation of the construction phase, as well as post-maintenance stage, the performance should be given adequate attention, particularly in the design, there is one saying goes: "Good performance is designed, not to adjust out of. " Meanwhile, in the optimization of database maintenance work, a SQL statement to write good or bad, to demonstrate the efficiency of the database can vary more than a thousand times. Therefore, database optimization, need to program developers (R & D department), database management personnel (engineering), and the project maintenance personnel (project group) protracted and unremitting efforts to achieve.

4.4 How to ask questions

Engineering Department responsible for all third-party hardware and software products to support the work of every day to face a variety of technical support needs. If the database performance problems, we hope that the question raised by colleagues, can provide the following information, or help desk to guide them to answer these questions:

Database server and storage equipment, hardware configuration (mainly the CPU, memory and SWAP space, storage devices and RAID-level models, etc.);

The version of the operating system platform (for UNIX, preferably with the results of uname-a);

The version of database software, specifically to the small version, such as the 10.2.0 .1 Or 9.2.0.8;

System is currently the performance of statistical results (top, vmstat, prstat, iostat, etc.);

The database parameter configuration, pfile / spfile file, or show parameter results;

Data file and table space distribution;

Online redo logs of the distribution;

alert logs (if the log large, you can intercept the recent 10000 records);

Baocuo and alarm screenshots, detailed information (including foreground and background), the problem has led to what effect, affecting the extent of the;

A concrete manifestation of poor performance, such as the speed of execution on a particular SQL statement, or the application of an operation to reduce wait too long;

Systems and applications is the recent change, if so, what changes and adjustments made;

Dynamic performance views (V $ SYSTEM_EVENT, V $ SESSION_EVENT, V $ SESSION_WAIT and V $ SESSION)

AWR and ADDM results

statspack report

Memory hit rate in all regions

More non-yellow background for the need to provide the information, yellow background part is optional information, when the problem requires in-depth analysis, often need to use that information.

5 How to Optimize

5.1 Optimization of ideas and principles of the

Potential impact on business performance, real-world applications. When trying to solve performance problems, the need to ensure that we have to carefully monitor the areas for improvement, with particular attention to the changes in the system before and after modification. We must adopt a systematic approach to discover the source of performance problems and implement appropriate solutions. This approach requires that before doing any change must be the use of resources and response time to establish a baseline, and required to re-examine the performance of the system until after the environmental change can only do a small amount of changes.

The database server may be encountered due to competition over resources, leading to bottlenecks. In fact, the computer system when taking into account in the design of a lack of resources when the resources are available to try another compensation, this sometimes lead to compensate for the resource deficit. If the physical memory is exhausted, the operating system memory area will be replaced-to-disk (SWAP partition) and going, this will lead to I / O bottlenecks. Science, a general principle: time and space constraints between the expense of time can be for space, or space can be sacrificed in exchange for time. Therefore, we need to find a balance between a variety of resources, avoiding short-board effects.

5.2 Optimization Process

Figure 5-1 Database Basic Optimization Process

Based on the diagram, the proposed optimization of the basic process:

1, receives performance problems, according to the information collection listed in 3.4 a list of information collection, collation Well, this is also the baseline data.

2, first check the hardware problems, hardware, operating system, databases and applications running on the physical basis, if the hardware fails, the system is difficult to normal operation, such as: disk array battery failure, its write IO performance will be a decrease of 60% or more immediately .

3, the hardware no problem, for some of the more obvious performance problems, or problems can be directly analyzed Baocuo phenomenon, then the appropriate treatment, such as: database does not insert any data, according to prompts that can navigate to the file system directory or a table full of space has been filled; or an application module is running normally, but the upgrade of the sharp decline in performance of procedures may be required to R & D personnel inspection procedures. Typically, intuitive problem is rare.

4, parallel to consider parameters, the physical hardware (CPU, memory, network and IO), bug (including the operating system and the database itself), and SQL impact. Some of the more typical database parameters, such as 9i the data cache size, 10g The sga_target, for different application business, combined with the physical memory size, can be more obvious to set a reasonable level; the physical hardware, CPU, memory, and network to focus on whether the performance of its configuration to meet application needs, if applied correctly, CPU and memory a long time to maintain a high utilization rate, usually the processing performance can not keep up due to their application needs, this time to consider expansion will be a quick run their own access to a database server 100M Either 10M When the switch from the client initiated the visit, performance will be greatly affected. IO is very easy to become a performance bottleneck of a factor, which is due to the physical structure of the disk device caused, usually, for the data file contention easy to form hot disk, once found such a situation, if you can not upgrade storage, can be considered hot files will be scattered to different disks and devices, or from the application point of view in space and time may have originally spread onto IO-intensive to read and write operations; while some performance problems are caused by the operating system and the bug database, created, and these can be manufacturer's Web site found the information, download patches, by way of repairing systems and databases to obtain performance improvements; According to statistics, the impact of database performance factors, adjustment of the application database performance improvements, can account for 60% of the effect ( see Figure 5-2). So, can awr, statspack information resource consumption, removal of some large SQL submitted to the R & D analysis and optimization, giving priority to the principal contradiction. This part of the optimization of the R & D recommended books are Oracle's vice president of technology, edited by Tomas Kyte's "Effective Oracle by Design" and "Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions".

Figure 5-2 Database Performance Factors

5, when the above conventional methods are ineffective, the need to consider in-depth observation of the internal operation of the database to find clues leading to poor performance: the view from the properties of the focus view to wait for events; 10g Start, ADDM can automatically identify and report a variety of resource bottlenecks, such as: CPU competition, lock-related issues, or some very bad performance, such as SQL statements. Oracle Enterprise Manager server can provide high-level view of the use of resources and detailed view, to help administrators to quickly find performance issues of reason; in 11g China, ADDM can be performed in the cluster analysis tasks. ADDM able to Enterprise Manager, the panel issued a warning to managers that the current competition for resources occurred in the situation. SQL Tuning Advisor component can even find performance problems are given for the proposed solution; In the meantime, you can design and application of personnel to discuss, from the business process to consider the possibility of optimization, My Oracle support Web site (raw Metalink) has also launched a server by querying the knowledge base and requests (SR) way to get technical support.

6, optimization is complete, customer testing to confirm, record the latest performance data, optimize the results compared against baseline data to observe the period of time, sum up experience and accumulate experience for the next optimization.

6 common causes of database performance

1. Hardware failure

2. Initialization parameters properly

3. Network Connection Management

4. Cursor improper use of

5.IO mismanagement

6. Online redo logs too little (small), switching frequency is too fast

7. Db_writer process, little multi-CPU servers

8. Cache problem

9. Rollback segment too small, pct_free and pct_used not set properly

10. Large table full table scans

11. Indexes, statistical information such as the failure, SQL queries do not take the index

11. Disk Sort

12.SQL writing there is no bind variables

13. Recursive sql

14.SQL optimized model inappropriate (CBO and RBO)

SQL optimization (3) - four index 2009-03-28 13:10:06
SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
An example of the use of TKPROF 2009-02-26 01:38:19

Oracle Database

Oracle database fragmentation step by step explaining how to sort out

Related Posts

How to quickly identify lock waiting for Oracle Database

Related Posts

Oracle database, the operation method of the partition table

Related Posts

Oracle database character set conversion law of a comprehensive analysis of

Related Posts

Oracle Database Backup and Recovery Summary -exp/imp

Related Posts

Oracle database ASM Answer Center 1

Related Posts

Oracle database ASM Answer Center 2

Related Posts

To re-install the system, oracle database recovery

Related Posts

Oracle database indexes created to do three appropriate

Related Posts

Oracle Database scheduling problem by the Chinese alphabet, NLS_SORT set

Related Posts

Step by step explain the Oracle database administrator responsibilities 【1】

Related Posts

The daily work of Oracle database administrator

Related Posts

Manually create the ORACLE database (10g) - Record only

Question:

Environment:

Source:

Experiment:

Conclusion:

Added:

Related Posts

Oracle database in the Recycle Bin

Related Posts

Configure the Oracle database and monitoring systems with Linux since the launch of

Related Posts

Oracle database password file to use and maintenance of

Oracle database password file to use and maintenance of

Related Posts

Completely remove Oracle Database

Related Posts

Silent installation of Oracle Database Practice

Silent installation of Oracle Database Practice

Directory

Preface

Silent install Oracle Database 10g chapter

Oracle Database 9i silent installation chapter

Related Posts

ORACLE database structure diagram

Related Posts

Oracle Database Optimization of the basic ideas

Related Posts