Oracle

[Change] Use shell handle the database alert log oracle

kods.net — Sat, 06 Feb 2010 07:55:02+0000

rn
2008-10-17 22:37
rn rn rn rn rn
From: ITPUB.NET
Link: http://tech.it168.com/a2008/1016/208/000000208136.shtml

rn
Technical documentation】【IT168
In this paper, using the shell to realize oracle database alert log processing algorithms and implementation process, and give a sample, examples show that the analysis of the script can be quickly and effectively alert logs, to meet the demand, the final look ahead to deal with the process.

rn
1.
Outlined the background for the daily number of repetitive operations, as well as deal with the problem site in the user process, using some automated scripts to accomplish some work, make some boring work of change were simple, and quick, but also is not prone to error. In certain provinces of Unicom Corporation and the Treasurer and implementation services in the process learned that users have the following needs: to provide shell scripts to achieve the alert log of oracle database to track processing, for example according to the time (in days) to see and deal with alert logs.

Without scripting, the common approach is to download the alert files, use the text editing tools to find, statistics, positioning, filtering, download the trace file. Is generally more time-consuming, and may be omitted.

This first set out the algorithm, and then gives sample code and implementation, and finally gives outlook, the script includes shell scripts and sql scripts.

rn
2. Algorithm

rn

Figure 1 flow chart to determine how many days need to analyze the log since the situation on the time for processing, according to timestamp format (This article deals with the time of the alert file format is similar to: Fri Aug 1 13:22:37 2008), to obtain the starting line number analysis from the starting line number to the end of all content.
ORA-filter all of the information, and give them statistics.
Of critical error message (ORA-00600, ORA-000060, ORA-1555, ORA-07445), for separate treatment.
Find and obtain the trace file. Generated ftp format, use the ftp command to download the trace file.
Use operating system commands, access to an error corresponding meaning.

rn

rn
3. Shell code
processing alert files called scripts check_alert.sh

rn
rn
#! / bin / sh
# Set the alert log file environment variables
ORIGINAL_ALERT = "/ arraybill / home / oracle / app / oracle / admin / hnbill / bdump / alert_hnbill.log"
# Determine the input parameters: days
if [$ #-ne 1]
then
echo "wrong argument, usage: sh check_alert.sh 3"
exit 1
fi
if [$ 1-lt 0]
then
echo "Sorry, please enter a non-negative integer"
exit 1
fi

# Query parameters corresponding to the time before the system time
sh dated_alert.sh $ 1> / dev / null
# Decomposition time format, pay attention, which may be a days, it could be two, the month is three, month and day length of the string consisting of a fixed
str_day = $ (sed 1d tmp_date.log | awk '(if (substr ($ 1,1,1) == 0) print substr ($ 1,2,1); else print substr ($ 1,1,2))')
str_length = $ (echo $ str_day | awk '(print length ($ 1))')
# 3 letters of the month will be broken down into the first letter capitalized, other lower-case
str_month = $ (sed 1d tmp_date.log | awk '(print substr ($ 1,4,3))' | sed '1, $ y / [JFMASODN] ABCDEFGHIJKLMNOPQRSTUVWXYZ / [JFMASODN] abcdefghijklmnopqrstuvwxyz / ')

if [$ str_length-eq 1]
then
echo "awk'/""$ str_month $ str_day" "/ (print NR) '" $ ORIGINAL_ALERT ""> tmp.sh
# echo "$ str_month $ str_day"

fi
if [$ str_length-eq 2]
then
echo "awk'/""$ str_month $ str_day" "/ (print NR) '" $ ORIGINAL_ALERT ""> tmp.sh
# echo "$ str_month $ str_day"

fi

chmod 777 tmp.sh
chmod 777 tmp.sh
sh tmp.sh | awk 'NR == 1 (print $ 1)'> rownum.lst

# Get the alert to deal with the beginning of the log line number and ending line number
alert_row_begin = $ (cat rownum.lst | awk '(print $ 1)')
alert_row_end = $ (wc-l $ ORIGINAL_ALERT | awk '(print $ 1)')
echo "awk 'NR == $ alert_row_begin, NR == $ alert_row_end (print \ $ 0)'" $ ORIGINAL_ALERT ""> tmp.sh

# Line number according to the scope of interception of alert log content, and saved to a temporary file in tmp_alert.tmp

sh tmp.sh> tmp_alert.tmp

echo
echo "-----------------------------------------------"
echo "Error Summary Start Time: $ begin_date_time"
echo "-----------------------------------------------"
echo

echo "Number Error number"
echo "---- -------------------"
grep ORA-tmp_alert.tmp | awk '(print substr ($ 0,0,9))' | sort-nr | uniq-c | sort-nr

# If there are 600 errors, 600 error handling script calls
if [$ (awk '/ ORA-00600 / (print NR)' tmp_alert.tmp | wc-l | awk '(print $ 1)')-gt 0]
then
sh check_00600.sh
fi
# 07445 If there are errors, call 07445 error handling script
if [$ (awk '/ ORA-07445 / (print NR)' tmp_alert.tmp | wc-l | awk '(print $ 1)')-gt 0]
then
sh check_07445.sh
fi

# If there are 000.06 thousand errors, call the 000060 error handling script
if [$ (awk '/ ORA-000060 / (print NR)' tmp_alert.tmp | wc-l | awk '(print $ 1)')-gt 0]
then
sh check_000060.sh
fi

# 01555 If there are errors, call 01555 error handling script
if [$ (awk '/ ORA-01555 / (print NR)' tmp_alert.tmp | wc-l | awk '(print $ 1)')-gt 0]
then
sh check_01555.sh
fi

echo
# Remove the call to the temporary files generated in the process
rm-f tmp_3_line.sh
rm-f tmp_date.log
rm-f tmp.sh
rm-f ora01555_trc.log
rm-f ora006000_trc.log
rm-f ora07445_trc.log
rm-f ora000060_trc.log
rm-f trc_tmp
rm-f tmp_alert.tmp
rm-f ora006000_rownum.log
rm-f rownum.lst

rn

Formatted date generated script dated_alert.sh

rn
rn
echo "set heading off"> get_date.sql
echo "set feedback off">> get_date.sql
echo "set term off">> get_date.sql
echo "spool tmp_date.log">> get_date.sql
echo "select sysdate-" $ 1 "from dual;">> get_date.sql

echo "spool off">> get_date.sql
echo "exit">> get_date.sql

export NLS_LANG = american

sqlplus "/ as sysdba" @ get_date.sql < EOF

rm-f get_date.sql

rn

rn

rn
ORA-00600 error handling script check_00600.sh
This just gives ORA-00600 error handling script, other errors similar to space limitations, other error handling script is omitted here.

rn
rn
echo
echo "----------------------"
echo "ORA-00600 error trace information"
echo "----------------------"

awk '/ ORA-00600 / (print NR)' tmp_alert.tmp | awk '(print "awk" "' \''" "NR ==" $ 1-1 "(print" "\" mget \ "" "substr ($ 4,1, length ($ 4) -1 )}""' \'' "" tmp_alert.tmp ") '> tmp1.sh
chmod 777 tmp1.sh
chmod 777 tmp1.sh
echo
echo
sh tmp1.sh | sort-r | uniq> ora006000_trc.log

# For all of the ORA-00600 trace file processing, eliminating duplicate files, to determine whether the file exists, and generate a usable format for ftp
echo ""> trc_tmp
for trc_file in $ (cat ora006000_trc.log | awk '(print $ 2)')
do
if [-f $ trc_file]
then
echo "mget $ trc_file">> trc_tmp
else
echo "can not find the trace file: $ trc_file">> trc_tmp
fi
done
cat trc_tmp | sort-r
echo
echo

echo> tmp_3_line.sh
chmod 777 tmp_3_line.sh
chmod 777 tmp_3_line.sh

# For each ORA-00600, printing timestamp, trace file and the specific error from
for tmp_row_num in $ (awk '/ ORA-00600 / (print NR)' tmp_alert.tmp)
do
let tmp_time = $ tmp_row_num-2
let tmp_trc = $ tmp_row_num-1
echo "awk 'NR ==" $ tmp_time "(print \ $ 0)' tmp_alert.tmp">> tmp_3_line.sh
echo "awk 'NR ==" $ tmp_trc "(print \ $ 0)' tmp_alert.tmp">> tmp_3_line.sh
echo "awk 'NR ==" $ tmp_row_num "(print \ $ 0)' tmp_alert.tmp">> tmp_3_line.sh
echo "echo">> tmp_3_line.sh
done

sh tmp_3_line.sh

echo
echo

# Error message telling you
echo "---------------"
echo "on the ORA-00600"
echo "---------------"
oerr ora 00600
echo

rn

Output Sample

rn
rn
$ Sh check_alert.sh 2
----------------------------------------------
Error Summary Start Time: Wed Sep 10 00:11:39 2008
-----------------------------------------------

Error Number Number
---- -------------------
3 ORA-00600
1 ORA-01555

----------------------
ORA-00600 error tracking information
----------------------

mget / arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_5984.trc
Can not find trace file: / arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_10650.trc

Fri Sep 10 11:55:42 2008
Errors in file / arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_15196.trc:
ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected got

Fri Sep 10 11:56:16 2008
Errors in file / arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_15196.trc:
ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected got

Tue Sep 10 19:48:49 2008
Errors in file / arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_5984.trc:
ORA-00600: internal error code, arguments: [729], [1200], [space leak], [], [], [], [], []

---------------
About ORA-00600
---------------
00600, 00000, "internal error code, arguments: [% s], [% s], [% s], [% s], [% s], [% s], [% s], [% s] "
/ / * Cause: This is the generic internal error number for Oracle program
/ / Exceptions. This indicates that a process has encountered an
/ / Exceptional condition.
/ / * Action: Report as a bug - the first argument is the internal error number

-------------------------
ORA-01555 error tracking information
-------------------------
Thu Sep 11 04:38:18 2008
ORA-01555 caused by SQL statement below (Query Duration = 12429 sec, SCN: 0x0240.ba061afa):
Thu Sep 11 04:38:18 2008
SELECT * FROM BB_SERVICE_RELATION_T WHERE CITY_CODE =: B4 AND SERVICE_KIND =: B3 AND IF_VALID = 1 AND SERVING_STATUS <> 10 AND SERVING_STATUS <> 20 AND (PRESERVE02 <> 1 OR PRESERVE02 IS NULL) AND MOD (USER_ID,: B2) =: B1 - 1

---------------
About ORA-01555
---------------
01555, 00000, "snapshot too old: rollback segment number% s with name \"% s \ "too small"
/ / * Cause: rollback records needed by a reader for consistent read are
/ / Overwritten by other writers
/ / * Action: If in Automatic Undo Management mode, increase undo_retention
/ / Setting. Otherwise, use larger rollback segments

rn
4. Post-processing and Prospects
This time format only in line with the alert document processing, the script need to be further improved to enhance its scalability, that is able to define a list of errors is equivalent to a configuration table, just need to modify the list of errors, you can decide which error is need to focus attention. In addition, the need to add a module, if the alert file not found on the corresponding time stamp records, the need for an exception handling.

To the right to find errors, to timely post-processing, such processing includes: analysis of errors to find documents, need to optimize the statement is optimized, need to patch the bug, as the case may download and patch.

SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28

Oracle create table space. To create a user and to authorize. View permissions

kods.net — Thu, 04 Feb 2010 18:10:02+0000

Create a temporary table space rn
Create a temporary table space
CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE 'C: \ oracle \ product \ 10.1.0 \ oradata \ orcl \ test_temp01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
rn
To create a user table space

rn
To create a user table space
CREATE TABLESPACE test_data
LOGGING
DATAFILE 'C: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ TEST_DATA01.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
rn
Create a user and set the table space

rn
To create a user and specify the table space
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;
rn
To grant permission to the user

rn
To grant permission to the user
GRANT
CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW, CREATE ANY INDEX, CREATE ANY PROCEDURE,
ALTER ANY TABLE, ALTER ANY PROCEDURE,
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO username;
rn
Role This role will be to grant username, that is, to make username the user can manage and use the resources owned by role

rn
GRANT role TO username;
rn

----------------------------------------------- View user rights -------------------------------------------------- -------

View all users

rn
View all users
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;
rn
See the user object or role privileges

rn
View User Object Permissions
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
rn
View all the roles

rn
View all users
SELECT * FROM DBA_ROLES;
rn
View user or role which has the role of

rn
View user or role which has the role of
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

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 data dictionary and dynamic performance views

kods.net — Thu, 04 Feb 2010 16:25:03+0000

Common data dictionary:

dba_data_files: usually used to query the information on the database file

dba_db_links: in all databases including database links, which is databaselinks.

dba_extents: a database of information on all partitions

dba_free_space: the freedom of all the table space partition

dba_indexs: all the indexes on the database description

dba_ind_columns: In all tables and clustered indexes on the column compression

dba_objects: all the objects in the database

dba_rollback_segs: rollback segment description

dba_segments: All database storage sub-paragraph

dba_synonyms: on the synonym of Information Query

dba_tables: database, a description of all the data tables

dba_tabespaces: The information on the table space

dba_tab_columns: description of all the tables, views, and gathered out

dba_tab_grants / privs: object permissions granted
dba_ts_quotas: all user table space quota

dba_users: With regard to the data for all users of information
dba_views: a database of all the text view

Commonly used dynamic performance views

v $ datafile: the database used data file information

v $ librarycache: shared pool SQL statement, the management information
v $ lock: access the database through the session, set the object lock all of the information

v $ log: from the control file to extract the information related to redo log group
v $ logfile group of the instance to reset the log file name and location information

v $ parameter: initialization parameter file, the value of all the items
v $ process: the current process of information

v $ rollname: rollback segment information,

v $ rollstat: Online rollback Statistics

v $ rowcache: in-memory data dictionary activity / performance information

v $ session: the session information

v $ sesstat: In the v $ session to report on the current session of the statistical information

v $ sqlarea: shared pool using the current cursor statistics, the cursor is a memory region that Oracle open when dealing with SQL statements.

v $ statname: in the v $ sesstat reported the meaning of the various statistics

v $ sysstat: based on the current operating system session conducted by Statistics
v $ waitstat: a database of more than one session to access the data in detail. When there is more than one session to access the same information, there may be waiting for the situation.

Summarizes how these completely different from the views and data dictionary, is not so easy to confuse. Hei hei! ! !

Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
use sqlldr 1 Introduction 2009-03-27 19:39:01
Turning optimization oracle article 2009-03-26 21:39:35
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17

oracle time, plus or minus a comprehensive

kods.net — Thu, 04 Feb 2010 01:04:31+0000

oracle time, plus or minus a comprehensive

Addition
select sysdate, add_months (sysdate, 12) from dual; - plus 1 year
select sysdate, add_months (sysdate, 1) from dual; - Canada in January
select sysdate, to_char (sysdate +7, 'yyyy-mm-dd HH24: MI: SS') from dual; - plus 1 week
select sysdate, to_char (sysdate +1, 'yyyy-mm-dd HH24: MI: SS') from dual; - plus 1 day
select sysdate, to_char (sysdate +1 / 24, 'yyyy-mm-dd HH24: MI: SS') from dual; - plus 1 hour
select sysdate, to_char (sysdate +1/24/60, 'yyyy-mm-dd HH24: MI: SS') from dual; - plus 1 minute
select sysdate, to_char (sysdate +1/24/60 / 60, 'yyyy-mm-dd HH24: MI: SS') from dual; - plus 1 seconds

Subtraction
select sysdate, add_months (sysdate, -12) from dual; - by 1 year
select sysdate, add_months (sysdate, -1) from dual; - by January
select sysdate, to_char (sysdate-7, 'yyyy-mm-dd HH24: MI: SS') from dual; - minus 1 week
select sysdate, to_char (sysdate-1, 'yyyy-mm-dd HH24: MI: SS') from dual; - minus 1 day
select sysdate, to_char (sysdate-1/24, 'yyyy-mm-dd HH24: MI: SS') from dual; - minus 1 hour
select sysdate, to_char (sysdate-1/24/60, 'yyyy-mm-dd HH24: MI: SS') from dual; - minus 1 minute
select sysdate, to_char (sysdate-1/24/60/60, 'yyyy-mm-dd HH24: MI: SS') from dual; - minus 1 sec

Between the date of date and does not add up

Dates and numbers can be added to

SQL> select sysdate +1 from dual;

Date and dates can subtract

SQL> select sysdate-sysdate from dual;

Add_Months (D, X)
Back to D plus X months after the date of the date of
Last_Day (D)
Back to D, contains the date of the date of the last day of the month

add_months (sysdate, 1) plus one month

add_months (sysdate, -1) by one month

sysdate +1 plus one day

-------------------------------------------------- -----------------------------
Months_Between (D1, D2)
Back to D1, D2 between the number of months

New_Time (D, Z1, Z2)
Back to the time zone Z1 where time is D, time zone Z2

Next_Day (D, S)
Back to satisfy the condition S, the next day
S for the day of the week

Round (D, F)
D carried out in accordance with the date format, rounding F

Sysdate
Back to the database the current time

Trunc (D, F)
Accordance with the format of F truncated time D

In addition: Date can be directly subtraction, such as: sysdate +1, sysdate -1

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

sysdate +1 plus one day
sysdate +1 / 24 + 1 hour
sysdate +1 / (24 * 60) plus 1 minute
sysdate +1 / (24 * 60 * 60) plus a second

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

With regard to the process of creating oracle dblink Some experience bar

kods.net — Wed, 03 Feb 2010 18:07:51+0000

Two different database servers, from one database server to read a user to another database server under a user's data, this time you can use the dblink.
In fact, dblink in the view and the database is almost built dblink need to know to be the time to read the database ip address, ssid, and database user name and password.
Creating can be used in two ways:
1, the local service has been configured

create public database
link fwq12 connect to fzept
identified by neu using 'fjept'
CREATE DATABASE LINK database link name CONNECT TO username IDENTIFIED BY password USING 'local configuration of the data instance name';
2, local service is not configured

create database link linkfwq
connect to fzept identified by neu
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.142.202.12) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fjept)
)
) ';

host = the ip address of the database, service_name = database ssid.
In fact, two ways to configure the dblink in a similar vein, I personally feel the second method is better still, so unaffected by the domestic services.

Database connection string can be NET8 EASY CONFIG or directly modify the TNSNAMES.ORA where defined.

Database Parameters global_name = true to request the database link name with the same remote database name

Database global name can be found using the following command
SELECT * FROM GLOBAL_NAME;

Queries the remote database table
SELECT ... ... FROM table @ database link name;
Query, delete and insert data and operating the local database is the same, only the table name needs to be written, "table @ dblink server" only.

Incidentally, under the synonym creation:

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

Remove dblink: DROP PUBLIC DATABASE LINK linkfwq.

If you create a global dblink, you must use systm or sys user, in the database before the Canadian public.

oracle database basic use and parameters 2009-04-10 06:10:04
Oracle10g Network Configuration 2009-04-05 05:19:49
SQLPlus skills 2009-04-01 09:37:29
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Oracle deadlock related 2009-03-30 16:46:54

ORA-01940 error resolution process

kods.net — Wed, 03 Feb 2010 16:12:56+0000

DMP in the daily logical backup into a database, in the process of the data in the guide can not be removed before the user action occurs the following error:
OS: linux

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

SQL> drop user test cascade;

drop user test cascade

*

Error line 1:

ORA-01940: cannot drop a user that is currently connected

Address:

Find out the reasons is because in the original DMP logical backup of data in the test user exists under the job, due to import data of these job has already begun, so there is in the process caused

JOB users to perform tasks in the test session, indicating that the user is being used, so the user can not be drop out of the

The following error ORA-01940 is the process of recovery:

Creating a Test Table

drop table test1

select * from test1

create table test1 (datetime date);

Create a custom process

create or replace procedure MYPROC as

begin

for i in 1 .. 100000 loop

insert into TEST1 (datetime) values (sysdate);

end loop;

end;

PL / SQL procedure successfully completed

Create a JOB, a day 1440 minutes, that is to run every 5 minutes during test time

declare

job number;

begin

sys.dbms_job.submit

(job => job,

what => 'MYPROC;',

next_date => sysdate,

interval => 'sysdate + (1 / 1440) * 5');

commit;

end;

PL / SQL procedure successfully completed

Running JOB

SQL> begin

2 sys.dbms_job.run (241);

3 end;

4 /

PL / SQL procedure successfully completed

There needs to delete a user daimin, they will report the following error

In order to sys landing system, remove the user daimin

- See daimin user job under the

SQL> select job, what, interval, to_char (next_date, 'yyyy-mm-dd hh24: mi: ss')

2 from dba_jobs where schema_user = 'DAIMIN';

JOB WHAT INTERVAL TO_CHAR (NEXT_DATE, 'YYYY-MM-DDH

241 MYPROC; sysdate + (1 / 1440) * 5 2008-12-16 10:10:03

See Running JOB

SQL> select * from dba_jobs_running;

SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE

1619 2412008-12-16 10:25:51 0

View user's active session daimin

SQL> select sid, serial #, username, program, machine, status

2 from v $ session

3 where username = 'DAIMIN'

4 AND STATUS = 'ACTIVE';

SID SERIAL # USERNAME PROGRAM MACHINE STATUS

1619 2360 DAIMIN plsqldev.exe STAPLES \ DELLF98P-10QC ACTIVE

- In order to remove the sys user down daimin users, will be reported the following error

SQL> drop user daimin cascade;

drop user daimin cascade

ORA-01940: cannot drop a user that is currently connected

Attempt to remove the job

SQL> begin

2 FOR r_job IN (select job, what, interval, to_char (next_date, 'yyyy-mm-dd hh24: mi: ss')

3 from dba_jobs where schema_user = 'DAIMIN'

4) LOOP

5 dbms_job.REMOVE (r_job.job);

6 end loop;

7 commit;

8 end;

9 /

begin

FOR r_job IN (select job, what, interval, to_char (next_date, 'yyyy-mm-dd hh24: mi: ss')

from dba_jobs where schema_user = 'DAIMIN'

) LOOP

dbms_job.REMOVE (r_job.job);

end loop;

commit;

end;

ORA-23421: job number 241 is not a job in the job queue

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_IJOB", line 529

ORA-06512: at "SYS.DBMS_JOB", line 171

ORA-06512: at line 5

Analysis of error: due to the sys user to delete the user daimin under the JOB, it'll get an error

Solution:

1, kill the task of implementing JOB conversation process;

alter system disconnect session 'sid, serial #' immediate;

Or Alter system kill session 'sid, serial #';

Either

SQL> drop user aa;
drop user aa
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL> select sid, serial # from v $ session
2 where username = 'AA';

SID SERIAL #
---------- ----------
13633

SQL> alter system kill session '136, 33 ';

System altered.

2, by setting in the initial file, set the initial parameters JOB_QUEUE_PROCESSES = 0 to make Oracle will kill CJQ0 and the corresponding job processes;

Modify the init.ora file, and then restart the database, or the implementation of the ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;

Modified the initial parameters set JOB_QUEUE_PROCESSES = 0, restart the database after the logical backup into operation, remove users, this time deleted successfully!

SQL optimization (4) - Table of the three connections 2009-03-29 23:13:41
SQL optimization (3) - four index 2009-03-28 13:10:06
use sqlldr 1 Introduction 2009-03-27 19:39:01
An example of the use of TKPROF 2009-02-26 01:38:19

sqlldr guide data

kods.net — Wed, 03 Feb 2010 15:15:40+0000

In the Oracle database, we usually between tables in different database records, copy or migration will use the following methods:

1. A record of the table to export to a section separated by a semicolon insert statement, then execute the table inserted into the B
2. The establishment of inter-database dblink, then create table B as select * from A @ dblink where ..., or insert into B select * from A @ dblink where ...
3. Exp A table, and then imp to the B form, exp Shike Jia query
4. Procedures to achieve select from A .., and then insert into B ..., must be submitted in batches
5. Another thing is that this is to say to the Sql Loader (sqlldr) to import the data, the effect is obvious compared to insert one by one

1 ways to record a long time was a nightmare, must be submitted in batches of Article San Wubai Otherwise, the client will not survive, but the import process is very slow. If Otherwise generate REDO to improve insert into the performance, it is necessary to do the following:

alter table B nologging;

insert / * + APPEND * / into B (c1, c2) values (x, xx); insert / * + APPEND * / into B select * from A @ dblink where .....;

Well, briefly in front of Oracle data import and export of a variety of ways, I am sure there is more to understand. Here the focus to talk about Oracle's Sql Loader (sqlldr) usage.

Carried out under the command line, Oracle's sqlldr command, you can see a detailed parameter description it is necessary to focus on the following parameters:

userid - Oracle's username / password [@ servicename]
control - control file may contain tables of data
-------------------------------------------------- -------------------------------------------------- ---
log - log import log files, the default for the control file (remove the extension). log
bad - bad data file, the default for the control file (remove the extension). bad
data - data files, generally in the control file. Using the parameter control file does not specify the data file is more suitable for automatic operation
errors - errors allowed the number of records can be used to control a record he can not be wrong
rows - the number of records submitted to time, the default is 64
skip - the number of rows to skip, for example exported data file is a header in front of a few lines, or other description

There are more parameters that describe the sqlldr please refer to: sql loader usage.

Sqlldr using examples to demonstrate the use of, there are two kinds of use:

1. Only use a control file, in this control file contains the data
2. Using a control file (as a template) and a data file

In general in order to facilitate the separation of templates and data, and the program will use a different division of the second approach, so the first look at such usage. Data file can be CSV file or any other split-delimited data file can use PL / SQL Developer or Toad Export, SQL * Plus can also be used to format output of the spool, or the UTL_FILE package is generated. In addition, with Toad able to directly generate the control file that contains the data.

First of all, assuming there is such a table users, and insert 5 records:

create table users (

user_id number, - the user ID user_name varchar2 (50), - user name

login_times number, - the number of landing last_login date - the last login date

)

insert into users values (1, 'Unmi', 3, sysdate);

insert into users values (2, NULL, 5, to_date ('2008-10-15 ',' YYYY-MM-DD ')); insert into users values (3, 'every other leaf warbler', 8, to_date ('2009-01-02 ',' YYYY-MM-DD '));

insert into users values (4, 'Kypfos', NULL, NULL); insert into users values (5, 'I do not know autumn', 1, to_date ('2008-12-23 ',' YYYY-MM-DD '));

The second way: Using a control file (as a template) and a data file

1) Establishing a data file, we are here with the PL / SQL Developer export table users to record users_data.csv file, reads as follows:

"", "USER_ID", "USER_NAME", "LOGIN_TIMES", "LAST_LOGIN"

"1", "1", "Unmi", "3", "2009-1-5 20:34:44" , "2", "2 ",""," 5", "2008-10-15"

, "3", "3", "every other leaf warbler", "8", "2009-1-2" "4", "4", "Kypfos ","",""

, "5", "5", "I do not know of Autumn", "1", "2008-12-23"

2) the establishment of a control file users.ctl, reads as follows:

OPTIONS (skip = 1, rows = 128) - sqlldr command displays the option to write here, edge to, skip = 1 is used to skip the first row of data

LOAD DATA INFILE "users_data.csv" - specify the external data file, you can write multiple INFILE "another_data_file.csv" to specify multiple data files

- Here you can also use BADFILE, DISCARDFILE to specify the data and discard the bad data file, truncate - operation type, with truncate table to clear the table in the original records

INTO TABLE users - To insert a table record Fields terminated by "," - the data recorded on each line to use "," separated

Optionally enclosed by ' "' - data in each field with the '"' box effect, such as the field of "," separator trailing nullcols - table fields are not allowed when the value of the corresponding empty

( virtual_column FILLER, - it is a virtual field, is used to skip from the PL / SQL Developer generated the first column number

user_id number, - the field you can specify the type, otherwise considered to be CHARACTER type, log file showing the user_name,

login_times, last_login DATE "YYYY-MM-DD HH24: MI: SS" - designated to receive the date format, rather with the to_date () function is converted

)

Description: The type of truncate operation in one location using the following values:

1) insert - as the default way of loading the beginning of the data requirements table is empty
2) append - Append a new record in the table
3) replace - delete the old records (using delete from table statement), replace the load into a new record
4) truncate - delete the old records (using the truncate table statement), replace the load into a new record

3) execute the command:

sqlldr dbuser / dbpass @ dbservice control = users.ctl

The dbservice instructions recorded in a database table users and data files on the same.

After the implementation of the End sqlldr would like to look at the generated several documents, such as users.log log files, users.bad bad data files. In particular, to take a look at the log file, from which allows you to better understand the Sql Loader, inside the control file parsing, list the type of each field, loading statistics records, the cause of errors and other information.

The first approach, using only one control file in this control file contains the data

1) the users_data.cvs the contents of the supplement to the users.ctl in and to BEGINDATA connection, but also the INFILE "users_data.csv" read INFILE *. Meanwhile, in order of description of a larger problem, the data-processing a bit. At this point, the complete users.ctl content of the document are:

OPTIONS (skip = 1, rows = 128) - sqlldr command displays the option to write here, edge to, skip = 1 is used to skip the first row of data

LOAD DATA INFILE * - because the data together with the control file, so use * indicates

append - there used to append operation, the additional records in the table users INTO TABLE users

when LOGIN_TIMES <> '8 '- can also be used when qualifying clause, choose to import the records Fields terminated by ","

trailing nullcols (

virtual_column FILLER, - skipped by the PL / SQL Developer generated the first column number user_id "user_seq.nextval", - direct access to this column the next sequence value, rather than the value of the data provided in the

user_name " 'Hi' | | upper (: user_name )",--, can also use SQL functions or operations for data processing login_times terminated by ",", NULLIF (login_times = 'NULL') - can be individually specified for the column separator

last_login DATE "YYYY-MM-DD HH24: MI: SS" NULLIF (last_login = "NULL") - When the field is "NULL" when it was NULL )

BEGINDATA - data from here , USER_ID, USER_NAME, LOGIN_TIMES, LAST_LOGIN

1,1, Unmi ,3,2009-1-5 20:34 2,2, Fantasia ,5,2008-10-15

3,3, separated leaf warbler ,8,2009-1-2 4,4, Kypfos, NULL, NULL

5,5, I wonder if Autumn ,1,2008-12-23

2) To implement the same command:

sqlldr dbuser / dbpass @ dbservice control = users.ctl

For example, the console will show this information:

C: \> sqlldr dbuser / dbpass @ dbservice control = users.ctl

SQL * Loader: Release 9.2.0.1.0 - Production on Wednesday, January 7 22:26:25 2009

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

Reach commit point, the logical record count 4
Reach commit point, the logical record count 5

The above control file contains the contents of the more complex (demonstration purposes), please understand that according to notes the significance of each parameter. Can also use this to explore more.

Concluded by saying that under the relevant SQL * Loader performance and concurrent operation

1) ROWS The default value is 64, you can specify a more appropriate based on the actual ROWS parameter to specify the number of records for each submission. (Experienced in the PL / SQL Developer in the implementation of a few to more than one insert statement that the case right?)

2) Conventional import by using INSERT statement to import the data. Direct import can skip the database-related logic (DIRECT = TRUE), data directly into the data file, you can import the data to improve performance. Of course, in many cases, can not use this parameter (if the primary key to repeat the words of the state of the index will become UNUSABLE!).

3) by specifying UNRECOVERABLE option, you can close the database log (whether or not to alter table table1 nologging it?). This option can only be used in conjunction with direct.

4) For very large data files to import must use concurrent operation, and that is to run multiple import tasks.

sqlldr userid = / control = result1.ctl direct = true parallel = true
sqlldr userid = / control = result2.ctl direct = true parallel = true
sqlldr userid = / control = result2.ctl direct = true parallel = true

When loading large amounts of data (approximately more than 10GB), the best inhibiting the production log:

SQL> ALTER TABLE RESULTXT nologging;

This does not produce REDO LOG, can improve efficiency. And then load data in the CONTROL file, add the above line: unrecoverable, this option must be connected with shared use of DIRECT.

Concurrent operation, ORACLE saying that they could handle 100GB of data per hour capacity! In fact, it is estimated to go to 1-10G even pretty good, the structure began to be used the same file, but only a small amount of data, after the success of start loading large amounts of data, so that to avoid waste of time.

(Note: in general can only use ASCII code form, remember to convert code, or import the data is empty)

References: 1. Oracle SQL * Loader - English, Sql Loader official instructions, including multiple types of Demo
2. Sql loader usage - lists the choice of parameters sql loader Chinese specifications
3. Use SQL Loader to import large amounts of data, to avoid the frequent use of SQL to write library - a simple example, fast with the usage of about Sql Loader
4. Oracle SQL Loader syntax details
5. Oracle sql loader full-Gong Lue - fairly genuine. And talk about how to use SQL * Plus or UTL_FILE package of the spool data file is generated
6. SQL * Loader Control File Reference - English, control file to use for reference
7. To learn the use of oracle sql loader
8. With sqlloader (sqlldr) load LOB data - LOB inner class is an external file into a database using sql loader
9. SQLLDR loaded directly several parameters of the test
10.Maximizing SQL * Loader Performance

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

sqlplus can execute PL / SQL procedure written

kods.net — Wed, 03 Feb 2010 15:07:06+0000

sqlplus can execute PL / SQL to write programs.

SQL * Plus: Release 10.2.0.1.0 - Production on Wednesday, February 3 09:59:25 2010

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

Connect to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1 DECLARE
2
3 a NUMBER: = 3;
4
5 BEGIN
6
7 a: = a + 1;
8
9 END;
10 *

PL / SQL procedure successfully completed.

As with Oracle SQL programs, we can invoke a PL / SQL program either by typing it in sqlplus or by putting the code in a file and invoking the file in the various ways we learned in Getting Started With Oracle.

PL / SQL allows you to branch and create loops in a fairly familiar way.

An IF statement looks like:

IF  THEN  ELSE  END IF;

The ELSE part is optional. If you want a multiway branch, use:

IF  THEN ...

ELSIF  THEN ...

... ...

ELSIF  THEN ...

ELSE ...

END IF;

The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:

DECLARE

    a NUMBER;

    b NUMBER;

BEGIN

    SELECT e,f INTO a,b FROM T1 WHERE e>1;

    IF b=1 THEN

        INSERT INTO T1 VALUES(b,a);

    ELSE

        INSERT INTO T1 VALUES(b+10,a+10);

    END IF;

END;

.

run;

Loops are created with the following:

LOOP

     /* A list of statements. */

END LOOP;

At least one of the statements in should be an EXIT statement of the form

EXIT WHEN ;

The loop breaks if is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

DECLARE

    i NUMBER := 1;

BEGIN

    LOOP

        INSERT INTO T1 VALUES(i,i);

        i := i+1;

        EXIT WHEN i>100;

    END LOOP;

END;

.

run;

Some other useful loop-forming statements are:

EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.

A WHILE loop can be formed with

    WHILE  LOOP

        

    END LOOP;

A simple FOR loop can be formed with:
```
    FOR  IN .. LOOP

        

    END LOOP;
```
Here, can be any variable; it is local to the for-loop and need not be declared. Also, and are constants.

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 Wait Interface

kods.net — Wed, 03 Feb 2010 14:46:48+0000

File:	Oracle Wait Interface.rar
Size:	2522KB
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

sqlldr use of the whole

kods.net — Wed, 03 Feb 2010 13:27:57+0000

SQL * LOADER is the ORACLE data loading tools normally used to operating system files to migrate to ORACLE database.

SQL * LOADER is the option of using a large data warehouse loading, because it provides the fastest route (DIRECT, PARALLEL). Now, we put aside the theory aside, using examples to enable you to quickly master the SQL * LOADER is used.
First of all, we recognize that what SQL * LOADER.
In the NT,, SQL * LOADER command for SQLLDR, under the UNIX generally sqlldr / sqlload.
Such as the implementation: d: \ oracle> sqlldr
SQL * Loader: Release 8.1.6.0.0 - Production on Tuesday January 8 11:06:42 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.

Usage: SQLLOAD keyword = value [, keyword = value ,...]
Valid keywords:
userid - ORACLE username / password
control - Control file name
log - Log file name
bad - Bad file name
data - Data file name
discard - Discard file name
discardmax - Number of discards to allow (all default)
skip - Number of logical records to skip (Default 0)
load - Number of logical records to load (all default)
errors - Number of errors to allow (Default 50)
rows - Number of rows in conventional path bind array or between direct p
ath data saves
(Default: Conventional path 64, all direct paths)
bindsize - Size of conventional path bind array in bytes (default 65536)
silent - Supdivss messages during run (header, feedback, errors, discards, part
itions)
direct - use direct path (Default FALSE)
parfile - parameter file: name of file that contains parameter specification
s
parallel - do parallel load (Default FALSE)
file - File to allocate extents from
skip_unusable_indexes - disallow / allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance - do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued - commit loaded rows when load is discontinued (Default FALSE)
readsize - Size of Read buffer (Default 1048576)
PLEASE NOTE: command-line parameters can be specified by a location or keyword.
Examples of the former 'sqlload scott / tiger foo';
Examples of the latter is the 'sqlload control = foo userid = scott / tiger'.
Location of the specified parameters of time must be earlier than but not later than the parameters specified by the keyword. For example, the
'SQLLOAD SCott / tiger control = foo logfile = log',
But the 'not allowed sqlload scott / tiger control = foo log',
Even if permitted to parameter 'log' of the correct location.
d: \ oracle>
We can see some basic help information, here, I used the Chinese WIN2000 ADV SERVER.
We know, SQL * LOADER can only import plain text, so we now begin to explain examples of its usage.
First, existing data sources result.csv, want to pour in FANCY user under ORACLE.
result.csv content:
1, the default Web site, 192.168.2.254:80:, RUNNING
2, other, 192.168.2.254:80: test.com, STOPPED
3, third, 192.168.2.254:81: thirdabc.com, RUNNING
From this, we see that 4, respectively, separated by commas, for variable-length strings.
Second, in formulating control file result.ctl
result.ctl content:
load data
infile 'result.csv'
into table resultxt
(resultid char terminated by ',',
website char terminated by ',',
ipport char terminated by ',',
status char terminated by whitespace)
Description:
infile refers to the data source file where we omitted the default discardfile result.dsc badfile result.bad
into table resultxt default is INSERT, also into table resultxt APPEND to append mode or REPLACE
terminated by ',' refers to a comma-separated terminated by whitespace at the end separated by a space
3, this time the implementation of our load:
D: \> sqlldr userid = fancy / testpass control = result.ctl log = resulthis.out
SQL * Loader: Release 8.1.6.0.0 - Production on Tuesday January 8 10:25:42 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL * Loader-941: In describing the error when the table RESULTXT
ORA-04043: Object does not exist RESULTXT prompt error, because the database does not correspond to the table.
Fourth, the establishment of the database table create table resultxt
(resultid varchar2 (500),
website varchar2 (500),
ipport varchar2 (500),
status varchar2 (500))
/
5, re-implementation of the load
D: \> sqlldr userid = fancy/k1i7l6l8 control = result.ctl log = resulthis.out
SQL * Loader: Release 8.1.6.0.0 - Production on Tuesday January 8 10:31:57 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Reach commit point, the logical record count 2
Reach commit point, the logical record count 3
Has been successfully! We can process the log files to analyze: resulthis.out reads as follows:
SQL * Loader: Release 8.1.6.0.0 - Production on Tuesday January 8 10:31:57 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control file: result.ctl
Data File: result.csv
Error File: result.bad
Obsolete documents: the absence of the designated
:
(Which can waste all the records)
Load Number: ALL
Skip count: 0
Allowed error: 50
Bind array: 64 rows, maximum 65536 bytes to continue: have not been used in the specified path: General table RESULTXT
Have been loaded from every logical record into the table INSERT option to force column names to this position of the length of the suspension package data types
------------------------------ ---------- ----- ---- -- --- ---------------------
RESULTID FIRST *, CHARACTER
WEBSITE NEXT *, CHARACTER
IPPORT NEXT *, CHARACTER
STATUS NEXT * WHT CHARACTER

Table RESULTXT:
3 rows loaded successfully due to data errors, 0 lines did not load.
Since all WHEN clauses failed, 0 lines did not load.
Since all fields are empty, the 0-line did not load.

The space allocated for the integration of the array: 65016 bytes (63 lines)
In addition to bind an array of memory outside the space allocation: 0 bytes Total logical records skipped: 0
The total number of logical records read: 3
The total number of logical records rejected: 0
The total number of logical records discarded: 0
From Tuesday January 08 10:31:57 2002 started running Tuesday January 08 10:32:00 2002 after the end of operation time: 00: 00: 02.70
CPU time: 00: 00: 00.10 (available
6, concurrent operation
sqlldr userid = / control = result1.ctl direct = true parallel = true
sqlldr userid = / control = result2.ctl direct = true parallel = true
sqlldr userid = / control = result2.ctl direct = true parallel = true
When loading large amounts of data (approximately more than 10GB), the best inhibiting the production log:
SQL> ALTER TABLE RESULTXT nologging;
This does not produce REDO LOG, can improve efficiency. And then load data in the CONTROL file, add the above line: unrecoverable
This option must be connected with shared use of DIRECT.
Concurrent operation, ORACLE saying that they could handle 100GB of data per hour capacity! In fact, it is estimated to go to 1-10G even pretty good, the structure began to be used the same file, but only a small amount of data, after the success of start loading large amounts of data, so that to avoid waste of time.

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

Export data from Excel to Oracle Method Summary - --

kods.net — Wed, 03 Feb 2010 12:44:36+0000

We all generally like to use Excel to save a variety of commonly used forms of data, but If you want information system with data stored in Excel is not very realistic and will bring a lot of operational inconvenience. This is necessary to Excel into a variety of popular database software for the development use. These commonly used software has a small database system Access, medium and large database systems SQL Server and Oracle and other large-scale database systems.
So how easily import Excel data to these databases as well?

1, Access, and SQL Server itself as Microsoft's products, provide information on, including Excel, including direct import feature.

Second, while Oracle will need to spend more points, twists and turns, where I used the method and method of online search to be described below.

The first step, the Excel data into Access, here, should be explained that, due to the restrictions Excel data format, database software, compared with the relatively loose, so the process of importing the data may occur in some of the data into a cell is incorrect. Access will generate an errata, can be against the table to see is how can I import more data are generated in the problem.

The second step, establishing ODBC data source, through the control panel - "Data Sources (ODBC), create a system DSN, in the establishment of DSN's time to select the database driver, please select the corresponding driver, my machine is Oracle in OraHome92, and then will pop up a dialog box, do further configuration, the TNS Service Name will be listed on this machine available in the "Local Net Service Name", please select the corresponding service name entered to access the database after the user (in the import can be modified), and to this ODBC data source, different name on it.

The third step, through the Access export to Oracle. In the export of "Save as type" drop-down box, select "ODBC Database ()," ODBC Data Source dialog box will pop up to select the data source, followed by a pop-up on the corresponding data source, database connection information, the default user name is to build ODBC data source specified in the export can be revised to provide other users.

Shows that this can be seen, through the Access to export to other database systems are very convenient, as long as the machine on the target database system, ODBC driver, and set up ODBC data source, you can successfully exported.

Method 2:

Import into sql server, using SQL Server provides tools for import export Oracle, but for some image field in support of good, if you do not this field is on the line.

Method 3:

Advanced users can adopt the following methods (only a text field):

Excel should first save as. Csv format file, this format is a comma-delimited text file data fields, such as test.csv, and then write a insert.ctl
To use sqlldr to import!
insert.ctl reads as follows:
load data - 1, control file identifies
infile 'test.csv' - 2, to enter the data file is named test.csv
append into table table_name - 3, the additional records to the table table_name
fields terminated by ',' - 4, field terminated in ',' is a comma
(field1,
field2,
field3,
...
fieldn )----- column corresponds to the order of the definition of

Note that the order field in parentheses with the csv file, and then you can correspond to execute the following commands:
sqlldr user / password control = insert.ctl

Method 4 (only text field):

In fact, if you is not the case of a single file (less than 100,000 lines), you can Select COPY, and then use PL / SQL Developer: first, the establishment of a good table, pay attention to the source table and destination table fields in the corresponding sequence, and then run SQL statement select * from table for update or in the left tree menu, select the appropriate forms, right-click in the pop-up menu, click "Edit Data". and then open the form data at the top of the lock, and then click the plus sign, add The new line of blank lines. click of the mouse to the first space and then paste. COMMIT can be. (small tips, Oracle does not support, but is useful, there will be no problem)

Oracle Database exp imp Import Export by user examples 2009-04-06 18:58:52
Oracle Database Exp / Imp tools for performance tuning 2009-04-06 11:12:24
oracle database backup and restore 2009-04-03 18:35:52
oracle database backup and recovery 2009-04-03 10:09:46
SQLPlus skills 2009-04-01 09:37:29

On the oracle of the four log

kods.net — Wed, 03 Feb 2010 06:04:52+0000

Database in their daily work is that we often use the log file, oracle log file is as follows.
background_dump_dest = / opt / oracle / admin / modl / bdump
user_dump_dest = / opt / oracle / admin / modl / udump
core_dump_dest = / opt / oracle / admin / modl / cdump
audit_file_dest = / opt / oracle / admin / modl / adump

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

RMAN said, I can back up (9) - RMAN incremental backup and the backup retention policy

kods.net — Tue, 02 Feb 2010 20:51:00+0000

Advanced exercises chapter 8.5 RMAN backup

Previous chapter describes how to use various RMAN backup, advanced articles are mainly RMAN some of the extensions and enhancements, learning the content of your front end can be completed in The backup of the database, but by learning that this is, you can better, faster, more scientific and more reasonable ... ... (Cloze, please conscientiously at suitable locations to add 80000 more at the beginning of the adjective) to complete the database backup.

The contents of this section are many and mixed, for convenience in reading, the following will be introduced one by one in the form of multiple-choice questions, if you rush that Han, you can look that part of his concern.

8.5.1 has chosen an incremental backup

Although the backup with the BACKUP command generates a backup only focused on those who use the data blocks, the actual size of the backup set has been lower than the target database's data files much smaller. But to modify the data files are not frequent, but larger database, perform a backup certainly hope that there is a feature that backup if they can only back up those modified data to a backup set is not so much smaller will do. This is naturally thought of Oracle, incremental backup feature is designed to meet this need arose.

If the database is running in non-archive mode, then you can only clean off in the database (in NORMAL, IMMEDIATE, TRANSACTIONAL mode off) and take place under conditions consistent incremental backups, if the database is running in archive mode, then either closed state in the database, you can also open in the database back up (again illustrates the advantages of open archive model, archive logs but much of the more disk space, the benefits are not some, many, but also added a layer of the database is equivalent to Youji Insurance ).

8.5.1.1 Incremental backup options

The establishment of an incremental backup is quite simple in essence is to specify a parameter INCREMENTAL LEVEL = n, in the implementation of the BACKUP command with the can, you can create two levels of incremental backups, with integer numbers 0 ... n, said (n largest not more than 4 ), from level 0, all incremental backups must first create a level 0 backup. 0 backup is equivalent to a full backup of the database.

Note, by BACKUP DATABASE are also created a full backup of the database, but this is not the same as a full backup of the incremental backup level 0 backup (although it is a full backup of the database), because the conventional full backup does not contain additional the amount of backup strategy, do not support on its basis to create an incremental backup set.

When you create an incremental backup, either to back up the entire database, you can also only the table space or even to create an incremental backup of the data file level, such as:

The establishment of full incremental level 0 database backup:

BACKUP INCREMENTAL LEVEL = 0 DATABASE;

For the data file user01.dbf Level 1 incremental backup of the establishment:

BACKUP INCREMENTAL LEVEL = 1 DATAFILE 'F: \ ORAHOME1 \ ORADATA \ JSSWEB \ USERS01.DBF';

USERS tablespace to establish the level of an incremental backup:

BACKUP INCREMENTAL LEVEL = 1 TABLESPACE USERS;

8.5.1.2 Incremental Backup Type

RMAN provides two types of incremental backup: DIFFERENTIAL (difference), and CUMULATIVE (cumulative), these two types of incremental backup set is created when the algorithm is not the same. By default, RMAN creates an incremental backup is a DIFFERENTIAL way, if you want to build CUMULATIVE the way incremental backups, you need to implement explicitly specify the BACKUP command.

For example, incremental backups CUMULATIVE create a full backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE DATABASE;

With regard to the conceptual interpretation of incremental backups, such as the DIFFERENTIAL and CUMULATIVE are two ways to see that this is the difference between the rumor, in parentheses, rumored finishing, if you see that this is not a time to rumor, this ... ... Do not worry, be patient wait, bread and milk will be there.

8.5.1.3 Incremental Backup Notes

Many friends of the RMAN's incremental backup recognize there is some misunderstanding that the adoption of incremental backups, not only can reduce the backup set size, but also to improve the efficiency of the backup, saving backup time, and I would like to say, some very special circumstances This is not entirely wrong.

In 9i and earlier versions, regardless of whether it is an incremental backup, RMAN backups in the implementation, we need to first read all the data blocks of all the memory, checking each of the SCN of a data block header information, and the parent with the backup set incremental backups compared to determine whether the block has been modified. If you find that block has been modified, then the block where the data file to be re-backup (yes, check at the block level, but the backup is the data file level). It can be seen, unless you change the system are very frequent (too frequent changes whether the system is also necessary to create an incremental backup, this is a problem), or I / O devices with remarkable efficiency (I mean, amazing slow), Otherwise incremental backups only save storage space, we can not reduce the backup time, can not meet the purpose of improving the efficiency of the backup, and even may lead to a longer backup time (because more data block checking operation).

In 10g, Oracle offers a new feature: block change tracking (Block Change Tracking). Enable block change tracking, RMAN not have to go scan the data file for each block, and directly through the block you can modify the trace file which blocks access to the information changes have taken place.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/ LOACTION / TRK_FILENAME';

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SELECT STATUS FROM V $ BLOCK_CHANGE_TRACKING;

Need to remind one, be sure to recognize that, although the backup time may be shortened, and the backup set size may be smaller, but the recovery operation, the time required may increase, because the database recovery, they have to read more the backup set to complete the restore operation.

Backup and restore itself is relative, there's idiom is called the Consumer He rose only fitting to describe the relationship between Talia.

8.5.2 Set whether to use RMAN backup retention policy

Strategy, tut, never thought咱could say such a professional word, ah, they said experts, is to be able to describe in any affair, very professional, I have to learn a few more similar words, to make their own distance from the experts More recently, some, or, I moved to the experts directly to the edge of bed to go live.

If you are The management of very large databases, and backup implementation is also more frequent (nonsense, are also not infrequent in such a dry, excellent DBA must have a corresponding identity of good work habits), it is necessary for you to save these backup files formulate a reasonable strategy. The Norwegian's Norwegian, the move of the move, the removal of the deletion, reasonable release, to maximize use of limited disk space Well.

Created through RMAN backup pieces, because the backup file is created and maintained by RMAN, so manual deletion is not wise, and also provides a backup RMAN retention policies, rational design, by the RMAN automatically delete old backup files are also more secure more convenient.

Provides two kinds of RMAN backup retention policy: Based on the time and the number of redundancy-based backup retention policies.

8.5.2.1 Time-based backup retention policy

To put it more simply, that is, you want the database to restore to the earliest days ago. Example, recovery time will be set to 7, then the RMAN backup is retained can guarantee that you will recover the database to any time within a week, under those documents.

Set time-based backup retention policies can be CONFIGURE command, for example:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS;

Note: n = a positive integer greater than 0.

After the implementation of the command, RMAN will always be those who will recover the database to retain n days before the state need to use a backup, for example, recovery time is set to 7 days, then the backup of all data files must meet the following conditions:

SYSDATE - BACKUP CHECKPOINT TIME> = 7

Does not satisfy the above conditions any of the backup will be obsolete RMAN and can be removed through the DELETE OBSOLETE command. OK, finished the basic knowledge, here you have the time to test, question: If you meet the conditions you want to delete the backup, Zezheng? Han? So soon forgotten? Quickly and then go back and take a look at 8.3.

8.5.2.2 Based on the number of redundant backup retention policy

Based on the number of redundancy is essentially a data file in various forms (including the backup sets and image copies) exist in the number of backups. If a redundant backup of data files exceeds the number of a specified number, RMAN backup will be obsolete the oldest.

Similarly, volume-based backup retention policy is through the CONFIGURE command to set, for example:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY n;

n = a positive integer greater than 0.

DBA can also set the following command does not use any backup retention policy:

RMAN> CONFIGURE RETENTION POLICY TO NONE;

If you do not set up any backup retention policy, use the REPORT OBSOLETE and DELETE OBSOLETE command will not be any matching records, but the REPORT OBSOLETE and DELETE OBSOLETE command also supports the REDUNDANCY and RECOVERY WINDOW parameters, parameter values correspond to the rules and CONFIGURE commands to configure a backup retention policies are identical, so if you decide to display and delete expired orders written in the script on a regular basis, I do not through the backup retention policies, but rather directly through the REPORT and DELETE commands itself to achieve this is feasible.

8.5.2.3 according to backup strategy to maintain a backup set

For the RMAN backup retention policy is set, after, RMAN will automatically determine which backup set or image copy files no longer retained. These backup files will be marked as "obsolete" (Obsolete), through the REPORT OBSOLETE command to view the current state of the backup files are obsolete, or through the DELETE OBSOLETE command to delete these obsolete backups. For example:

Report

Type keyword completion time of the file name / handle

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

Backup set 2104-7 months -07

Backup Section 2104-7 dated -07 D: \ BACKUP \ C-3391142503-20070704-01

RMAN> DELETE OBSOLETE;

RMAN retention policy will be applied to the order

RMAN retention policy will be set to 3-day recovery window

The distribution channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 14 devtype = DISK

Remove the following obsolete backups and copies:

Type keyword completion time of the file name / handle

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

Backup set 2104-7 months -07

Backup Section 2104-7 dated -07 D: \ BACKUP \ C-3391142503-20070704-01

Are you sure you want to delete the above objects (enter YES or NO)? Y

Backup paragraph has been deleted

Backup segment handle = D: \ BACKUP \ C-3391142503-20070704-01 recid = 21 stamp = 627061645

An object has been deleted

Delete command in the implementation of two things need to know:

If you have been judged to waste a single data file backup is a mirror copy, then the implementation of the DELETE command to delete this image directly copy the files.
If judged as obsolete backup is one part of the backup set, you must wait until the entire backup set all the other files are obsolete before they could delete the backup set.

For the incremental backup, whether it is time-based or based on the number of redundancy, RMAN backups are testing whether the 0 to meet the demand.

oracle database backup and restore 2009-04-03 18:35:52
rman incremental backups 2009-04-02 15:10:17
RMAN created physical standby 2009-03-30 22:49:29
Block Oracle paragraph (reprint) 2009-03-26 17:41:14

RMAN said, I can back up (8) - RMAN commonly used commands Continued

kods.net — Tue, 02 Feb 2010 18:09:11+0000

Advanced exercises chapter 8.5 RMAN backup

8.5.1 has chosen an incremental backup

8.5.1.1 Incremental backup options

BACKUP INCREMENTAL LEVEL = 0 DATABASE;

For the data file user01.dbf Level 1 incremental backup of the establishment:

BACKUP INCREMENTAL LEVEL = 1 DATAFILE 'F: \ ORAHOME1 \ ORADATA \ JSSWEB \ USERS01.DBF';

USERS tablespace to establish the level of an incremental backup:

BACKUP INCREMENTAL LEVEL = 1 TABLESPACE USERS;

8.5.1.2 Incremental Backup Type

8.5.1.3 Incremental Backup Notes

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/ LOACTION / TRK_FILENAME';

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SELECT STATUS FROM V $ BLOCK_CHANGE_TRACKING;

8.5.2 Set whether to use RMAN backup retention policy

8.5.2.1 Time-based backup retention policy

8.5.2.2 Based on the number of redundant backup retention policy

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY n;

n = a positive integer greater than 0.

DBA can also set the following command does not use any backup retention policy:

8.5.2.3 according to backup strategy to maintain a backup set

Delete command in the implementation of two things need to know:

If you have been judged to waste a single data file backup is a mirror copy, then the implementation of the DELETE command to delete this image directly copy the files.
If judged as obsolete backup is one part of the backup set, you must wait until the entire backup set all the other files are obsolete before they could delete the backup set.

For the incremental backup, whether it is time-based or based on the number of redundancy, RMAN backups are testing whether the 0 to meet the demand.

rman incremental backups 2009-04-02 15:10:17
RMAN created physical standby 2009-03-30 22:49:29
Block Oracle paragraph (reprint) 2009-03-26 17:41:14
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17

Hash join algorithm principle

kods.net — Tue, 02 Feb 2010 16:51:22+0000

Hash join algorithm principle

Since the oracke 7.3 years, oracle provides a new join techniques, that is, hash join. Hash Join can only be used for the same connection, and only in the CBO optimizer mode. Relative to the nested loop join, hash join is more suited to deal with large result sets. Hash join do not need to drive the table there is no index.

1. Hash Join an overview of

Hash join algorithm is a basic idea is that according to a small row sources (called the build input, we recorded a smaller table for S, a larger table for the B) to create a presence in the hash area memory hash table, and then use a large row sources (called the probe input) to detect the previously built in hash table. If the hash area memory is not big enough, hash table can not be completely stored in the hash area memory. In view of this situation, Oracle connection keys using a hash function will build input and probe input is not connected to split into multiple partitions (respectively denoted by S _i and B _i), this stage is called the partition phase; and their corresponding partition, namely, S _i and B _i do Hash join, at this stage is called the join phase.

If the partition for a partition of the hash table is built too much, then, oracle on the use of nested-loops hash join. The so-called nested-loops hash join is the right part of the S _i build hash table, and then read all of the B _i with the built in hash table to do to connect, and then the remaining S _i build hash table, and then all of the B _i with the The hash table was built to do connections, until all of the S _i are connected finished.

Hash Join algorithm has a limit, it is because the assumption that two keys on the table connection is uniform, meaning that each partition has a similar data. However, in practice data is uneven, in order to properly solve this problem, oracle has introduced several technologies, bitmap vector filtering, roles were reversed, histogram, the specific meaning of these terms will be detailed later.

二. Principles of Hash Join

We use an example to explain the principles of Hash Join algorithm, as well as the above-mentioned terms.

Consider the following two data sets.

S = (1,1,1,3,3,4,4,4,4,5,8,8,8,8,10)

B = (0,0,1,1,1,1,2,2,2,2,2,2,3,8,9,9,9,10,10,11)

Hash Join The first step is to determine the small table (that is, build input) is fully stored in the hash area memory. If we can completely stored in memory in the memory, to create hash table, this is the most simple hash join.

If you can not all be stored in memory, you need to build input partition. The number of partitions is called fan-out. Fan-out is hash_area_size and the cluster size to decide. One cluster size is equal to db_block_size * hash_multiblock_io_count, hash_multiblock_io_count is implicit in oracle9i parameter. It should be noted that the fan-out is not the build input size / hash_ara_size, that the decision oracle partition size may still not be fully stored in the hash area memory. A large fan-out led to many small district, affecting performance, while a small fan-out led to a small number of large partitions, so that each partition can not all be stored in memory, which also affects the performance of hash join.

Oracle uses a hash function is applied to the internal link button on the S and B will be split into multiple partitions, where we assume for the sake of this hash function is more than a function, that is, Mod (join_column_value, 10). This produces 10 partitions, the following table.

Zoning

B0

B1

B2

B3

B4

B5

B6

B7

B8

B9

Value

0,0,10,10

1,1,1,1,11

2,2,2,2,2,2

3

NULL

NULL

NULL

NULL

8

9,9,9

S0

10

√

S1

1,1,1

√

S2

Null

S3

3,3

√

S4

4,4,4,4

S5

5

S6

NULL

S7

NULL

S8

8,8,8,8

√

S9

NULL

After this partition, there is only need to join the corresponding partitions can be made between (that is, the so-called partition pairs), if there is a partition as a NULL, then join the corresponding partitions can be ignored.

In the S table is read into memory partition, oracle that is the only key values of records connections to build a so-called bitmap vector, it needs to account for hash area memory about 5%. Here, namely, (1,3,4,5,8,10).

When the partition table B, it will be a connection for each key value on a bitmap vector comparison, if not one of them, it will discard its record. In our example, B in the table the following data will be discarded

(0,0,2,2,2,2,2,2,9,9,9,9,9). This process is a bitmap vector filtering.

When the S1, B1 finished connection, and then pairs of Si, Bi connected, where oracle will compare two partitions, select the little that do build input, is the dynamic roles were reversed, the roles were reversed this dynamic occurs in addition to the first pair of partition partition other than the above.

三. Hash Join Algorithm

Step 1: to determine whether all of the small tables stored in the hash area memory and, if so, then do the memory hash join. If not, switch to the second step.

Step 2: determine the number of fan-out.

(Number of Partitions) * C <= F _avm * M

Where C is Cluster size,

Step 3: Read the small table S, using an internal hash function (referred to here as hash_fun_1), will connect key mapped to a partition at the same time to connect with hash_fun_2 function key produces another hash value, this hash value is used create a hash table in use, and the keys stored together with the connection.

Step 4: To build input to establish the bitmap vectors.

Step 5: If there is no room in memory, and will partition to write to the disk.

Step 6: Read the rest of the small table S, repeat the third step, until all of the small table S read.

Step 7: The partition sorted by size, select the establishment of several geographical hash table (select the partition where the principle is to select the largest number).

Step 8: According to the previous calculation using hash_fun_2 a good hash function values, the establishment of hash table.

Step 9: Read the table B, using the bitmap bitmap vectors vector filtering.

Step 10: The data used by filtering the data mapped to the hash_fun_1 function corresponding to the partition, and calculate the hash value of hash_fun_2.

Step 11: If the drop-divisions in the memory, will be in front of the function by hash_fun_2 calculated hash value already exists in memory hash table to do connections, The result was caused by disk. If the fall of the partition is not memory, the corresponding value will be the corresponding S partition table together.

Step 12: Continue to read the table B, repeat Step 9 until the completion of Form B to read.

Step 13: Read the corresponding (Si, Bi) to do hash connection. Dynamic role in the swap will happen here.

Step 14: If the partition after the partition than the smallest memory, large, occurred nested-loop hash join.

四. Hash Join the cost of

1. In-Memory Hash Join

Cost (HJ) = Read (S) + build hash table in memory (CPU) + Read (B) +

Perform In memory Join (CPU)

Ignore the cpu time, then the

Cost (HJ) = Read (S) + Read (B)

2. On-Disk Hash Join

Based on the above description of the steps, we can see that

Cost (HJ) = Cost (HJ1) + Cost (HJ2)

Where Cost (HJ1) is the cost of scanning S, B list, and can not be placed in memory on the part of the write-back disk, corresponding to the previous Step 2 to Step 12. Cost (HJ2) shall be done nested-loop hash join costs, corresponding to the previous step 13 to step 14.

Where Cost (HJ1) is approximately equal to Read (S) + Read (B) + Write ((SM) + (BB * M / S)).

Because in doing nested-loop hash join, the right of each chunk of the build input, all need to read the entire probe input, so

Cost (HJ2) is approximately equal to Read ((SM) + n * (BB * M / S))

Where n is the nested-loop hash join need to loop through the number of times.

n = (S / F) / M

In general, if n is 10, then, hash join performance would be greatly diminished. From the formula for calculating n can be seen, n and Fan-out in inverse proportion to improve the fan-out, can be reduced n. When the hash_area_size is fixed, we can reduce the cluster size to increase the fan-out.

From here we can see that, improve hash_multiblock_io_count value of the parameter does not necessarily improve the performance of hash join.

五. Other

1. Confirmed that the small table is the table-driven

2. Confirmed that involves analysis of the tables and connection keys before.

3. If the connection key data on non-uniform, then suggested a histogram.

4. If you can, adjusting the size of large hash_area_size or pga_aggregate_target value.

5. Hash Join is suitable for small tables and large table join returns a large result set connections.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
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 10g em to change

kods.net — Tue, 02 Feb 2010 14:43:17+0000

The oracle10g in, em access address: http://ip:1158/em

In oracle 11g in, em access to the address changed to: https: / / ip: 1158/em

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 Application recovery SCN

kods.net — Tue, 02 Feb 2010 10:48:39+0000

Check the database backup
RMAN> LIST BACKUP OF DATABASE;
FULL SCN = 26905728480

Check the archive log backup
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
635 (26,905,725,274) - "638 (26,928,633,424)

A local archive log file list is: 639 --- 763, after a problem 765 - 770

The current database SCN = 35441074539

CONTROL backup
RMAN> LIST BACKUP OF CONTROLFILE
2006-09-08 backup SCN = 26928866676

Began to recover:

1. To restore archived log files
RMAN> RUN (
ALLOCATE CHANNEL D1 TYPE DISK;
SET ARCHIVELOG DESTINATION TO '/ ORACLE / ORADATA / ARCHIVELOG';
RESTORE ARCHIVELOG SEQUENCE BETWEEN 635 AND 638;
RELEASE CHANNEL D1;)

2. To restore the old control file
SQL> STARTUP NOMOUNT;
RMAN> restore controlfile from 'Z: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ ORCL \ AUTOBACKUP \ 2006_09_08 \ O1_MF_S_600573612_2J0QKFTK_.BKP';

3. To restore database files
run (
allocate channel d1 type disk;
restore database;
release channel d1;) 15:00 began.

4. Data Recovery
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
AUTO

SQL> ALTER DATABASE OPEN RESETLOGS;

5. Backup the database
RMAN> BACKUP FULL DAETABASE FORMAT 'E: / RMAN / DB_% U. BAK';

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 mode

kods.net — Tue, 02 Feb 2010 01:10:44+0000

oracle model of an archive. See whether the oracle database archive mode:
1.select name, log_mode from v $ database;
NAME LOG_MODE
------------------ ------------------------
QUERY NOARCHIVELOG
2. Using the ARCHIVE LOG LIST command
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / data/oracle/product/10.2.0/db_1 / / dbs / arch
Oldest online log sequence 739
Current log sequence 741
二. What is the Oracle archive mode?
Oracle databases online redo logs, this log is to record the changes made to the database, such as insert, delete, update data on these operations are recorded in online redo log inside. General database at least two online redo log group. When an online redo log group is filled, when log switch occurs, when the online redo log group 2 as the current use of the log, when the filled online redo log group 2 when the log switch will happen , to write the online redo log group 1, it is so repeatedly.
If the database is in non archive mode, on-line will be discarded when you log in the switch. While in archive mode, when the log switch occurs when the logs will be switched archiving. For example, the current use of online redo log 1, when a filled at a time when the log switch, start writing online redo log 2, when the contents of an online redo log will be copied to another specified directory. This directory is called the archive directory, copy the file called archived redo log.
When it is running a database using the archive can be disaster recovery.
1. Archive log mode and non-archive log mode of the difference between the non-archive log mode can only do cold backup, and recovery can only do a full backup. The latest full backup to the system error data for the period can not be restored.
Archiving can be done hot backup mode, and can do incremental backups can be done partially.
With the ARCHIVE LOG LIST can see a view mode state, or non-archive mode archive log mode.

三. Configuration database archiving mode

Changes in the non-archive mode to archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE ARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

5) SQL> do a full backup, as non-archive log mode for the resulting backup archive log mode no longer be used. This step is very important to the non-!

To change the archive mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

3. Enable automatic archiving: LOG_ARCHIVE_START = TRUE
Archiving mode, the log file group not allowed to be overwritten (rewritten), when filled, after the log file, without manual archiving, then the system will hang, know that archiving is completed.
At this time can only be read but not write.
During operation shut down and restart the process of archiving logs
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

4. Manual archiving: LOG_ARCHIVE_START = FALSE
Archive the current log file
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Archive log files serial number 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archiving of all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
To change the archive log target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& PATH';

5. Archive mode and non-archive mode conversion

The reverse process of Step 4.

6. Configure multiple archive processes
Q: When do you need to use multiple archiving process?
A: If the archive process will consume a large amount of time, you can start multiple archiving process, which is a dynamic parameter, you can use ALTER SYSTEM dynamic modification.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;
Oracle9i can specify up to 10 archiving process and the archiving process-related dynamic performance views
v $ bgprocess, v $ archive_processes

7. Configuration archiving target, multi-archiving target, remote archiving goals, objectives LOG_ARCHIVE_DEST_n archive log format archive
Local archiving objectives:
SQL> LOG_ARCHIVE_DEST_1 = "LOCATION = D: ORACLEARCHIVEDLOG";
Remote archive target:
SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1";
Mandatory archiving objectives, and if an error, 600 seconds and try again:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION = E: ORACLEARCHIVEDLOG MANDATORY REOPEN = 600";
Optional archiving objectives, and if wrong, abandon the archive:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION = E: ORACLEARCHIVEDLOG OPTIONAL";

Filed target state: the goals and turn off archiving archiving archiving goals off target 1
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER
Open the archive target 2
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Archive log format
LOG_ARCHIVE_FORMAT

8. Access to archived log information
V $ ARCHVIED_LOG
V $ ARCHVIE_DEST
V $ LOG_HISTORY
V $ DATABASE
V $ ARCHIVE_PROCESSES

ARCHIVE LOG LIST;

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
DATA GURAD use DBMS_FILE_TRANSFER and cloning configuration database 2009-02-23 19:07:40
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

How to start Oracle service in UNIX

kods.net — Mon, 01 Feb 2010 23:30:23+0000

A. In order to start Oracle service in UNIX you need to login as a Oracle user.
Use su - command to login as oracle user.
Start Oracle server in UNIX

Type the following command, enter:
$ Su - oracle
Now use lsnrctl command to start service (usually located at / home/oracle/oracle/product/10.2.0/db_1/bin directory):
$ Lsnrctl start

Next start database:

$ Dbstart
Stop Oracle service in UNIX

To stop Oracle servuice type following two commands:
$ Lsnrctl stop
$ Dbshut

NOTE: Note that the start of the sid is specified by the environment variable, this is very important. If you think of multiple instances, one is by modifying the environment variables, start over. Or is to create multiple users, each user environment variables does not make sense to start a different sid.

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

linux install oracle10G

kods.net — Mon, 01 Feb 2010 22:30:03+0000

In the Linux x86 to install Oracle Database 10 g
Author: John Smiley rn

Learning in RHEL 2.1, RHEL 3 or SLES 8 on a scratch installation of Oracle Database 10 g of basic knowledge (only for evaluation) rn

Contents Overview Part 1: Install Linux
RHEL 2.1
RHEL 3
SLES 8
Part 2: Configure Linux for Oracle
Verify the system requirements to create the directory to create Oracle group and user accounts to configure Linux kernel parameter settings for the Oracle User Shell restrictions prevent errors! (Only SLES 8)
Oracle user's environment variables Part 3: Installing Oracle
Install the software Part 4: Configuration Storage File System Automatic Storage Management Conclusion Appendix
rn
rn

rnrn
Overview rn

This is the Linux software is installed on Oracle 10 g's Guide series, the main components of the first part. These articles described in detail all three Linux-distributions have been certified in English (no description Asianux), and assuming the use of low-cost Intel x86 hardware. The same time, step by step demonstration for the assessment of key Oracle 10 g product in the common hardware installation and configuration process. rn

The fundamental goal of the series is to demonstrate step by step how to install and configure the 10 g of the grid all the components. Through this series, you will learn how to install and configure a single instance of Oracle 10 g database (this), two-node Oracle RAC 10 g database, Oracle Application Server 10 g and Oracle Enterprise Manager 10 g Grid Control for all major components. rn

The series provides a method to use the most simple way (the least number of steps) to complete the task. This method usually means that the selected configuration will only apply in the assessment. Therefore, the series is neither applicable to build production-quality environment, nor do they reflect best practices. rn

The following is described in these articles, access to Oracle 10 g certified three Linux distributions: rn

Red Hat Enterprise Linux 2.1 rn
Red Hat Enterprise Linux 3 rn
Novell SUSE Linux Enterprise Server 8 rn

If you are new to Linux and / or Oracle, that this first article specially for you. It first describes the basics from scratch and then gradually demonstrates how to install Oracle Database 10 g. rn
This paper consists of four parts: Part 1 describes how to install the Linux operating system, Part 2 describes how to configure Oracle for Linux, Part 3 describes the Oracle Database installed the main points of Part 4 describes how to create other file systems and describes the Automatic Storage Management (ASM) (Oracle Database 10 g in a great new feature, which dramatically simplifies storage management). Appendix provides a new database to help you understand the usage of information, including how to interactively access the database, how to stop and start the database and related services.

rn

rnrn
Part 1: Install Linux rn

This guide uses the following hardware configuration of the server: rn

Two 800MHz Pentium III CPU rn
512MB RAM rn
2 SCSI host adapter (Ultra SCSI 160) rn
Eight SCSI disk drives (2 x 9GB + 6 x 36GB) rn
A 100Base-T Ethernet Adapter rn
1 1000Base-T Ethernet Adapter

rn
Disk can be configured so that each SCSI host adapter to connect to a 9GB disk and three 36GB disks. rn

Please note that, although it hardly is a "powerful" the setting, but even using the low setting and then be able to complete most of the tasks described in this guide. Basic database installation requires only one CPU, 512MB memory and a free space of at least 6.5GB of disk drives (IDE, SCSI or FireWire). rn

Now, we will demonstrate step by step Linux operating system installed on the server process. This hypothesis of a new installation of Linux systems (as opposed to upgrade), and assuming the dedicated Oracle server, and the server, no other operating systems or data. rn

Red Hat Enterprise Linux 2.1 rn

Oracle 10 g certified in Red Hat Enterprise Linux 2.1 (Advanced Server and Enterprise Server) (Update 3) or later to run. Red Hat Network (RHN) to provide updates that can be used as used to create the ISO file or CD as a single package to update to download. rn

To install the application on the new Linux updates, the easiest and fastest way is to use the updated CD installation. If Linux is already installed, or CD, do not update, you can apply these updates through the RHN. Because this guide is for a new Linux installation, so you will use the updated CD. rn

Use the updated CD concentration of the first CD to start the server. rn
- You may need to change the BIOS settings to allow boot from the CD.
rn
Startup screen appears on display at the bottom of the boot:. rn
- Choose Enter, from the console on the implementation of the graphical installation. (For other installation methods and options, please refer to Red Hat Installation Guide.)
rn
- Installation program scans the hardware, Red Hat splash screen is displayed briefly, and then began to show a series of screen prompts.
rn
Select Language rn
- Accept the default values.
rn
Configure the keyboard rn
- Accept the default values.
rn
Configure the mouse rn
- Accept the default values.
rn
Type of installation rn
- Accept the default values.
rn
Set disk partition rn
- This article does not describe the detailed method of disk partitions, but assumes you are familiar with disk partitioning methods. rn
  (Warning: an error on the disk partition on the hard disk is to delete all the contents of the most reliable and most efficient methods. If you are unsure how to partition, please stop, find someone Bang Bang you, or you will risk the danger of losing data !) rn
  
  This article uses the following partition scheme (file systems are ext3):
  
  The first controller (/ dev / sda) on a 9GB disk contains the following partition, used to store all Linux and Oracle software:
  - 100MB / boot partition (for RHEL 2.1, minimum 50MB)
  -1,500 MB swap partition - the size of this partition is set for the system at least twice the RAM, but no more than 2GB (32-bit systems do not support more than 2GB swap file). If you need more than 2GB of swap space, you create more than one swap partition.
  -7,150 MB root partition - the partition will be used for all directories, including / usr, / tmp, / var, / opt, / home and so on. This is simply to explain and simplify the installation of this guide. A more reliable partitioning scheme is to bring these directories to a separate file system.
rn
Configure the boot loader rn
- Accept the default values.
rn
Configure the boot loader password rn
- If you want to set a password for GRUB, please enter here. For the evaluation system, without setting up of such.
rn
Configure the network rn
- Under normal circumstances, it is best to use a static IP address configuration database server. To do this, uncheck Configure using DHCP and enter the server's network configuration. Be sure to enter the host name and has been selected Activate on boot. rn
rn
Configure the firewall rn
- For this demonstration needs, will not configure the firewall. Select No firewall (security administrator, please do not angry).
rn
Other language support rn
- Accept the default values.
rn
Select Time Zone rn
- Choose your time zone settings. The system clock set to UTC on the server is usually in terms of a good practice. To do this, click the System clock uses UTC.
rn
Configure Account rn
- For the root user to select a password, and enter the given domain. Create an account for themselves. (Do not at this time to create oracle account; we will create it later.)
rn
Select the package group rn
- Select Gnome or KDE (or both options) graphical user interface (default Gnome). Note: These two are very popular GUI, which Gnomern more common in the United States to use. Personally, I prefer KDE, it is because it is a terminal emulator can be easily cut and paste, which can be copied to multiple input connections (for example, when you configure rnRAC multiple nodes, this will save type operation) .
rn
- Select Software Development
rn
The relevance of unresolved rn
- If you see this screen, simply select the Install packages to satisfy dependencies (default) and continue.
rn
Configuration graphical interface (X) rn
- Accept the default values, unless the installer does not recognize your video card. If your video card can not be identified will not be able to continue.
rn
Ready to install rn
- Click Next
rn
Installation package rn
- Software will be copied to the hard disk was installed. Prompted to change disks, and then when the installation is complete click Next.
rn
Create a boot disk rn
- If you need to create a boot disk, or select Skip boot disk creation.
rn
Configuration Monitor rn
- If the installation program correctly identify your monitor, you accept the default values. Otherwise, from the list, select a compatible monitor.
rn
Congratulations rn
- Removed from the system installation media, and then click Next.

The system automatically restarts, and displays a graphical login screen. If you are using the basic product CD instead of updating the implementation of the installation CD, then proceed through the RHN applications before updating. rn
Verify that your installation rnrn

If you complete the above steps, you should have Oracle 10 g all necessary packages and updates. You can perform the following steps to verify your installation. rn

The required kernel version :2.4.9-e .25 (or later) rn

By running the following command to check the kernel version: rn

uname -r

例如：
# uname -r
2.4.9-e.27smp

other required package versions (or later): rn

gcc-2.96-124 rn
make-3.79 rn
binutils-2.11 rn
openmotif-2.1.30-11 rn
glibc-2.2.4-31

To view the system is installed on which version of these packages, run the following command:

rpm -q gcc make binutils openmotif glibc

例如：  
# rpm -q gcc make binutils openmotif glibc
gcc-2.96-118.7.2
make-3.79.1-8
binutils-2.11.90.0.8-12
openmotif-2.1.30-11
glibc-2.2.4-32.8

if the system on the lack of any package version, or versions older than the version specified above, you can from the Red Hat Network to download and install the update. rn
Red Hat Enterprise Linux 3 rn

Oracle 10 g certified in the update of the situation without running Red Hat Enterprise Linuxrn3 (Advanced Server and Enterprise Server) the basic version. If you have updated CD, you can use the updated version of the boot rather than the basic version of CDrn start CD during the installation process automatically applied all updates. Oracle support for Red Hat all updates.

Using the first CD to start the server. rn
- You may need to change the BIOS settings to allow boot from the CD.
rn
Startup screen appears on display at the bottom of the boot:. rn
- Choose Enter, from the console on the implementation of the graphical installation. (For other installation methods and options, please refer to Red Hat Installation Guide.)
rn
- Installation program scans the hardware, Red Hat splash screen is displayed briefly, and then began to show a series of screen prompts.
rn
Select Language rn
- Accept the default values.
rn
Configure the keyboard rn
- Accept the default values.
rn
Welcome Screen rn
- Click Next.
rn
Configure the mouse rn
- Accept the default values.
rn
Type of installation rn
- Select Custom.
rn
Set disk partition rn
- This article does not describe the detailed method of disk partitions, but assumes you are familiar with disk partitioning methods. rn
  (Warning: an error on the disk partition on the hard disk is to delete all the contents of the most reliable and most efficient methods. If you are unsure how to partition, please stop, find someone Bang Bang you, or you will risk the danger of losing data !) rn
  
  This article uses the following partition scheme (file systems are ext3):
  
  The first controller (/ dev / sda) on a 9GB disk contains the following partition, used to store all Linux and Oracle software:
  - 100MB / boot partition
  -1,500 MB swap partition - the size of this partition is set for the system at least twice the RAM, but no more than 2GB (32-bit systems do not support more than 2GB swap file). If you need more than 2GB of swap space, you create more than one swap partition.
  -7,150 MB root partition - the partition will be used for all directories, including / usr, / tmp, / var, / opt, / home and so on. This is simply to explain and simplify the installation of this guide. A more reliable partitioning scheme is to bring these directories to a separate file system.
rn
Configure the boot loader rn
- Accept the default values.
rn
Configure the network rn
- Under normal circumstances, it is best to use a static IP address configuration database server. To do this, click Edit.
rn
- The emergence of a pop-up window. Uncheck the Configure using DHCP check box, and enter the server's IP address and network mask. Make sure that Activate on boot, and then click OK.
rn
- In the Hostname box, select manually and enter the host name.
rn
- In the Miscellaneous Settings box, enter the other network settings.
rn
Configure the firewall rn
- For this demonstration needs, will not configure the firewall. Select No firewall.
rn
Other language support rn
- Accept the default values.
rn
Select Time Zone rn
- Choose your time zone settings. The system clock set to UTC on the server is usually in terms of a good practice. To do this, click the System clock uses UTC.
rn
Set Root Password rn
- Enter the root password, and enter it again to confirm it.
rn
Select the package group rn
- Choose only the package assembly shown here, other options not to vote for.
rn
- Desktop
  - X Window System
  - Gnome
  - KDE
  - Please refer to me RHES 2.1 parts provided on the GUI choice views.
rn
- Application
  - Editor
  - Graphical Internet
rn
- Servers
  - Do not select any of the options in this group.
rn
- Development
  - Development Tools
rn
- System
  - Management Tools
rn
- Red Hat Enterprise Linux
  - Do not select any of the options in this group.
rn
- Miscellaneous
  - The original software development rn
- Click Next to continue.
rn
Ready to install rn
- Click Next.
rn
Installation package rn
- Software will be copied to the hard disk was installed. Prompted to change disks, and then when the installation is complete click Next.
rn
Configuration graphical interface (X) rn
- Accept the default values, unless the installer does not recognize your video card. If your video card can not be identified will not be able to continue.
rn
Configuration Monitor rn
- If the installation program correctly identify your monitor, you accept the default values. Otherwise, from the list, select a compatible monitor.
rn
Custom graphics configuration rn
- Accept the default values.
rn
Congratulations rn
- Removed from the system installation media, and then click Next.
rn
The system automatically restarts and displays a new Welcome screen. rn
- Click Next.
rn
License rn
- Read the license agreement. If you agree to these terms, select Yes, I agree to the License Agreement and click Next.
rn
Date and time rn
- Set the date and time.
rn
- If you want to use the NTP server (recommended), select Enable Network Time Protocol and enter the NTP server's name.
rn
User Account rn
- Create an account for themselves.
rn
- At this time not to oracle create an account. Described later in this section how to create oracle account.
rn
Red Hat Networkrn
- If you want to immediately use or activate the Red Hat Network account, you accept the default values, click Next and follow the Red Hat product activation instructions included with the product.
rn
Other CDrn
- Click Next.
rn
Complete the setup rn
- Click Next.
rn
The emergence of a graphical login screen. rn
Congratulations! Your Linux software is now installed.

Verify that your installation rn
If you complete the above steps, you should have Oracle 10 g all necessary packages and updates. You can perform the following steps to verify your installation. rn

The required kernel version :2.4.21-4. EL (which is the basic version of RHEL 3 with kernel version. This provided the kernel or update any version of the kernel are applicable to Oracle 10 g.) Rn

By running the following command to check the kernel version: rn

uname -r

例如：
# uname -r
2.4.21-4.0.1.ELsmp

other required package versions (or later): rn

gcc-3.2.3-2rn
make-3.79 rn
binutils-2.11 rn
openmotif-2.2.2-16 rn
setarch-1.3-1 rn
compat-gcc-7.3-2.96.122 rn
compat-gcc-c + + -7.3-2.96.122 rn
compat-libstdc + + -7.3-2.96.122 rn
compat-libstdc + +-devel-7.3-2.96.122 rn
compat-db-4.0.14.5 (Oracle 10g Database Installation Guide will be listed it as is necessary, but where it was not required)

To view the system is installed on which version of these packages in order to root user run the following command:

rpm -q gcc make binutils openmotif setarch compat-db compat-gcc \
compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel

例如：
# rpm -q gcc make binutils openmotif setarch compat-db compat-gcc \
>      openmotif compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel
gcc-3.2.3-20
make-3.79.1-17
binutils-2.14.90.0.4-26
openmotif-2.2.2-16
setarch-1.3-1
package compat-db is not installed
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3-2.96.122
compat-libstdc++-7.3-2.96.122
compat-libstdc++-devel-7.3-2.96.122

Please note that is not installed package compat-dbrn. During installation packages are available to any group not included in this package must be installed in a separate step. If the system on the lack of any other package versions, or versions older than the version specified above rn (compat-db exception), you can from the Red Hat Network to download and install the update.
Install compat-db rn

Insert the original Red Hat Enterprise Linux media, the second CD. (Update 2 is not included in the package, it exists only in the original media.) Rn

The CD automatically mount. rn

In order to root user run the following command:

rpm -ivh /mnt/cdrom/RedHat/RPMS/compat-db-4.0.14-5.i386.rpm

例如：
# rpm -ivh /mnt/cdrom/RedHat/RPMS/compat-db-4.0.14-5.i386.rpm
Preparing...         ########################################### [100%]
1:compat-db       ########################################### [100%]

rn
rn

SUSE Linux Enterprise Server 8 rn

Oracle 10 g certified in Novell SUSE Linux Enterprise Serverrn (SLES) 8 on the run. Novell via CD or through their support portal available online and package updates Service Pack. In this guide, we will rnCD install SLES 8 (Service Pack 3).

To start the server using the SLES 8 CD. rn
- You may need to change the BIOS settings to allow boot from the CD.
rn
There SUSE Linux Enterprise Server Setup screen appears. rn
- Select Installation.
rn
- Setup scans your hardware and display YaST interface.
rn
Select Language rn
- Accept the license agreement.
rn
- Accept the default value is English (US). rn
rn
Installation settings rn
- Choose New Installation.
rn
Zoning rn
- This article does not describe the detailed method of disk partitions, but assumes you are familiar with disk partitioning methods. rn
  (Warning: an error on the disk partition on the hard disk is to delete all the contents of the most reliable and most efficient methods. If you are unsure how to partition, please stop, find someone Bang Bang you, or you will risk the danger of losing data !) rn
  
  This article uses the following partition scheme (file systems are ext3):
  
  The first controller (/ dev / sda) on a 9GB disk contains the following partition, used to store all Linux and Oracle software:
  - 100MB / boot partition
  -1,500 MB swap partition - the size of this partition is set for the system at least twice the RAM, but no more than 2GB (32-bit systems do not support more than 2GB swap file). If you need more than 2GB of swap space, you create more than one swap partition.
  -7,150 MB root partition - the partition will be used for all directories, including / usr, / tmp, / var, / opt, / home and so on. This is simply to explain and simplify the installation of this guide. A more reliable partitioning scheme is to bring these directories to a separate file system.
rn
Software rn
- Click the Software link.
rn
- Software Selection screen appears.
rn
- Click Detailed Selection.
rn
- On the left window displays the software selection list. Click the check box next to each option to select / deselect.
rn
- Select the following software (this is the recommended software set; do not select other software):
  - C / C + + compilers and tools
  - KDE (or Gnome)
  - LSB Runtime Environment
  - Help and Support Documentation
  - Graphical Base System
  - YaST2 to configure the module
  - SLES management tools
rn
- We do not recommend installing the following items, as they may provide the same services to Oracle products conflict:
  - Simple Web Server
  - Authentication Server (NIS, LDAP, Kerberos)
rn
Time Zone rn
- Set your time zone.
rn
Click Accept. rn
A warning dialog box appears. Are ready to continue, click Yes, install. rn
Depending on the installation program prompts the replacement CD. rn
After installing this software, will display a window, suggesting that the basic system has been installed successfully. rn
- Remove the installation CD, and then click OK.
rn
The system restart. rn
Enter the root user's password, and enter it again to confirm it. rn
Create an account for themselves. Do not at this time to create oracle account; we will create it at a later date. rn
Desktop settings rn
- Accept the default values.
rn
Automatically detect the emergence of a local printer on the warning window. rn
- Click Skip detection.
rn
Configure the network interface, and are ready to continue operations and click Next. rn
- Proposal to set a static IP address for the server.
rn
- Construction of the database without the need to configure printers, modems, ISDN adapter or sound card.
rn
The emergence of a graphical login screen. rn
Now, you need to install Service Pack 3. Log on to your own. rn
Insert update CD, and then click the Patch CD Update. rn
Start YaST2 (SUSE's system management tools). rn
- Choose SUSE> System> YaST2. (SUSE is located in the Department of the screen bottom left side faces of the green lizard with a smile, a round button. It is equivalent to "Start.")
rn
- A window appears that prompts users to enter the root password. Enter a password, and then click OK.
rn
- There YaST Control Center.
rn
YaST Online Update window appears. rn
- Accept the default values, and then click Next.
rn
A window appears that displays an updated list of patches. rn
- YaST before updating the rest of the system need to be updated on its own. The required package has been selected. At this point do not select any other package.
rn
- Click Accept.
rn
A window appears that prompts to re-start the online update. rn
- Click OK.
rn
A window appears that prompts to install successfully. rn
- Click OK.
rn
In the YaST Online Update window, click Finish. rn
In the YaST Control Center window, click Close. rn
Repeat steps 21 and 22. rn
A window appears that displays an updated list of patches. rn
- Click Accept to accept the default values.
rn
- Likely contain a variety of tips (such as "Make sure you update sendmail") a small pop-up window. Click OK to close them.
rn
- This step will take some time. Hover your cursor at the Next button. If the update is still running, it displays a clock icon.
rn
A window appears that prompts to install successfully. rn
- Click OK.
rn
In the YaST Online Update screen, click Next. rn
- Installation program to write the system configuration.
rn
In the YaST Control Center, click Close. rn
Write-off. rn
- SUSE> Logout.
rn
Remove the update CD. rn
Restart the system. rn
- In the graphical login screen, click Menu.
rn
- Select Shutdown.
rn
- Select Restart computer.
rn
- Enter the root password.
rn
- Click OK.
rn
Congratulations! Your Linux software is now installed.

uname -r

例如：
# uname -r
k_smp-2.4.21-138

other required package versions (or later): rn

gcc-3.2.2-38 rn
make-3.79 rn
binutils-2.12 rn
openmotif-2.2.2-124

To view the system is installed on which version of these packages in order to root user run the following command:

rpm -q gcc make binutils openmotif

例如：  
# rpm rpm -q gcc make binutils openmotif
gcc-3.2.2-38
make-3.79.1-407
binutils-2.12.90.0.15-50
openmotif-2.2.2-124

If the system on the lack of any package version, or versions older than the version specified above, from the SUSE Linux Portal, you can download and install the update. rn
rn

rnrn
Part 2: For Oracle Configuration Linuxrn

Linux software has been installed, you need to configure it for Oracle. This section will explain step by step for Oracle Database 10 g configure Linux process. rn

Verify the system requirements rn

To verify that the system meets the Oracle 10 g database, the minimum requirements in order to root user to log in and run the following command. rn

To view the available RAM and swap space, run the following command:

grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo

例如：
# grep MemTotal /proc/meminfo
MemTotal:512236 kB
# grep SwapTotal /proc/meminfo
SwapTotal:1574360 kB

rn
The minimum required RAM for the 512MB, while the minimum required swap space is 1GB. For the RAM is less than or equal to 2GB system, swap space should be twice the amount of RAM; for more than 2GB of system RAM, swap space should be one to two times the number of RAM. rn

Oracle 10 g software also needs 2.5GB of free disk space, and the database will require an additional 1.2GB of free disk space. / tmp directory needs at least 400MB of free space. To check the available disk space on the system, run the following command:

df -h

例如：
# df -h
Filesystem        Size  Used Avail Use% Mounted on
/dev/sda3         6.8G  1.3G  5.2G  20% /
/dev/sda1         99M   17M   77M  18% /boot

rn
This example shows that, / tmp directory not have their own file system. (For this guide, it is the root file system part of it.) Root file system available space is 5.2 GB, in addition to meet the installation (2.5 + 1.2 + 0.4 = 4.1GB) things, there is a small surplus. rn

Create Oracle group and user accounts rn

Next, create for the installation and maintenance of Oracle 10 g software for Linux groups and user accounts. The user account will be known as the oracle, and the group will be called oinstall and dba. In order to root user execute the following command:

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
id oracle

例如：
# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba
# /usr/sbin/useradd -m -g oinstall -G dba oracle
# id oracle
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)

rn
Set oracle account password:

passwd oracle

例如：
# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd:all authentication tokens updated successfully.

rn
Create a directory rn

Now, create a stored Oracle 10g software and database files. This guide when creating the directory structure, naming conventions in line with the Optimal Flexible Architecture (OFA) specifications. For more information on OFA standards, see the Oracle Database for UNIX Systems Installation Guide 10g Appendix D. rn

The following assumptions in the root file system, create the directory. This is the sake of simplicity is not recommended as a universal practice. These directories are usually created as a separate file system. rnrn

In order to root user execute the following command:

mkdir -p /u01/app/oracle
mkdir -p /u02/oradata
chown -R oracle:oinstall /u01/app/oracle /u02/oradata
chmod -R 775 /u01/app/oracle /u02/oradata

例如：
# mkdir -p /u01/app/oracle
# mkdir -p /u02/oradata
# chown -R oracle:oinstall /u01/app/oracle /u02/oradata
# chmod -R 775 /u01/app/oracle /u02/oradata

rn
Configure Linux kernel parameters rn

Linux kernel very well. Unlike most other * NIX systems are different, Linux allows the system up and running most of the kernel parameters to modify. After the completion of the kernel parameter changes do not have to restart the system. Oracle Database 10 g requires the following kernel parameter settings as shown. Which gives the minimum, so if your system uses the value of the larger, do not change it.

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

rn
If you follow the above instructions to install the Linux, and kernel parameters used in all default values, you can simply log in with root user after the next command.

cat >> /etc/sysctl.conf <kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
EOF
/sbin/sysctl -p

例如：
# cat >> /etc/sysctl.conf <
> kernel.shmall = 2097152
> kernel.shmmax = 2147483648
> kernel.shmmni = 4096
> kernel.sem = 250 32000 100 128
> fs.file-max = 65536
> net.ipv4.ip_local_port_range = 1024 65000
> EOF
# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
kernel.sysrq = 0
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

rn
In order to root user run the following command to verify your settings: rn

/sbin/sysctl -a | grep shm
/sbin/sysctl -a | grep sem
/sbin/sysctl -a | grep file-max
/sbin/sysctl -a | grep ip_local_port_range

例如：
# /sbin/sysctl -a | grep shm
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shm-use-bigpages = 0
# /sbin/sysctl -a | grep sem
kernel.sem = 250        32000   100     128
# /sbin/sysctl -a | grep file-max
fs.file-max = 65536
# /sbin/sysctl -a | grep ip_local_port_range
net.ipv4.ip_local_port_range = 1024     65000

rn
If the system parameter settings, parameter values smaller than the above, then edit the / etc / sysctl.conf file, add or change these parameters. Is complete, run the following command to activate the changes:

/sbin/sysctl -p

rn
For SLES 8, after the completion of the above steps to run the following command.

/sbin/chkconfig boot.sysctl on

rn
Shell limits for the oracle user settings rn

Oracle recommends that each Linux account, the process can be used to open the file number and the number of set limits. To make these changes to root user run the following command:

cat >> /etc/security/limits.conf <oracle               soft    nproc   2047
oracle               hard    nproc   16384
oracle               soft    nofile  1024
oracle               hard    nofile  65536
EOF

cat >> /etc/pam.d/login <session    required     /lib/security/pam_limits.so
EOF

rn
For RHEL 2.1 and RHEL 3, use the following command:

cat >> /etc/profile <if [ \$USER = "oracle" ]; then  
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF

cat >> /etc/csh.login <if ( \$USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF

rn
For SLES 8, use the following command:

cat >> /etc/profile.local <if [ \$USER = "oracle" ]; then  
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF

cat >> /etc/csh.login.local <if ( \$USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF

rn
Avoid the error! (Only SLES 8) rn

In SLES 8 install Oracle Enterprise Manager 10 g when there is an error-rn Because the required ports that the / etc / services as a reservation to cause installation failures. OEM DBConsole need to port 1830, and in SLES8rn environment, this port has / etc / services reservations. This error in MetaLink on the error number is 3513603. rn

To avoid problems during installation in order to root user to log on and install the Oracle 10 g before the software / etc / services file port 1830-1849 correspond to the line commented out. (Note: At this point, you may want to restart the server to ensure that the above changes take effect.) Rn

oracle user's environment variables rn

To use the Oracle product, should or must be set a few environment variables. For the database server, it is recommended to set the following environment variables: rn

ORACLE_BASE
ORACLE_HOME
ORACLE_SID
PATH rn

If you are on the same server you have multiple Oracle product or a database, then the ORACLE_HOME, ORACLE_SID, and PATHrn variables may change. ORACLE_BASE variable should not be changed, and can when needed in your login configuration file to set it. Oracle provides a utility called oraenvrn to set other variables.

Log in to oracle and through. Bash_profile or. Profile (bash or ksh), add the following lines will be added to the registry ORACLE_BASE configuration file:

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

rn
Or. Login (csh), add the following lines:

setenv ORACLE_BASE /u01/app/oracle

rn
This change will be the next time you log on to the oracle account the entry into force. To make changes to the current session is active, simply run this command from the command line. rn

rn

rnrn
Part 3: Installing Oracle rn

OTN download from the Oracle Database 10 g. Oracle offers a development and testing license free of charge. But does not provide support, and that the license does not allow for productive purposes. OTN provides a complete license agreement. rn

For Oracle 10 g distributions media can be used on a server, the easiest way is to download it directly to the server. rn

Using a graphical login to oracle log. rn

Create a directory to store Oracle 10 g distributions:

mkdir 10g_db

rn
From the OTN download Oracle Database 10g, your browser (Mozilla relatively easy to use) point http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/linuxsoft.html. Fill Eligibility Export Restrictions page, and read the OTN License Agreement. If you accept the restrictions and the license agreement, click I Accept. rnrn

Click ship.db.cpio.gz link, and save the file in the directory created for this purpose (10g_db) in - If you have not logged on to OTN, you may be prompted to log on at this time. rn

Extract this file:

cd 10g_db
gunzip ship.db.cpio.gz
cpio -idmv < ship.db.cpio

rn
Install the software rn

Using the oracle account. rn

Specify the database name (ORACLE_SID). The name is usually not more than five characters. This installation demo1. rn

Set environment variables: rn

Borne shell and Korn shell

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_SID=demo1; export ORACLE_SID

C shell

setenv ORACLE_BASE /u01/app/oracle
setenv ORACLE_SID demo1

Change the directory to Oracle 10 g to extract the location of the software. rn

例如：
$ cd $HOME/10g_db

rn
Change the directory to Disk1.

例如：
$ cd Disk1

rn
Start Oracle Universal Installer.

$ ./runInstaller

rn
rn

Welcome rn
- Click Next.
rn
Specify the list of directories and certificate rn
- If you've been to follow the steps in this guide, you can use the default values. Otherwise, edit the list of the directory path to point to the correct directory.
rn
- The operating system group name should be oinstall.
rn
If this is the first time on this machine to install Oracle, then you will receive a pop-up window prompts need to be root user to run orainstRoot.sh script. Root user to log on, change to the window in the specified directory, the implementation of the script, and then continue. rn
Specify the file location rn
- If you've been to follow the steps in this guide, you can use the default values. Otherwise, before continuing to ensure that the correct source path and destination path.
rn
Choose the type of installation rn
- Accept the default value of Enterprise Edition.
rn
With a specific product related prerequisite checks rn
- If you've been to follow the steps in this guide, all checks should be passed smoothly. If one or more of the check fails, then correct the problem before continuing.
rn
Select Database Configuration rn
- Accept the default value is Create a starter database and General Purpose.
rn
Specify the database configuration options rn
- The global database name entered into the database. The name should include the ORACLE_SID and the server domain name (for example, demo1.orademo.org, which demo1 yes ORACLE_SID, orademo.org is a domain name).
rn
- Enter the global database name, SID frame will be automatically filled.
rn
- Accept the default database character set.
rn
- Select Create database with sample schemas.
rn
Select Database Management Option rn
- Select Use Database Control for Database Management.
rn
Specify the database file storage options rn
- Select File System, and then enter the database file you want to use the path name (in this case, / u02/oradata).
rn
Designated backup and recovery options rn
- Select Do not enable Automated backups.
rn
Specify the database schema password rn
- Select Use the same password for all the accounts.
rn
- Choose a password, then enter twice to confirm.
rn
Abstract rn
- Shows a summary of the installed product.
rn
- Click Install.
rn
Installation rn
- This screen after installation of Oracle software, and links to several stages.
rn
- The end of the installation process a pop-up window displays configuration information. Note the Enterprise Manager URL, and then click OK to close the window.
rn
- Pop-up a "Setup Privileges" window prompts need to be root user to run the configuration script. Root user to log on, switch to the window in the direction of the directory, then run the root.sh script. The script prompts for the location of the local bin directory. Press Enter key to accept the default value. When the script is complete, return to the Setup Privileges window and click OK. (As shown below.)
rn rn rn rn

rn
The installation is complete rn
- Note the summary display URL, and ready when you click Exit.
rn
Congratulations! Your new Oracle 10 g database has been launched and can be used.

rn rn rn rn

rn
rn

rnrn
Part 4: Configure Storage rn

Part 3, we created a database using a single file system (/ u02/oradata) to disk storage. But for the Oracle database configuration is stored in different ways. rn

Part 4 describes the database configuration for this disk storage of other methods. In particular, it describes how to create other file systems, and how to use Automatic Storage Management rn (ASM). How to use the raw devices and Oracle Cluster File System (OCFS) will be in this series, is described in another article, the article describes how to use Oraclern Real Application Clusters (RAC) to create cluster database.

File System rn

The file system is the Oracle database to store data files, redo logs and control files are the most commonly used method. File system, easy to implement, and no third-party software to manage it. rn

In most cases, the file system in Linux, created during the initial installation. But sometimes in the initial installation (for example, after installing a new disk drive) to create a new file system. rn

This section describes how to build a new file system, Oracle database and how to use it. Unless otherwise stated, must be root user to run all commands. rn

To partition the disk rn

You must use an empty disk partition to create the file system. If you already have an empty disk partition available, then skip to the next step. rn

The following is a file system for Linux to create a new partition example. (Warning: an error on the disk partition on the hard disk is to delete all the contents of the most reliable and most efficient methods. If you are unsure how to partition, please stop, find someone Bang Bang you, or you will risk the danger of losing data .) rn

This example uses / dev / sdb (one is not an empty SCSI disk partition) for the entire disk (36 GB) to create a single partition.

例如：
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel.Changes will remain in memory only,
until you decide to write them.After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 4427.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help):p

Disk /dev/sdb:255 heads, 63 sectors, 4427 cylinders
Units = cylinders of 16065 * 512 bytes

Device Boot    Start       End    Blocks   Id  System

Command (m for help):n
Command action
e   extended
p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-4427, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-4427, default 4427):
Using default value 4427

Command (m for help):w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING:If you have created or modified any DOS 6.x
partitions, please see the fdisk manual page for additional
information.
Syncing disks.

rn
Now verify the new partition:

例如：
# fdisk /dev/sdb

The number of cylinders for this disk is set to 4427.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help):p

Disk /dev/sdb:255 heads, 63 sectors, 4427 cylinders
Units = cylinders of 16065 * 512 bytes

Device Boot    Start       End    Blocks   Id  System
/dev/sdb1             1      4427  35559846   83  Linux

Command (m for help):q

rn
Create a file system rn

Use ext3 to create the new file system. You can also use other file systems, but ext3 crash in the system provides the fastest recovery.

例如：
# mke2fs -j /dev/sdb1
mke2fs 1.26 (3-Feb-2002)
Filesystem label=
OS type:Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
4447744 inodes, 8889961 blocks
444498 blocks (5.00%) reserved for the super user
First data block=0
272 block groups
32768 blocks per group, 32768 fragments per group
16352 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632,
        2654208, 4096000, 7962624

Writing inode tables:done
Creating journal (8192 blocks):done
Writing superblocks and filesystem accounting information:done

This filesystem will be automatically checked every 23 mounts or
180 days, whichever comes first.Use tune2fs -c or -i to override.

rn
Create a mount point rn

The file system must have a mount point, it's just an empty directory, the new file system here with the system directory tree "connected." Mount point naming should follow the Oracle Flexible Architecture (OFA) standard. For more information on OFA standards, see the Oracle 10g Database Installation Guide Appendix D. rn

Because you have already used in Part 1 of / u01 and / u02 directory name, and therefore used in this example / u03.

例如：
# mkdir /u03

rn
The new file system will be added to / etc / fstab rn

So the new file system will be mounted automatically at boot time, you need to describe the new file system and its installation location of the / etc / fstab file, add a line. Use a text editor to / etc / fstab add the following line as shown.

/dev/sdb1         /u03           ext3    defaults       1 1

rn
Mount the new file system rn

Mount the file system after use. Only mount the file system before you can store files. Use the following command to mount the file system and verify that it is available.

mount /u03
df -h /u03

例如：
# mount /u03
# df -h /u03
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb1             33G   33M  31G   1%   /u03

rn
Create Oracle directory and set permissions rn

Now, create a directory used to store Oracle files. The sample used in the OFA database directory name to follow the standard naming conventions, that is, ORACLE_SID = demo1.

mkdir -p /u03/oradata/demo1
chown -R oracle:oinstall /u03/oradata
chmod -R 775 /u03/oradata

rn
In the new file system to create a new table space rn

The new file system is available. Next, in the file system to store database objects to create a new tablespace. To SYSTEM user to connect to the database, then run the CREATE TABLESPACE statement, and in the new file system specified in the data file.

例如：
$ sqlplus

SQL*Plus:Release 10.1.0.2.0 - Production on Sun Jun 13 15:01:08 2004

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

Enter user-name:system
Enter password:

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

SQL> create tablespace data1
2  datafile '/u03/oradata/demo1/data1_01.dbf' size 100M
3  extent management local
4  segment space management auto;

Tablespace created.

rn
Now, you can use this new table space to store database objects such as tables and indexes. rn

例如：
SQL> create table demotab (id number(5) not null primary key,
2  name varchar2(50) not null,
3  amount number(9,2))
4  tablespace data1;

Table created.

rn
Automatic Storage Management (ASM) rn

I put the most important elements remain in the final. ASM is Oracle Database 10 g rn in a very good new features, it provides a platform-independent way the file system, logical volume manager as well as software RAID services. ASMrn can be striped and mirrored disks, enabling database is loaded in the case of add or remove disks, as well as self-balancing I / O to remove "hot spots." It also supports direct and asynchronous I / O and use the rnOracle9 i introduced in the Oracle Data Manager API (simplified I / O system call interface). rn

ASM is not a common file system, and can only be used for Oracle data files, redo logs and control files. ASMrn files in either the database is automatically created and named (by using the Oracle managed file properties), or you can manually create and name from the DBA. ASMrn can not be accessed because the operating system stored in files, so the use of ASM database files to perform backup and restore operations only way is through Recovery Manager (RMAN).

ASM as a separate instance of the implementation of Oracle, and only when it is running other databases in order to access it. In Linux, only to run OCSSD services (from Oracle Universal Installer is installed by default) to use ASM. ASM needs little memory: For most systems, only 64 MB. rn

Install ASM rn

In the Linux platform, ASM can use raw devices, or through ASMLib interface management device. For ease of use and performance reasons, Oracle recommends the use of original equipment ASMLib. ASMLib now available for free download from OTN. This section explain step by step to configure a simple to use ASMLib build an ASM instance and ASM for disk storage using the database as a whole. rn

To determine the required ASMLib version of the rn

ASMLib to the package consisting of three Linux packages assembly provides: rn

oracleasmlib - ASM library rn
oracleasm-support - for managing ASMLib utility rn
oracleasm - ASM database kernel module rn

Each Linux distro has its own ASMLib package assembly. Each distributions, each kernel version has a corresponding oracleasm package. The following section describes how to determine the required package set. rn
First, using the root user to log in and run the following command to determine the use of cores:

uname -rm

例如：
# uname -rm
2.4.9-e.27smp i686

rn
This example shows that this is an Intel i686 CPU to use the SMP (multi-processor) systems 2.4.9 kernel. rn

Use this information to find the corresponding ASMLib on OTN package: rn

The Web browser to http://www.oracle.com/global/cn/technology/tech/linux/asmlib rnrn
Choose the appropriate link for your Linux version. rn
Download for your Linux version of oracleasmlib and oracleasm-support package rn

Download to your kernel corresponds oracleasm package. For the example above, download oracleasm-2.4.9-e-smp-1.0.0-1.i686.rpm package. rn
Next, in order to root user execute the following command to install the package:

rpm -Uvh oracleasm-kernel_version-asmlib_version.cpu_type.rpm \
oracleasmlib-asmlib_version.cpu_type.rpm \
oracleasm-support-asmlib_version.cpu_type.rpm

例如：
# rpm -Uvh \
> oracleasm-2.4.9-e-smp-1.0.0-1.i686.rpm \
> oracleasmlib-1.0.0-1.i386.rpm \
> oracleasm-support-1.0.0-1.i386.rpm
Preparing...                #################################### [100%]
1:oracleasm-support      #################################### [ 33%]
2:oracleasm-2.4.9-e-smp  #################################### [ 66%]
Linking module oracleasm.o into the module path [  OK  ]
3:oracleasmlib           #################################### [100%]

rn
Configuration ASMLib rn

The use of ASMLib, you must run the configuration script to prepare the driver. In order to root user run the following command, and respond to the following examples shown in the prompts.

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.The following questions will determine whether the driver is
loaded on boot and what permissions it will have.The current values
will be shown in brackets ('[]').Hitting  without typing an
answer will keep that current value.Ctrl-C will abort.

Default user to own the driver interface []:oracle
Default group to own the driver interface []:dba
Start Oracle ASM library driver on boot (y/n) [n]:y
Fix permissions of Oracle ASM disks on boot (y/n) [y]:y
Writing Oracle ASM library driver configuration            [  OK  ]
Creating /dev/oracleasm mount point                        [  OK  ]
Loading module "oracleasm"                                 [  OK  ]
Mounting ASMlib driver filesystem                          [  OK  ]
Scanning system for ASM disks                              [  OK  ]

rn
Now, as shown below to enable ASMLib driver. rn

# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration            [  OK  ]
Scanning system for ASM disks                              [  OK  ]

rn
Configuration for the ASM disk rn

Next, tell the driver you want to use ASM disk. Please note that these disks do not contain any content (or does not contain a partition) of disk space. Disk partition can be used for ASM, but recommended that you do not do this. rn

Through a root user run the following command to mark the disk used by the ASMLib:

/etc/init.d/oracleasm createdisk DISK_NAME device_name

rn
(Hint: DISK_NAME shall be composed of capital letters. The current version has an error, that is, if you use lower case letters, ASM instance will not recognize the disk.)

例如：
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb
Marking disk "/dev/sdb" as an ASM disk                     [  OK  ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/sdc
Marking disk "/dev/sdc" as an ASM disk                     [  OK  ]
# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd
Marking disk "/dev/sdd" as an ASM disk                     [  OK  ]
# /etc/init.d/oracleasm createdisk VOL4 /dev/sdf
Marking disk "/dev/sdf" as an ASM disk                     [  OK  ]
# /etc/init.d/oracleasm createdisk VOL5 /dev/sdg
Marking disk "/dev/sdg" as an ASM disk                     [  OK  ]
# /etc/init.d/oracleasm createdisk VOL6 /dev/sdh
Marking disk "/dev/sdh" as an ASM disk                     [  OK  ]

rn
The following example demonstrates how to set out the grounds of ASMLib tag access to all disks.

# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
VOL5
VOL6

rn
Now that you have installed the ASMLib, and the disk has been marked as available, the following, you can create an ASM instance, and build one using ASM for disk storage database. The easiest way is to use the Database Configuration Assistant (DBCA) to complete this operation. rn

Log in to oracle and start DBCA:

$ dbca

rn
rn

Welcome rn
- Click Next.
rn
Operation rn
- Select Create a Database.
rn
Database Template rn
- Select General Purpose.
rn
Database ID rn
- Enter the global database name.
rn
Management options rn
- Select Configure the Database with Enterprise Manager.
rn
- Select Use Database Control for Database Management.
rn
Database Certificate rn
- Select Use Same Password for All Accounts.
rn
- Enter a password and confirm.
rn
Storage options rn
- Select Automatic Storage Management (ASM).
rn
Create ASM instance rn
- Enter the password and confirm the ASM instance.
rn
Pop up a window, suggesting that DBCA will immediately create and start the ASM instance. rn
- Click OK.
rn
ASM instance to create a pop-up window with a gear box. rn
ASM disk group rn
- Since this is a new ASM instance, and you have not created any ASM instance, so the window does not display the available disk group.
rn
- Click Create New.
rn
Create Disk Group rn
- Disk group name
  - Enter the disk group name, such as DATA1.
rn
- Redundancy
  - High mirrors data twice.
  - Normal image data once.
  - External does not mirror the data in the ASM. If the external RAID arrays to provide redundancy, you normally use this option. rn
- Select members of the disk
  - You must manually direct ASM where to find ASMLib disk, therefore, click Change Disk Discovery Path
  - Pop up a window that prompts the disk discovery path. As shown below will be found to change the path to the disk "ORCL: *".
  - Click OK to continue.
rn rn rn rn

rn
- You will return to the Select Member Disks window. All of your ASMLib disks should now be displayed in this window.
  Click the OK at the beginning of the box, select the disk group by adding disks. Are finished, click OK.
rn rn rn rn

rn
ASM disk group rn
- You will return to the ASM Disk Groups window. At this point, appears in the previous step to create the disk group.
rn
- Click the OK at the beginning of the box to select the disk group. (In this instance the use of a single disk group.)
rn
- Click Next.
rn
Database File Location rn
- Select Use Oracle-Managed Files.
rn
- Make sure that the Database Area is set for you to create a disk group. It contains a leading plus sign - for example, + DATA1.
rn
Restore Configuration rn
- Select Specify Flash Recovery Area.
  - Fast recovery area - Enter the ASM for fast recovery disk group.
  - Fast recovery area size - Accept the default value is 2048.
rn
- If you want to use the archive log mode, then select Enable Archiving. For the assessment of the database, you can not vote for that.
rn
Database contents rn
- Select Sample Schemas.
rn
- We do not use custom scripts, so click Next.
rn
Initialization parameters rn
- Accept the default values, and then click Next.
rn
Database storage rn
- Accept the default values, and then click Next.
rn
Creating Options rn
- Select Create Database (default) and click Finish.
rn
Confirmed rn
- Ends For more information check the database and click OK.
rn
DBCA displays an action list and a progress bar. According to the different hardware, database creation may take several minutes to several hours of time. rn
When the database is created, it will show a summary window displays. Note the Enterprise Manager URL, and then click Exit.

rn rn rn rn

rn
rn

rnrn
Conclusion rn

Now that your database is up and running, you can start using Oracle 10 g provides many new features. Preferably from the Oracle Enterprise Manager to start using, it is because it is completely rewritten, using Web-based interface. If you are unsure where to start, Oracle concepts guide and a two-day DBA Quick guide will help you become familiar with the new database. OTN also provides some designed to help you take full advantage of 10 g article. My favorite was prepared by Arup Nanda series of articles "Oracle Database 10 g: for the DBA to provide 20 of the most important feature." Rn

In the next article, we will explain step by step how to install and configure a fully functional two-node Oracle RAC 10 g database. rn

rn

rnrn
Appendix rn

rn

Use SQL * Plus to access the database rn

To oracle Log in to Linux. Set the environment.

rn
Set Oracle environment variables:

$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_SID=demo1
$ export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
$ export PATH=$PATH:$ORACLE_HOME/bin

rn
Run SQL * Plus:

$ sqlplus

SQL*Plus:Release 10.1.0.2.0 - Production on Sat Jun 5 16:59:21 2004

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

Enter user-name:/ as sysdba

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

SQL>

rn
Use Oracle Enterprise Manager 10 g Database Control

rn
In the Web browser, connect to the installation process provided in the URL.

rn
For example:
http://ds1.orademo.org:5500/em (If the server is not DNS, then you must use IP addresses instead of host name.)

rn
User Name: SYS
Password: "Install the password selected during"
Connect to: SYSDBA rn

Click

rn rn rn rn

rn
Welcome into the Oracle Enterprise Manager 10 g database control, wonderful world!

rn
Start and stop Oracle Enterprise Manager Database Control:

$ emctl start dbconsole
$ emctl stop dbconsole

rn
Access to the database using iSQL * Plus

rn
iSQL * Plus is a long history of SQL * Plus an interactive Web-based versions of tools used to access the database. To use the iSQL * Plus, please click the Related Links section of the console rnOEM the iSQL * Plus link, or pointing your browser to the installation process provided in the iSQL * Plus URL.

rn
For example:
http://ds1.orademo.org:5560/isqlplus (If the database server is not DNS, then you must use IP addresses instead of host name.)

rn
User Name: SYSTEM
Password: "Install the password selected during> rn

Click .

rn
In the Workspace box, type the SQL command, and then click Execute.

rn rn rn rn

rn
Start and stop iSQL * Plus:

$ isqlplusctl start
$ isqlplusctl stop

rn
Start and stop the listener:

rn
Listener to accept client connection requests, and verify that certificate to create a database connection. To use the OEM or iSQL * Plus, you must first start the listener.

$ lsnrctl start
$ lsnrctl stop

rn
Start and stop the database:

rn
To start and stop the database, the easiest way is to start and stop the console from the OEM. To do this from the command line, please log in to oracle after the use of SQL * Plus, as follows:

rn
Start:

$ sqlplus

SQL*Plus:Release 10.1.0.2.0 - Production on Sun Jun 13 22:27:48 2004

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

Enter user-name:/ as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                   778036 bytes
Variable Size             162275532 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL> exit

rn
Shutdown:

$ sqlplus

SQL*Plus:Release 10.1.0.2.0 - Production on Sun Jun 13 22:25:55 2004

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

Enter user-name:/ as sysdba

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

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

rn
Oracle processes are listed:

$ ps a€“fuoracle

rn
What is the ocssd.bin process?

rn
Oracle Cluster Synchronization Services (OCSSD) is the Oracle Cluster Ready Services (CRS), a component for the RAC and the use of a single instance database ASMrn. It is installed by default, uses very few system resources, and / etc / inittabrn in the configuration. These processes start automatically when the system starts every time. Oracle is not recommended to disable OCSSD, even if you are not running ASM.

rn rn rn

RedHat AS 3 at oracle10.2.1 install document 2009-03-13 16:56:34
Oracle XDB relax resolve port conflict of 8080 2009-03-13 16:47:42
AX3.0 + Oracle 10G RAC installation 2009-03-12 19:19:31
An example of the use of TKPROF 2009-02-26 01:38:19

Oracle

[Change] Use shell handle the database alert log oracle

Related Posts

Oracle create table space. To create a user and to authorize. View permissions

Related Posts

Oracle data dictionary and dynamic performance views

Related Posts

oracle time, plus or minus a comprehensive

oracle time, plus or minus a comprehensive

Related Posts

With regard to the process of creating oracle dblink Some experience bar

Related Posts

ORA-01940 error resolution process

Related Posts

sqlldr guide data

Related Posts

sqlplus can execute PL / SQL procedure written

Related Posts

Oracle Wait Interface

Related Posts

sqlldr use of the whole

Related Posts

Export data from Excel to Oracle Method Summary - --

Related Posts

On the oracle of the four log

Related Posts

RMAN said, I can back up (9) - RMAN incremental backup and the backup retention policy

Advanced exercises chapter 8.5 RMAN backup

8.5.1 has chosen an incremental backup

8.5.1.1 Incremental backup options

8.5.1.2 Incremental Backup Type

8.5.1.3 Incremental Backup Notes

8.5.2 Set whether to use RMAN backup retention policy

8.5.2.1 Time-based backup retention policy

8.5.2.2 Based on the number of redundant backup retention policy

8.5.2.3 according to backup strategy to maintain a backup set

Related Posts

RMAN said, I can back up (8) - RMAN commonly used commands Continued

Advanced exercises chapter 8.5 RMAN backup

8.5.1 has chosen an incremental backup

8.5.1.1 Incremental backup options

8.5.1.2 Incremental Backup Type

8.5.1.3 Incremental Backup Notes

8.5.2 Set whether to use RMAN backup retention policy

8.5.2.1 Time-based backup retention policy

8.5.2.2 Based on the number of redundant backup retention policy

8.5.2.3 according to backup strategy to maintain a backup set

Related Posts

Hash join algorithm principle

Related Posts

oracle 10g em to change

Related Posts

ORACLE Application recovery SCN

Related Posts

oracle-archive mode

Related Posts

How to start Oracle service in UNIX

Related Posts

linux install oracle10G

Related Posts