Month: February 2019

PreparedStatement like Fuzzy query

PreparedStatement like Fuzzy query

The PreparedStatement Use like

PreparedStatement fuzzy query waste a lot of trouble, before there has been no attention to this issue. Under normal circumstances, we precise inquiry, similar sql statement: select * from table where name =? PreparedStatement setString method and then call to? The specified value. Fuzzy query should be how to write? First of all, I tried: select * from customer where name like ‘%?%’.
The program error because? Be included in single quotation marks, and PreparedStatement not see it as a parameter. Later checked the Internet some of the information found can write the select * from table where name like? ; Specified parameters? Designated as “%” + name + “%”, name is specified query conditions. This has been OK.
Under normal circumstances, I always identified subconscious? Is then passed to replace the specified parameters, but in fact we can on the parameters specified in the packaging? , Such as here in the parameters before and after the addition of one percent, and then to pass?

String expr = “select * from table where url like?”;
pstmt = con.prepareStatement (expr);
String a = “a”;
pstmt.setString (1, “%” + a + “%”) ;/ / automatically add the parameter in single quotes (packaged)
pstmt.execute ();
System.out.println (the pstmt.toString ()) ;/ / print sql
/ / Default generated SQL: select * from table where url like ‘% http%’

Posted by databasesql

How can JDBC connect to each type of databases?

1, Oracle8/8i/9i database (thin mode)

Class.forName (“oracle.jdbc.driver.OracleDriver”). NewInstance ();
String url = “jdbc: oracle: thin: @ localhost: 1521: orcl”; / / orcl database SID
String user = “test”;
String password = “test”;
Connection conn = DriverManager.getConnection (url, user, password);

2, DB2 database

Class.forName (“the COM.ibm.db2.jdbc.app.DB2Driver”). NewInstance ();
String url = “jdbc: db2 :/ / localhost: 5000/sample”; / / sample for your database name
String user = “admin”;
String password = “”;
Connection conn = DriverManager.getConnection (url, user, password);

3 Sql Server7.0/2000 database

Class.forName (“com.microsoft.jdbc.sqlserver.SQLServerDriver”). NewInstance ();
String url = “jdbc: microsoft: sqlserver :/ / localhost: 1433; DatabaseName = mydb”;
/ / MyDB as database
String user = “sa”;
String password = “”;
Connection conn = DriverManager.getConnection (url, user, password);

4, the Sybase database

Class.forName (“com.sybase.jdbc.SybDriver”). NewInstance ();
String url = “jdbc: sybase: Tds: localhost: 5007/myDB” ;/ / myDB for your database name
The Properties sysProps = System.getProperties ();
SysProps.put (“user”, “userid”);
SysProps.put (“password”, “user_password”);
Connection conn = DriverManager.getConnection (url, SysProps);

5, Informix database

Class.forName (“com.informix.jdbc.IfxDriver”). NewInstance ();
String url = “jdbc: informix-sqli :/ / 123.45.67.89:1533 / myDB: INFORMIXSERVER = myserver;
user = testuser; password = testpassword “; / / myDB for the database name
Connection conn = DriverManager.getConnection (url);

6, MySQL database

The Class.forName (“org.gjt.mm.mysql.Driver”). NewInstance (); / / Class.forName (“the com.mysql.jdbc.Driver,”);
String url = “jdbc: mysql :/ / localhost / myDB? User = soft & password = soft1234 & useUnicode = true & characterEncoding = 8859_1”
/ / MyDB as database name
Connection conn = DriverManager.getConnection (url);

7, PostgreSQL database

Class.forName (“org.postgresql.Driver”). NewInstance ();
String url = “jdbc: postgresql :/ / localhost / myDB” / / myDB for the database name
String user = “myuser”;
String password = “mypassword”;
Connection conn = DriverManager.getConnection (url, user, password);

Access the database directly used in conjunction with ODBC

The Class.forName (sun.jdbc.odbc.JdbcOdbcDriver);
String url = “jdbc: odbc: Driver = {MicroSoft Access Driver (*. Mdb)}; DBQ =” + application.getRealPath (“/ Data / ReportDemo.mdb”);
Connection conn = DriverManager.getConnection (url, “”, “”);
Statement stmtNew = conn.createStatement ();

Posted by databasesql

Principles of Computer Organization …..

Transfer from Baidu know
The computer system consists of two parts of the hardware and software components.
(1) the composition of the hardware (input devices, output devices, memory, arithmetic unit, controller)
Input devices: computer from external information devices such as a mouse, keyboard, light pen, scanner, microphone, digital camera, camera,
Tablet output device: the results of the computer processing of information expressed in the form of people able to identify devices such as monitors, printers, plotters, speakers, projector memory: such as a hard disk, optical drive, U disk operations: arithmetic, logical operations controller: If the instruction is fetched from memory, the control computer part of the coordinated operation of the controller and the computing unit is integrated in the CPU(2) the composition of the software software-defined: the program and related documentation collectively referred to as software: system software (the use and management of computer software) and application software (designed for the preparation of an application software)
Common system software: operating systems, database management systems and programming languages ??common applications: the auxiliary teaching software, CAD software, word processing software,
Information management software and automatic control software

————————————————– ————————————————– ————-
I would like a computer professional who knows, but so the back is good!
Posted by databasesql

Oracle RMAN Quick Start Guide

Oracle RMAN Quick Start Guide

Foreword

This article introduces the the RMAN common method, which contains some of the author’s own experiences, experiments inside all the basic WIN 2K and Oracle 8.1.6 environment test is successful (because the environment is relatively easy to implement).

This article draws on the master online article, I hope heroes do not take offense, be thanked here.

This article is mainly in Beijing during a business trip to write home after finishing modify, rush time, because of space limitations, some technical details can not be covered one by one, and only hope to help beginners, really want to skilled grasp RMAN must be a long-time practice can only be honed, especially in engineering to gain valuable experience in troubleshooting.

1 What is RMAN

RMAN can be used to backup and restore the database files, archive logs and control files. It can also be used to perform a complete or incomplete database recovery.

Note: RMAN can not be used in the the backup initialization parameter file and password file.

RMAN starts Oracle server process on the database for backup or restore. Backup, restore, recovery is driven by these processes.

RMAN can be controlled by the OEM Backup Manager GUI, but are not the focus of discussion in this article.

Terminology professional Glossary

2.1 Backup sets backup set

The backup set has the following characteristics:
Includes one or more data files or archived logs to Oracle proprietary format to save a complete backup piece collection constitute constitute a full backup or incremental backup

2.2 Backup pieces backup piece

A backup set consists of a number of backup pieces. Each backup piece is a single output file. A backup piece size is limited; If there is no size limit, the backup set is only a backup piece. File system supported by the size of the backup piece can not be greater than the maximum value of the length of the document.

Of 2.3 Image Copies mirror backup

Mirror backup separate files (data files, archive logs, control file) backup. It is very similar to the operating system level file backup. It is not a backup set or backup piece, also has not been compressed.

2.4 Full Backup sets full backup set

Full backup is a backup of one or more data files used in the data block. Have not used the data block is not backed up, that is to say, the Oracle backup set compression.

2.5. Incremental backup sets incremental backup set

Incremental backup is a backup of one or more data files have been modified since the on the same level or lower-level backup of data blocks. The same as the full backup, incremental backup compression.

2.6 File multiplexing

A plurality of different data file’s data blocks can be mixed backup in a backup set.

2.7. Recovery catalog resyncing recovery directory synchronization

Recovery Manager to perform backup, copy, restore or switch command, the recovery catalog automatically updated, but the log and archive log information is not automatically credited to the recovery catalog. The need for directory synchronization. Use the resync catalog command to synchronize.

  RMAN> resync catalog;

 RMAN-03022:'re compiling command: resync
 RMAN-03023: executing command: resync
 RMAN-08002: Starting full recovery catalog resync
 RMAN-08004: full resync complete

(3) recovery catalog

3.1 the concept of recovery catalog

The recovery catalog is used by RMAN maintenance warehouse used to place the backup information. RMAN recovery catalog records information to determine how to perform the backup and recovery operations.

The recovery catalog can exist in the ORACLE database plan.

Although the recovery catalog can be used to back up multiple databases, it is recommended to create a separate database for the recovery catalog database.

Recovery catalog database recovery catalog can not use the backup itself.

3.2 Establish recovery catalog

The first step is created in the directory database recovery catalog table space:

  SQL> create tablespace rman_ts datafile "d :/ oracle / oradata / rman / rman_ts.dbf" size 20M;

 Table space has been created.

Created in the directory database RMAN user and authorization:

  SQL> create user rman identified by rman default tablespace rman_ts temporary 
   tablespace temp quota unlimited on rman_ts;

 The user has been created.

 SQL> grant recovery_catalog_owner to rman;

 Grant succeeded.

 SQL> grant connect, resource to rman;

 Grant succeeded.

The third step is created in the directory database recovery catalog

  C :/> rman catalog rman / rman

 Recovery Manager: Version 8.1.6.0.0 - Production

 RMAN-06008: connected to recovery catalog database RMAN-06428: not installed recovery catalog RMAN> create catalog tablespace rman_ts;

 RMAN-06431: recovery catalog has been created

Note: While using RMAN recovery catalog is not necessarily required, but recommended. Restore the information described in a catalog can control file to record RMAN restore the database to use this information. Do not use a recovery catalog backup and recovery operations will be limited.

3.3 The use of the advantages of recovery catalog

Can be stored in the script;

Documented long-time backup and recovery operations;

4 Start RMAN

RMAN for interactive command-line processing interface, you can also run from Enterprise Manager.

In order to use the example below, first check the environment in accordance with:

the target database is called “his” and has the same TNS alias
user rman has been granted “recovery_catalog_owner” privileges
The target database connection as internal account, or connected to the recovery catalog database is called “rman” and has the same TNS alias to other SYSDBA type account
the schema containing the recovery catalog is “rman” (same password)

Before using RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment variable, the the many RMAN LIST command output is related to the time and date, and that the user wishes to perform time-based recovery is also very important.

The following example is a demonstration of the environment variable:

NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
NLS_DATE_FORMAT = DD-MON-YYYY HH24: MI: SS

Connected to recovery catalog RMAN recovery catalog database must be open, at least the target database STARTED (unmount), otherwise RMAN returns an error, the target database must be placed in archive mode.

4.1 Use without RMAN recovery catalog

Set the target database ORACLE_SID, run:

  % Rman nocatalog
 RMAN> connect target
 RMAN> connect target internal / <PASSWORD> @ his

4.2 Use with RMAN recovery catalog

  % Rman rman_ts rman / rman @ rman
 RMAN> connect target

 % Rman rman_ts rman / rman @ rman target internal / <PASSWORD> @ his

4.3 Using RMAN

Once connected to the target database, you can perform the specified interface or prior script stored RMAN commands Here is an instance of the RMAN interface:

 RMAN> resync catalog; RMAN-03022:'re compiling command: resync RMAN-03023: executing command: resync RMAN-08002: Starting All the Resync recovery catalog RMAN-08004: full resync complete

Use scripts instance:

  RMAN> execute script alloc_1_disk;

Create or alternative storage script:

  RMAN> replace script alloc_1_disk { 
    2> allocate channel d1 type disk;
    >}

Register or cancellation of the target database.

5.1 Register the target database

Database status:

The recovery catalog Status: open

Target database: load or open

The target database before the first use RMAN must be registered in the recovery catalog:

The first step to start the Recovery Manager and connect to the target database:

  C :/> rman target internal / oracle @ his catalog rman / rman @ rman
 Recovery Manager: Version 8.1.6.0.0 - Production
 RMAN-06005: connected to target database: HIS (DBID = 3021445076)
 RMAN-06008: connected to recovery catalog database

The second step, the registration database:

  RMAN> register database;
 RMAN-03022:'re compiling command: register
 RMAN-03023: executing command: register
 RMAN-08006: registered in the recovery catalog database RMAN-03023: executing command: full resync
 RMAN-08002: Starting full recovery catalog resync
 RMAN-08004: full resync complete

5.2 cancellation of the target database

The RMAN provides a canceled tools, and called DBMS_RCVCAT Kit, please note that canceled the target database will not be able to use the backup set to restore the database containing the recovery catalog.

In order to be able to cancellation of the database, you need to get database code (DB_ID) and database key (DB_KEY). Connecting to the target database will get DB_ID.

  C :/> rman target internal / oracle @ his catalog rman / rman @ rman
 Recovery Manager: Version 8.1.6.0.0 - Production
 RMAN-06005: connected to target database: HIS (DBID = 3021445076)
 RMAN-06008: connected to recovery catalog database

Dbid = 3021445076 DBID = 3021445076 query the database key code to connect to the target database, query the db table:

  SQL> select * from db;

   DB_KEY DB_ID CURR_DBINC_KEY
   ----------------------------------
   130214450762

Get DB_KEY = so, the target database DB_KEY = 1, DBID = 3021445076, the use of the two values ??use DBMS_RCVCAT Kit can be canceled database:

  SQL> execute dbms_rcvcat.unregisterdatabase (1,3021445076);
 PL / SQL procedure successfully completed.

At this point, the completion of the cancellation of database operations.

6 Operation existing backup

6.1 added to the directory database

Database status:

Recovery catalog: Open

Target database: load or open

If you want to exist the 8.x versions previously created backup data registered to the target database can be used manually added to the recovery catalog

  RMAN> catalog datafilecopy "/ oracle / .... / system01.dbf";

Use the following command to restore the files contained in the directory

  RMAN> list copy of database;

6.2 deleted from the directory database

Step 1: Check the backup information:

  RMAN> list backup;
 RMAN-03022:'re compiling command: list

List of backup sets

  Collection of keyword Recid mark LV tag collection count is completed --------------------------------------- --------------------------------
 110,425,013,644,550 501 364 446 2 06-8 -03

Backup segment list

  Keyword Pc # Cp # state of complete time range --------------------------------------- -------------------------------
 1106 1 1 AVAILABLE 06-8 May -03 D :/ ORACLE/ORA81/DATABASE/02EU4DMU_1_1

Data files include a list of

  File Name LV Type checkpoint SCN checkpoint ---------------------------------------- ------------------------------
    3 D :/ ORACLE/ORADATA/HIS/USERS01.DBF 0 Full 160052 06-8 May -03

Backup set keyword 1104.

Step 2: Define delete channel:

  RMAN> allocate channel for delete type disk;
 RMAN-03022:'re compiling command: allocate
 RMAN-03023: is running the command: ALLOCATE
 RMAN-08030: allocated channel: delete
 RMAN-08500: channel delete: sid = 19 devtype = DISK

The third step: delete backupset backup set

 RMAN> change backupset 1104 delete; RMAN-03022: compile command: change RMAN-08073: deleted the backup segment RMAN-08517: backup piece handle = D :/ ORACLE/ORA81/DATABASE/02EU4DMU_1_1 recid = 2 stamp = 50 1364447 RMAN -03023: executing command: partial resync RMAN-08003: the Start section recovery catalog resync RMAN-08005: partially completed resync

Note: the implementation of Part delete the backup set, backup piece or maintenance recovery catalog command, you need to specify the channel, such as:

  RMAN> allocate channel for delete type disk;

 RMAN> allocate channel for maintenance type disk;

7 in the non-archive mode backup

Database status:

Recovery catalog: Open

Target database: routine is started or database loading

Recovery catalog database need to open the target database must be started (or load). Because the target database is not in archive mode, so the database can not be opened when backup recovery operations. And not tablespace in hot backup mode is equivalent to the copy of the file system level, the state of non-archive mode, open the database data files can not be backed up.

7.1 database backup

  RMAN> run {
    2> # backup the complete database to disk
    3> allocate channel dev1 type disk;
    4> backup
    5> full
    6> tag full_db_backup
    7> format "/ oracle / backups / db_t% t_s% s_p% p"
    (Database);
    The 9> release channel Dev1;
   10>}

Line #
2: indicates a comment line (# comment character)
3 & 9: See section 15 – Channels channel definition
5: Full backup (default if full or incremental not specified) full backup mode (the default)
6: Meaningful string (<= 30 chars) (backup set ID <= 30 characters)
7: filename backup piece file name to use for backup pieces, including Substitution variables can be included in place of the variable.
8: Indicates all files including controlfiles are to be backed up that back up all data files include the control file

Records in the recovery catalog backup set information is displayed by the following command:

  RMAN> list backupset of database;

7.2 Backup table space

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> backup
    4> tag tbs_users_read_only
    5> format "/ oracle / backups / tbs_users_t% t_s% s"
    > (Tablespace users)
    7>}

Use the following command to display the table in the recovery catalog backup information:

  RMAN> list backupset of tablespace users;

Suppose the the USERS tablespace after a backup set to READ ONLY table space, full database backup can not back up the table space can be specified in the backup, in order to achieve this aim, the skip readonly.

Note that the target database does not need to be open, as long as the load can be, because the table space information is stored in the control file.

7.3 backup separate data files

 RMAN> run {2> allocate channel dev1 type "SBT_TAPE"; 3> backup 4> format "% d_% u" 5> (datafile "/ oracle / dbs / sysbigdb.dbf"); 6> release channel dev1; 7> }

Line #
2: use the MML (media manager layer) to allocate the tape drive, you must specify the type SBT_TAPE;

Note that because no identification, identity is empty;

Use the following command to restore the table space backup directory:

  RMAN> list backupset of datafile 1;

7.4 backup data files

  RMAN> run {
    2> allocate channel dev1 type "SBT_TAPE";
    3> copy datafile "/ oracle / dbs / temp.dbf" to "/ oracle / backups / temp.dbf";
    4> release channel Dev1>;
    5>}

Use the following command to restore the copy of the files in the directory:

  RMAN> list copy of datafile "/ oracle / dbs / temp.dbf";

The copy of the data file and the backup data file is not the same, the data file is a copy of the file the mirror. The backup of the file to generate a backup set.

7.5 backup control file

  RMAN> run { 
    2> allocate channel dev1 type "SBT_TAPE";
    3> backup
    4> format "cf_t% t_s% s_p% p"
    5> tag cf_monday_night
    6 (current controlfile);
    7> release channel Dev1>;
    8>}

Note: The full database backup automatic backup control file.

Backup archive mode

Database status:

Recovery catalog: Open

Target database: routine is started, the database is loaded or open

Command and non-archive mode used by the backup operation is basically the same.

8.1. Backing up archived logs backup archive log

The following script backup archive log:

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> backup
    4> format "/ oracle / backups / log_t% t_s% s_p% p"
    5> (archivelog all)
    6> release channel Dev1>;
    7>}

The following script archive log from # 90 to 100:

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> backup
    4> format "/ oracle / backups / log_t% t_s% s_p% p"
    5> (archivelog from logseq = 90 until logseq = 100 thread 1);
    6> release channel Dev1>;
    7>}

The following script backup archive log within 24 hours, will be automatically deleted after the backup is complete archive logs. If the backup fails, the archive logs are not deleted.

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> backup
    4> format "/ oracle / backups / log_t% t_s% s_p% p"
    5> (archivelog from time "sysdate-1" all delete input);
    6> release channel Dev1>;
    7>}

Use the following command to restore the archive log directory:

  RMAN> list backupset of archivelog all;

Note: RMAN find the archive log backup the specified logs, if you can not find the log, it will not return an error message.

8.2. Backing up the online logs up online logs

The online log can not use RMAN to back up, it must be archived.

In order to achieve this, you must execute the following SQL statement: RMAN

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> sql "alter system archive log current";
    4> backup
    5> format "/ oracle / backups / log_t% t_s% s_p% p"
    6> (archivelog from time "sysdate-1" all delete input);
    7> release channel Dev1>;
    8>}

The above script can perform a full online database backup, ensure that all redo log can restore the database to a consistent state.

Note: You can not identify the archive log backup set.

9 incremental backup

N-level incremental backup backs up all changes from the N-level or smaller level since data block content. Incremental backup is divided into two, one is the cumulative incremental backup, a non-cumulative incremental backup.

Cumulative incremental backup includes all changes since the backup data blocks since the last time at the lower level.

Non-cumulative incremental backup from the previous one in the same level or lower-level changes since the backup data block.

9.1 Level 0 – incremental backup strategy is based

Level 0 incremental backup strategy – the basis of the incremental backup strategy

 RMAN> run {2> allocate channel dev1 type disk; 3> backup 4> incremental level 0 5> filesperset 4 6> format "/ oracle/backups/sunday_level0_% t" 7> (database); 8> release channel dev1; 9 >}

Line #
4: 0 backup
5: Define the maximum number of files in each backupset

LIST statement to check the list display of the database backup set, the “type” Incremental “LV” column will display “0”.

9.2 using the incremental backup case

A typical case of incremental backups as follows:
Sunday night – level 0 backup performed
Monday night – level 2 backup performed
Tuesday night – level 2 backup performed
Wednesday evening – level 1 backup performed
Thursday night – level 2 backup performed
Friday night – level 2 backup performed
Saturday night – level 2 backup performed
Sunday night – level 0 backup performed

10 Recovery

Restore the case are as follows:

10.1. Database Open datafile deleted when the database is open, the file is deleted

The data file is deleted when the database is opened. There are two ways to open the database restore: Restore the data file or table space. The following two examples show that the method:

(A) datafile Recovery Data File Recovery

 RMAN> run {2> allocate channel dev1 type disk; 3> sql "alter tablespace users offline immediate"; 4> restore datafile 4; 5> recover datafile 4; 6> sql "alter tablespace users online"; 7> release channel dev1 ;}

(B) the tablespace Recovery tablespace recovery

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> sql "alter tablespace users offline immediate";
    4> restore tablespace users;
    5> recover tablespace users;
    6> sql "alter tablespace users online";
    7> release channel Dev1>;
    8>}

Note: If you restore the system tablespace files, the database must be shut down, because the system table space can not be offline.

10.2. Complete restore (lost online redo) and rollforward – database closed completely restored (loss of an online journal) and roll forward – the database is closed

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> set until logseq = 105 thread = 1;
    4> restore controlfile to "/ oracle / dbs / ctrltargdb.ctl";
    5> replicate controlfile from "/ oracle / dbs / ctrltargdb.ctl";
    6> restore database;
    7> sql "alter database mount";
    > Recover database;
    9> sql "alter database open resetlogs";
   10> release channel Dev1>;
   11>}

Notes:
Set until “specified in the order to return to the specified log file. This point in the data file recovery is very important, otherwise RMAN will attempt to restore the most recent data files, the data files may be specified log before.
Replicate controlfile copy the restored control file INIT.ORA specified control file.

If the database using the WITH RESETLOGS open, you need to use the RESET DATABASE command to change the registration database. After opening the database with the RESETLOGS command is strongly recommended to do a full database backup.

10.3 restore data subset of the files, fully restored

  RMAN> run {
    2> allocate channel dev1 type disk;
    3> sql "alter database mount";
    4> restore datafile 2;
    > Restore datafile 3;
    6> restore archivelog all;
    > Recover database;
    8> sql "alter database open";
    The 9> release channel Dev1;
   10>}

11. Script

Create or replace the script:

  RMAN> create script alloc_disk {
    2> # Allocates one disk
    3> allocate channel dev1 type disk;
    4> setlimit channel dev1 kbytes 2097150 maxopenfiles 32 readrate 200;
    5>}

 RMAN> replace script rel_disk {
    2> # releases disk
    3> release channel Dev1>;
    5>}

 RMAN> replace script backup_db_full {
    2> # Performs a complete backup 
    3> execute script alloc_disk;
    4> backup
    5> ..... 
 
 
 
   
  
  
   6> execute script rel_disk;
   
  
  
   7>} 
 
 

The first two scripts were used channel of distribution and recovery.

alloc_disk script additionally specify the maximum number of megabytes of backup piece, backup can be open at the same time the maximum number of input files, and the maximum number of read data buffer for each input file per second.

The third script calls previously stored two scripts for database backup.

Demonstration to run a stored script:

  RMAN> run {
    2> execute script backup_db_full;
    >}

Note: The the stored script must {…. execute <script>; and ….} command call.

12 Corruption Detection

RMAN can back up data files contain corrupted data block query the view the V $ BACK_CORRUPTION and V $ COPY_CORRUPTION can get information on bad data.

By setting the set maxcorrupt can skip a specified number of bad blocks to avoid the backup fails.

  RMAN> replace script backup_db_full {
    2> # Performs a complete backup
    3> execute script alloc_disk;
    4> set maxcorrupt for datafile 1 to 0;
    5> backup
    6> ..... 
 
 
 
   
  
  
   7> execute script rel_disk;
   
  
  
   8>} 
 
 

At the script maxcorrupt for datafile 1 to 0 is set to 0, so if there is an error data block found in the data file 1 at the script execution will fail.

13 channels.

A channel is a connection between RMAN and the target database, allocate channel command to start a server process in the target database, you must define the server process to perform a backup or restore operation using the I / O type

Channel control command can be used:
Control RMAN to use the O / S resources affect the degree of parallelism limit read rate parameters specify the I / O bandwidth limit value (set)
Define the backup piece size limit (set limit kbytes)
Specify the current open file limit value (set limit the MaxOpenFiles),

14. Report & list commands

14.1 List

The list command queries the recovery catalog and produce formatted query:

  RMAN> list backupset of datafile 1; 
  
   Key File Type LV Completion_time Ckp SCN Ckp Time 
   -------------------------------------------------- -------- 
   165 1 Full Oct 03 11:24 32022 Oct 03 11:24 
   208 1 Full Oct 24 14:27 52059 Oct 24 14:26 
   219 1 Full Oct 24 14:31 52061 Oct 24 14:31 
   << Other entries here >> 
  
   RMAN> list backupset of archivelog all; 
  
   Key Thrd Seq Completion time 
   --------------------------------- 
   179 1 94 Oct 03 11:26 
   179 1 95 Oct 03 11:26 
   << Other entries here >>

14.2. Report

REPORT command can query the same recovery catalog, but REPORT command syntax can build more useful information instruction REPORT command output can be saved to the message log file, but must be connected to recovery catalog to specify MSGLOG or LOG option.

The list of files that can list all the database can not be restored:

  RMAN> report unrecoverable database;

Show all data files:

 RMAN> report schema; RMAN-03022:'re compiling command: report database schema report file kilobytes table space RB segs Name 1 121472 SYSTEM YES D :/ ORACLE/ORADATA/HIS/SYSTEM01.DBF 2 327680 RBS YES D :/ ORACLE / ORADATA/HIS/RBS01.DBF 3 110592 USERS NO D :/ ORACLE/ORADATA/HIS/USERS01.DBF 4 73728 TEMP NO D :/ ORACLE/ORADATA/HIS/TEMP01.DBF 5 12288 TOOLS NO D :/ ORACLE / ORADATA / HIS/TOOLS01.DBF 6 59392 INDX NO D :/ ORACLE/ORADATA/HIS/INDX01.DBF

15 Tips

Often synchronized recovery catalog

To confirm delete unwanted archive log

Backup strategy simple principles

Posted by databasesql

Oracle 11g officially released

Oracle 11g conference held in New York City on July 11, 2007 11:00 EDT (Beijing time at 22:00 on the 11th), 11g is Oracle 30 years to publish the most important version of the database, according to the needs of users Information Lifecycle Management (Information Lifecycle Management) innovation. A substantial increase in system performance security, the Data Guard maximize availability, the use of new advanced data compression technology to reduce data storage expenses significantly shorten the time it takes for the application test environment deployment and analysis of test results, an increase of RFID Tag, DICOM medical images, 3D space, and other important data type support, strengthen on Binary XML support and performance optimization.

Posted by databasesql

NET in a variety of database connection Daquan reprint Chendy the

SQL Server
  • ODBC
    • Standard Security:
      “Driver = {SQL Server}; Server = Aron1; Database = pubs; Uid = sa; Pwd = asdasd;”
    • Trusted Connection:
      “Driver = {SQL Server}; Server = Aron1; Database = pubs; Trusted_Connection = yes;”
    • Prompt for username and password:
      oConn.Properties (“Prompt”) = adPromptAlways
      oConn.Open “Driver = {SQL Server}; Server = Aron1; DataBase = pubs;”
  • OLEDB, OleDbConnection (. NET)
    • Standard Security:
      “Provider = sqloledb; Data Source = Aron1; Initial Catalog = pubs; User Id = sa; Password = asdasd;”
    • Trusted Connection:
      “Provider = sqloledb; Data Source = Aron1; Initial Catalog = pubs; Integrated Security = SSPI;”
(Use serverName / instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
    • Prompt for username and password:
      oConn.Provider = “sqloledb”
      oConn.Properties (“Prompt”) = adPromptAlways
      oConn.Open “Data Source = Aron1; Initial Catalog = pubs;”
    • Connect via an IP address:
      “Provider = sqloledb; Data Source = 190.190.200.100,1433; Network Library = DBMSSOCN; Initial Catalog = pubs; User ID = sa; Password = asdasd;”
(DBMSSOCN = TCP / IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
  • The SqlConnection (NET)
    • Standard Security:
      “Data Source = Aron1; Initial Catalog = pubs; User Id = sa; Password = asdasd;”
    • Trusted Connection:
      “Data Source = Aron1; Initial Catalog = pubs; Integrated Security = SSPI;”
(Use serverName / instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
    • Connect via an IP address:
      “Data Source = 190.190.200.100,1433; Network Library = DBMSSOCN; Initial Catalog = pubs; User ID = sa; Password = asdasd;”
(DBMSSOCN = TCP / IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
    • Declare the SqlConnection:
C #:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection ();
oSQLConn.ConnectionString = “my connectionstring”;
oSQLConn.Open (); 

VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection ()
oSQLConn.ConnectionString = “my connectionstring”
oSQLConn.Open ()

  • Data Shape
    • MS Data Shape
      “Provider = MSDataShape; Data Provider = SQLOLEDB; Data Source = Aron1; Initial Catalog = pubs; User ID = sa; Password = asdasd;”
  • Read more
    • How to define wich network protocol to use
      • Example:
        “Provider = sqloledb; Data Source = 190.190.200.100,1433; Network Library = DBMSSOCN; Initial Catalog = pubs; User ID = sa; Password = asdasd;”
Name
Network Library
dbnmpntw
Win32 Named Pipes
dbmssocn
Win32 Winsock TCP / IP
dbmsspxn
Win32 SPX / IPX
dbmsvinn
Win32 Banyan Vines
dbmsrpcn
The win32 Multi-Protocol (Windows RPC)
      • Important note!
        When connecting through the SQLOLEDB provider use the syntax Network Library = dbmssocn
        and when connecting through MSDASQL provider use the syntax Network = dbmssocn
    • All SqlConnection connectionstring properties
      • This table shows all connectionstring properties for the ADO.NET SqlConnection object. Most of the properties are also used in ADO. All properties and descriptions is from msdn.
Name
Default
Description
Application Name
The name of the application, or ‘. Net SqlClient Data Provider’ if no application name is provided.
AttachDBFilename
-Or-
Extended Properties
-Or-
Initial File Name
The name of the primary file, including the full path name, of an attachable database. The database name must be specified with the keyword ‘database’.
Connect Timeout
-Or-
Connection Timeout
15
The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Connection Lifetime
0
When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by connection lifetime. Useful in clustered configurations to force load balancing between a running server and a server just brought on-line.
Connection Reset
‘True’
Determines whether the database connection is reset when being removed from the pool. Setting to ‘false’ avoids making an additional server round-trip when obtaining a connection, but the programmer must be aware that the connection state is not being reset.
Current Language
The SQL Server Language record name.
Data Source
-Or-
Server
-Or-
Address
-Or-
Addr
-Or-
Network Address
The name or network address of the instance of SQL Server to which to connect.
Enlist
‘True’
When true, the pooler automatically enlists the connection in the creation thread’s current transaction context.
Initial Catalog
-Or-
Database
The name of the database.
Integrated Security
-Or-
Trusted_Connection
‘False’
Whether the connection is to be a secure connection or not. Recognized values ??are ‘true’, ‘false’, and ‘sspi’, which is equivalent to ‘true’.
Max Pool Size
100
The maximum number of connections allowed in the pool.
Min Pool Size
0
The minimum number of connections allowed in the pool.
Network Library
-Or-
Net
‘Dbmssocn’
The network library used to establish a connection to an instance of SQL Server. Supported values ??include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (Shared Memory) and dbmsspxn (IPX / SPX) and dbmssocn (TCP / IP).
The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, “.” Or “(local)”), shared memory is used.
Packet Size
8192
Size in bytes of the network packets used to communicate with an instance of SQL Server.
Password
-Or-
Pwd
The password for the SQL Server account logging on.
Persist Security Info
‘False’
When set to ‘false’, security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values ??including the password.
Pooling
‘True’
When true, the SQLConnection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool.
User ID
The SQL Server login account.
Workstation ID
the local computer name
The name of the workstation connecting to SQL Server.
      • Note
        Use; to separate each property.
        If a name occurs more than once, the value from the last one in the connectionstring will be used.
        If you are building your connectionstring in your app using values ??from user input fields, make sure the user can’t change the connectionstring by inserting an additional property with another value within the user value.
? Access
  • ODBC
    • Standard Security:
      “Driver = {Microsoft Access Driver (*. Mdb)}; Dbq = / somepath / mydb.mdb; Uid = Admin; Pwd = asdasd;”
    • Workgroup:
      “Driver = {Microsoft Access Driver (*. Mdb)}; Dbq = / somepath / mydb.mdb; SystemDB = / somepath / mydb.mdw;”, “admin”, “”
  • OLEDB, OleDbConnection (. NET)
    • Standard security:
      “Provider = Microsoft.Jet.OLEDB.4.0; Data Source = / somepath / mydb.mdb; User Id = admin; Password = asdasd;”
    • Workgroup (system database):
      “Provider = Microsoft.Jet.OLEDB.4.0; Data Source = / somepath / mydb.mdb; Jet OLEDB: System Database = system.mdw;”, “admin”, “”
    • With password:
      “Provider = Microsoft.Jet.OLEDB.4.0; Data Source = / somepath / mydb.mdb; Jet OLEDB: Database Password = MyDbPassword;”, “admin”, “”
? Oracle
  • ODBC
    • New version:
      “Driver = {Microsoft ODBC for Oracle}; Server = OracleServer.world; Uid = Username; Pwd = asdasd;”
    • Old version:
      “Driver = {Microsoft ODBC Driver for Oracle}; ConnectString = OracleServer.world; Uid = myUsername; Pwd = myPassword;”
  • OLEDB, OleDbConnection (. NET)
    • Standard security:
      “Provider = msdaora; Data Source = MyOracleDB; User Id = UserName; Password = asdasd;”
This one’s from Microsoft, the following are from Oracle
    • Standard Security:
      “Provider = OraOLEDB.Oracle; Data Source = MyOracleDB; User Id = Username; Password = asdasd;”
    • Trusted Connection:
      “Provider = OraOLEDB.Oracle; Data Source = MyOracleDB; OSAuthent = 1;”
  • The OracleConnection (NET)
    • Standard:
      “Data Source = Oracle8i; Integrated Security = yes”;
This one works only with Oracle 8i release 3 or later
    • Declare the OracleConnection:
C #:
using System.Data.OracleClient;
OracleConnection oOracleConn = new OracleConnection ();
oOracleConn.ConnectionString = “my connectionstring”;
oOracleConn.Open (); 

VB.NET:
Imports System.Data.OracleClient
Dim oOracleConn As OracleConnection = New OracleConnection ()
oOracleConn.ConnectionString = “my connectionstring”
oOracleConn.Open ()

  • Data Shape
    • MS Data Shape:
      “Provider = MSDataShape.1; Persist Security Info = False; Data Provider = MSDAORA; Data Source = orac; user id = username; password = mypw”
? MySQL
  • ODBC
    • Local database:
      “Driver = {mySQL}; Server = mySrvName; Option = 16834; Database = mydatabase;”
    • Remote database:
      “Driver = {mySQL}; Server = data.domain.com; Port = 3306; Option = 131072; Stmt =; Database = my-database; Uid = username; Pwd = password;”
  • OLEDB, OleDbConnection (. NET)
    • Standard:
      “Provider = MySQLProv; Data Source = mydb; User Id = UserName; Password = asdasd;”
  • The MySqlConnection (NET)
    • EID:
      “Data Source = server; Database = mydb; User ID = username; Password = pwd; Command Logging = false”
This one is used with DbProvider, an add-on to. NET
    • Declare the MySqlConnection:
C #:
using EID.MySqlClient;
MySqlConnection oMySqlConn = new MySqlConnection ();
oMySqlConn.ConnectionString = “my connectionstring”;
oMySqlConn.Open (); 

VB.NET:
Imports EID.MySqlClient on
Dim oMySqlConn As MySqlConnection = New MySqlConnection ()
oMySqlConn.ConnectionString = “my connectionstring”
oMySqlConn.Open ()

? IBM DB2
  • OLEDB, OleDbConnection (. NET) from ms
    • TCP / IP:
      “Provider = DB2OLEDB; Network Transport Library = TCPIP; Network Address = XXX.XXX.XXX.XXX; Initial Catalog = MyCtlg; Package Collection = MyPkgCol; Default Schema = Schema; User ID = MyUser; Password = MyPW”
    • APPC:
      “Provider = DB2OLEDB; APPC Local LU Alias ??= MyAlias; APPC Remote LU Alias ??= MyRemote; Initial Catalog = MyCtlg; Package Collection = MyPkgCol; Default Schema = Schema; User ID = MyUser; Password = MyPW”
? Sybase
  • ODBC
    • Standard:
      “Driver = {SYBASE SYSTEM 11}; Srvr = Aron1; Uid = username; Pwd = password;”
    • Intersolv 3.10:
      “Driver = {INTERSOLV 3.10 32-BIT Sybase}; Srvr = Aron1; Uid = username; Pwd = password;”
? Informix
  • ODBC
    • Informix 3.30:
      “Dsn =”; Driver = {INFORMIX 3.30 32 BIT}; Host = hostname; Server = myserver; Service = service-name; Protocol = olsoctcp; Database = mydb; UID = username; PWD = myPwd
    • Informix-CLI 2.5:
      “Driver = {Informix-CLI 2.5 (32 Bit)}; Server = myserver; Database = mydb; Uid = username; Pwd = myPwd”
? DSN
  • ODBC
    • DSN:
      The DSN = MyDSN; uid = username; PWD =;
    • File DSN:
      “FILEDSN = c :/ myData.dsn; Uid = username; Pwd =;”
? Excel
  • ODBC
    • Standard:
      “Driver = {Microsoft Excel Driver (*. Xls)}; DriverId = 790; Dbq = C :/ MyExcel.xls; DefaultDir = c :/ mypath;”
  • OLE DB
    • Standard:
      “Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C :/ MyExcel.xls; Extended Properties = Excel 8.0; HDR = Yes;”
“HDR = Yes;” indicates that the first row contains columnnames, not data
? DBF / FoxPro
  • ODBC
    • standard:
      “Driver = {Microsoft dBASE Driver (*. Dbf)}; DriverID = 277; Dbq = c :/ mydbpath;”
  • OLEDB, OleDbConnection (. NET)
    • standard:
      “Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c :/ folder; Extended Properties = dBASE IV; User ID = Admin; Password =”
? Pervasive
  • ODBC
    • Standard:
      “Driver = {Pervasive ODBC Client Interface}; ServerName = srvname; dbq = @ dbname”
? UDL
  • UDL
    • UDL:
      “File Name = c :/ myDataLink.udl;”
    • Trackback:
Posted by databasesql

[R] Hibernate

Hibernate

Hibernate is an open source object-relational mapping framework, it very lightweight JDBC object package so that Java programmers can use arbitrary object-oriented programming thinking to manipulate the database. Hibernate can be applied to any occasions using JDBC, either in the Java client program can also be used in the Servlet / JSP Web application, most revolutionary, Hibernate can replace the CMP in the application EJB J2EE architecture completion of data persistence task.

Introduction to the core interface

Hibernate core interface, a total of five, namely: the Session, SessionFactory, Transaction, Query and Configuration. The five core interfaces are used in any development. These interfaces can not only access to persistent objects, but also to transaction control. Following these five core interfaces were to be introduced.

Session interface is responsible for the implementation of persistent object CRUD operations (CRUD task is to complete the communication with the database contains a lot of common SQL statements). Note that Session is not thread safe. Hibernate session is different from the HttpSession in JSP applications. When the term of the session, in fact, refers to the Hibernate session, after will HttpSesion object called a user session.

SessionFactory interface:

SessionFactory interface is responsible for the initialization Hibernate. It acts as a source of data storage agent, and is responsible for creating the Session object. Here used in the factory pattern. Note that SessionFactory is not lightweight, because under normal circumstances, a project usually need only a SessionFactory enough, when the need to operate more than one database can be specified for each database a the SessionFactory.

Configuration interface:

Configuration interface is responsible for configuration and startup Hibernate to create a SessionFactory object. In the the Hibernate startup process, the Configuration instance of the class first position to map the location of the document, read the configuration, and then create the SessionFactory object.

Transaction interface:

Transaction interface is responsible for affairs related operations. It is optional, developers can design and write your own underlying transaction code.

ยท Query and Criteria Interface: Query and Criteria interface is responsible to perform a variety of database queries. It can be used either expression of the the HQL language or SQL statement.

Hibernate primary key

Assigned

Assigned by the user-generated primary key value, and to specify an exception is thrown before save ()

Features: the generation of the primary key value is completely determined by the user, regardless of the underlying database. Users need to maintain the primary key value in the before calling session.save () to specify the primary key value.

Hilo

Hilo high and low bit algorithm to generate the primary key, high and low bit algorithm uses a high value and a low value, and then the two values ??obtained by the algorithm stitching together as only the primary key in the database. Hilo requires additional database tables and fields to provide high value source. Please state by default table

hibernate_unique_key default field called next_hi. next_hi must have a record otherwise an error occurs.

Features: the need for additional support of the database table, can guarantee the same database primary key only, but can not guarantee the uniqueness of the primary key between multiple databases. The the Hilo primary key generated by Hibernate maintenance, Hilo has nothing to do with the underlying database, but should not manually modify the hi / lo algorithm to use the value of the table, otherwise it will cause duplicate primary key exception.

Increment

Increment primary key value automatically growth to generate a new primary key value, but requires that the underlying database supports Sequence. Such as Oracle, DB2, and so on. Need to mapping file xxx.hbm.xml, joined Increment identifiers settings.

Features: Hibernate maintenance, suitable for all database is not suitable for the more complicated process to update the database, suitable for a single process to access the database. Can not be used in a clustered environment.

Identity

Identity was based on the underlying database to support growth in different databases with different primary key growth mode.

The Features: associated with the underlying database, database support IDENTITY, such as MySQL is AUTO_INCREMENT in SQL Server is Identity supported database MySQL, SQL Server, DB2, Sybase and HypersonicSQL or. Identity without the Hibernate user intervention, more convenient to use, but not easy to transplant procedures between different databases.

Sequence

Sequence Sequence mode requires that the underlying database support, such as Oracle Database

Features: need support serialization of the underlying database, DB2, PostgreSql, Qracle, SAPDb between different databases transplant program in support of the sequence database, especially from the sequence database support transplanted to the database does not support serialization need to modify the configuration file

Native

Native primary key generated based on different underlying database primary key generated automatically selects Identity, Sequence, Hilo

Features: Depending on the underlying database using different primary key generation methods. Hibernate will use different mapping based on the underlying database, thus facilitating the transplant procedure, the project If more than one database, you can use this way.

UUID

UUID using 128-bit UUID algorithm to generate the primary key to ensure that the primary key in the network environment only, will be able to guarantee the uniqueness of the primary key in the different databases and different servers.

Features; to ensure that the primary key in the database only to generate the primary key of take up more storage space

Foreign GUID

Foreign for one-to-one relationship. GUID primary key generated using a special algorithm to generate unique primary keys, support for SQL Server and MySQL

Hibernate source in the role of several packages brief introduction

net.sf.hibernate. *

The class of the package are basically interface class and exception classes

net.sf.hibernate.cache. *

JCS implementation class

net.sf.hibernate.cfg. *

Configuration file reading class

net.sf.hibernate.collection. *

The the Hibernate collection interface implementation class, such as List, Set, Bag and so on, Hibernate reason to write your own collection interface implementation class to support lazy loading

net.sf.hibernate.connection. *

Provider of several

net.sf.hibernate.dialect. *

Support a variety of database features, each Dialect achieve class represents a database, the database data types supported and other features, such as whether there AUTOINCREMENT whether there Sequence, paging SQL

net.sf.hibernate.eg. *

The Hibernate documentation used in the example

net.sf.hibernate.engine. *

This package class role is more casual

net.sf.hibernate.expression. *

HQL supports the expression

net.sf.hibernate.hq. *

HQL implementation

net.sf.hibernate.id. *

ID generator

net.sf.hibernate.impl. *

The core of the package, some important interface implementation class, if the Session, SessionFactory, Query, etc.

net.sf.hibernate.jca.

JCA support, packaging Session interface implementation class support JCA

net.sf.hibernate.jmx. *

I do not know JMX JMX is used to write the App Server hypervisor, is probably the JMX part of the implementation of the interface, making the App Server can be managed via the JMX interface Hibernate

net.sf.hibernate.loader. *

Is also the core of the package, mainly generated SQL statement

net.sf.hibernate.lob. *

BLOB and CLOB support

net.sf.hibernate.mapping. *

the hbm file attributes achieve

net.sf.hibernate.metadata.

Meta implementation of PO

net.sf.hibernate.odmg. *

ODMG an ORM standard, this package is the the ODMG standard implementation class

net.sf.hibernate.persister. *

Core package to achieve lasting mapping between objects and tables

net.sf.hibernate.proxy. *

Proxy and Lazy Loading support

net.sf.hibernate.ps. *

The package is PreparedStatment Cache

net.sf.hibernate.sql. *

Generate the the JDBC sql statement the package

net.sf.hibernate.test. *

Test class, you can use JUnit to test Hibernate

net.sf.hibernate.tool.hbm2ddl.

HBM configuration file generated DDL

net.sf.hibernate.transaction. *

The Hibernate Transaction achieve class

net.sf.hibernate.type. *

Hibernate persistent objects defined data types of properties

net.sf.hibernate.util. *

Some tools, the role of scattered

net.sf.hibernate.xml. *

XML data binding

Cache Manager

Hibernate provides two Cache, first-level cache is a cache of the Session level, it is a transaction-scoped cache. This level cache hibernate management, under normal circumstances without the need for intervention; second level cache SessionFactory-level cache, it belongs to the scope of the process or cluster-wide cache. This level of cache can be configured and change, and can be dynamically loaded and unloaded. Hibernate query results of a query cache, it relies on the second-level cache.

1 cache and secondary cache comparison:

The range of the first-level cache second level cache to store data in the form of interrelated bulk data cache of persistent objects object scope of the transaction, each transaction has a separate range of first-level cache process or cluster scope cache is the same process all transactions within or cluster-wide shared concurrent access strategy, as each transaction has a separate first-level cache, does not appear concurrency issues, without having to provide concurrent access strategy the same data multiple transactions simultaneously accessing the second-level cache, must provide appropriate concurrent access strategy, to ensure that the specific transaction isolation level data expiration policies did not provide data expiration policies. Object in a cache never expires, unless the application explicitly clear the cache or clear specific object must provide data expiration policies, such as the maximum number of objects in the cache memory, allowing the object is in the cache the longest time, and allows the objects in the cache the longest idle time physical storage media memory memory and hard disk. The bulk of the object data is first stored in the memory-based cache to the specified limit, when the number of objects in memory of the data expires policy, will the rest of the object is written to the hard disk-based cache. Software implementation of cache cache implementations provided by third parties are included in the implementation of the Hibernate Session to the Hibernate only provide a cache adapter (the cacheProvider). Specific caching plugin integrated into Hibernate. Cache enabled application through the Session interface to execute the save, update, delete, load the operating data and query the database, Hibernate will enable first-level cache copy of the data in the database in the form of objects into the cache, bulk update and bulk delete operations, if you do not want the first-level cache is enabled, you can bypass the Hibernate API directly through JDBC API to perform the operation. Users can configure the second-level cache on the granularity of a single class or a single collection. If the instance of the class is often read but rarely modified, you can consider the use of the second-level cache. Only for a class or collection of the second-level cache is configured, Hibernate will only run an instance of it to the second-level cache. User Management cache physical media of the first-level cache memory, limited memory capacity, must pass the appropriate search strategy and retrieval methods to limit the load object number. , Evit of () method of the Session can explicitly clear the cache object, but this method is not recommendable. The physical medium of the second-level cache memory and hard drive, so the second-level cache can store large amounts of data, data expiration policies maxElementsInMemory attribute values ??can control the number of objects in memory. The second-level cache management mainly includes two aspects: Select the desired use of the second-level cache of persistent classes, set the appropriate concurrent access strategy: Choose a cache adapter, set the appropriate data expiration policies.

2 level cache management:

When the application calls the session save (), update (), the savaeOrUpdate (), get () or load (), and calls the query interface list (), iterate () or filter () method in the Session cache does not yet exist corresponding object, Hibernate will put the object is added to the first-level cache. When to clear the cache, Hibernate will change according to the state of the object cache to synchronize to update the database. Session for the application provides two cache: evict (Object obj): purged from the cache parameters specified persistent object. clear (): empty the cache persistent objects.

3 second-level cache management:

3.1 Hibernate’s second-level cache strategies general process is as follows:

1) criteria query always issues a select * from table_name where …. (all fields) so that the SQL statement to query the database once to get all the data objects.

2) get all data objects based on the ID into the second-level cache.

3) When Hibernate ID to access the data object when the first check from the Session level cache; finding out, if the configuration of the secondary cache, then the check from the second-level cache; finding out, and then query the database, the results into the ID to the cache.

4) delete, update, increase data, updating the cache at the same time.

Hibernate second-level cache strategy is for ID query caching strategy, there is no role for criteria query. To this end, Hibernate provides the Query Cache Query for the conditions.

3.2 What kind of data is suitable for storing the second-level cache? 1 is rarely modified data is not very important data, allowing occasionally concurrent data will not be concurrent access to data reference data refers to the supply with reference constant data, the limited number of instances, it instance referenced by many instances of other classes, instances rarely or never been modified.

3.3 is not suitable for storing data to the second level cache? 1 frequently modified data financial data, and definitely not concurrent shared data with other applications.

3.4 commonly used the caching plugin Hibernater secondary cache is a plug-in, the following are several commonly used caching plugin:

l EhCache: can be used as a process-wide cache, data storage physical media can be memory or hard disk, and provides support for Hibernate query cache.

l OSCache: as a process-wide cache, data storage physical media can be memory or hard disk, provides a rich cache data expiration policies, and provides support for Hibernate query cache.

l SwarmCache: as a cache cluster-wide, but does not support the Hibernate query cache.

l JBossCache: can be used as a cache cluster-wide to support transactional concurrent access policy, and provides support for Hibernate query cache.

3.5 Configuration of the main steps of the second-level cache:

1) Select the persistent classes need to use the second-level cache, set its name cache concurrent access strategy. This step is most worthy of serious consideration.

2) Select the appropriate cache plug-ins, and then edit the configuration file for the plug-in.

Hibernate lazy loading

Hibernate object-relational mapping object initialization delay and non-delay. Non- will be read together with the object of all other objects when reading an object. This can sometimes lead to hundreds (if not thousands) SELECT statement is executed in the read object. This problem sometimes occurs when using the two-way relationship, often resulting in the entire database initialization phase is read out. Of course, you can take the trouble to check the relationship of each object with other objects, and the most expensive deleted, but in the end, we may lose wanted to get ORM tool of convenience.

An obvious solution is to use Hibernate’s lazy loading mechanism. Initialization strategy that targets only one call it one-to-many or many-to-many relationship when the relationship between object read. This process is transparent to developers, and only a few requests for database operations, and would therefore be more significant performance gains. A defect of this technology is to delay the loading technique requires a Hibernate Session object has been open. This will become a major problem in the abstract persistence layer out by using the DAO pattern. The persistence mechanism completely abstract, logical database, including open or close the session, not at the application layer. The most common is the simple interface DAO implementation class database logic package up. A fast but clumsy solution is to abandon the DAO mode, the database connection logic to the application layer. This may be effective for some small applications, but in large systems, this is a serious design flaw that prevents the scalability of the system.

In the Web tier lazy loading

Fortunately, the Spring framework Hibernate lazy loading with DAO integration provides a convenient solution. A Web application, for example, the the Spring provide OpenSessionInViewFilter and OpenSessionInViewInterceptor. We can choose a class to achieve the same functionality. The only difference of the two methods is that the interceptor in the Spring container is running and configured in the web application’s context, filter before the Spring running and configured in web.xml. No matter what, they are open Hibernate session request the current session with the current thread-bound (database). Once bound to the thread, this open Hibernate session in DAO implementation class to use transparent. This session will delay loading values ??in the database object view remains open. Once the logical view, Hibernate session will be closed in the doFilter method of the Filter, or Interceptor postHandle method.

Achieve added in web.xml

<filter>

<filter-name> hibernateFilter </ filter-name>

<filter-class>

org.springframework.orm.hibernate3.support.OpenSessionInViewFilter

</ Filter-class>

</ Filter

<filter-mapping>

<filter-name> hibernateFilter </ filter-name>

<url-pattern> *. do </ url-pattern>

</ Filter-mapping>

Session interface:

Posted by databasesql

SQL on the lower level Tandem Operations trigger

/ *
1. For example, an employee number, to be able to query the information of the employee’s supervisor and indirect superior cascade query.
2. For example, an employee number to check out the employee’s subordinates and indirect subordinates cascade query.
3. Cascade delete. For example, specifying an employee number, all subordinate staff were all deleted when delete the employee’s basic information. Whether superiors or subordinates, including staff.
4. Cascading updates. When an employee’s job number is changed, all the employees immediate supervisor the staff reportto field values ??into a new job number.
* /

create table emp
(
eid int primary key,
ename varchar (20),
sal money,
reportto int references emp (eid) – the employee’s direct supervisor)
– Drop table emp
insert into emp select 1001, ‘rain’, 1000, null
union select 1002, ‘ann’, 3000,1001
union select 1003, ‘lopez’, 2000,1001
union select 1004, ‘nakata’, 3000,1002
union select 1005, ‘tae’, 1500,1004
union select 1006, ‘raul’, 900,1004
union select 1007, ‘owen’, 15000,1006
– Select * from emp
/ * Cascade query For example, an employee number, to check out the employee’s supervisor and indirect superior. * /
create proc p1 @ eid int
as
begin
declare @ re table (eid int, level int)
declare @ l int
set @ l = 0
insert @ re select @ eid, @ l
while @ @ rowcount> 0
– Global variable @ @ rowcount, recording the last operation affects the number of rows begin
set @ l = @ l +1
insert @ re select a.reportto, @ l from emp as a, @ re as b where a.eid = b.eid and b.level = @ l-1
– Loop current record directly subordinate insert @ re
end
select a. * from @ re as b inner join emp as a on a.eid = b.eid
end
– Drop proc p1
exec p1 1005

/ * Cascade query For example, an employee number, be able to query the employee’s subordinates and indirect subordinates. * /
create proc p2 @ eid int
as
begin
declare @ re table (eid int, level int)
declare @ l int
set @ l = 0
insert @ re select @ eid, @ l
while @ @ rowcount> 0
– Global variable @ @ rowcount, recording the last operation affects the number of rows begin
set @ l = @ l +1
insert @ re select a.eid, @ l from emp as a, @ re as b where a.reportto = b.eid and b.level = @ l-1
– Cycle the immediate superior of the current record into @ re
end
– Select * from @ re
select a. * from @ re as b inner join emp as a on a.eid = b.eid
end
– Drop proc p2
exec p2 1004

/ * Cascade delete. * /
– Specify, for example, an employee number, all subordinate staff were all deleted when the employee is removed – the basic information. No matter – the higher or lower levels, including staff.
create proc p3 @ eid int
as
begin
delete from emp where eid = @ eid
end
– Drop proc p3

create trigger emp_del
on emp instead of delete
as
begin
declare @ eid int
if (not exists (select * from sysobjects where name = ‘del’))
begin
select * into del from emp where 1> 2
end
– Such as the trigger for the first time you run, create del table insert into del select * from deleted
declare cr cursor scroll for select eid from del
open cr
fetch last from cr into @ eid

declare @ re table (eid int, level int)
declare @ l int
set @ l = 0

close cr
deallocate cr
insert @ re select @ eid, @ l
while @ @ rowcount> 0
– Global variable @ @ rowcount, recording the last operation affects the number of rows begin
set @ l = @ l +1
insert @ re select a.eid, @ l from emp as a, @ re as b where a.reportto = b.eid and b.level = @ l-1
– Loop current record directly subordinate insert @ re
end
– Select * from @ re
– Select a. * From @ re as b inner join emp as a on a.eid = b.eid
– Delete emp where eid in (select eid from @ re)
set @ l = (select max (level) from @ re)
– The most subordinate level value assigned to _AT_ l
while @ l> = 0
begin
delete emp from emp as a inner join @ re as b on a.eid = b.eid and b.level = @ l

set @ l = @ l-1
– Delete records from the lowest level bottom-up, otherwise it will be a foreign key constraint to terminate the end
end
– Drop trigger emp_del
exec p3 1002
select * from emp

/ * Cascading updates. When an employee’s job number is changed, all the employees immediate supervisor the staff reportto field values ??into a new job number.
* /
create proc p4 @ eid int, @ newid int
– The staff number _AT_ eid modify _AT_ NEWID modify the employee number as
begin
update emp set eid = @ newid where eid = @ eid

end
– Drop proc p4

create trigger emp_update
on emp instead of update
as
begin
if (update (eid))
begin
declare @ eid int, @ newid int
– Employee number _AT_ eid modify, the staff after _AT_ NEWID modified if (not exists (select * from sysobjects where name = ‘olddata’))
begin
select * into olddata from emp where 1> 2
end
if (not exists (select * from sysobjects where name = ‘newdata’))
begin
select * into newdata from emp where 1> 2
end
insert into olddata select * from deleted
insert into newdata select * from inserted
declare cr1 cursor scroll for select eid from olddata
open CR1
fetch last from cr1 into @ eid
declare cr2 cursor scroll for select eid from newdata
open CR2
fetch last from cr2 into @ newid

close cr1
deallocate cr1
close CR2
deallocate cr2
rollback
– Back to back, or the emp table will be a new record for the duplicate key … do not know the specific principles, Mongolian out – select * from emp
insert into emp select * from newdata where eid = @ newid
– Insert a new record, the same with the updated records update emp set reportto = @ newid where reportto = @ eid
– Be directly subordinate superiors updated with the new record delete emp where eid = @ eid
– Delete the original recording end
end
– Drop trigger emp_update
– Select * from newdata
exec p4 1004,1008
– Parameter 2 for the employee number does not exist in the original table, avoid duplicate key select * the FROM emp

Posted by databasesql

Database index principle

1 Index:
Index is similar to the directory of the book, is mainly used to improve query efficiency, according to the conditions query, the first query the index, and then through the index to find the relevant data, the index is equivalent to recorded a keyword assigned to a different file or file Lane different positions, of course, the index itself is through the file saved.
(2) the type of index There are two basic index structure is a sequential index is sorted in accordance with the order of values ??(the value of the file inside, that is, its construction of the index field values, the order on the index file inside ), a hash index is the value assigned to the average number of hash buckets, positioning through a hash function.
2.1 The sequential index being indexed according to a certain order, then such an index called the clustered index. Otherwise known as non-clustered index.
If the field is indexed each has an index value corresponding thereto, then this index is called dense index, otherwise called a sparse index.
Order index is divided into two categories, single-stage index (do not use) and multi-level index (usually a B + tree, heavy use).
Single-stage index all index fields and the corresponding file location arranged according to the order, such an index to find them more slowly, because it is stored in the order, you can use binary search, but overall efficiency is not high, This index is the most basic index, generally do not have, the Oracle there does not seem to support such an index.
Multi-level index is actually above the the coupled index (sparse index) in a single-stage index, that is, the index pointing to the index, secondary index also be coupled at the tertiary index can add, add to the last uppermost only one node (the root node), it becomes a tree.
We often hear the concept of the B + tree is almost The purpose of this tree and red-black tree, but also to try to keep the balance of the tree, of course, the red-black tree is a binary tree, but the B + tree is not a binary tree below the node can have multiple children node, database developers will set a maximum number of child nodes, this value is not too small, so the B + tree in general relatively chunky, lanky red-black tree.
About B + tree insertion, deletion, will involve a number of algorithms to maintain the balance of the tree, not detailed here. ORACLE default index of this structure.
If you often need two fields at the same time AND query, then use two separate index is better to create a composite index, usually two separate index database can only use one of the composite index because the index itself corresponds to two fields on the efficiency will be greatly improved.
2.2 hash index
The second index is called the hash index is an index hash function to locate, but rarely used alone hash index, but is hash file organization with more.
Hash file organization is based on a key through a hash calculation corresponding records are placed in the same tank, in which case the same key corresponding record must be in the same file, thus reducing the file read the frequency and to improve the efficiency.
Hash index is based on the hash code of the corresponding key to find the final index entries, in fact, almost the same, and the B-tree, which is an index on the two secondary indexes, I understand hash index sparse index of level or higher, or the barrel too much, efficiency is not high.
2.3 bitmap index
Bitmap index is a special kind of index of the the simple query design one for more than one field, the scope is relatively small, only applies to the field value is fixed and the kind of values ??that a few, such as gender, only male and female, or level, status, etc., and only at the same query on multiple fields in order to realize the advantages of a bitmap.
The basic idea of the bitmap of each condition with a 0 or 1 to indicate if there are five records, sex is male, female, male, male, female, If you use a bitmap index creates two bitmaps corresponding male 01001 of 10110 and the corresponding woman, doing what good is it, that is the same time, more of this type of field and or or query, you can use the results bit by bit or to direct get .

Summary:
B + tree most commonly used, the performance is not bad, for a range query and single-valued query. In particular range queries have to use the B + tree in this order it.
HASH if only single-valued query words faster than B + tree a little faster, but Oracle does not seem to support the HASH index only supports the HASH table space.
Bitmap usage is very limited, only a few can be used to determine the true for using this index (value type is small and requires complex queries), or to establish a lot of bitmap on the significance Nothing.

Posted by databasesql

Err.Description

We generally debugger, some inexplicably failed, and no error message prompts, especially when performing database operations, obviously performed later, but I could not record changes in the database, it is depressing! Have engaged in FSO delete operations there have been such a problem, later a Err.Description put up immediately an error, so this stuff must make full use!

Specific

On Error Resume Next
The … program
If Err.Number> 0 Then
Response.Write Err.Description
Response.End
End If

Posted by databasesql