2009/07

Logical backup case: windows platform guide data oracle logical backup scripts, backup automatically compressed rar

kods.net — Fri, 31 Jul 2009 20:40:02+0000

This is the server from time to time in the backup scripts used by a total of two documents auto_bakcup_oracle.bat, parfile.txt the contents of the paper is as follows:

auto_backup_oracle.bat Content:

rn

rn rn rn rn

@echo off
rem .
rem .
rem ======================================================================
rem .
rem 这是一个运用Oracle的导出工具export导数据的脚本，有以下特征：
rem 1、灵活，可根据参数文件，自由定制导出选项。
rem 2、方便，以导出时间命名文件，且导出后自动压缩处理。
rem 3、安全，可以在OS的JOB里，设置自动定时备份。
rem .
rem 注意事项：
rem 经测试发现Windows2003取日期字符串的实现方式与Windows2000不同。
rem 若在Windows2000下，日期字符串为：%date:~4,4%%date:~9,2%%date:~12,2%
rem .
rem 陈楠 2008-05-15
rem .
rem ======================================================================
rem .
rem .


REM 取日期字符串,适用于windows2003
set mydate=%date:~0,4%%date:~5,2%%date:~8,2%

REM 如果小时数〈10 ，则前面补0
if "%time:~0,1%"==" " (set mytime=0%time:~1,1%%time:~3,2%%time:~6,2%) else (set mytime=%time:~0,2%%time:~3,2%%time:~6,2%)

REM 取日期时间命名文件
set filename=%mydate%%mytime%


REM 根据parfile.txt 参数文件导出数据 ...
exp parfile=parfile.txt file=%filename% log=%filename%


REM 用rar压缩后，删除原文件
"C:\Program Files\WinRAR\rar" a -df %filename% %filename%.*

echo on

rn
Export Parameter File parfile.txt:

rn rn rn rn

userid=chennan/chennan@cwtest
owner=cwgladm
#tables=bas_corp
CONSISTENT=y

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

How to solve session lock?

kods.net — Fri, 31 Jul 2009 17:14:40+0000

1> to find out it;

SELECT sid, serial #, action
FROM V $ SESSION
WHERE STATUS = 'ACTIVE'
AND LOCKWAIT IS NOT NULL

2> to kill session;

alter system kill session 'sid, serial #';

OEM setup 2009-04-07 18:23:58
Detailed: database name. Instance name. ORACLE_SID. Database name 2009-04-06 07:22:20
LISTENER and EZCONNECT 2009-04-01 21:42:20
Oracle deadlock related 2009-03-30 16:46:54
An example of the use of TKPROF 2009-02-26 01:38:19

Timesten 11g with Oracle released In-Memory Database Cache 11g

kods.net — Fri, 31 Jul 2009 06:50:15+0000

Oracle released timesten 11g with In-Memory Database Cache 11g new version of the original version the following elements:

Oracle Announces Oracle ® TimesTen In-Memory Database 11g and Oracle In-Memory Database Cache 11g

Latest Release Enhances Performance, Scalability, Availability and Integration with Oracle Database

Redwood Shores, CA - July 28, 2009

News Facts

Today, Oracle announced the availability of Oracle ® TimesTen In-Memory Database 11g and Oracle In-Memory Database Cache 11g, the database caching option for Oracle Database 11g.

This latest release features major enhancements including performance and scale-out through the Database Cache Grid; support for PL / SQL, Oracle Call Interface (OCI), and Pro * C embedded SQL Programming language; and rock solid reliability and high availability through automated failover via integration with Oracle Clusterware and Oracle Data Guard.

These new product capabilities further enable enterprises to benefit from the extreme performance that in-memory database caching in the middle-tier offers.

Oracle In-Memory Database Cache is fully integrated with the Oracle infrastructure software stack including Oracle Database, Oracle Real Application Clusters, Oracle Enterprise Manager, Oracle SQL Developer, and components of Oracle Fusion Middleware.

Oracle TimesTen In-Memory Database is a stand-alone in-memory relational database with full persistence and recoverability. Oracle In-Memory Database Cache is the pre-integrated SQL cache database option for the Oracle Database. Both products are designed for deployment in the middle-tier.

Improved Performance for Mission-Critical Applications

Oracle In-Memory Database Cache provides applications with extreme performance by caching data in memory for fast and consistent response times. The in-memory caches are transactional read-write caches with full persistence, recoverability, and automatic data synchronization with the Oracle Database.

New features include:

Database Cache Grid: enables incremental scale-out of in-memory database caching with online addition / removal of cache grid nodes without service interruption. It also provides data location transparency so that applications need not know where the data resides; each cache grid node can access data from its local memory, from another cache grid node, or from the backend Oracle Database. Database Cache Grid provides transactional consistency across cache nodes with high availability and data synchronization with the backend Oracle database.

PL / SQL, OCI, and Pro * C Support: simplifies the use of the Oracle In-Memory Database Cache by existing Oracle Database applications to improve application response time and throughput.

Cross-Tier High Availability: delivers improved high availability out-of-the-box, with automated failover of the in-memory databases and applications. Integration with Oracle Clusterware and Oracle Data Guard achieves near zero-downtime with virtually no data loss in case of failure.

Supporting Quotes

"As a pioneer in electronic trading solutions, NYFIX has helped transform the global trading environment through business and technology innovations," said Don Henderson, Chief Technology Officer, NYFIX. "One of the key technology drivers in today's equities trading market is low latency. Our clients want the ability to place orders and get immediate responses so they can keep pace with fast-moving market opportunities. Using the Oracle In-Memory Database Cache and Oracle Database to help run our Millennium dark pool, we are able to provide our clients with the fast and reliable response times they demand. "

"Our customers demand fast and reliable service with little lag time," said Tomohiro Takeda, Director, Strategy & Planning, Passenger Service Systems, IT Service & Planning, Japan Airlines. "The combination of Oracle Database with the Oracle In-Memory Database Cache option has allowed us to exponentially increase our number of server transactions for online search of domestic flight fare to over ten times faster and has enabled us to deliver on the fast and reliable service our customers have come to expect from Japan Airlines. "

"Oracle continues to add important enhancements to the Oracle TimesTen product, including tighter integration with its flagship Oracle Database. The Oracle In-Memory Database Cache option will enable Oracle Database users to realize the benefits of database caching technology with minimal changes to the source code of their existing applications. Database caching is used to deliver improved performance while ensuring high availability and reliability. These are critical elements for industries with real-time data management requirements, "said Carl Olofson, Research Vice President, IDC.

"The demand for high-performance applications continues to expand across industries," said Rex Wang, Vice President Product Marketing, Oracle. "The tight integration between Oracle Database 11g and the Oracle In-Memory Database Cache option allows customers to obtain significant performance boosts to existing applications with minimal changes to the applications. And, the new Database Cache Grid capabilities enable customers to scale their middle-tier platform incrementally, as needed by their performance and capacity requirements. "

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
Oracle XDB relax resolve port conflict of 8080 2009-03-13 16:47:42
An example of the use of TKPROF 2009-02-26 01:38:19

Table undotbs1 damage on the solution space

kods.net — Fri, 31 Jul 2009 04:40:03+0000

Applications encountered in the production of the following questions.
rn
Environment: OS redflag Server 5
rn
DB Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 RAC
rn

rn
In two tips for the database log
rn
Errors in file / opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE data block corrupted (file No. 2, Block No. 104420)
ORA-01110: data file 2: '+ DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:23 2009
Errors in file / opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE data block corrupted (file No. 2, Block No. 104420)
ORA-01110: data file 2: '+ DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:24 2009
Errors in file / opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE data block corrupted (file No. 2, Block No. 104420)
ORA-01110: data file 2: '+ DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:25 2009
Errors in file / opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE data block corrupted (file No. 2, Block No. 104420)
ORA-01110: data file 2: '+ DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:25 2009
Errors in file / opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE data block corrupted (file No. 2, Block No. 104420)
ORA-01110: data file 2: '+ DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:30 2009
Errors in file / opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
rn

rn

rn
Landing inspection, marked with the following statement to view the use of space.
rn
SQL> select f.tablespace_name tablespace_name, round ((d.sumbytes/1024/1024/1024), 2) total_g,
2 round (f.sumbytes/1024/1024/1024, 2) free_g,
round ((d.sumbytes-f.sumbytes) / 1024/1024/1024, 2) used_g,
3 4 round ((d.sumbytes-f.sumbytes) * 100/d.sumbytes, 2) used_percent
5 from (select tablespace_name, sum (bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name, sum (bytes) sumbytes from dba_data_files group by tablesp 6 ace_name) d
7 where f.tablespace_name = d.tablespace_name
8 order by d.tablespace_name;
rn
TABLESPACE_NAME TOTAL_G FREE_G USED_G USED_PERCENT
------------------------------ ---------- ---------- ---------- ------------
INDEX_DATA 7.81 7.81 0 0
MONTH_SPACE01 7.81 7.78 .03 .4
MONTH_SPACE02 7.81 7.78 .03 .38
MONTH_SPACE03 7.81 7.81 0 .05
MONTH_SPACE04 7.81 7.81 0 .05
PCC_LS_YWXX_SPACE01 7.81 7.76 .05 .69
PCC_LS_YWXX_SPACE02 7.81 7.76 .05 .65
PCC_LS_YWXX_SPACE03 7.81 7.81 .01 .09
PCC_LS_YWXX_SPACE04 7.81 7.79 .02 .26
POST_KF 31.25 27.66 3.59 11.5
SYSAUX 2.27 1.17 1.1 48.42
rn
TABLESPACE_NAME TOTAL_G FREE_G USED_G USED_PERCENT
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 1 .51 .49 48.99
TS_PCC_LS_YWXX 15.63 15.62 0 0
TS_PCC_REC_AUDIO 15.63 15.62 0 0
UNDOTBS1 4 .01 3.99 99.79
UNDOTBS2 4 3.99 .01 .33
USERS .49 .49 0 .19
VADIO_DATA 7.81 7.81 0 0
rn
18 rows selected.
rn

rn
Found space UNDOTBS1 table space is used up, can not expand.
rn
Obviously there have been large affairs occupy a lot of UNDO tablespace. Oracle's AUM (Auto Undo Management) from birth often only been extended, does not shrink (shrink) the situation (usually we can provide sufficient size of UNDO tablespace, and then cancel the automatic extended attributes).
rn

rn
Now we can use the following steps to recovery UNDO space:
rn
(1) Verify that the file
rn
SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';
rn
FILE_NAME
-------------------------------------------------- --------------------------------------------------
BYTES/1024/1024
---------------
+ DATA1/postdb/datafile/undotbs1.260.666122861
4096
rn
(2) to check the status of UNDO Segment
SQL> select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize;
rn
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- --------- ------------- ----------
15 .000297546 .002128601 10
0 .000358582 .000358582 0
19 .001091003 .013786316 4
12 1.001091003 .003044128 1
13 .001091003 .004997253 3
14 .001091003 .002067566 1
16 .001091003 .002067566 3
11 .001091003 .003044128 4
18 .001091003 .002067566 1
17 .002067566 .006950378 1
20 .002067566 .002067566 3
rn
11 rows selected.
rn
(3) create a new UNDO tablespace
SQL> create undo tablespace undotbs2 datafile '+ DATA1' size 4000M ;;

Tablespace created.
rn
(4) switching UNDO table space for the new UNDO tablespace
SQL> alter system set undo_tablespace = undotbs2 scope = both;

System altered.
rn
(5) wait for all the original UNDO tablespace UNDO SEGMENT OFFLINE
rn

SQL> select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize;
rn
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------- ------ ---------------------- ----------
15 0 ONLINE .000297546 .002128601 10
0 0 ONLINE .000358582 .000358582 0
19 0 ONLINE .001091003 .013786316 4
12 1 ONLINE .001091003 .003044128 1
13 0 ONLINE .001091003 .004997253 3
14 0 ONLINE .001091003 .002067566 1
16 0 ONLINE .001091003 .002067566 3
11 0 ONLINE .001091003 .003044128 4
18 0 ONLINE .001091003 .002067566 1
17 0 ONLINE .002067566 .006950378 1
20 0 ONLINE .002067566 .002067566 3
rn
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------- ------ ---------------------- ----------
6 0 PENDING OFFLINE 2.9671936 2.9671936 0

12 rows selected.
See:
SQL> select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize;
rn
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------- ------ ---------------------- ----------
15 0 ONLINE .000297546 .002128601 10
0 0 ONLINE .000358582 .000358582 0
19 0 ONLINE .001091003 .013786316 4
12 1 ONLINE .001091003 .003044128 1
13 0 ONLINE .001091003 .004997253 3
14 0 ONLINE .001091003 .002067566 1
16 0 ONLINE .001091003 .002067566 3
11 0 ONLINE .001091003 .003044128 4
18 0 ONLINE .001091003 .002067566 1
17 0 ONLINE .002067566 .006950378 1
20 0 ONLINE .002067566 .002067566 3
rn
11 rows selected.
rn

rn
(6) to delete the original UNDO tablespace
SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

Elapsed: 00:00:03.13
rn

rn
(7) to check the situation as a result of the space I use the ASM management, 10gR2 can use tools provided by letter to asmcmd View space occupancy.
[oracle @ danaly ~] $ export ORACLE_SID = + ASM
[oracle @ danaly ~] $ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
ASMCMD> exit

Space have been released.
rn

rn

rn

rn

rn

rn

rn

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

10Grac the ORA-600 [kclfusion_1] lead to node restart

kods.net — Fri, 31 Jul 2009 02:25:02+0000

Oracle 10.2.0.3 RAC the ORA-600 [kclfusion_1] bug

Morning, suddenly received a monitoring report to the police, was found in the environment rac dang a node, and

HP Itanium platform, database oracle 10.2.0.3 rac

Check the alert log and found that error is
ORA-00600: internal error code, arguments: [kclfusion_1], [], [], [], [], [], [], []

Check through metalink and found a bug, need to hit this patch to fix 4637902.

rn rn rn

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 agent, IBM software, redhat good price

kods.net — Thu, 30 Jul 2009 20:43:45+0000

Larry Beijing Jingcheng Technology Development Co., Ltd. (hereinafter referred to Larry technology) is that the community and businesses to provide comprehensive information technology solutions for the core business of high-tech enterprises. At present, there are two parts of the business - enterprise IT systems integration and sales of hardware and software products and systems research and development applications.

Larry Beijing Jingcheng Technology Development Co., Ltd. was founded in 2003, five million yuan registered capital. Larry Technology in Beijing in 2007 as a double soft certification companies, and in 2009 won the three-tier qualification system.

Company Philosophy: adhere to the technologies and services for the center to the user demand, technology and market-oriented combination provides users with a high standard of products, high-quality services.

As a professional application software and systems service providers, Larry and many of the world's companies are well-known computer manufacturers to form a multi-faceted cooperation, has been IBM, ORACLE, REDHAT, SYMANTEC, MICROSOFT, such as proxy from multiple vendors.

ORACLE enterprise-class software partners

IBM partners in clear water and blue sky

Gold Certified Partner redhat

Please contact us if there is demand for:

Larry Beijing Jingcheng Technology Development Co., Ltd.

Sales Department Tel :010 - 64981188

Company URL: www.rozsoft.com

Install oracle on Linux 2009-08-20 06:00:09
oracle archive is full, delete some can not be re-filed after the dissemination of data stream 2009-07-30 05:31:45
DBA hands of Rational Data Architect 2009-06-18 10:34:15
Oracle is willing to buy 7.4 billion US dollars from Sun 2009-04-21 10:20:46

oracle background processes

kods.net — Thu, 30 Jul 2009 17:19:07+0000

The function of the background process for a brief introduction:
DBWR process: the process of implementation of the data files will be written into the buffer zone, buffer storage area is responsible for the management of an ORACLE background processes. When the buffer of a buffer is modified, it is marked as "dirty", DBWR will be the main task is to "dirty" disk write buffer to the buffer zone to maintain "clean." As the buffer storage areas fill in the database buffer dirty or user process, not by a decrease in the number of the buffer zone. When not used in the buffer zone fell to very few, so that the user process block read from disk into memory storage area can not be found when the buffer is not used, DBWR will manage the buffer storage area, so that users can not use the total process of buffer.

ORACLE using LRU (LEAST RECENTLY USED) algorithm (least recently used algorithm) to maintain the data in memory is the most recently used block, so that I / O minimum. Foreshadowed in the following circumstances DBWR write to the disk buffer dirty:

When a server process will be a buffer zone into the "dirty" table, the expression of the critical length dirty, the service will notify the DBWR process to write. The critical parameter is the length of DB-BLOCK-WRITE-BATCH half the value.

When a server process in the LRU table to find DB-BLOCK-MAX-SCAN-CNT buffer, no buffer available unused, it ceased to find and notify the DBWR to write. Emergence of overtime (three seconds each), DBWR will inform itself. When the check point, LGWR will notify DBWR. The former two cases, DBWR dirty table will be written into the disk blocks, each block can be written to initialize the parameters from DB-BLOCK-WRITE-BATCH by specified. If you do not dirty the table parameter specifies the number of buffer blocks, DBWR from LUR table to find a dirty buffer.

If not DBWR activity in three seconds, while there is overtime. In this case DBWR to specify the number of LRU table to find the buffer zone, will have to find any dirty buffers written to disk. Whenever there is overtime, DBWR buffer to find a new group. Find each DBWR buffer by the number of sleep parameters for the DB-BLOCK-WRITE-BATCH twice the value. Empty if the database is running, DBWR final storage area will be written into the disk buffer.

In the event of check points, LGWR modified to designate a buffer zone required to be written to disk. DBWR will write to the disk buffer specified.

In some platforms, an example may have multiple DBWR. In such cases, some can write a disk block, and some other block can be written into the disk. DB-WRITERS control parameters of the process of the number of DBWR.

LGWR process: the process will be written into the log buffer of a disk log file, which is responsible for managing a log buffer ORACLE background processes. LGWR process will write to the disk since the last full year of output log, LGWR output:

When the user when the process of submitting a written record of a submission.

Every three seconds to the log output buffer.

When the log buffer of 1 / 3 full when the log output buffer.

DBWR buffer zone will be changed when the disk will be written into the log output buffer.

LGWR process synchronously written to mirror the activities of online log file groups. If the group a file is deleted or unavailable, LGWR can continue to write other documents of the group.

Log buffer is a buffer cycle. When the log buffer LGWR will write to the log of the log file, the server process may be a new item into the log buffer to the log. LGWR usually write very quickly to ensure that there will always be room for the log buffer to log into the new item.

Note: Sometimes when the need for more when the log buffer, LWGR services in a log before items will be written, and these only when the log of affairs submitted at a later date after the permanent.

ORACLE submitted using the fast mechanism, when a user issued COMMIT statement, a COMMIT log record buffer Add immediately, but the corresponding change in the data buffer is delayed until more effective when they are written into the data files. When submitted to a Panel, they were assigned to a system changes its (SCN), with the transaction log of a

Effect recorded in the log. Since the SCN recorded in the log, resulting in parallel server configuration options, the recovery operations can take place in parallel.

CKPT process: the process in the checkpoint occurs, all data on the title of the document be amended to instruct the checkpoint. In normal circumstances, the task of implementation by the LGWR. However, if the checkpoint is clear that reducing system performance, the CKPT process can run, the LGWR process from the implementation of the original checkpoint separating the work, the realization by the CKPT process. For many applications, CKPT process is unnecessary. Database only when there are many data files, LGWR when the checkpoint is obviously able to reduce the performance to run CKPT. CKPT process does not write to disk blocks, the work is completed by the DBWR.

CHECKPOINT-PROCESS initialization parameter control to enable the CKPT process or not. When the default FALSE, which is not so.

SMON process: the process of implementation of examples of examples of when to start recovery, is also responsible for cleaning up the temporary segment is no longer used. With the Parallel Server option in the circumstances, SMON failure of CPU or to resume examples examples. SMON process being called up regularly to check whether there is a need, or other process of discovery can be called when necessary.

PMON process: the process of failure of the user when the process of implementation of the process of recovery, is responsible for clean-up inside the storage area and the release of the resources used in the process. For example: it is to reset the active transaction table, and the release of the blockade, the failure of the process ID from the activities of the process of removal of the table. PMON also periodically check the scheduling process (DISPATCHER) and the server process, and if dead, then restart (not including the intention to delete the process).

PMON regularly being called up to check whether there is a need, or other process of discovery can be called when necessary.

RECO process: the process is distributed in the option of a process used to automatically resolve the distributed transaction failures. A node RECO background process automatically connect to contain unresolved distributed transaction in the other database, RECO automatically resolve all pending matters. Have been processed in any of the corresponding pending transaction row from the database for each table by deleting the hoisting of affairs.

When a database server of the RECO background process of attempting to set up the same remote server communication, if the remote server is not available or when the network connection could not be established, RECO automatically after a time interval to connect again.

RECO background process is allowed only if the distributed transaction systems appear, and DISTRIBUTED - TRANSACTIONS parameter is greater than 0.

ARCH process: the process will have to fill up the online log files are copied to the designated storage devices. When the log is to use ARCHIVELOG mode and automatic archiving only exist when the ARCH process.

LCKn process: it is in a parallel server environment options can be up to 10 process (LCK0, LCK1 ... ..., LCK9), for example between the blockade.

Dnnn process (scheduling process): The process allows the user to process a limited share of the server process (SERVER PROCESS). There is no scheduling process, each user process requires a dedicated server process (DEDICATEDSERVER PROCESS). For more clues to the server (MULTI-THREADED SERVER) process to support multiple users. If in the system with a large number of users, and more clues to the server can support a large number of users, particularly in the client server environment _.

Examples in a database can create multiple scheduling process. Network protocol for each of at least the establishment of a scheduling process. Database Administrator in accordance with the process of the operating system can be connected to each restriction on the number of the scheduler decided to start a few of the best in the run-time instance of scheduling the process to increase or delete. Need more clues to the server SQL * NET version 2 or later versions. Clues in a multi-server configuration, a network process to wait for the receiver client application connection requests, and each sent to a dispatching process. If the client application can not be connected to a scheduling

Process, the network receiver process will start a dedicated server process. The network receiver process is not an integral part of ORACLE instance, it is to deal with the network ORACLE process. In the example starts, the network receiver is opened, for the user to connect to ORACLE to establish a communication path, and then a scheduling process for each connection request to the scheduling process to give it the address of the receiver. When a user process even

Access request, the network analysis of the receiver process the request and decide whether or not the user can use a scheduling process. If so, the process of the network to return to the receiver's address scheduling process, the process after the user to connect directly to the scheduling process. Some users can not process scheduling process communications (if you are using SQL * NET users of previous versions), the network receiver process so users can not connect to a scheduling process. In this case, the network receiver to establish a dedicated server process, to establish a suitable connection.

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

perl export data

kods.net — Thu, 30 Jul 2009 06:30:14+0000

Wrote a user to export the data in the following table tool
rn

rn
Use the following
rn
. / expdp.pl
rn
hbjxzl.cublog.cn auxiliary Data Utility Version 1.0.0
rn
This script will auxiliary data and struct.
-h - Give help screen
-E - for export dict
-D export - for export dict's data
-T drop - for export table's data
Examples:
auxiliary.pl-h / H
auxiliary.pl-e / E
auxiliary.pl-d / D
auxiliary.pl-t / T
rn
Also need a configuration file
rn

rn
[oracle @ localhost DBD] $ cat exp.dict
dsg.test # owner here is the user may also be users. the existence of the forms.
rn

rn

rn rn rn rn

#!/usr/bin/perl 

# dbusers - manage MySQL user table

use ExtUtils::testlib;

use Getopt::Long;
use Config;
use Getopt::Std;
use Oraperl;
$dbname="dsg";
$dbuser="dsg";
$dbpasswd="dsg";
format HEADER =

 hbjxzl.cublog.cn auxiliary Data Utility Version 1.0.0

 This script will auxiliary data and struct. 
 -h - Give help screen
 -E - for export dict 
 -D export - for export dict's data
 -T drop - for export table's data
 Examples:
   auxiliary.pl -h/H
   auxiliary.pl -e/E
   auxiliary.pl -d/D
   auxiliary.pl -t/T
.

sub do_help {
  # Give help screen and exit

  $~ = "HEADER";
  write;
  exit();
}

sub replace_null_to_{
  $str=shift;
  if($str){
   $str =~ s/ /_/g;
  }
  return $str;
}

sub replace_enter_to_{
  $str=shift;
  if($str){
   $str =~ s/\n//g;
  }
  return $str;
}

sub write_file{
  $put_name=shift;
  $put_trunc_name=shift;
  $put_drop_name=shift;
  open(OUTPUT, "> $put_name ")
    or die "Couldn't open $put_name for write: $!\n";
  if ($put_trunc_name){
    open(TRUNCATE_OUTPUT, "> $put_trunc_name ")
      or die "Couldn't open $put_trunc_name for write: $!\n";
  }
  if ($put_drop_name){
    open(DROP_OUTPUT, "> $put_drop_name ")
      or die "Couldn't open $put_drop_name for write: $!\n";
  }
}
sub close_write_file{
  close(OUTPUT);
  close(TRUNCATE_OUTPUT);
  close(DROP_OUTPUT);
}

sub read_file{
  $get_name=shift;
  open(INPUT, "< $get_name ")
    or die "Couldn't open $put_name for read: $!\n";
}

sub close_read_file{
  close(INPUT);
}

sub connect_db{
    my $dbname=shift;
    my $dbuser=shift;
    my $dbpasswd=shift;
    #print " test dbname is ",$dbname," user is ", $dbuser," passwd is ", $dbpasswd,"\n";

    $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser,$dbpasswd,{ RaiseError=>1 });
    $dbh->{LongReadLen} = 512 * 1024;
    return $dbh;
}

sub dis_connect_db{
    $dbh->disconnect;
}
sub print_array{
   my @cfg_lines=split(" ",$_);
   foreach (@cfg_lines){
      print $_,"\t";
   }
   print "\n";
   return @cfg_lines;
}

sub obj_sql{
  my $sql_code=shift;
  my $get_sql="";
  if($sql_code ==1){
    $get_sql="select owner,table_name,column_name,data_type,data_length from dba_tab_columns where owner = ? and table_name = ? "; 
 #for all table's column

  }elsif($sql_code==2){
    $get_sql="select owner,table_name from dba_tables where owner like ? and cluster_name is null and iot_type is null";
  }elsif($sql_code==3){
  }
  return $get_sql;
}

sub tab_col_sql{
  my $exp_owner=shift;
  my $exp_tab =shift;
  my $exp_dict_file=lc shift;
  my $select_col="";
  #print ">>test exp_owner is ",uc $exp_owner,"\n";

  #print ">>test exp_tab is ",$exp_tab,"\n";

  #print ">>test exp_dict_file is ",$exp_dict_file,"\n";

  open(INPUT_COL,"< $exp_dict_file") or die "Couldn't open $exp_dict_file for read: $!\n";
    while(){
      chomp;
      @line_raw=split(" ",$_);
      $exp_tab =~ s/\$/\_/g;
      $exp_owner =~ s/\$/\_/g;
      #print "exp_tab is ",$exp_tab,"\n";

      $match_var=$line_raw[0];
      $match_var =~ s/\$/\_/g;
      if( $match_var =~ m/\"$exp_owner\"\.\"$exp_tab\"/ ){
         $select_col=join(",",$select_col,$line_raw[1]);
      # print "Test select col ",$select_col,"\n";
      }
    }
    $select_col=substr($select_col,1,length($select_col)-1);
   #print "test select col ",$select_col,"\n";
  close(INPUT_COL);
  return $select_col;
}

sub tab_dict{
    my $dbh = shift;
    my $tab_owner= shift;
    my $tab_name = shift;
    my $obj_type= shift;
    my $get_obj_sql;
    $put_file_name=lc join(".",$tab_owner,$tab_name,"dmp");
    if($obj_type==1){
      $get_obj_sql=&obj_sql($obj_type);
    }elsif($obj_type==2){
      my $read_file=join(".",$tab_owner,"dict");
      $get_obj_sql=&tab_col_sql(uc $tab_owner,uc $tab_name,$read_file);
      $get_obj_sql=join(" ","select",$get_obj_sql,"from",$tab_owner);
      $get_obj_sql=join(".",$get_obj_sql,$tab_name);
      #print $get_obj_sql,"\n";
    }
    my $sth = $dbh->prepare($get_obj_sql);
    if($obj_type==1){
       $sth->execute($tab_owner,$tab_name);
    }elsif($obj_type==2){
       $sth->execute;
    }
    $tab_text="";
    
    if($obj_type==2){
      open(OUT_PUT, "> $put_file_name ")
        or die "Couldn't open $put_file_name for write: $!\n";
    }
    while (my @row= $sth->fetchrow){
       my $arr_len=scalar @row;
       if($obj_type==2){
           for($i = 0 ; $i < $arr_len ; $i ++){
             print OUT_PUT $row[$i],"\t";
           }
         print OUT_PUT "\n";
       }else{
         $tab_text=join("\"",$tab_text,$row[0]);
         $tab_text=join("\".\"",$tab_text,$row[1]);
         $tab_text=join("\"\t\"",$tab_text,$row[2]);
         $tab_text=join("\"\t",$tab_text,$row[3]);
         if ($row[3] =~ m/NUMBER/ || $row[3] =~ m/CHAR/ || $row[3] =~ m/ROW/){
           $tab_text=join("(",$tab_text,$row[4]);
           $tab_text=join(")",$tab_text,"");
         }
             $tab_text=join("\n",$tab_text,"");
       }
     }
     $sth->finish();
    if($obj_type==1){
      print OUTPUT "="x60,"\n";
      print OUTPUT $tab_text;
    }elsif($obj_type==2){
      close(OUT_PUT);
    }
}

sub owner_all_tab{
    my $dbh =shift;
    my $exp_own =shift; 
    my $oper_p =shift; 
    my $get_obj_sql=&obj_sql(2);
    #print "test exp_owner is ",uc $exp_own,"\n";
    #print "test get_obj_sql is ",$get_obj_sql,"\n";
    my $sth = $dbh->prepare($get_obj_sql);
       $sth->execute($exp_own);
    my $exp_process_num=0;
    while (@row= $sth->fetchrow){
       print $row[0],"\t",$row[1]," Please waiting....";
       &tab_dict($dbh,$row[0],$row[1],$oper_p);
       $exp_process_num++;
       print " over \n";
    }
       $sth->finish();
    print "\n";
}

sub oper_from_file{
 $oper_p =shift;
 &connect_db($dbname,$dbuser,$dbpasswd);
  &read_file("exp.dict") or die "Cannot get exp.dict for reading !"; 
    while(){
     chomp;
     if(/^\s*#/ or /^\s*$/){
        next;
      }
     s/\s*#.*//;
     tr/\t //d;
    my @exp_dict= split /\./;
    my $exp_owner=$exp_dict[0];
    my $exp_tab=$exp_dict[1];
    my $exp_dict_file =join(".",$exp_owner,"dict");
    #print "oper is ",$oper_p," exp_owner is ",$exp_owner," exp_dict_file is ",$exp_dict_file,"\n";
     if ($oper_p==1){
       &write_file($exp_dict_file);
       &owner_all_tab($dbh,uc $exp_owner,$oper_p);
       &close_write_file;
     }elsif($oper_p == 2){
       &owner_all_tab($dbh,uc $exp_owner,$oper_p);
     }elsif($oper_p==3){
       #my $put_file_name=join(".",$exp_owner,$exp_tab,"dat");
       #print "exp_owner is $exp_dict[0]\t exp_tab is ",$exp_tab,"\t",$put_file_name,"\n" ,
       #print "Exp table's data!\n";
       &tab_dict($dbh,$exp_owner,$exp_tab,2);
     }
   }
  &close_read_file;
  &dis_connect_db(); 
}

sub do_opts{
  %options=();
  getopts("eEdDtThH",\%options);
  &do_help if ($options{h} or $options{H});
  # like the shell getopt, "d:" means d takes an argument
  if (defined $options{e}){
     &oper_from_file(1);
  }elsif(defined $options{E}){
     &oper_from_file(1);
  }elsif( defined $options{d}){
     &oper_from_file(2);
  }elsif( defined $options{D}){
     &oper_from_file(2);
  }elsif( defined $options{t}){
     &oper_from_file(3);
  }elsif( defined $options{T}){
     &oper_from_file(3);
  }elsif( defined $options{h}){
     $do_help;
  }elsif( defined $options{H}){
     $do_help;
  }else{
    print "test opts Err:\n";
  }
}

$argv=scalar @ARGV; 
#print "test argv is :",$argv,"\n

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 archive is full, delete some can not be re-filed after the dissemination of data stream

kods.net — Thu, 30 Jul 2009 05:31:45+0000

Last night the Treasury found that the target stream archive full, 100%, the archive log oracle error is reported directly on the deletion of part of the documents in the archives,

ls -rht|head -n 100|xargs rm -rf

With pl / sql login, the error did not, and never seriously.

This morning one of my colleagues say I do not update data, landing data terminal is a look yesterday, it is estimated that the problem of stream synchronization,

Quickly open the computer, remote access to the room, open oracle enterprise console manage, view the capture state

select status from dba_capture

enable the state are normal, look at the state apply

select status from dba_apply

enable a state of normal

Look at the propagation, there are reported 16 errors, see the error content is archive err, on the scheduling in the resumption of propagation, then restart the capture

In terms of propagation, the error is still the number 16, but also to increase the number of queue

Again apply, has been idle state, apply a few had not been increased, or apply no restart, apply a few have been 0

Carefully considered unreasonable ah, into the queue has been to increase the number, but do not apply the data received, stream where mass is definitely the problem or propagation

Queue will not be the problem? To restart the capture queue here, another capture, then apply to restart the queue, open apply, apply a few or 0, then it would not be a question of queue

And then look back propagation, although a few have been to increase the queue, but they are not spread to apply

Helpless, living dead horse Madang medicine, and had no choice but to stop capture, try to initialize propagation, and even if there is data loss do to remedy the damage, it is important to let the new data over the past

BEGIN
   DBMS_CAPTURE_ADM.STOP_CAPTURE(CAPTURE_NAME => 'CAPTURE_MAIN');
   DBMS_PROPAGATION_ADM.STOP_PROPAGATION(

PROPAGATION_NAME=>'PROPAGATION_MAIN',

                  FORCE=>TRUE);
   DBMS_PROPAGATION_ADM.START_PROPAGATION(

                  PROPAGATION_NAME=>'PROPAGATION_MAIN');
END;

Manually open the capture, see propagation, well, no errors, or apply a look at, apply the number has been increasing, and finally normal

The new data, such as mass over the last after the next data on both sides, there is no loss of data, the exception handling on the end of the

PS:

Since the archive is full, resulting in the dissemination of data stream can not be normal

After filing to remove part of the normal work of the oracle are, stream will not automatically resume the

stream is really very fragile and can not toss ah

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

oracle10g fine-grained auditing

kods.net — Thu, 30 Jul 2009 05:25:46+0000

Test the success of their own examples:

sqlplus / nolog
conn / as sysdba

begin
dbms_fga.add_policy (
object_schema => 'system', # # # # program name
object_name => 'nbstutb', # # # # Table name
policy_name => 'nbstu', # # # # Since the definition of the strategy
statement_types => 'SELECT, DELETE, INSERT, UPDATE'
);
end;
/

Removal of test data: removal of fine-grained audit of the audit records:

sqlplus / nolog

conn / as sysdba

delete sys.fga_log $;
commit;

ORACLE 10g Fine-grained auditing

ORACLE 10g In fine-grained auditing (FGA) has made a lot of expansion. Fine-grained audit 9i only supports the SELECT statement, and 10g DML provides support (support for Insert, Update and Delete, Merge is not an independent statement of the DML).

10g Not only the fine-grained auditing to audit a related field can be audited on a number of fields. Audit_column_opts can be set up in dbms_fga.all_columns or dbms_fga.any_columns.

Audit logs can be SQL text and bind variables into the LO B field. If the cost of the audit is too large, you can shut down the information written into the LOB.

The following is a 10G DBMS_FGA.ADD_POLICY example, one can see the fine-grained auditing and 9i are very different:

dbms_fga.add_policy (object_schema => 'PIET', object_name => 'EMP',

policy_name => 'MYPOLICY1', audit_condition => NULL,

audit_column => 'SALARY, COMMISSION_PCT',

audit_column_opts => DBMS_FGA.ALL_COLUMNS,

audit_trail => DBMS_FGA.DB_EXTENDED,

statement_types => 'INSERT, UPDATE');

An example the following INSERT presentation of the audit process:

SQL> connect piet / piet

Connected.

SQL> CREATE TABLE EMP (

EMPNO NUMBER (4) NOT NULL,

ENAME VARCHAR2 (10),

JOB VARCHAR2 (9),

MGR NUMBER (4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),

HIREDATE DATE,

SAL NUMBER (7,2),

COMM NUMBER (7,2),

DEPTNO NUMBER (2) NOT NULL,

CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

Table created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,'17-NOV-81 ', 5000, NU;

1 row created.

SQL> grant all on emp to miller;

Grant succeeded.

SQL> conn system / manager

SQL> execute sys.DBMS_FGA.ADD_POLICY (--

object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1', --

audit_condition => 'sal <1000', --

audit_column => 'comm', --

enable => TRUE, --

statement_types => 'INSERT');

PL / SQL procedure successfully completed.

SQL> select * from DBA_AUDIT_POLICY_COLUMNS;

OBJECT_SCHEMA OBJECT_NAME

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

POLICY_NAME POLICY_COLUMN

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

PIET EMP

MYPOLICY1 COMM

SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,

POLICY_COLUMN, ENABLED, SEL, INS, UPD , DEL

from DBA_AUDIT_POLICIES;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT POLICY_COLUMN ENA SEL INS UPD DEL

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

PIET EMP MYPOLICY1 sal <1000 COMM YES NO YES NO NO

SQL> conn miller / miller

Connected.

The following example is inserted into a sal a record of less than 1000, in line with audit requirements:

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10);

1 row created.

Insert the following example of a sal more than 1000 records, do not meet the conditions of the audit:

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (3000, 'TOM', 20000, 1000, 20);

1 row created.

Insert the following statement does not contain a field audit, non-audit:

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, DEPTNO)

VALUES (1111, 'RAMA', 98,30);

1 row created.

SQL> commit;

Commit complete.

Click below to verify the results of the audit:

conn system / manager

SQL> select DB_USER, OBJECT_SCHEMA "SCHEMA", OBJECT_NAME,

POLICY_NAME, SQL_TEXT

from dba_fga_audit_trail;

DB_USER SCHEMA OBJECT POLICY_NAME

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

SQL_TEXT

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

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10)

The following examples demonstrate that contains the function (sysdate, uid, user, round, etc.) of the audit:

execute dbms_fga.drop_policy (object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1');

SQL> execute sys.DBMS_FGA.ADD_POLICY (--

object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1', --

audit_condition => 'round (sal, -2)> = 3000', --

audit_column => 'comm', --

enable => TRUE, --

statement_types => 'INSERT');

PL / SQL procedure successfully completed.

SQL> connect miller / miller

Connected.

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (5000, 'RUDY', 2979, 15, 10);

1 row created.

SQL> commit;

Commit complete.

Now to verify what the audit findings:

conn system / manager

SQL> select DB_USER, OBJECT_SCHEMA "SCHEMA", OBJECT_NAME,

POLICY_NAME, SQL_TEXT

from dba_fga_audit_trail;

DB_USER SCHEMA OBJECT POLICY_NAME

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

SQL_TEXT

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

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10)

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (5000, 'RUDY', 2979, 15, 10)

The following is an audit of the use of user functions:

execute dbms_fga.drop_policy (object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1');

SQL> execute sys.DBMS_FGA.ADD_POLICY (--

object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1', --

audit_condition => 'ename = USER', --

audit_column => 'comm', --

enable => TRUE, --

statement_types => 'INSERT');

PL / SQL procedure successfully completed.

SQL> connect miller / miller

Connected.

SQL> conn piet / piet

Connected.

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (89, 'MILLER', 800, 1000, 10);

1 row created.

SQL> commit;

Commit complete.

SQL> conn miller / miller

Connected.

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (69, 'MILLER', 700, 700, 10);

1 row created.

SQL> commit;

Commit complete.

Just to test the audit findings:

con system / manager

SQL> select DB_USER, OBJECT_SCHEMA "SCHEMA", OBJECT_NAME,

POLICY_NAME, SQL_TEXT

from dba_fga_audit_trail;

DB_USER SCHEMA OBJECT POLICY_NAME

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

SQL_TEXT

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

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10)

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (5000, 'RUDY', 2979, 15, 10)

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (69, 'MILLER', 800, 1000, 10)

. . . . . . . . . . . . . . . . . . . .

dbms_fga.add_policy (
object_schema => 'PIET',
object_name => 'EMP',
policy_name => 'MYPOLICY1',
audit_condition => NULL,
audit_column => 'SALARY, COMMISSION_PCT',
audit_column_opts => DBMS_FGA.ALL_COLUMNS,
audit_trail => DBMS_FGA.DB_EXTENDED,
statement_types => 'INSERT, UPDATE');

FGA strategy (fine-grained auditing)

FGA strategy (fine-grained auditing)

DBMS_FGA: audit has become more concerned about a certain area, and more precise.

Through it, you can know which set out to visit and what line.

DBMS_FGA package has four processes:
Add the use of predicate ADD_POLICY out the audit and audit strategy
DROP_POLICY to delete the audit strategy
Disable DISABLE_POLICY audit strategy, while keeping the table or view associated with the strategy
ENABLE_POLICY opening strategy

Test users usually visit daily itmanager.perfdata table,
FGA therefore can create a strategy for perfdatda.value out any audit of any visit:
begin
dbms_fga.add_policy (
object_schema => 'ITMANAGER',
object_name => 'PERFDATA',
policy_name => 'SAL_SELECT_PERFDATA',
audit_column => 'VALUE'
);
end;

View related include:

Fine-grained audit strategy DBA_FGA_AUDIT_TRAIL audit trail entries
FGA audit strategy to increase GRANT RESOURCE, CONNECT TO BANK IDENTIFIED BY BANK;

CREATE TABLE BANK.ACCOUNTS
(
ACCT_NO NUMBER PRIMARY KEY,
CUST_ID NUMBER NOT NULL,
BALANCE NUMBER (15,2) NULL
);
insert into bank.accounts values (1,1,10000);
insert into bank.accounts values (2,2,20000);
commit;

Begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_Access');
dbms_fga.add_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS');
end;
/

select * from bank.accounts;
select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

Auditors and audit requirements set out in add_policy add audit_column => 'BALANCE'
audit_condition => 'BALANCE> = 11000'
Begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS');

dbms_fga.add_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
audit_column => 'BALANCE',
audit_condition => 'BALANCE> = 11000',
policy_name => 'ACCOUNTS_ACCESS');
end;
/

This is not, Why?
select BALANCE from bank.accounts;
select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

FGA governance strategy to delete the policy, you can use the following statement:
begin
dbms_fga.drop_policy (

object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS'
);
end;
/

Strategy for change, there is no check as used with the solution. To change any of the parameters of the strategy, the strategy must be deleted, and then change the parameters of the use of strategies to add.
Auditors need to temporarily disable the collection - for example, if you want to move leads to a different table table space or table you want to remove clues. You can disable the FGA method according to the strategy are as follows:
begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE
);
end;
/
Is very simple to re-enable enable => TRUE

Presentation when the audit operation and when not to audit the operation of the various situations the state audit SQL statements select balance from accounts; audit. Add the user to choose the strategy set out by the designated audit BALANCE.
select * from accounts; audit. Even if the user is not specified column BALANCE, * also implicitly select it.
select cust_id from accounts where balance <10000; audit. Even if the user is not specified column BALANCE, where clause also implicitly select it.
select cust_id from accounts; not audit. Users have no choice out BALANCE.
select count (*) from accounts; not audit. User does not have explicit or implicit choice of column BALANCE.

Processor module is not only the function of FGA audit trail record of the incident; FGA also can be the implementation process.

Process can perform an action, such as when the user select from the table when a particular e-mail sent to the audit warned, or you can write the audit trail in a different.
This code can be stored in the process of independent package or in the process, the processor module is called strategy.
In fact due to security reasons, it does not need to base itself in the same mode, you may want to deliberately place it in a different mode.
SELECT arise as a result of the process as long as the implementation will be very similar to DML statement to start the trigger, you can also be regarded as the SELECT statement trigger.
The following parameter specifies a processor module will be assigned to the strategy:
handler_schema process has a data name of the software process model handler_module Development Network processor module package can also be used to replace the name of the process name. In this case, the parameters handler_module in the format specified in package.procedure.

FGA data dictionary view
FGA strategy definition DBA_AUDIT_POLICIES is located in the data dictionary view. Table 2 contains the list view in a number of important short description.
Audit trail collection in the SYS-owned table FGA_LOG $ in. SYS-owned for any original form, this form some view in a user-friendly display of information. DBA_FGA_AUDIT_TRAIL the form of a view.
Out is an important SQL_BIND, it specified the use of query the value of bind variables - this is a significant function of the tool to enhance a message.
Another important column is the SCN, when the occurrence of a particular query, it changed its recording system.
This information is used to identify users in a specific time to see what, rather than the current value, it uses flashback query, this query can be displayed in the SCN value specified in the data.

View and FGA
So far I have discussed the application in the form FGA; Let us now look at how to view the use of FGA. Assume that the definition in the ACCOUNTS table VW_ACCOUNTS view as follows:

create view bank.vw_accounts as select * from bank.accounts;

select * from bank.vw_accounts;
select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

If you just want to audit queries on the view instead of the query table, you can establish a strategy for the view itself.
View by name rather than the name of the table passed to the process of packaging parameters dbms_fga.add_policy in object_name, can accomplish this task.
OBJECT_NAME then out of DBA_FGA_AUDIT_TRAIL will show the name of view, and will not appear on the table access to additional records.

Records of other uses in addition to the choice of access to the table, FGA can also be used for certain other situations:
You can use the data warehouse FGA, in order to capture a specific table, view, or materialized views on all statements, which helps plan the index. You do not need to go to V $ SQL view to obtain such information. Even if the SQL statement has exceeded the duration of the V $ SQL, in the FGA audit trail will always provide it.
FGA capture as a result of bind variables, which can help you understand the value of the mode of bind variables, which contribute to the design collection, such as histogram.
Processor module or to audit DBA to send a warning, which helps to track malicious applications.
FGA as a result of SELECT statement can be used as triggers, you may need this kind of functionality at any time to use it.
Conclusion FGA in Oracle Database enables you to support strategy and functions of privacy. Because of the internal audit took place in the database rather than applications, so users no matter what access method (such as SQL * Plus and other tools or applications), an audit of the operation and promised to set up very simple.
Next time I will discuss the technologies, as well as senior FGA in Oracle Database 10g new features, these features make a very strong function of FGA, applicable to all types of audits.

Data dictionary view DBA_AUDIT_POLICIES important OBJECT_SCHEMA out its strategy for the definition of the FGA or OBJECT_NAME view the owner's name or view name POLICY_NAME strategy - for example, ACCOUNTS_ACCESS
Add POLICY_TEXT strategy specified in the audit of the conditions - for example, BALANCE> = 11000
Audit POLICY_COLUMN out - for example, BALANCE
ENABLED if the opening is YES, otherwise NO
PF_SCHEMA processor module has a strategy mode (if it exists)
PF_PACKAGE processor module package name (if exists)
The process of PF_FUNCTION processor module name (if exists)

Data dictionary view columns DBA_FGA_AUDIT_TRAIL important conversation identifier SESSION_ID audit; and V $ SESSION view different TIMESTAMP session identifier at the time of the audit records generated time tag database queries issued DB_USER users USERHOST users OS_USER operating system user to connect the host machine Customers who CLIENT_ID identifier (if the process of packing the call set dbms_session.set_identifier)
External authentication EXT_NAME name customers, such as LDAP users to access the table OBJECT_SCHEMA trigger an audit of the owner of the table OBJECT_NAME the SELECT operation on the table to trigger an audit of the name of the table to trigger the audit strategy POLICY_NAME name (if the definition of a number of table strategy, each strategy will be to insert a record.
In this case, the column shows the line which is inserted by which the strategy. )
SCN recorded in the audit of the Oracle system change number
SQL_TEXT submitted by the user SQL_BIND the SQL statement used by the SQL statements to bind variables (if exists)

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

Summed up the issue of Oracle Character Set

kods.net — Thu, 30 Jul 2009 04:00:12+0000

$ echo $ NLS_LANG

rn
AMERICAN_AMERICA.ZHS16GBK

rn
If the inspection found that the server side and client-side character set inconsistent, please amend the unified server-side with the same character set.

rn
Third, modify the character set oracle

rn
Mentioned above, oracle character set are mutually inclusive relationship. If us7ascii is a subset of zhs16gbk from us7ascii there will be no data to explain zhs16gbk problems, there will be no data loss. All the characters in the utf8 should focus on is the biggest, because it is based on unicode, to preserve double-byte characters (and therefore the storage space occupied by more).

rn
Once the database is created, the database character set theory can not be changed. Therefore, in the design and installation of the early days of considering the use of a character set which is very important. According to Oracle's official description, character set conversion from a subset of the superset supported, not the contrary. Between the two character sets, if there is no subset and superset of the relationship, then the character set conversion is not subject to oracle support. On the database server, the wrong character set changes will lead to a lot of unpredictable consequences, may seriously affect the normal operation of the database, it must be modified before the character set to confirm the existence of two sub-sets and superset relations. Generally speaking, unless a last resort, we do not propose to amend the oracle database server-side character set. In particular that we are the two most common character set ZHS16GBK and ZHS16CGB231280 does not exist between the subset and superset relations, so in theory the two between the character set conversion is not supported.

rn
1, modify server-side character set (not recommended)

rn
Before the oracle 8 can be used to directly modify the data dictionary table props $ to change the database character set. However, after oracle8 at least three records of a database system table character set of information, only props $ table does not change completely and may cause serious consequences. The right to amend as follows:

rn
$ sqlplus / nolog

rn
SQL> conn / as sysdba;

rn
If the database server has been activated at this time, the SHUTDOWN IMMEDIATE command to the implementation of the closure of the database server, and then perform the following command:

rn
SQL> STARTUP MOUNT;

rn
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

rn
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;

rn
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;

rn
SQL> ALTER DATABASE OPEN;

rn
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;

rn
SQL> ALTER DATABASE national CHARACTER SET ZHS16GBK;

rn
SQL> SHUTDOWN IMMEDIATE;

rn
SQL> STARTUP

rn
2, to amend a document character set dmp

rn
Mentioned above, dmp files section 2, paragraph 3-byte character set information recorded, the dmp files directly to amend Article 2, paragraph 3 of the contents of bytes can be 'cheated' over oracle checks. To do so in theory is only a subset of the superset can be modified, but many cases in the absence of a subset and superset relations can also modify the circumstances, we used a number of character sets, such as US7ASCII, WE8ISO8859P1, ZHS16CGB231280, ZHS16GBK basically can be changed. Because dmp file is changed, so have little effect.

rn
Specific methods are more changes, the simplest is to directly modify UltraEdit the dmp file, paragraphs 2 and 3 bytes. Dmp file for example, want to change the character set ZHS16GBK, can use the following SQL to identify the character set that corresponds to 16 hexadecimal code:

rn
SQL> select to_char (nls_charset_id ( 'ZHS16GBK'), 'xxxx') from dual;

rn
0354

rn
Dmp file and then revised to 2,3-byte to 0354.

Dmp files if a lot could not be opened with ue, on the need to use the method of procedure. Online java stored procedure was used to write the conversion program (java stored procedure with the advantage of a good general education, the shortcomings are more trouble). I tested through the windows. Oracle database but requested JVM options must be installed. Friends who are interested can look at code

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

ORACLE9I day-to-day operation

kods.net — Thu, 30 Jul 2009 01:05:07+0000

rn
A, ORACLE startup and shut down

rn
1, in the stand-alone environment

rn
To start or shut down ORACLE system must first switch to the ORACLE user, as

rn
su - oracle

rn
a, start the ORACLE system

rn
oracle> svrmgrl

rn
SVRMGR> connect internal

rn
SVRMGR> startup

rn
SVRMGR> quit

rn
b, shut down ORACLE system

rn
oracle> svrmgrl

rn
SVRMGR> connect internal

rn
SVRMGR> shutdown

rn
SVRMGR> quit

rn
Oracle9i database start command:

rn
$ Sqlplus / nolog

rn
SQL * Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003

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

rn
SQL> connect / as sysdba

rn
Connected to an idle instance.

rn
SQL> startup ^ C

rn
SQL> startup

rn
ORACLE instance started.

rn

rn
2, in the two-machine environment

rn
To start or shut down ORACLE system must first switch to the root user, as

rn
su - root

rn
a, start the ORACLE system

rn
hareg-y oracle

rn
b, shut down ORACLE system

rn
hareg-n oracle

rn
What are the Oracle database start-up mode

rn

rn
Description:

rn
There are several ways to start:

rn
1, startup nomount

rn
Non-installation boot, under the executable to start this way: the reconstruction of the control file and rebuild the database

rn
Read init.ora file, restart instance, that the SGA and background processes to start, which only need to activate init.ora file.

rn
2, startup mount dbname

rn
Start the installation, under the executable to start this way:

rn
Database log archiving,

rn
Database media recovery,

rn
So that online or offline data files,

rn
Re-positioning data files, redo log files.

rn
The implementation of "nomount", and then open the control file to confirm data files and online log file,

rn
However, at this time not data files and log files to check to check.

rn
3, startup open dbname

rn
First implementation of "nomount", then "mount", then open the Redo log file, including all database files,

rn
This way can access the data in the database.

rn
4, startup, equivalent to the following three command

rn
startup nomount

rn
alter database mount

rn
alter database open

rn
5, startup restrict

rn
Constraint start

rn
In this way to start the database, but only allows users with certain access privileges

rn
Non-privileged user access, we will have the following tips:

rn
ERROR:

rn
ORA-01035: ORACLE only with RESTRICTED SESSION allow users to use

rn
6, startup force

rn
Mandatory start-up mode

rn
When the database is not closed, can be used to complete the database startup force the closure of

rn
To close the database, and then the database command to start the implementation of the normal

rn
7, startup pfile = parameter file name

rn
Initialization parameter file with the start-up mode

rn
Read parameter files first, and then set the parameter file to start the database

rn
Cases: startup pfile = E: Oracleadminoradbpfileinit.ora

rn
8, startup EXCLUSIVE

rn

rn
Second, the user how to effectively use the data dictionary

rn
ORACLE database's data dictionary is an important part of one, it generated with the database created, with the database changes,

rn
Reflected in the number of sys user tables and views. Data Dictionary of the English name is an uppercase character.

rn
Data dictionary are user information, the user's permission information, all data object information, table constraints, statistical analysis, such as a database view.

rn
We can not manually modify data dictionary information.

rn
Very often, ORACLE users in general do not know how to use it effectively.

rn
dictionary of all data dictionary table name and explained that it has a synonym dict

rn
dict_column all the data dictionary table field names and interpretation of

rn
If we want to query with the index-related data dictionary can be used the following SQL statement:

rn
SQL> select * from dictionary where instr (comments, 'index')> 0;

rn
If we want to know user_indexes table details the meaning of the name field, you can use the following SQL statement that:

rn
select segment_name, tablespace_name, r.status,

rn
(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,

rn
max_extents, v.curext CurExtent

rn
From dba_rollback_segs r, v $ rollstat v

rn
Where r.segment_id = v.usn (+)

rn
order by segment_name;

rn
4, view the control file

rn
select name from v $ controlfile;

rn
5, view the log file

rn
select member from v $ logfile;

rn
6, view the use of table space

rn
select sum (bytes) / (1024 * 1024) as free_space, tablespace_name

rn
from dba_free_space

rn
group by tablespace_name;

rn
SELECT A. TABLESPACE_NAME, A. BYTES TOTAL, B. BYTES USED, C. BYTES FREE,

rn
(B. BYTES * 100) / A. BYTES "% USED", (C. BYTES * 100) / A. BYTES "% FREE"

rn
FROM SYS.SM $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C

rn
WHERE A. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE_NAME;

rn
7, view the database object library

rn
select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;

rn
8, see the database version

rn
Select version FROM Product_component_version

rn
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';

rn
9, the creation date to view the database and archiving methods

rn
Select Created, Log_Mode, Log_Mode From V $ Database;

rn
IV, ORACLE management of user connections

rn
By system administrators to see the current database there are several user connections:

rn
SQL> select username, sid, serial # from v $ session;

rn
If you want to stop a connection with

rn
SQL> alter system kill session 'sid, serial #';

rn
If this command will not do, the process of looking for it a number of UNIX

rn
SQL> select pro.spid from v $ session ses, v $ process pro where ses.sid = 21 and ses.paddr = pro.addr;

rn
Description: 21 is the sid of a number of connections

rn
And then use the kill command to kill the process number.

rn

rn
5, SQL * PLUS using

rn
a, near to the SQL * Plus

rn
$ sqlplus username / password

rn
Quit SQL * Plus

rn
SQL> exit

rn
b, to help in the information under sqlplus

rn
List of all SQL commands and SQL * Plus command

rn
SQL> help

rn
Are listed in order of a particular information

rn
SQL> help command name

rn
c, show the table structure DESCRIBE command

rn
SQL> DESC table name

rn
d, SQL * Plus commands in the Edit

rn
Shows SQL commands buffer

rn
SQL> L

rn
Modify SQL command

rn
First of all, to be correct line into the current row

rn
SQL> n

rn
CHANGE command changes with

rn
SQL> c / old / new

rn
Re-verify that the correct

rn
SQL> L

rn
INPUT command can be used in the SQL buffer to add one or more lines of

rn
SQL> i

rn
SQL> input

rn
e, call the external system editor

rn
SQL> edit the file name

rn
DEFINE command can use the system variable EDITOR to change the type of text editor in the paper are defined as follows login.sql his

rn
DEFINE_EDITOR = vi

rn
f, run the command file

rn
SQL> START test

rn
SQL> @ test

rn
SQL * Plus statements used

rn
a, the table to create, modify, delete

rn
Create table orders the following format:

rn
create table table name (column description list);

rn
Table-based commands add a new column as follows:

rn
ALTER TABLE table ADD (column description list)

rn
Cases: In order to increase test Table 1 Age, used to store the age of

rn
sql> alter table test

rn
add (Age number (3));

rn
Amend the definition of order-based list is as follows:

rn
ALTER TABLE table name

rn
MODIFY (column name data type)

rn
Example: Count table test out the width to 10 characters long

rn
sql> alter atble test

rn
modify (County char (10));

rn
b, a table will delete the form of statement is as follows:

rn
DORP TABLE table;

rn
For example: table delete to delete table data and table definition

rn
sql> drop table test

rn
c, table space to create, delete

rn
6, ORACLE logical backup file of SH

rn
Full backup of the SH file: exp_comp.sh

rn
rq = `date +"% m% d "`

rn
Can be used to create table field names in Chinese, but the best of the field names in English

rn
Create table fields can be added to the default value, for example, DEFAULT SYSDATE

rn
This insertion and changes every time, do not process the operation of the field action can be time

rn
Can create a table to add constraints to the field

rn
For example, does not allow duplicate UNIQUE, keyword PRIMARY KEY

rn
2.ALTER (change table, index, view, etc.)

rn
Change the name of the table

rn
ALTER TABLE table 1 TO table 2;

rn
In the table after adding a field

rn
ALTER TABLE table name ADD paragraph description field names;

rn
Amend the definition of exterior field description

rn
ALTER TABLE table MODIFY of the fields described in paragraph names;

rn
Add fields to the exterior condition

rn
ALTER TABLE table name ADD CONSTRAINT constraint PRIMARY KEY (field names);

rn
ALTER TABLE table name ADD CONSTRAINT constraint UNIQUE (field names);

rn
On the table or remove the memory database

rn
ALTER TABLE table CACHE;

rn
ALTER TABLE table NOCACHE;

rn
3.DROP (delete tables, indexes, views, synonyms, procedures, functions, database links, etc.)

rn
Delete table and all its constraints

rn
DROP TABLE table CASCADE CONSTRAINTS;

rn
4.TRUNCATE (clear all records and deeds, to retain the table structure)

rn
TRUNCATE table;

rn
III. Query (SELECT) part

rn
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE conditions;

rn
Field names can be brought into the function

rn
For example: COUNT (*), MIN (field names), MAX (field names), AVG (fieldname), DISTINCT (field names),

rn
TO_CHAR (DATE field names, 'YYYY-MM-DD HH24: MI: SS')

rn
NVL (EXPR1, EXPR2) function

rn
Explained:

rn
IF EXPR1 = NULL

rn
RETURN EXPR2

rn
ELSE

rn
RETURN EXPR1

rn
DECODE (AA, V1, R1, V2, R2 ....) function

rn
Explained:

rn
IF AA = V1 THEN RETURN R1

rn
IF AA = V2 THEN RETURN R2

rn
.. ...

rn
ELSE

rn
RETURN NULL

rn
LPAD (char1, n, char2) function

rn
Explained:

rn
Char1 character n in accordance with the development of the median show that the shortage of char2-digit string with the replacement of the left side of the space

rn
Field names can be between the arithmetic operations

rn
For example: (1 * Field Name Field Name 1) / 3

rn
Query can be nested

rn
For example: SELECT ... ... FROM

rn
(SELECT ... ... FROM table 1, [table 2, ... ...] WHERE conditions) WHERE condition 2;

rn
Query results of the two can be set to operate

rn
For example: and set UNION (removing duplicate records), and set UNION ALL (do not remove duplicate records), Difference Sets MINUS, intersection INTERSECT

rn
Sub-query

rn
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] GROUP BY field names 1

rn
[HAVING conditions];

rn
Between two or more table join queries

rn
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE

rn
Table 1. Field name = table name 2. Fieldname [AND ... ...];

rn
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE

rn
Table 1. Field name = table name 2. Field Name (+) [AND ... ...];

rn
There are (+) of the field-position auto-fill empty value

rn
Query result set of the sort operation, the default is ascending order ASC, is descending DESC

rn
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...]

rn
ORDER BY field names 1, Field Name 2 DESC;

rn
Fuzzy string comparison

rn
INSTR (field names, 'string')> 0

rn
Field Name LIKE 'string%' [ '% string%']

rn
Each table has a hidden field ROWID, it marks the uniqueness of records.

rn
IV. ORACLE data objects in common (SCHEMA)

rn
1. Index (INDEX)

rn
CREATE INDEX index name ON table (field 1, [field 2, ... ...]);

rn
ALTER INDEX index name REBUILD;

rn
A table is best not to index more than 3 (except special pages), the best single-field index, combined with an analysis of SQL statements implementation

rn
Can also create a combination of multi-field index and the index based on function

rn
ORACLE8.1.7 string of maximum length can be indexed for 1578 single-byte

rn
ORACLE8.0.6 string can be indexed to the maximum length of 758 single-byte

rn
2. View (VIEW)

rn
Of CREATE VIEW view AS SELECT .... FROM ... ..;

rn
ALTER VIEW view name COMPILE;

rn
View only a SQL query, which can form complex relationship between the simplicity of.

rn
3. Synonyms (SYNONMY)

rn
CREATE SYNONYM synonym name FOR table;

rn
CREATE SYNONYM synonym name FOR table @ database link name;

rn
4. Database link (DATABASE LINK)

rn
Database link CREATE DATABASE LINK name CONNECT TO username IDENTIFIED BY password USING 'database connection string';

rn
Database connection string can be used directly NET8 EASY CONFIG modify TNSNAMES.ORA or in definition.

rn
Database parameters global_name = true when asked the name of the database link with the remote database name

rn
Global database name can be identified with the following command

rn
SELECT * FROM GLOBAL_NAME;

rn
Query the remote database table

rn
SELECT ... ... FROM table @ database link name;

rn
5. Rights Management (DCL) statements

rn
1.GRANT giving permission

rn
Commonly used in the collection system has the following three rights:

rn
CONNECT (basic connection), RESOURCE (Programming), DBA (database management)

rn
Commonly used data objects have the following five rights:

rn
ALL ON data object name, SELECT ON data object name, UPDATE ON data object name,

rn
DELETE ON object name data, INSERT ON data object name, ALTER ON data object name

rn
GRANT CONNECT, RESOURCE TO username;

rn
GRANT SELECT ON table TO user name;

rn
GRANT SELECT, INSERT, DELETE ON table TO user name 1, User Name 2;

rn
2.REVOKE recovery authority

rn
REVOKE CONNECT, RESOURCE FROM username;

rn
REVOKE SELECT ON table FROM user name;

rn
REVOKE SELECT, INSERT, DELETE ON table FROM user name 1, User Name 2;

rn
Query the database Error No. 63:

rn
select orgaddr, destaddr from sm_histable0116 where error_code ='63 ';

rn
Users query the database to open an account to submit the largest and the largest number of issued: select MSISDN, TCOS, OCOS from ms_usertable;

rn
Query the database in the sum of a variety of error codes:

rn
select error_code, count (*) from sm_histable0513 group by error_code order

rn
by error_code;

rn
If the database query statements for a single type of query statistics.

rn
select sum (Successcount) from tbl_MiddleMt0411 where ServiceType2 = 111

rn
select sum (successcount), servicetype from tbl_middlemt0411 group by servicetype

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

Common SQL statements

kods.net — Wed, 29 Jul 2009 22:15:02+0000

rn
rn

rn
1, see table space name and the size of

rn
select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size

rn
from dba_tablespaces t, dba_data_files d

rn
where t.tablespace_name = d.tablespace_name

rn
group by t.tablespace_name;

rn

rn
2, see table space name and the physical file size

rn

rn
select tablespace_name, file_id, file_name,

rn
round (bytes / (1024 * 1024), 0) total_space

rn
from dba_data_files

rn
order by tablespace_name;

rn

rn
3, view the rollback segment name and size

rn

rn
select segment_name, tablespace_name, r.status,

rn
(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,

rn
max_extents, v.curext CurExtent

rn
From dba_rollback_segs r, v $ rollstat v

rn
Where r.segment_id = v.usn (+)

rn
order by segment_name;

rn

rn
4, view the control file

rn

rn
select name from v $ controlfile;

rn

rn
5, view the log file

rn

rn
select member from v $ logfile;

rn

rn
6, view the use of table space

rn

rn
select sum (bytes) / (1024 * 1024) as free_space, tablespace_name

rn
from dba_free_space

rn
group by tablespace_name;

rn

rn
SELECT A. TABLESPACE_NAME, A. BYTES TOTAL, B. BYTES USED, C. BYTES FREE,

rn
(B. BYTES * 100) / A. BYTES "% USED", (C. BYTES * 100) / A. BYTES "% FREE"

rn
FROM SYS.SM $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C

rn
WHERE A. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE_NAME;

rn

rn
7, view the database object library

rn

rn
select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;

rn

rn
8, see the database version

rn

rn
Select version FROM Product_component_version

rn
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';

rn

rn
9, the creation date to view the database and archiving methods

rn

rn
Select Created, Log_Mode, Log_Mode From V $ Database;

rn

rn
10, to capture long-running SQL

rn

rn
column username format a12

rn
column opname format a16

rn
column progress format a8

rn

rn
select username, sid, opname,

rn
round (sofar * 100 / totalwork, 0) | | '%' as progress,

rn
time_remaining, sql_text

rn
from v $ session_longops, v $ sql

rn
where time_remaining <> 0

rn
and sql_address = address

rn
and sql_hash_value = hash_value

rn
/

rn
11. See data table parameters

rn
SELECT partition_name, high_value, high_value_length, tablespace_name,

rn
pct_free, pct_used, ini_trans, max_trans, initial_extent,

rn
next_extent, min_extent, max_extent, pct_increase, FREELISTS,

rn
freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,

rn
empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,

rn
last_analyzed

rn
FROM dba_tab_partitions

rn
- WHERE table_name =: tname AND table_owner =: towner

rn
ORDER BY partition_position

rn

rn
12. See the services have not submitted

rn
select * from v $ locked_object;

rn
select * from v $ transaction;

rn

rn
13. Find object for which the process used by

rn
select

rn
p.spid,

rn
s.sid,

rn
s.serial # serial_num,

rn
s.username user_name,

rn
a.type object_type,

rn
s.osuser os_user_name,

rn
a.owner,

rn
a.object object_name,

rn
decode (sign (48 - command),

rn
1,

rn
to_char (command), 'Action Code #' | | to_char (command)) action,

rn
p.program oracle_process,

rn
s.terminal terminal,

rn
s.program program,

rn
s.status session_status

rn
from v $ session s, v $ access a, v $ process p

rn
where s.paddr = p.addr and

rn
s.type = 'USER' and

rn
a.sid = s.sid and

rn
a.object = 'SUBSCRIBER_ATTR'

rn
order by s.username, s.osuser

rn

rn
14. Rollback Segment View

rn
select rownum, sys.dba_rollback_segs.segment_name Name, v $ rollstat.extents

rn
Extents, v $ rollstat.rssize Size_in_Bytes, v $ rollstat.xacts XActs,

rn
v $ rollstat.gets Gets, v $ rollstat.waits Waits, v $ rollstat.writes Writes,

rn
sys.dba_rollback_segs.status status from v $ rollstat, sys.dba_rollback_segs,

rn
v $ rollname where v $ rollname.name (+) = sys.dba_rollback_segs.segment_name and

rn
v $ rollstat.usn (+) = v $ rollname.usn order by rownum

rn

rn
15. The process of consumption of resources (top session)

rn
select s.schemaname schema_name, decode (sign (48 - command), 1,

rn
to_char (command), 'Action Code #' | | to_char (command)) action, status

rn
session_status, s.osuser os_user_name, s.sid, p.spid, s.serial # serial_num,

rn
nvl (s.username, '[Oracle process]') user_name, s.terminal terminal,

rn
s.program program, st.value criteria_value from v $ sesstat st, v $ session s, v $ process p

rn
where st.sid = s.sid and st.statistic # = to_number ('38 ') and (' ALL '=' ALL '

rn
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

rn

rn
16. See lock (lock) the situation

rn
select / * + RULE * / ls.osuser os_user_name, ls.username user_name,

rn
decode (ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

rn
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,

rn
o.object_name object, decode (ls.lmode, 1, null, 2, 'Row Share', 3,

rn
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

rn
lock_mode, o.owner, ls.sid, ls.serial # serial_num, ls.id1, ls.id2

rn
from sys.dba_objects o, (select s.osuser, s.username, l.type,

rn
l.lmode, s.sid, s.serial #, l.id1, l.id2 from v $ session s,

rn
v $ lock l where s.sid = l.sid) ls where o.object_id = ls.id1 and o.owner

rn
<> 'SYS' order by o.owner, o.object_name

rn
17. See wait (wait) situation

rn
SELECT v $ waitstat.class, v $ waitstat.count count, SUM (v $ sysstat.value) sum_value

rn
FROM v $ waitstat, v $ sysstat WHERE v $ sysstat.name IN ( 'db block gets',

rn
'consistent gets') group by v $ waitstat.class, v $ waitstat.count

rn
18. Show sga situation

rn
SELECT NAME, BYTES FROM SYS.V_ $ SGASTAT ORDER BY NAME ASC

rn
19. Show catched object

rn
SELECT owner, name, db_link, namespace,

rn
type, sharable_mem, loads, executions,

rn
locks, pins, kept FROM v $ db_object_cache

rn

rn
20. See V $ SQLAREA

rn
SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,

rn
VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,

rn
USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,

rn
BUFFER_GETS, ROWS_PROCESSED FROM V $ SQLAREA

rn

rn
21. See the number of object categories

rn
select decode (o.type #, 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5,

rn
'SYNONYM', 6, 'SEQUENCE', 'OTHER') object_type, count (*) quantity from

rn
sys.obj $ o where o.type #> 1 group by decode (o.type #, 1, 'INDEX', 2, 'TABLE', 3

rn
, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'OTHER') union select

rn
'COLUMN', count (*) from sys.col $ union select 'DB LINK', count (*) from

rn
22. By the user to view object types

rn
select u.name schema, sum (decode (o.type #, 1, 1, NULL)) indexes,

rn
sum (decode (o.type #, 2, 1, NULL)) tables, sum (decode (o.type #, 3, 1, NULL))

rn
clusters, sum (decode (o.type #, 4, 1, NULL)) views, sum (decode (o.type #, 5, 1,

rn
NULL)) synonyms, sum (decode (o.type #, 6, 1, NULL)) sequences,

rn
sum (decode (o.type #, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))

rn
others from sys.obj $ o, sys.user $ u where o.type #> = 1 and u.user # =

rn
o.owner # and u.name <> 'PUBLIC' group by u.name order by

rn
sys.link $ union select 'CONSTRAINT', count (*) from sys.con $

rn
23. Information on the connection

rn
1) see which users connect

rn
select s.osuser os_user_name, decode (sign (48 - command), 1, to_char (command),

rn
'Action Code #' | | to_char (command)) action, p.program oracle_process,

rn
status session_status, s.terminal terminal, s.program program,

rn
s.username user_name, s.fixed_table_sequence activity_meter,''query,

rn
0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial # serial_num

rn
from v $ session s, v $ process p where s.paddr = p.addr and s.type = 'USER'

rn
order by s.username, s.osuser

rn
2) According to v.sid view the resources occupied by the corresponding connection and so on

rn
select n.name,

rn
v.value,

rn
n.class,

rn
n.statistic #

rn
from v $ statname n,

rn
v $ sesstat v

rn
where v.sid = 71 and

rn
v.statistic # = n.statistic #

rn
order by n.class, n.statistic #

rn
3) According to sid view of the corresponding connections are running sql

rn
select / * + PUSH_SUBQ * /

rn
command_type,

rn
sql_text,

rn
sharable_mem,

rn
persistent_mem,

rn
runtime_mem,

rn
sorts,

rn
version_count,

rn
loaded_versions,

rn
open_versions,

rn
users_opening,

rn
executions,

rn
users_executing,

rn
loads,

rn
first_load_time,

rn
invalidations,

rn
parse_calls,

rn
disk_reads,

rn
buffer_gets,

rn
rows_processed,

rn
sysdate start_time,

rn
sysdate finish_time,

rn
'>' | | Address sql_address,

rn
'N' status

rn
from v $ sqlarea

rn
where address = (select sql_address from v $ session where sid = 71)

rn

rn
24. The use of table space query

rn
select a.tablespace_name "Tablespace Name",

rn
100-round ((nvl (b.bytes_free, 0) / a.bytes_alloc) * 100,2) "occupancy rate (%)",

rn
round (a.bytes_alloc/1024/1024, 2) "the capacity of (M)",

rn
round (nvl (b.bytes_free, 0) / 1024/1024, 2) "Free (M)",

rn
round ((a.bytes_alloc-nvl (b.bytes_free, 0)) / 1024/1024, 2) "the use of (M)",

rn
Largest "the largest expansion of paragraph (M)",

rn
to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') "sampling time"

rn
from (select f.tablespace_name,

rn
sum (f.bytes) bytes_alloc,

rn
sum (decode (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes

rn
from dba_data_files f

rn
group by tablespace_name) a,

rn
(select f.tablespace_name,

rn
sum (f.bytes) bytes_free

rn
from dba_free_space f

rn
group by tablespace_name) b,

rn
(select round (max (ff.length) * 16/1024, 2) Largest,

rn
ts.name tablespace_name

rn
from sys.fet $ ff, sys.file $ tf, sys.ts $ ts

rn
where ts.ts # = ff.ts # and ff.file # = tf.relfile # and ts.ts # = tf.ts #

rn
group by ts.name, tf.blocks) c

rn
where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

rn

rn
25. Query table space extent of the debris

rn

rn
select tablespace_name, count (tablespace_name) from dba_free_space group by tablespace_name

rn
having count (tablespace_name)> 10;

rn

rn
alter tablespace name coalesce;

rn
alter table name deallocate unused;

rn

rn
create or replace view ts_blocks_v as

rn
select tablespace_name, block_id, bytes, blocks, 'free space' segment_name from dba_free_space

rn
union all

rn
select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents;

rn

rn
select * from ts_blocks_v;

rn

rn
select tablespace_name, sum (bytes), max (bytes), count (block_id) from dba_free_space

rn
group by tablespace_name;

Oracle deadlock related 2009-03-30 16:46:54
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
Terror CPU 2009-03-30 10:35:18
Turning optimization oracle article 2009-03-26 21:39:35
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48

Export Oracle database data into the basic methods and regular backup script unix

kods.net — Wed, 29 Jul 2009 21:23:34+0000

Oracle database import and export of basic methods of data collection using the most recent import and export data oracle encountered a lot of trouble! Oracle database was carried out on a serious study, summed up the import and export order of several methods available for the future.
Data Export:
1, will test full export database, user name, system password manager Export to d: \ daochu.dmp in
exp system / manager @ test file = d: \ daochu.dmp full = y
2, the system user database table and export sys user
exp system / manager @ test file = d: \ daochu.dmp owner = (system, sys)
3, the database table inner_notify, notify_staff_relat Export
exp aichannel / aichannel @ testdb2 file = d: \ data \ newsmgnt.dmp tables = (inner_notify, notify_staff_relat)
4, will be in the database field in the table1 table filed1 to "00" Export data from the beginning
exp system / manager @ test file = d: \ daochu.dmp tables = (table1) query = \ "where filed1 like 00% \"
Derived above is commonly used for compression, both the dmp file using winzip to compress can be very good. In the above command can also be followed by compress = y to achieve.

Data Import:
1, will be d: \ daochu.dmp in the test data into the database.
imp system / manager @ test file = d: \ daochu.dmp imp aichannel / aichannel @ hust full = y file = file = d: \ data \ newsmgnt.dmp ignore = y
Above may be a bit, because some form already exists, then it is error, not to import the table. Followed by the ignore = y on it.
2, d: \ daochu.dmp import table table1
imp system / manager @ test file = d: \ daochu.dmp tables = (table1)
Basically, the above import and export good enough for us. A number of cases is to first delete the table completely, and then import.

Note: The operator must have sufficient authority, it will prompt enough permissions. Can be connected to the database of. Tnsping test can be used to access the database can connect to the test.

Above the records, only oracle data import and export of the most basic way to practical application in a variety of factors will affect the results of import and export, there is a need for flexibility in the use of these basic commands, but we have to co-ordinate the operation of the whole environment to oracle consideration to the question.

This article comes from CSDN blog, reprint, please indicate the source: http://blog.csdn.net/usthycp/archive/2008/10/29/3176451.aspx

under unix script oracle backup database from time to time (by the user backup)

Can use the following command crontab way 14:00 every day from time to time the implementation of the script up to 30 sub-files, each file size of the largest form of 1G to export the database,
If the database is a large amount of data, more than 30G, will export is unsuccessful, the amount of data at this time only in accordance with the size of the adjustment to the value of variable num. The post-script to export the database to use gzip compression, then saved to the system / data / expfiles, prior to export, will be moved to the previous day's backup / data / expfiles_bak,
Such a backup can keep the last two days of data backup.

outfile = `date +% Y% m% d_% H% M`
num = 30
i = 1
files =

if [!-d / data / expfiles]; then
mkdir / data / expfiles
fi

if [!-d / data / expfiles_bak]; then
mkdir / data / expfiles_bak
fi

dfile = `ls -1 / data / expfiles_bak / | awk '(if (NR <2) (print $ 1))'`
prefix = `echo | awk '(print substr ("'"${ dfile }"'", 1,13))'`
rm-f / data / expfiles_bak / $ (prefix) *

dfile = `ls -1 / data / expfiles / | awk '(if (NR <2) (print $ 1))'`
prefix = `echo | awk '(print substr ("'"${ dfile }"'", 1,13))'`
mv / data / expfiles / $ (prefix) * / data / expfiles_bak

while [$ i-lt $ num]
do
files = $ files / data / expfiles / $ (outfile) _ $ i.dmp,
i = `expr $ i + 1`
done
files = $ files / data / expfiles / $ (outfile) _ $ i.dmp

# echo $ files
exp userid = user / passwd @ acct FILE = $ files filesize = 1024M grants = N 2>> exp_rpt.log

gzip / data / expfiles / $ (outfile) *

Article Source: http://www.diybl.com/course/7_databases/oracle/oraclejs/200798/70924_6.html

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

About Oracle data types

kods.net — Wed, 29 Jul 2009 21:15:13+0000

rn
rn
I. Overview

rn
Defined in ORACLE8: scalar (SCALAR), composite (COMPOSITE), reference (REFERENCE) and four LOB data types, the following details of their characteristics.

rn
Second, scalar (SCALAR)

rn
Legitimate scalar column types and database types used in the same, in addition to some expansion of it. It is divided into seven groups: the number of characters, line, date, line marking, and the credibility of Boolean.

rn
Figures, it has three basic types - NUMBER, PLS_INTEGER, and BINARY_INTENER. Can be described NUMBER integer or real number, and PLS_INTEGER and can only be described BINARY_INTENER integer.

rn
NUMBER, is stored in decimal format, it is easy to store, but in the calculation, the system will automatically convert it to be a binary operation. It is the definition of NUMBER (P, S), P is the precision, the largest 38, S is the calibration range can be -84 ... 127 value. For example: NUMBER (5,2) that can be used to store -999.99 ... 999.99 value. P, S can be omitted in the definition, such as: NUMBER (5), NUMBER, etc.;

rn
Used to describe BINARY_INTENER not stored in the database, but with symbols used to calculate the integer value. It's up to 2 forms of expression binary code. Cycle counters often use this type.

rn
PLS_INTEGER and BINARY_INTENER The only difference is that in the calculation of overflow which occurred when, BINARY_INTENER type of variable will be automatically assigned to a NUMBER and not go wrong type, PLS_INTEGER variable type error will occur.

rn
Characters, including the CHAR, VARCHAR2 (VARCHAR), LONG, NCHAR and NVARCHAR2 types.

rn
CHAR, description of fixed-length string, if not the actual value of the length of the definition, the system will be filled spaces. It's a statement as follows CHAR (L), L is the length of the string, the default is 1, as the largest variables 32,767 characters, as the data is stored on the largest ORACLE8 for 2000.

rn
VARCHAR2 (VARCHAR), description of variable-length string. It means the following statement VARCHAR2 (L), L is the length of the string, there is no default value, as the largest 32,767-byte variable, as data is stored on the largest ORACLE8 for 4000. Byte in a multi-language environment, the actual number of characters stored value may be smaller than L, for example: when the language environment in Chinese (SIMPLIFIED CHINESE_CHINA.ZHS16GBK), a VARCHAR2 (200) out of the data can be saved, or 200 English characters 100 Chinese characters.

rn
LONG, store in the database can be used to save up to 2G of data as a variable, can be expressed as a 32,760 byte maximum length of string variable.

rn
NCHAR, NVARCHAR2, the national character set specified with the NLS environment variable set of language is closely related to the use of methods and CHAR, VARCHAR2 same.

rn
Lines, including RAW and LONG RAW types. Used to store binary data, and not in the character set conversion between.

rn
RAW, similar to CHAR, a statement the way RAW (L), L for length, in bytes, as the largest database in 2000 listed as the biggest variable 32767 bytes.

rn
LONG RAW, similar to LONG, listed as the largest database of 2G bytes of data storage, as a maximum 32,760-byte variable.

rn
Date, only one type - DATE, the time used to store information, station 7 bytes (from the second century), there is no "millennium bug" problem.

rn
Line marking, only one type - ROWID, used to store "line identifier" ROWIDTOCHAR function can be used to convert the line to become the characters logo.

rn
Boolean, only one type - BOOLEAN, can be expressed only TRUE, FALSE or NULL.

rn
Credible, there is only one type - MLSLABEL, can be used to save TRUSTED ORACLE binary variable-length tags. ORACLE in the standard, the only store NULL values.

rn
Third, composite (COMPOSITE)

rn
Scalar type is predefined, the use of these types can be derived from a number of complex types. There are records in table.

rn
Records, can be seen as a combination of scalar structure, it is a statement as follows:

rn
TYPE record_type_name IS RECORD

rn
(Filed1 type1 [NOT NULL] [: = expr1]

rn
.......

rn
filedn typen [NOT NULL] [: = exprn])

rn
Which, record_type_name is the record type name. (Is not watched as CREATE TABLE ?......) reference variables must be related to the definition, recording only a TYPE, not VARIABLE.

rn
Table, not the physical storage of data tables, here is a variable type, also known as PL / SQL table, it is similar to C language array, also in a similar way. It's a statement as follows:

rn
TYPE table_type_name IS TABLE OF scalar_type INDEX BY BINARY_INTENER;

rn
Which, table_type_name is the type of name, scalar_type scalar type is a type of statement. Reference must also be related to the definition of variables. Table and the array is different from table 2, KEY and VALUE, KEY statement is the definition of BINARY_INTENER, VALUE statement is the definition of scalar_type.

rn
In addition to records and tables, there are object types, collections (nested tables and VARRAYS) type, which will be devoted to explain.

rn
Fourth, reference (REFERENCE)

rn
PL/SQL8.0 before in only one type - REF CURSOR, the cursor is. The definition of its relatively simple,

rn
CURSOR cursor_name IS select ..... from .....;

rn
PL/SQL8.0 after the introduction of the REF type, which point to an object. This type will be explained separately. (Such as I find out again, so in the face of an expert ......)

rn
5, LOB type

rn
LOB variable is mainly used to store large amounts of data fields of the database, the largest 4G byte can store the content (as much, and keep VCD?, A waste!). Are:

rn
CLOB: The LONG and ORACLE7 type is similar to single-byte character data storage. (Oh do not be used to keep the Chinese...)

rn
NCLOB: used to store multi-byte character set data width. (For example: keep "啸傲江湖" on very good)

rn
BLOB: and LONG RAW is similar to the structure used to store non-binary data.

rn
BFILE: It ORACLE database used to allow outside storage of large binary form of the text read-only access.

rn
Other

rn
In fact, to complete the understanding of scalar variables basically to meet the C / S mode of conventional database applications, and the type of follow-up to tell the truth, I play a 5-year ORACLE, are rarely used.

rn
Attachment: reference "ORACLE8 PL / SQL Programming" ( "ORACLE8 PL / SQL PROGRAMMING") ORACLE Series Mechanical Technology Industry Press

rn
Note: This article is cited by PL / SQL which can be defined data types, not all types can be specified in the building when the data type for the column.

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
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 error backup solution

kods.net — Wed, 29 Jul 2009 18:50:13+0000

rn
rn

rn
ORACLE backup solution error

rn

rn
First, backup overview

rn
1, the use of thermal backup, need to run in archive mode, the database can be fully restored, that is, from a backup to the database after the collapse of the data will not be lost;

rn
2, can be used to back up the whole database backup, for special circumstances, you can back up only specific data files, such as backup only the user table space (generally written especially for some of the data files frequently, you can increase the backup frequency alone );

rn
3, if during the recovery process and found that damage to a number of data files that can be used one method to restore data files, can also be used to restore the entire database methods;

rn
4, to run in archive mode will have a lot of archive logs, the log of the restoration of the database is very important, as long as the existence of backup and archiving, the database will be able to achieve full recovery (no loss of data);

rn
5, the recovery process carried out under the mount, if the restoration of the success, and then open the database

rn
Second, Oracle to back up the reasons for the failure

rn
1, did not find the existence of archived logs (Oracle is not running in archive mode)

rn
Third, change the operating mode Oracle

rn
1, $ mkdir / ora_arch1 # create a oracle archive log archive directory

rn
2, $ sqlplus / nolog

rn
SQL> conn / as sysdba;

rn
Connected.

rn
SQL> startup mount; # start to mount the database state

rn
SQL> alter database archivelog; # to oracle model for archiving

rn
SQL> archive log start; # start archiving mode

rn
SQL> archive log list; # See oracle archive case

rn
Database log mode Archive Mode

rn
Automatic archival Enabled

rn
Archive destination / ora_arch1

rn
Oldest online log sequence 　　 565

rn
Next log sequence to archive 　 567

rn
Current log sequence 　 567

rn
SQL> alter database open; # open database

rn
SQL> alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / ora_arch1' # / ora_arhc1 must be in front of mkdir / ora_arch1 operation of the establishment of the same path (the path set oracle archive) SQL> alter system set log_archive_format = '% t_% s.dbf' scope = spfile; # set archive types

rn
SQL> alter system set log_archive_start = true scope = spfile; # set up automatic archiving mode

rn
Restart the database, the settings take effect

rn
SQL> shutdown immediate;

rn
SQL> startup

rn
SQL> archive log list; # See archived case

rn
Database log mode Archive Mode

rn
Automatic archival Enabled

rn
Archive destination / ora_arch1

rn
Oldest online log sequence 　　 565

rn
Next log sequence to archive 　 567

rn
Current log sequence 　 567

rn

Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Block Oracle paragraph (reprint) 2009-03-26 17:41:14
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17

(Transfer from LU) how the client connected to ORACLE database

kods.net — Wed, 29 Jul 2009 18:35:05+0000

rn
rn
How to configure the client can connect to the database:

rn
For a client machine can connect oracle database on the client machines need to install the oracle client software, the only exception is when the java database connectivity, you can use jdbc thin models, do not have installed the oracle client software. To join the machine to fit you in the oracle database, you do not need a separate machine in the oracle client installed, because when installed oracle database will automatically install the oracle client.

rn
Used sql server database and then use the new oracle might have some questions: what I asked when the sql server do not have sql server installed this client? The reason is simple, sql server is microsoft, and it integrates the operating system in the sql server client, microsoft, if there is an agreement with the oracle, oracle client will also be integrated into the operating system, then we would not have installed on the client machine oracle soft client machine can access the database, but it seems things can not be achieved.

rn
Some people may ask: Why in the sql server port is not listening, said to be in the oracle listener port configuration? In fact, there is also sql server listening port, but microsoft will be listening port is fixed at 1433, does not allow any changes you, so give you a sense of illusion is not sql server listening port, cough, microsoft too many things to closure fitted to the black box, easy-to-use but also the need for side effects. The listening port of oracle directly in the configuration file to allow any changes, but no matter how it changes with the oracle server side set up the same listening port.

rn
Well, get on, how can we do to make the client machine can connect to oracle database?

rn
A. Installation of related software

rn
B. Appropriate configuration

rn
A. At an appropriate location to install the appropriate software:

rn
On the client machine:

rn
1. On the client machines to install the Oracle Net communications ORACLE software, it is included in the oracle client software.

rn
2. Properly configured sqlnet.ora file:

rn
NAMES.DIRECTORY_PATH = (TNSNAMES, ....)

rn
NAMES.DEFAULT_DOMAIN = DB_DOMAIN

rn
Under normal circumstances we do not have NAMES.DEFAULT_DOMAIN parameters. If you want to not use the parameters of the parameter # comment out or delete can be, for NAMES.DIRECTORY_PATH parameter default values can be used for parameter NAMES.DEFAULT_DOMAIN will need to comment out sometimes, there is a detailed explanation below.

rn
3. Tnsname.ora documents properly configured

rn
Machines in the server-side:

rn
1. Ensure the listener has started

rn
2. To ensure that the database has already started.

rn
If the database does not start, use:

rn
Oracle 9i:

rn
dos> sqlplus "/ as sysdba"

rn
sqlplus> startup

rn
Oracle 8i:

rn
dos> svrmgrl

rn
svrmgrl> connect internal

rn
svrmgrl> startup

rn
Command to start the database

rn
If the listener does not start, use:

rn
lsnrctl start [listener name]

rn
lsnrctl status [listener name]

rn
Command to start listener

rn
B. Appropriate configuration

rn
How to correctly configure tnsname.ora documents:

rn
Client machines can use oracle Net Configuration Assistant or oracle Net Manager graphical configuration tool for client-side configuration, the configuration tool actually modify tnsnames.ora file. Therefore, we can directly modify the tnsnames.ora file, the following documents in order to directly modify the tnsnames.ora example:

rn
The location of the file as: ... \ network \ admin \ tnsnames.ora (for windows)

rn
... / Network / admin / tnsnames.ora (for unix)

rn
Here, assuming that the server named testserver, service called orcl.testserver.com, the use of the listening port for 1521, the document tnsnams.ora a test network service name (database alias) as follows:

rn
test =

rn
(DESCRIPTION =

rn
(ADDRESS_LIST =

rn
(ADDRESS = (PROTOCOL = TCP) (HOST = testserver) (PORT = 1521))

rn
)

rn
(CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com)

rn
)

rn
)

rn
The contents of the red need to modify the content of the actual situation, it is explained as follows:

rn
PROTOCOL: client and server-side communications protocol, usually TCP, do not change the content in general.

rn
HOST: database listener's machine name or IP address of the machine, the database listener and database in general in the same machine, so when I say the machine where the database listener is the general machinery refers to the database is located. In UNIX or WINDOWS, you can listen in the database through the machine where the command prompt using the command hostname to be the machine name, or ipconfig (for WINDOWS) or ifconfig (for UNIX) command to be IP addresses. It should be noted that, regardless of using the machine name or IP address, the client must pass ping command ping the machine where the database listener's machine name, or the need to add in the hosts file of the machine where the database listener the name of the machine Analysis.

rn
PORT: database listener port is listening, you can see the server side listener.ora file or listen in the database where the machine through the command prompt lnsrctl status [listener name] command View. Port value here must be familiar with the database listener is listening the same port.

rn
SERVICE_NAME: the server side, after landing with system users, sqlplus> show parameter service_name command View.

rn
How to use configuration of network services to connect to the database:

rn
Sqlplus procedure used test for testing of network services, such as sqlplus system / manager @ test. If you can not connect to the database, tnsname.ora document in the test of network services (net service) followed by the Oracle database DB_Domain parameter values, by using sqlplus> show parameter db_domain View command. Parameter values for db_domain here testserver.com, be added to the back of network services, the revised tnsname.ora services on the network name says:

rn
test.testserver.com =

rn
(DESCRIPTION =

rn
(ADDRESS_LIST =

rn
(ADDRESS = (PROTOCOL = TCP) (HOST = testserver) (PORT = 1521))

rn
)

rn
(CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com)

rn
)

rn
)

rn
Sqlplus procedure with test.testserver.com testing of network services, such as sqlplus system / manager @ test.testserver.com.

rn
Even on the service name in the network behind the increase db_domain parameters need to be aware of sql * plus connection of the principle of the database, I followed to resolve common fault in 12,154 is given detailed instructions.

rn
If the above trick does not work, then you have to use the diversion of a large universe.

rn
The client part of the network services

rn
test.testserver.com =

rn
(DESCRIPTION =

rn
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = testserver) (PORT = 1521))

rn
)

rn
(CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com)

rn
)

rn
)

rn
Copy to the server tnsnames.ora file. Server-side and then use sqlplus system / manager @ test.testserver.com to connect to the database.

rn
If a successful connection on your client network and server-side problem.

rn
If the connection fails, check with the front part of part of part of network services is correct, if satisfied that the correct part of network services and all clients are likely to crawl database system TCP / IP or the Oracle system, it was recommended that re - installation database.

rn
Troubleshooting solutions:

rn
TNS-12154 (ORA-12154): TNS: could not resolve service name

rn
The error that the network services used to connect in the tnsnames.ora file does not exist, such as the above tnsnames.ora in the network services were only a test, if a user in connection with sqlplus system / manager @ test1 then will be given TNS-12154 error.

rn
It should be noted that, sometimes even in the tnsnames.ora file of the network services, but with the connection of network services will be wrong to see such a typical configuration is as follows (on the client machine):

rn
sqlnet.ora file:

rn
NAMES.DIRECTORY_PATH = (TNSNAMES, ....)

rn
NAMES.DEFAULT_DOMAIN = server.com

rn
tnsnames.ora file:

rn
test =

rn
(DESCRIPTION =

rn
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = testserver) (PORT = 1521))

rn
)

rn
(CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com)

rn
)

rn
)

rn
sql * plus the basic mechanism of operation:

rn
User input in sqlplus system / manager @ test after, sqlplus program will automatically find the sqlnet.ora file NAMES.DEFAULT_DOMAIN parameters, if the parameters exist, the value of the parameter removed, added to the back of network services, namely, this case you enter from the sqlplus system / manager @ test automatically into sqlplus system / manager @ test.server.com, and then to find tnsnames.ora file test.server.com of network services, which of course can not find the because the documents were only a test network services, so error. The solution is to sqlnet.ora file can be commented out NAMES.DEFAULT_DOMAIN parameters, such as # NAMES.DEFAULT_DOMAIN = server.com. If NAMES.DEFAULT_DOMAIN parameter does not exist, sqlplus program tnsnames.ora file directly to the test to find the network service name, and then remove the host, port, tcp, service_name, the use of such information will be connected to the request sent to the correct database server on .

rn
Another principle in the tnsnames.ora configuration are not case-sensitive, but I did encounter the situation of case-sensitive, so it would be preferable to the use of Web services and tnsnames.ora configured in exactly the same.

rn
ORA-12514: TNS: listener could not resolve SERVICE_NAME given in connect Descriptor.

rn
The error that can be found in the network tnsnames.ora service name, but specified in the tnsnames.ora and server-side SERVICE_NAME inconsistent SERVICE_NAME. The solution is to modify the tnsnames.ora in the SERVICE_NAME.

rn
Confusing terminology introduced:

rn
Db_name: a database (Oracle database) the unique identifier, the database referred to as the first chapter of the Oracle database. Such that for a single database is sufficient, but with the database by a number of distributed database consisting of the popularity of such an order database to database management to a certain degree, since the name of each database are the same, resulting in management confusion. In order to resolve this situation, the introduction of Db_domain parameters, such identification in the database Db_name and Db_domain by joint decision of two parameters, because the database to avoid duplicate names caused confusion in management. This is similar to the machines of Internet management. Db_name and we will use two parameters Db_domain '.' To link up a database that will be the name of the database known as the Global_name, that it extends the Db_name. Db_name parameters can only be letters, numbers ,'_','#','$' component, and a maximum of eight characters.

rn
Db_domain: the definition of a database where the domain, the domain name with the Internet's' domain 'is not in any way related to, but the database administrator to better management of distributed database and determined according to the actual situation. In order to facilitate course management, can be equivalent to the Internet domain.

rn
Global_name: a database (Oracle database) the unique identifier, oracle command this method proposed database. The value is in the creation of the database is to decide, and the default value of Db_name. Db_domain. Parameters at a later document Db_name with any changes Db_domain parameters Global_name does not affect the value, if we try to amend Global_name, can only use ALTER DATABASE RENAME GLOBAL_NAME TO order be modified, and then modify the appropriate parameters.

rn
Service_name: The parameter is the newly introduced oracle8i. In 8i the past, we used to indicate the SID identifies an instance of the database, but Oracle's parallel environment, multiple instances of a database corresponds to, which will require a number of network services, and set the red tape. Parallel environment in order to facilitate the setting, the introduction of Service_name parameter corresponds to a database, rather than an example, and the parameters are many other benefits. The default values for parameters Db_name. Db_domain, that is equivalent to Global_name. Corresponds to more than a database can be Service_name, in order to achieve a more flexible configuration. This parameter is not directly related with the SID, that is, do not have Service name must be the same SID.

rn
Net service name: Web services, and also known as the alias database (database alias). Client program is needed to access the database, shielding the client how to connect to the server-side details of the location of the database to achieve the characteristics of transparency.

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

Archive oracle10g example (reprint)

kods.net — Wed, 29 Jul 2009 18:12:52+0000

Oracle10g Automatic archiving settings

Oracle10i Central Plains log_archive_start in some parameters have been abandoned, the archive log mode is set up we need to log_archive_format and log_archive_dest_N parameters, and then mount the database under the state alter database archivelog; can be set to the database archive log mode, Oracle will automatically archiving, on the contrary the use of alter database noarchivelog; can.

More than re-starting operation of the database will still be effective. The following is a demonstration of the process.

SQL> startup mount
ORACLE instance started.

Total System Global Area 399927208 bytes
Fixed Size 1284008 bytes
Variable Size 318767104 bytes
Database Buffers 33554432 bytes
Redo Buffers 46321664 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter archive_start

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
log_archive_start boolean FALSE

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination / data4/oracle10/app/prodUCt/10.0.0/dbs/arch
Oldest online log sequence 92
Next log sequence to archive 94
Current log sequence 94
SQL>

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 399927208 bytes
Fixed Size 1284008 bytes
Variable Size 318767104 bytes
Database Buffers 33554432 bytes
Redo Buffers 46321664 bytes
Database mounted.
Database opened.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled

Archive destination / data4/oracle10/app/product/10.0.0/dbs/arch
Oldest online log sequence 92
Next log sequence to archive 94
Current log sequence 94

SQL>! Ls arch
1_92_496877985.dbf arch_1_93_496877985.arc

SQL> alter system archive log current;

System altered.

SQL>! Ls arch
1_92_496877985.dbf arch_1_93_496877985.arc arch_1_94_496877985.arc

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>! Ls arch
1_92_496877985.dbf arch_1_94_496877985.arc arch_1_96_496877985.arc arch_1_98_496877985.arc
arch_1_93_496877985.arc arch_1_95_496877985.arc arch_1_97_496877985.arc arch_1_99_496877985.arc

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 day-to-day maintenance manual (Download)

kods.net — Wed, 29 Jul 2009 17:30:11+0000

rn
rn rn rn rn rn

rn rn rn rn rn rn rn rn rn rn rn

Document:	oracle manual routine maintenance. rar
Size:	243KB
Download:	Download

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

the database name (db_name). instance name (instance_name). ORACLE_SID

kods.net — Wed, 29 Jul 2009 14:55:06+0000

rn
Database name (DB_NAME), instance name (Instance_name), as well as the operating system environment variable (ORACLE_SID)

In ORACLE7, 8 database, only the database name (db_name) and database instance name (instance_name). In ORACLE8i, 9i in the emergence of new parameters, namely, the domain name database (db_domain), service name (service_name), as well as the operating system environment variable (ORACLE_SID). These are present in a database with the logo, the database used to distinguish between different parameters.

I. What is the database name (db_name)?
Database name is used to distinguish between internal identification data is stored in binary file database control parameters, in the data after the installation or to create will not be allowed to modify. Database after the installation is complete, the parameters are written into the database pfile parameter files, the format is as follows:
code:

rn
.........
db_name = "orcl" # (not allowed to modify)
db_domain = dbcenter.toys.com
instance_name = orcl
service_names = orcl.dbcenter.toys.com
control_file =(...............
.........

rn
In each run ORACLE8i database has a database name (db_name), if a server program to create the two databases, there are two database name. According to its control parameters are different in the control of pfile-related database.

Second, what is the database instance name (instance_name)
Database instance name is used in and the linkages between the operating system for the use of external connections. In the operating system to obtain the interaction between the database, database instance name must be used. For example, to a database and a server connection, it is necessary to know the database instance name, only know the database name is of no use with the database name is different in the data after the installation or create the database, instance name can be modified. Database after the installation is complete, the examples were written into the database pfile parameter files, the format is as follows:
code:

rn
db_name = "orcl" # (not allowed to modify)
db_domain = dbcenter.toys.com
instance_name = orcl # (can be amended, the same can also be different db_name)
service_names = orcl.dbcenter.toys.com
control_file =(...............
.........

rn
Database name and the relationship between the instance name.
Database name and instance name is usually the relationship between the one-to-one relationship, there is a database instance name, a name, if a server to create two databases, there are two databases, and the two database instances, and with a database to determine the two logos, and examples of connected users.
But in 8i, 9i's Parallel Server, the database with examples of one-to-one relationship between the non-existent, but one-to-many relationship, (corresponding to multiple instances of a database, the user only one time linked to an example, when a One example of failure, other examples of automated services to ensure the safe operation of the database.)

Third, the operating system environment variable (ORACLE_SID)
In practice, the database instance name is sometimes used to describe the instance name (instance_name) parameters, and sometimes the use of ORACLE_SID parameter. These are two examples of the database, What is the difference between them then? (Often confused)
code:

rn
(ORACLE_SID)
OS <---------------->; ORACLE database <--------( Instance_name (instance name))

rn
Above that the instance name instance_name, ORACLE_SID and the database and the relationship between the operating system, although here the two parameters are listed in the database instance name, but the instance_name parameter is the ORACLE database parameters, this parameter can query parameter files that ORACLE_SID parameter is the operating system environment variables.
ORACLE_SID environment variable for the operating system and operating system interaction. In other words, to be in the operating system instance name, it is necessary to use the ORACLE_SID. This parameter with the ORACLE_BASE, ORACLE_HOME, such as the use of the same. In the database after installation, ORACLE_SID parameter was used to define the name of the file database. Such as:
$ ORACLE_BASE / admin / DB_NAME / pfile / init $ ORACLE_SID.ora.
The definition of methods:
code:

rn
export ORACLE_SID = orcl

rn
If the same server to create a number of databases, while at the same time is bound to have more than one database instance, then the process can repeat the above definition in order to choose a different example.

Also can be used:

rn
code:
[oracle @ Datacent] $. oraenv

rn
ORACLE_SID to switch to a different operating system to start the adoption of different examples (instance)

rn

RMAN created physical standby 2009-03-30 22:49:29
Oracle deadlock related 2009-03-30 16:46:54
RedHat AS 3 at oracle10.2.1 install document 2009-03-13 16:56:34
Using SQL TRACE and TKPROF 2009-02-26 21:36:37
Diagnosis and principles of order 2009-02-25 20:27:28

2009/07

Logical backup case: windows platform guide data oracle logical backup scripts, backup automatically compressed rar

Related Posts

How to solve session lock?

Related Posts

Timesten 11g with Oracle released In-Memory Database Cache 11g

Oracle Announces Oracle ® TimesTen In-Memory Database 11g and Oracle In-Memory Database Cache 11g

Latest Release Enhances Performance, Scalability, Availability and Integration with Oracle Database

News Facts

Improved Performance for Mission-Critical Applications

Supporting Quotes

Related Posts

Table undotbs1 damage on the solution space

Related Posts

10Grac the ORA-600 [kclfusion_1] lead to node restart

Related Posts

oracle agent, IBM software, redhat good price

Related Posts

oracle background processes

Related Posts

perl export data

Related Posts

oracle archive is full, delete some can not be re-filed after the dissemination of data stream

Related Posts

oracle10g fine-grained auditing

ORACLE 10g Fine-grained auditing

Related Posts

Summed up the issue of Oracle Character Set

Related Posts

ORACLE9I day-to-day operation

Related Posts

Common SQL statements

Related Posts

Export Oracle database data into the basic methods and regular backup script unix

Related Posts

About Oracle data types

Related Posts

Oracle error backup solution

Related Posts

(Transfer from LU) how the client connected to ORACLE database

Related Posts

Archive oracle10g example (reprint)

Oracle10g Automatic archiving settings

Related Posts

ORACLE day-to-day maintenance manual (Download)

Related Posts

the database name (db_name). instance name (instance_name). ORACLE_SID

Related Posts