2009/03

Copy the database using RMAN

kods.net — Tue, 31 Mar 2009 20:18:52+0000

Create a new instance

Better prepare the directory structure

E: oracleproduct 10.1.0 admintestbdump

E: oracleproduct 10.1.0 admintestcdump

E: oracleproduct 10.1.0 admintestcreate

E: oracleproduct 10.1.0 admintestpfile

E: oracleproduct 10.1.0 admintestscripts

E: oracleproduct 10.1.0 admintestudmp

Create parameter files inittest.ini (the original library can be used to modify pfile), the main parameters are as follows:

db_name = test

background_dump_dest = E: oracleproduct 10.1.0 admintestbdump

core_dump_dest = E: oracleproduct 10.1.0 admintestcdump

user_dump_dest = E: oracleproduct 10.1.0 admintestudump

control_files = 'E: oracleoradatatestcontrol01.ctl', 'E: oracleoradatatest control02.ctl', 'E: oracleoradatatest control03.ctl'

DB_FILE_NAME_CONVERT = ( 'E: oracleoradataning', 'E: oracleoradatatest')

LOG_FILE_NAME_CONVERT = ( 'E: oracleoradataning', 'E: oracleoradatatest')

At E: oracleproduct 10.1.0 admintestpfile or E: oracleproduct10.1.0db_1database can.

Oradim use to create a new instance

c:> oradim-new-sid test

Routines have been created.

Create a password file using orapwd

c:> orapwd file = E: oracleproduct 10.1.0 db_1databasePWDtest.ora password = test entries = 10

Better monitoring and configuration tnsnames, then test the connection using sqlplus

c:> sqlplus / nolog

SQL * Plus: Release 10.1.0 .2.0 - Production on Sunday, March 27 21:01:13 2005

SQL> conn sys @ test as sysdba

Please enter password:

Is connected to the free routine.

2. Rman backup using the original database

RMAN> connect target sys / ning @ ning

RMAN> backup full database tag 'fullbk' format 'e: oracleorabackfull% u_% s_% p';

3. The use of copy database rman

c:> rman target sys / ning @ ning AUXILIARY sys / test @ test

RMAN> DUPLICATE TARGET DATABASE TO 'test';

4. Check replication status library

C:> sqlplus / nolog

SQL> conn sys @ test as sysdba

SQL> select instance_name, status from v $ instance;

INSTANCE_NAME STATUS

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

test OPEN

Inittest.ora then remove the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters, re-use the document to generate spfile

SQL> create spfile from pfile = 'E: oracleproduct 10.1.0 db_1databaseinittest.ora ';

Document has been created.

Advanced Application of Duplicate copy RMAN database (3) replication process 2009-12-12 08:56:44
RMAN Advanced Application of Duplicate Copy Database (4) real 2009-12-12 09:27:19
RMAN Advanced Application of Duplicate Copy Database (5) added 2009-12-12 02:45:57
Advanced Application of Duplicate copy RMAN database (2) auxiliary instance 2009-12-12 04:16:01
Advanced Application of Duplicate copy RMAN database (1) Overview 2009-12-12 09:42:35

oracle10g ASM transport_tablespace

kods.net — Tue, 31 Mar 2009 19:15:20+0000

Solution1:
source: windows2003 32bit, oracle10g 10.2.0.2
target: IBM aix 64bit, oracle10g 10.2.0.3, RAC, ASM

Solution2:
source: HP UX 64bit, oracle10g 10.2.0.3, RAC, ASM
target: IBM aix 64bit, oracle10g 10.2.0.3, RAC, ASM

Solution1:
source: windows2003 32bit, oracle10g 10.2.0.2
GRANT DBA TO xsb IDENTIFIED BY xsb;
CREATE TABLESPACE xsbtest_ts DATAFILE 'D: ORACLEPRODUCT10.2.0ORADATACSLSCxsbtest_ts01.DBF' SIZE 5m;
SELECT * FROM v $ datafile;
CREATE TABLE xsb.t1 TABLESPACE xsbtest_ts AS SELECT * FROM dba_users;
SELECT * FROM xsb.t1;
ALTER TABLESPACE xsbtest_ts READ ONLY;
SELECT * FROM v $ transportable_platform ORDER BY platform_id;
- conn sys AS SYSDBA
- BEGIN dbms_tts.transport_set_check ( 'xsbtest_ts', TRUE, TRUE); END;
- SELECT * FROM TRANSPORT_SET_VIOLATIONS;

exp 'sys AS SYSDBA' FILE = 1.dmp tablespaces = xsbtest_ts transport_tablespace = y

ftp target
bin
put D: ORACLEPRODUCT10.2.0ORADATACSLSCxsbtest_ts01.DBF
bye

ALTER TABLESPACE xsbtest_ts READ WRITE;

target: IBM aix 64bit, oracle10g 10.2.0.3, RAC, ASM
GRANT DBA TO xsb IDENTIFIED BY xsb;
- DROP TABLESPACE xsbtest_ts INCLUDING CONTENTS and datafiles;

rman target /
CONVERT DATAFILE '/ home/oracle/XSBTEST_TS01.DBF' FROM PLATFORM 'Microsoft Windows IA (32-bit)'
format '+ DATA/aeg2t/datafile/xsbtest_ts.dbf';
EXIT

imp 'sys as sysdba' file = 1.dmp tablespaces = xsbtest_ts transport_tablespace = y datafiles = '+ DATA/aeg2t/datafile/xsbtest_ts.dbf'

SELECT * FROM xsb.t1;

Solution2:
source: HP UX 64bit, oracle10g 10.2.0.3, RAC, ASM
GRANT DBA TO xsb IDENTIFIED BY xsb; CREATE TABLESPACE xsbtest_ts2 DATAFILE SIZE 5m;
SELECT * FROM dba_data_files;
CREATE TABLE xsb.t1 TABLESPACE xsbtest_ts2 AS SELECT * FROM dba_users;
SELECT * FROM xsb.t1;
ALTER TABLESPACE xsbtest_ts2 READ ONLY;
SELECT * FROM dba_tablespaces;
SELECT * FROM v $ transportable_platform ORDER BY platform_id;
- conn sys AS SYSDBA
- BEGIN dbms_tts.transport_set_check ( 'xsbtest_ts2', TRUE, TRUE); END;
- SELECT * FROM TRANSPORT_SET_VIOLATIONS;
show parameter comp

exp 'SYS AS SYSDBA' FILE = 1.dmp tablespaces = xsbtest_ts2 transport_tablespace = y

rman target /
copy DATAFILE # # TO '/ home/oracle/xsbtest_ts2.dbf';
or
CONVERT TABLESPACE xsbtest_ts2 TO platform 'AIX-Based Systems (64-bit)' format '/ home/oracle/xsbtest_ts2.dbf';
EXIT

ALTER TABLESPACE xsbtest_ts2 READ WRITE;

- DROP TABLESPACE xsbtest_ts2 INCLUDING CONTENTS and datafiles;
- drop user xsb cascade;

ls-lt | more

ftp target
bin
put / home/oracle/xsbtest_ts2.dbf
put / home/oracle/1.dmp
bye

target: IBM aix 64bit, oracle10g 10.2.0.3, RAC, ASM
GRANT DBA TO xsb IDENTIFIED BY xsb;
- DROP TABLESPACE xsbtest_ts2 INCLUDING CONTENTS and datafiles;

rman target /
CONVERT DATAFILE '/ home/oracle/xsbtest_ts2.dbf' format '+ DATA/aeg2t/datafile/xsbtest_ts2.dbf';
EXIT

show parameter comp

imp 'sys as sysdba' file = 1.dmp tablespaces = xsbtest_ts2 transport_tablespace = y datafiles = '+ DATA/aeg2t/datafile/xsbtest_ts2.dbf'

SELECT * FROM xsb.t1;

How to shut down and start Oracle10g RAC 2009-08-27 13:52:15
solaris 10 oracle10g install 2009-05-24 02:02:42
Adjustment oracle10G EM encountered several problems 2009-08-13 05:52:52
Installed on the Windows2008 problems oracle10g 2009-08-28 20:34:21
Archive oracle10g example (reprint) 2009-07-29 18:12:52

Oracle10g ASM database table space maintenance

kods.net — Tue, 31 Mar 2009 17:51:25+0000

1. Default naming Oracle when using ASM + OMF management, Oracle distribution file name will automatically create the appropriate file.
Our job to create table space can be simplified as follows:

create tablespace tablespace_name;

A command.
The following are the default Oracle naming examples:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jun 16 09:26:22 2005

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATADG/mmsdb/datafile/system.259.3
+DATADG/mmsdb/datafile/undotbs1.258.3
+DATADG/mmsdb/datafile/sysaux.257.3
+DATADG/mmsdb/datafile/users.256.3
+DATADG/mmsdb/datafile/platform.278.1
+DATADG/mmsdb/datafile/gateway.279.1

6 rows selected.

2. Since the definition of the name and file size if we want to create a custom file size and name, you can use the following method to create:

SQL> create tablespace cumail datafile '+DATADG/mmsdb/datafile/cumail'size 100m;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATADG/mmsdb/datafile/system.259.3
+DATADG/mmsdb/datafile/undotbs1.258.3
+DATADG/mmsdb/datafile/sysaux.257.3
+DATADG/mmsdb/datafile/users.256.3
+DATADG/mmsdb/datafile/platform.278.1
+DATADG/mmsdb/datafile/gateway.279.1
+DATADG/mmsdb/datafile/cumail

7 rows selected.

SQL> drop tablespace cumail including contents and datafiles;

Tablespace dropped.

3. ASSM managed to create the specified table space segment space required clause:

SQL> create tablespace cumail
  2  segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------------------------------
+DATADG/mmsdb/datafile/system.259.3
+DATADG/mmsdb/datafile/undotbs1.258.3
+DATADG/mmsdb/datafile/sysaux.257.3
+DATADG/mmsdb/datafile/users.256.3
+DATADG/mmsdb/datafile/platform.278.1
+DATADG/mmsdb/datafile/gateway.279.1
+DATADG/mmsdb/datafile/cumail.280.3

7 rows selected.

SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         LOCAL      MANUAL
UNDOTBS1                       LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO
PLATFORM                       LOCAL      MANUAL
GATEWAY                        LOCAL      MANUAL
CUMAIL                         LOCAL      AUTO

8 rows selected.

3. To change the file size

SQL> alter database datafile '+DATADG/mmsdb/datafile/cumail.280.3' resize 300m;

Database altered.

SQL>

4. If the property set the UNIFORM SIZE to some extent the need for property, it must show the designation.

SQL> create tablespace ticket extent management local uniform size 128k segment space management auto;

Tablespace created.


SQL> select name ,bytes/1024/1024 from v$datafile;

NAME                                     BYTES/1024/1024
---------------------------------------- ---------------
+DATADG/mmsdb/datafile/system.259.3                  450
+DATADG/mmsdb/datafile/undotbs1.258.3                 25
+DATADG/mmsdb/datafile/sysaux.257.3                  330
+DATADG/mmsdb/datafile/users.256.3                     5
+DATADG/mmsdb/datafile/platform.278.1               1000
+DATADG/mmsdb/datafile/gateway.279.1                1000
+DATADG/mmsdb/datafile/cumail.280.3                  300
+DATADG/mmsdb/datafile/ticket.281.1                  100

8 rows selected.

SQL> alter database datafile '+DATADG/mmsdb/datafile/ticket.281.1' resize 300m;

Database altered.

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

Checkpoint trigger conditions

kods.net — Tue, 31 Mar 2009 09:50:46+0000

checkpoint is a database of case, it would have modified the data from the disk cache to refresh and update the control files and data files. rn
We know that the checkpoint will be set dirty data, but when will it happen checkpoint? The following situations will trigger checkpoint. rn
1. When the switch happened when the log group rn
2. When in line with the LOG_CHECKPOINT_TIMEOUT, LOG_CHECKPOINT_INTERVAL, fast_start_io_target, fast_start_mttr_target parameters when rn
3. When run the ALTER SYSTEM SWITCH LOGFILE when rn
4. When run the ALTER SYSTEM CHECKPOINT when rn
5. When I run alter tablespace XXX begin backup, end backup when rn
6. When I run alter tablespace, datafile offline time; rnrn rn rn

checkpoint introduced 2009-07-05 15:17:45
oracle trigger syntax Xiangjie 2010-01-19 04:38:03
oracle10g Training Log ☆ 7 2009-08-22 12:10:04
Log oracle10g training ☆ 7 2009-08-17 23:35:01
Oracle Trigger Details 2010-01-10 01:55:05

oracle configuration

kods.net — Tue, 31 Mar 2009 09:50:36+0000

bash_profile
ORACLE_BASE = / opt / oracle; export ORACLE_BASE
ORACLE_HOME = $ ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID = oracle; export ORACLE_SID
NLS_LANG = AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG
PATH = / usr / sbin: $ PATH; export PATH
PATH = $ ORACLE_HOME / bin: $ PATH; export PATH
LD_LIBRARY_PATH = $ ORACLE_HOME / lib: / lib: / usr / lib; export LD_LIBRARY_PATH
/ 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
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
rn rn rn

oracle RAC client HA configuration (change) 2009-10-17 19:30:02
Oracle10g Network Configuration 2009-04-05 05:19:49
The third part (21) Advanced Configuration of the single-source multi-directional replication environment (3) 2009-04-09 02:30:45
checking network configuration requirements 2009-03-30 17:55:14
three listener configuration file 2009-04-01 18:18:15

(To) oracle job application

kods.net — Tue, 31 Mar 2009 09:50:05+0000

1. Rn

begin rn
sys.dbms_job.submit (job =>: job, rn
what => 'job_stat_cooperate;', rn
next_date => to_date ('01-07-2008 05:00:00 ',' dd-mm-yyyy hh24: mi: ss'), rn
interval => 'TRUNC (SYSDATE +30) +5 / 24'); rn
commit; rn
end; rn
/ B283e95da1736c5bfb2ecf4b4266705e

rn
Of each month at 5:00 on the 1st implementation of the JOB, wrote last week to try and come this week, also saw the implementation of the effect of OK. Record.

rn
TRUNC (SYSDATE +30) +5 / 24 that the cycle time, the time now with the date 30 days, together with a specific time.

rn
2.

rn
Oracle timer (Job) written summary of the time period
rn
Original author: a blog-hui Park Original: link rn
For DBA, the database Job familiar, and usually want the database from time to time because of the automatic implementation of a number of scripts, or to do a database backup, or to do data extraction, or to do database performance optimization, including the work of rebuilding the index and so on. However, Oracle Timer Job on treatment time, the ever-changing, I put the wording commonly used are summarized as follows:

rn
In concluding, Job first explain the parameters one by one:

rn
job parameters are from the Submit () the process of the return binary_ineger. This value is used to uniquely identify a job;

rn
what parameters are to be implemented by PL / SQL code block;

rn
parameters next_date means knowing when to run the job. Writing Job could not specify when the value;

rn
interval parameter when the job will be re-implementation.

rn
Interval This value is one of Job decide when it was re-run the key.

rn
For example: there is stored procedure p_dosomethings, required by the implementation of different time intervals.

rn
1declare
2 jobno number;
3begin
4 dbms_job.submit (
5 jobno,
6 'p_dosomething;', - what
7 to_date ('20090101020000 ',' yyyy-mm-dd hh24: mi: ss'),-- next_date, can not fill
8 'Interval time string' - interval, the key set
9);
10 commit;
11end;
12
rn

rn
1, the implementation of per minute

rn
Interval => TRUNC (sysdate, 'mi') + 1 / (24 * 60)

rn
2, every day from time to time the implementation of

rn
For example: every day 2:00 of the implementation of

rn
Interval => TRUNC (sysdate) + 1 +2 / (24)

rn
3, a week from time to time the implementation of

rn
For example: every Monday 2:00 Executive

rn
Interval => TRUNC (next_day (sysdate, 2)) +2 / 24 - On Monday, the second day of the week

rn
4, a month from time to time the implementation of

rn
For example: monthly at 2:00 on the 1st Executive

rn
Interval => TRUNC (LAST_DAY (SYSDATE)) +1 +2 / 24

rn
5, from time to time the implementation of quarterly

rn
For example the first day of each quarter, 2:00 implementation

rn
Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'Q') + 2 / 24

rn
6 per annum from time to time the implementation of

rn
For example: July 1 each year and at 2 o'clock on January 1

rn
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) +2 / 24

rn
7, from time to time the implementation of the annual

rn
For example: Every year at 2:00 on the January 1 implementation

rn
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) +2 / 24

rn
Listed above can not of course to take care of all aspects, but look a little change can be derived from numerous examples, you must be smart of you, I do not here many a wordy.

rn

3. Often to oracle database from time to time some of the script to run automatically, or to do a database backup, or to do data extraction, or to do database performance optimization, including the work of rebuilding the index and so on, when used in a function required dbms_job.submit to achieve Oracle Timer Job on treatment time. Dbms_job.submit use this function, we only need to consider two things: a mission organization, and customized implementation of the mandate of a point in time. But the most important and most difficult thing, in my opinion is the implementation of the mandate to identify a point in time. Defined point in time, other things was true. Dbms_job.submit the following functions are used:
rn
rn
rn
Java code

rn

dbms_job.submit (job out binary_integer, rn
what in archar2, rn
next_date in date, rn
interval in varchar2, rn
no_parse in boolean)

dbms_job.submit( job out binary_integer,rnwhat　　　　　　　in　　　archar2,rnnext_date　　　　in　　　date，rninterval　　　　　in　　　varchar2,rnno_parse　　　　　in　　　boolean)rn
One of:
● job: output variables, is the mission at the mission queue number;
● what: the name of the implementation of the mission and its input parameters;
● next_date: the implementation of the mission time;
● interval: time interval between the implementation of mission.
Interval This value is one of Job decide when it was re-implementation of the key; when the interval is set to null, the implementation of the end of job, they are deleted from the queue. If we are required periodically the implementation of the job, then use 'sysdate + m' express. How to better determine the interval between the execution time we have a necessary function TRUNC.

1.TRUNC (for dates)
TRUNC function for a specific element and the date value truncated.
The specific format of the grammar are as follows:
TRUNC (date [, fmt])
One of:
a date value of date
fmt date format, the date format will be specified by the amputation of the elements. Ignore it amputated from below the most recent date are the use of this function:
1) The year-on-year Censored
select TRUNC (TO_DATE ('2008-03-01 08:23 ',' yyyy-mm-dd hh: mi '),' yyyy ') from dual
-------------------------------------------------- ---------
2008-1-1
2) monthly Censored
select TRUNC (TO_DATE ('2008-03-01 08:23 ',' yyyy-mm-dd hh: mi '),' mm ') from dual
-------------------------------------------------- ------
2008-3-1
3) daily Censored
select TRUNC (TO_DATE ('2008-03-01 08:23 ',' yyyy-mm-dd hh: mi '),' dd ') from dual
-------------------------------------------------- --------------------
2008-3-1
4) time Censored
select TRUNC (TO_DATE ('2008-03-01 08:23 ',' yyyy-mm-dd hh: mi '),' hh ') from dual
-------------------------------------------------- --------------------
2008-3-1 8:00:00
5) by sub-Censored
select TRUNC (TO_DATE ('2008-03-01 08:23 ',' yyyy-mm-dd hh: mi '),' mi ') from dual
-------------------------------------------------- --------------------
2008-3-1 8:23:00

2. To determine the execution time interval
1), the implementation of per minute
Interval => TRUNC (sysdate, 'mi') + 1 / (24 * 60)
2), every day from time to time the implementation of such as: 2:00 every day of the implementation of
Interval => TRUNC (sysdate) + 1 +2 / (24)
3), such as a week from time to time the implementation: every Monday 2:00 Executive
Interval => TRUNC (next_day (sysdate, 2)) +2 / 24 - On Monday, the second day of the week
4) a month from time to time the implementation of such as: monthly at 2:00 on the 1st Executive
Interval => TRUNC (LAST_DAY (SYSDATE)) +1 +2 / 24
5), such as a quarterly from time to time the implementation of the first day of each quarter, 2:00 implementation
Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'Q') + 2 / 24
6), such as every six months to implement from time to time: July 1 each year and at 2 o'clock on January 1
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) +2 / 24
7), such as the implementation of the annual timing: Every year at 2:00 on the January 1 implementation
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) +2 / 24

3. Examples here provide a simple example, is completed at each time interval to insert a table within a Record
1) create a test table rn
rn

rn
SQL> create table test (id number, cur_time date); rn
Table has been created. rn
Construction sequence rn ----
CREATE SEQUENCE test_sequence rn
INCREMENT BY 1 - each add some rn
START WITH 1 - start counting rn from one
NOMAXVALUE - do not set the Maximum rn
NOCYCLE - has been cumulative, non-circulating rn
CACHE 10;

rn SQL> create table test(id number,cur_time date); rn 表已创建。rn----建sequencernCREATE SEQUENCE test_sequencernINCREMENT BY 1 　 -- 每次加几个 rn START WITH 1 　　 -- 从1开始计数 rn NOMAXVALUE　　　 -- 不设置最大值 rn NOCYCLE　　　　　 -- 一直累加，不循环 rn CACHE 10 ;rn

- Build trigger code: rn
rn

create or replace trigger tri_test_id rn
before insert on test - test table are rn
for each row rn
declare rn
nextid number; rn
begin rn
IF: new. Id IS NULLor: new. Id = 0 THEN - id are listed rn
select test_sequence.nextval - SEQ_ID is created just rn
into nextid rn
from sys.dual; rn
: New. Id: = nextid; rn
end if; rn
end tri_test_id; rn

create or replace trigger tri_test_idrn before insert on test --test 是表名rn for each rowrndeclarern nextid number;rnbeginrn IF :new.id IS NULLor :new.id=0 THEN --id是列名rn select test_sequence.nextval --SEQ_ID正是刚才创建的rn into nextidrn from sys.dual;rn :new.id:=nextid;rn end if;rnend tri_test_id; rn

2) create a custom process rn
rn

rn
SQL> create or replace procedure proc_test as rn
2 begin rn
3 insert into test (cur_time) values (sysdate); rn
4 end; rn
5 / rn

rn SQL> create or replace procedure proc_test as rn 2 begin rn 3 insert into test(cur_time) values(sysdate); rn 4 end; rn 5 /rn

Process has been created.

3) create a JOB rn
rn

SQL> declare job1 number; rn
begin rn
dbms_job.submit (job1, 'proc_test;', sysdate, 'sysdate +1 / 1440'); - every day 1440 minutes, that is, the process of one-minute test run first rn
end;

SQL> declare job1 number;rn beginrn dbms_job.submit(job1,'proc_test;',sysdate,'sysdate+1/1440');--每天1440分钟，即一分钟运行test过程一次rn end;rn

PL / SQL JOB has been completed successfully.
rn

rn

DATAGUARD logs Application Service 2010-01-22 13:21:36
biee Application Server Control to reset your password 2009-08-11 08:20:03
The first part of (6)-based application process (I) 2009-04-09 05:12:14
How to design a good database 2009-06-30 04:14:26
The first part (5) based on the communication process (below) 2009-04-09 12:20:13

ORACLE10G full version centos5 installed (the installation has passed)

kods.net — Tue, 31 Mar 2009 09:49:10+0000

ORACLE10G full version centos5 installed (the installation has passed)

1. Centos 5.0 install
rn
GUI must be installed, it is best not to start selinux
rn

rn

rn
二. Ready to install the software: (this is very important, is the first installation failed because the package did not play the whole)
rn
binutils
rn
compat-libstdc + +
rn
elfutils-libelf
rn
elfutils-libelf-devel
rn
glibc
rn
glibc-common
rn
glibc-devel
rn
gcc
rn
gcc-c + +
rn
libaio
rn
libaio-devel
rn
libgcc
rn
libstdc + +
rn
libstdc + +-devel
rn
make
rn
sysstat
rn
unixODBC
rn
unixODBC-devel
rn
Directly from the software on the installation CD-ROM will be able to find (behind the first name did not write the whole version, with CD-ROM can CENTOS5 normal installation)
rn

rn
三. Set up users and groups
rn
/ usr / sbin / groupadd oinstall
/ usr / sbin / groupadd dba
/ usr / sbin / useradd-g oinstall-G dba oracle
passwd oracle
rn

rn
四. Set up directories and permissions
rn
mkdir / oracle
mkdir / oracle/10g
chown-R oracle: oinstall / oracle
rn

rn
五. Edit Variables
rn
1.
rn
vi / etc / sysctl.conf
rn
kernel.shmall = 2097152
rn
kernel.shmmax = 2147483648
rn
kernel.shmmni = 4096
rn
kernel.sem = 250 32000 100 128
rn
net.ipv4.ip_local_port_range = 1024 65000
rn
net.core.rmem_default = 4194304
rn
net.core.rmem_max = 4194304
rn
net.core.wmem_default = 262144
rn
net.core.wmem_max = 262144
rn

rn
2.
rn
vi / etc / security / limits.conf
rn
oracle soft nproc 2047
rn
oracle hard nproc 16384
rn
oracle soft nofile 1024
rn
oracle hard nofile 65536
rn

rn
3.
rn
vi / etc / pam.d / login
rn
session required / lib / security / pam_limits.so
rn
session required pam_limits.so
rn

rn
4.
rn
vi / etc / profile
rn
if [$ USER = "oracle"]; then
rn
if [$ SHELL = "/ bin / ksh"]; then
rn
ulimit-p 16384
rn
ulimit-n 65536
rn
else
rn
ulimit-u 16384-n 65536
rn
fi
rn
fi
rn
5.
rn
cd / home / oracle
rn
rn

vi. bash_profile
rn
ORACLE_BASE = / oracle
rn
ORACLE_HOME = $ ORACLE_BASE/10g
rn
ORACLE_SID = wilson
rn
PATH = $ ORACLE_HOME / bin: $ PATH
rn

rn
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
rn

rn
6. LibXp installation
rn
yum-y install libXp
rn

rn
7. Rn
vi / etc / redhat-release
rn
To CentOS release 5 (Final) modified redhat 4

rn

rn
六. Oracle user to use linux
rn
Into the terminal
rn
sh / ORACLE directory / runInstaller
rn
Can be a normal installation, an error about permissions can be ignored
rn

rn
Seven. Running after installation
rn
ORACLE can not be installed because of auto-start, need to manually start the ORACLE, has two kinds of methods.
rn

rn
1. Manually activated
rn
su - oracle
rn
export ORACLE_HOME = / oracle installation directory
rn
export ORACLE_SID = sid Name
rn
sqlpuls / nolog
rn
Appear
rn
sql> conn sys / password as sysdba
rn
sql> startup
rn
sql> exit
rn

rn
cd / oracle directory / bin
rn
. / lsnrctl start
rn
Should not be error, it should be entirely under the ORACLE start up.
rn

rn
2. Auto-start, a problem sometimes, do not know how else
rn
First need to add two kinds of scripts (you can modify the red part of the)
rn
cd / etc / init.d /
rn
touch oracle
rn
vi oracle
rn
Write:
rn
#! / bin / bash
rn
#
rn
# Chkconfig: 2345 80 05
rn
# Description: Oracle 10g Server
rn
# / Etc / init.d / dbora
rn
#
rn
# Run-level Startup script for the Oracle Instance, Listener, and
rn
# Web Interface
rn

rn
export ORACLE_BASE = / oracle
rn
export ORACLE_HOME = $ ORACLE_BASE/10g
rn
export ORACLE_SID = resume
rn
export PATH = $ PATHORACLE_HOME / bin
rn

rn
ORA_OWNR = "oracle"
rn

rn
# If the executables do not exist - display error
rn

rn
if [!-f $ ORACLE_HOME / bin / dbstart-o!-d $ ORACLE_HOME]
rn
then
rn
echo "Oracle startup: cannot start"
rn
exit 1
rn
fi
rn
# Depending on parameter - startup, shutdown, restart
rn
# Of the instance and listener or usage display
rn

rn
case "$ 1" in
rn
start)
rn
# Oracle listener and instance startup
rn
echo-n "Starting Oracle:"
rn
# Su $ ORA_OWNR-c "$ ORACLE_HOME / bin / lsnrctl start"
rn
su $ ORA_OWNR-c $ ORACLE_HOME / bin / dbstart
rn
touch / var / lock / oracle
rn

rn
su $ ORA_OWNR-c "$ ORACLE_HOME / bin / emctl start dbconsole"
rn
echo "OK"
rn
;;
rn
stop)
rn
# Oracle listener and instance shutdown
rn
echo-n "Shutdown Oracle:"
rn
# Su $ ORA_OWNR-c "$ ORACLE_HOME / bin / lsnrctl stop"
rn
su $ ORA_OWNR-c $ ORACLE_HOME / bin / dbshut
rn
rm-f / var / lock / oracle
rn

rn
su $ ORA_OWNR-c "$ ORACLE_HOME / bin / emctl stop dbconsole"
rn
echo "OK"
rn
;;
rn
reload | restart)
rn
$ 0 stop
rn
$ 0 start
rn
;;
rn
*)
rn
echo "Usage:` basename $ 0 `start | stop | restart | reload"
rn
exit 1
rn
esac
rn
exit 0
rn

rn
Save Exit
rn

rn
Direct service oracle start running can be a
rn

rn
Say at what many in LINUX and the tomcat in the whole and apche
rn
vi / etc / httpd / conf / httpd.conf
rn
Add at the bottom
rn
ProxyPass / ajp: / / 127.0.0.1:8009 /
ProxyPassReverse / ajp: / / 127.0.0.1:8009 /
rn

rn
This allows the

rn rn rn

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

Merge the use of (1)

kods.net — Tue, 31 Mar 2009 09:48:55+0000

Merge to select from a table update or insert some data to another table. And finally is used to update or insert depends on the conditions of the statement.
rn
Here's our easy to cite an example:
rn

rn
SQL> create table merge_test1 (a number, b varchar2 (20))
rn
Table has been created.
rn
SQL> create table merge_test2 (a number, b varchar2 (20))
rn
Table has been created.
rn
SQL> insert into merge_test1 values (1, 'A');
rn
One line has been created.
rn
SQL> insert into merge_test1 values (2, 'B');
rn
One line has been created.
rn
SQL> insert into merge_test1 values (3, 'C');
rn
One line has been created.
rn
SQL> insert into merge_test2 values (3, 'C');
rn
One line has been created.
rn
SQL> insert into merge_test2 values (2, 'C');
rn
One line has been created.
rn
SQL> commit;
rn
Submitted finish.
rn

rn
SQL> select * from merge_test1;
rn
AB
---------- --------------------
1 A
2 B
3 C
rn
SQL> select * from merge_test2;
rn
AB
---------- --------------------
3 C
2 C

rn
Ready in front of work, with Merge update of Merge_test2
rn

rn
MERGE INTO MERGE_TEST2 M
USING MERGE_TEST1 N
ON (MA = NA)
WHEN MATCHED THEN
UPDATE SET MB = NB
WHEN NOT MATCHED THEN
INSERT (MA, MB) VALUES (NA, NB);
rn

rn
The updated results:
rn
SQL> select * from merge_test2;
rn
AB
---------- --------------------
1 A
3 C
rn
2 B
rn

rn
Summary:
rn
First, Merge usage generally have three types:
rn
1, only the update does not insert
rn
Merge into Merge_test2 M
rn
Using Merge_test1 N
rn
ON (MA = NA)
rn
WHEN MATCHED THEN
rn
UPDATE SET MB = NB;
rn
2, only the insert does not update
rn
MERGE INTO merge_test2 M
rn
USING merge_test1 N
rn
ON (MA = NA)
rn
WHEN NOT MATCHED THEN
rn
INSERT (MA, MB) VALUES (NA, NB);
rn
3, we insert also update
rn
MERGE INTO merge_test2 M
rn
USING merge_test1 N
rn
ON (MA = NA)
rn
WHEN MATCHED THEN
rn
UPDATE SET MB = NB
rn
WHEN NOT MATCHED THEN
rn
INSERT (MA, MB) VALUES (NA, NB)
rn

rn
Second, pay attention to matters
rn
1, merge statement, update can not be used to update the linked list (that is, ON (MA = NA))
rn
For example:
rn
MERGE INTO merge_test2 M
rn
rn
USING merge_test1 N
rn
ON (MA = NA)
rn
WHEN MATCHED THEN
rn
UPDATE SET MA = NB, MB = NB
rn
WHEN NOT MATCHED THEN
rn
INSERT (MA, MB) VALUES (NA, NB)
rn

rn
Our implementation of the above statement (note the red part) will have the following tips:
rn

Error Line 3:
ORA-38104: unable to update the ON clause quoted out: "M". "A"
rn

rn
2, Using the back may not necessarily be as follows:
rn
Grammar document is written on: table, view, subquery
rn
In other words it can be a query result set.
rn

rn
3, is also a mistake because of the associated value does not only caused by
rn
For instance:
rn
Inserted at a further merge_test1 data. Duplicate of the previous!
SQL> insert into merge_test1 values (3, 'd');
rn

rn
At this point the implementation of our
rn
rn
MERGE INTO merge_test2 M
rn
rn
USING merge_test1 N
rn
ON (MA = NA)
rn
WHEN MATCHED THEN
rn
UPDATE SET MB = NB
rn
WHEN NOT MATCHED THEN
rn
INSERT (MA, MB) VALUES (NA, NB)

rn

Error Line 1:
ORA-30926: unable to access the table at the source of a group of stable lines

rn

rn

rn

rn

Oracle Database exp imp Import Export by user examples 2009-04-06 18:58:52
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
SQL optimization (4) - Table of the three connections 2009-03-29 23:13:41
SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
SQL optimization (2) - there is the order of the index data 2009-03-28 08:01:36

SGA extended the principle of 32bit oracle

kods.net — Tue, 31 Mar 2009 09:48:01+0000

SGA extended the principle of 32bit oracle

From: http://www.itpub.net/247048.html

Because the median 32bitrnoracle restrictions can only visit the oracle process 4g (2 of 32 power) following virtual memory address, the time at a lot of people this is a very troublesome problem, because a lot of space inside and can not be used rn deposit, By default, the SGA can not exceed 1.7g. Under the U.S. such as linux have 8g memory, there are some empty do not worry. This time we have to consider how to expand rnoracle the SGA. Well, first of all, the oracle how to identify it 32bit? We can adopt the following query to be
rn
rnsys @ OCN> select * from v $ version;
rn
rnBANNER
rn ------------------------------------------------- ---------------
rnOracle9i Enterprise Edition Release 9.2.0.4.0 - Production
rnPL / SQL Release 9.2.0.4.0 - Production
rnCORE 9.2.0.3.0 Production
rnTNS for Linux: Version 9.2.0.4.0 - Production
rnNLSRTL Version 9.2.0.4.0 - Production
rn
rn
rn the case of 64bit oracle, in the query results will be displayed in 64bit words, does not appear, it must be 32bit oracle. Of course, on the adoption of os can see the file oracle
rn
rn [oracle @ ocn2 bin] $ cd $ ORACLE_HOME / bin
rn [oracle @ ocn2 bin] $ file oracle
rnoracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1, dynamically linked (uses shared libs), not stripped
rn [oracle @ ocn2 bin] $
rn
rn up in some os, on AIX such as, 64bit oracle will display information, 32bit will not display properly.
rn
rn confirmed at 32bit oracle, we need to understand that usually we can only access the OS process 4g space below, redhat linux AS 2.1rn or AS3.0 version of the exception, they can provide the VLM (Very largernmemory) function support, conversion can be made through the use of 36bit memory address to sign, then 2 of the 36 th power is a theoretical maximum memory access support 64g. In oracle, you are rn by the memory as a document to visit a virtual / dev / shmrn file system, the file system is composed entirely by memory, so that the restriction will exceed 4g. Then come back and see us, now that the process can access the following memory 4g why SGA is 1.7g usually does.
rn
rn rn in the OS, provides for a process in the application can access the virtual memory space for the 0 - 3g, and 3g - 4g this virtual address space are reserved for the kernel used. Should pay attention to where our emphasis is rn virtual address space, and did not say that the physical address space, that is, assuming there is 8g of memory, the 0 - 3g virtual address space that may arise at 8g memory 3g - 8g part memory segment, rn is not to say that physical memory are 0 - 3g paragraph. In this 0 - 3g virtual address, oracle and how to use it, Well This is a fixed address.
rn
rn
rn
rn +++++++++++++++ 4g
rn + -------------------------+
rn +------------------------- +
rn +------------------------- +
rn +++++++++++++++ 3g: kernel
rn + ------------------------- +
rn + ------------------------- +
rn +------------------------- +
rn +++++++++++++++ 2g: process stack
rn + -------------------------+
rn +------------------------- +
rn +++++++++++++++ 1.25g: SGA starting point
rn +++++++++++++++ 1g: oracle shared libraries loaded with the starting point
rn + -------------------------+
rn + -------------------------+
rn + -------------------------+
rn +++++++++++++++ 0g: oracle program (executable code) loaded with the starting point
rn
rn at this distribution of virtual addresses, 1.25g of sga are the starting point, and the process of distribution of the private space (stack part) is from near the beginning 3g Department. That is, in fact the process of SGA and private space are shared between rn the 1.25g-- 3g this part, because of the process private space especially in small, often habitually think our SGA can achieve 1.7g. The process of private space has 0.05g enough. Rnoracle start from the beginning one, or any one user process from start landing, all virtual addresses have a fixed distribution Well, only the user can also expand the private space. Look at our database after rnpmon process (in fact any process is the same) distribution of the virtual address. One because of my machines running on two databases, so one of us a look, take a look at the SGA-related information database
rn
rn [root @ ocnsb1 root] # su - oracle
rn [oracle @ ocnsb1 oracle] $ sqlplus "/ as sysdba"
rn
rnSQL * Plus: Release 9.2.0.4.0 - Production on Mon Jul 26 11:37:23 2004
rn
rnCopyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
rn
rn
rnConnected to:
rnOracle9i Enterprise Edition Release 9.2.0.4.0 - Production
rnWith the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
rnJServer Release 9.2.0.4.0 - Production
rn
rn
rnselect "> sys @ OCN> select INSTANCE_NAME from v $ instance;
rn
rnINSTANCE_NAME
rn ----------------
rnroocn1
rn
rnshow "> sys @ OCN> show sga
rn
rnTotal System Global Area 437327188 bytes
rnFixed Size 451924 bytes
rnVariable Size 301989888 bytes
rnDatabase Buffers 134217728 bytes
rnRedo Buffers 667648 bytes
rnsys @ OCN>
rn
rnexit "> sys @ OCN> exit
rnDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
rnWith the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
rnJServer Release 9.2.0.4.0 - Production
rn [oracle @ ocnsb1 oracle] $ ipcs
rn
rn ------ Shared Memory Segments --------
rnkey shmid owner perms bytes nattch status
rn0x73a32bdc 131072 oracle 640 457179136 50
rn0x84cc76ac 163841 oracle 640 1379926016 90
rn
rn ------ Semaphore Arrays --------
rnkey semid owner perms nsems status
rn0x8df96364 622592 oracle 640 64
rn0x53609d64 753665 oracle 640 504
rn
rn ------ Message Queues --------
rnkey msqid owner perms used-bytes messages
rn
rn [oracle @ ocnsb1 oracle] $
rn
rn me here there is only one shared memory segment, and is the SGA size (shmid to 131072). This is because much of the reason set shnmax
rn
rn [oracle @ ocn2 kernel] $ more / proc / sys / kernel / shmmax
rn3221225472
rn [oracle @ ocn2 kernel] $
rn
rn Next we look at the information PMON, first of all to find pmon process, and then go to / proc / pid / maps do not address the process of distribution of the virtual information
rn
rn [oracle @ ocnsb1 oracle] $ ps-ef | grep pmon
rnoracle 13655 1 0 Jul24? 00:00:00 ora_pmon_roocn1
rnoracle 13926 1 0 Jul24? 00:00:00 ora_pmon_ocn1
rnoracle 31435 31092 0 11:51 pts / 3 00:00:00 grep pmon
rn [oracle @ ocnsb1 oracle] $
rn
rn [oracle @ ocnsb1 oracle] $ more / proc/13655/maps
rn08048000-0a4ba000 r-xp 00000000 08:05 681621 / opt/oracle/products/9.2.0/bin/oracle
rn0a4ba000-0ad54000 rw-p 02471000 08:05 681621 / opt/oracle/products/9.2.0/bin/oracle
rn0ad54000-0ae07000 rwxp 00000000 00:00 0
rn
rn this oracle program are loaded with information, we can see that space used 0 - 0ae07000, this part of the size of less than 256MB
rn
rn40000000-40016000 r-xp 00000000 08:02 448102 / lib/ld-2.2.4.so
rn
rn This is the oracle shared libraries loaded with a starting point, 0x40000000 exactly 1g
rn
rn
rn40016000-40017000 rw-p 00015000 08:02 448102 / lib/ld-2.2.4.so
rn40017000-40018000 rw-p 00000000 00:00 0
rn40018000-40019000 r-xp 00000000 08:05 308464 / opt/oracle/products/9.2.0/lib/libodmd9.so
rn40019000-4001a000 rw-p 00000000 08:05 308464 / opt/oracle/products/9.2.0/lib/libodmd9.so
rn4001a000-40026000 r-xp 00000000 08:05 308345 / opt/oracle/products/9.2.0/lib/libskgxp9.so
rn40026000-4002a000 rw-p 0000b000 08:05 308345 / opt/oracle/products/9.2.0/lib/libskgxp9.so
rn4002a000-40038000 r-xp 00000000 08:05 308461 / opt/oracle/products/9.2.0/lib/libskgxn9.so
rn40038000-40039000 rw-p 0000d000 08:05 308461 / opt/oracle/products/9.2.0/lib/libskgxn9.so
rn40039000-4004d000 rw-p 00000000 00:00 0
rn4004d000-4032c000 r-xp 00000000 08:05 308455 / opt/oracle/products/9.2.0/lib/libjox9.so
rn4032c000-4043c000 rw-p 002de000 08:05 308455 / opt/oracle/products/9.2.0/lib/libjox9.so
rn4043c000-4043e000 rw-p 00000000 00:00 0
rn4043e000-40441000 r-xp 00000000 08:02 448115 / lib/libdl-2.2.4.so
rn40441000-40442000 rw-p 00002000 08:02 448115 / lib/libdl-2.2.4.so
rn40442000-40443000 rw-p 00000000 00:00 0
rn40443000-40465000 r-xp 00000000 08:02 448117 / lib/libm-2.2.4.so
rn40465000-40466000 rw-p 00021000 08:02 448117 / lib/libm-2.2.4.so
rn40466000-40475000 r-xp 00000000 08:02 448147 / lib/libpthread-0.9.so
rn40475000-4047d000 rw-p 0000e000 08:02 448147 / lib/libpthread-0.9.so
rn4047d000-40490000 r-xp 00000000 08:02 448120 / lib/libnsl-2.2.4.so
rn40490000-40491000 rw-p 00012000 08:02 448120 / lib/libnsl-2.2.4.so
rn40491000-40493000 rw-p 00000000 00:00 0
rn40493000-40494000 r-xp 00000000 08:02 352330 / usr/lib/libaio.so.1
rn40494000-40495000 rw-p 00000000 08:02 352330 / usr/lib/libaio.so.1
rn40495000-405ca000 r-xp 00000000 08:02 448111 / lib/libc-2.2.4.so
rn405ca000-405cf000 rw-p 00134000 08:02 448111 / lib/libc-2.2.4.so
rn405cf000-405d3000 rw-p 00000000 00:00 0
rn405d3000-405d4000 r-xp 00000000 08:02 146106 / lib/libredhat-kernel.so.1.0.1
rn405d4000-405d5000 rw-p 00000000 08:02 146106 / lib/libredhat-kernel.so.1.0.1
rn405d5000-405f9000 rw-p 00000000 00:00 0
rn405fa000-40604000 r-xp 00000000 08:02 448136 / lib/libnss_files-2.2.4.so
rn40604000-40605000 rw-p 00009000 08:02 448136 / lib/libnss_files-2.2.4.so
rn40605000-40685000 rw-p 00000000 08:02 69445 / dev / zero
rn40685000-406c6000 rw-p 00000000 00:00 0
rn
rn shared libraries consume less than 20MB of space
rn
rn
rn50000000-6b000000 rw-s 00000000 00:04 131072 / SYSV73a32bdc (deleted)
rn
rn This is SGA's starting point, 0x50000000 express 1.25g
rn
rn
rn6b000000-6b001000 r - s 1b000000 00:04 131072 / SYSV73a32bdc (deleted)
rn6b001000-6b0a2000 rw-s 1b001000 00:04 131072 / SYSV73a32bdc (deleted)
rn6b0a2000-6b0a3000 r - s 1b0a2000 00:04 131072 / SYSV73a32bdc (deleted)
rn6b0a3000-6b400000 rw-s 1b0a3000 00:04 131072 / SYSV73a32bdc (deleted)
rn
virtual space assigned to rnsga Here, by calculating the hexadecimal number 16, is with us with the size of the SGA, 131072 is ipcs to see us at the time of the shmid
rn
rn
rnbffe5000-bffee000 rwxp ffff8000 00:00 0
rnbfff0000-bfff1000 r-xs 00000000 08:02 69304 / dev / vsys
rn
rn exactly 0xc0000000 because 3g (16 hexadecimal number c = 12,4 * 3 = 12,0 x40000000 express 1g), while private space here to express the process of distribution of the starting point. Show rnoracle any landing process a user will find such a distribution of virtual addresses. Here we can easily look up, oracle programrn and database shared memory space is very small, there is no need to give so much, in fact, oracle programrn safe enough to 256M, and 50M shared libraries also give safe enough, but also from the theory, we can put the oracle program at 0x10000000 compression necessary to rn, the shared libraries required by the compression of memory at 0x12000000 the following, this can be the starting point for SGA to 0x12000000 (0.3g). Are from the original rn0x5000000 (1.25g) of the beginning, only around 1.7g distribution to SGA, now SGA from 0.3g distribution can begin near 2.7g, such as memory give 2.65g distribution rnSGA. To achieve this function, we need to recompile oraclernprogram, shared libraries to reduce the allocation of virtual memory address and the starting point for the location of the distribution of SGA. 0x4000000 the starting point for shared libraries loaded by the process of decision mapped_base to set rn
rn
rn [oracle @ ocnsb1 oracle] $ more / proc/13655/mapped_base
rn1073741824
rn
rn this size is 1G, it means that the loading shared libraries from the location of the virtual address 1g start, if this address to reduce the need to start before the oracle, that is to be put to use root user to start the process of oracle of mapped_base down to 256M , so that after oracle restart the process will have to inherit this value.
rn
rnsu - root echo 268435456> / proc / / mapped_base
rn course, we can also achieve some oracle user shell to automatically lower after landing mapped_base functions, this can be found on google, or refer to
rnhttp: / / www.puschitz.com / TuningLi ... ngSpaceForLargerSGA in the article reads as follows
rn
rnGiving Oracle Users the Privilege to Change the Base Address for Oracle's Shared Libraries Without Giving them root Access
rn
rnAs shown above, only root can change the base address "mapped base" forrnshared libraries. Using sudo we can give Oracle users the privilege tornchange "mapped base" for their own shells without giving them full rootrnaccess. Here is the procedure:
rn
rnsu - root
rn
rn # Eg create a script called "/ usr / local / bin / ChangeMappedBase"
rn
rn # which changes the "mapped base" for the parent process,
rn
rn # the shell used by the Oracle user where the "sudo" program # is executed (forked). Here is an example:
rn
rn # / bin / sh
rn
rn # Lowering "mapped base" to 0x10000000 echo 268435456> / proc / $ PPID / mapped_base
rn
rn # Make sure that owernship and permissions are correct chown root.root / usr / local / bin / ChangeMappedBase
rn
rnchmod 755 / usr / local / bin / ChangeMappedBase
rn
# Allow the Oracle user to execute / usr / local / bin / ChangeMappedBasernvia sudo echo "oracle ALL = / usr / local / bin / ChangeMappedBase">> rn / etc / sudoers
rnNow the Oracle user can run / usr / local / bin / ChangeMappedBase to change "mapped base" for it's own shell:
rn
rn $ su - oracle
rn
rn $ cat / proc / $ $ / mapped_base; echo 1073741824
rn
rn $ sudo / usr / local / bin / ChangeMappedBase Password:
rn
rn # type in the password for the Oracle user account
rn
rn $ cat / proc / $ $ / mapped_base; echo 268435456 $
rnWhen / usr / local / bin / ChangeMappedBase is executed the first time afterrnan Oracle login, sudo will ask for a password. The password that needsrnto be entered is the password of the Oracle user account.
rn
rn
rnChanging the Base Address for Oracle's Shared Libraries Automatically During an Oracle Login
rn
rnThe procedure in the previous section asks for a password each timern / usr / local / bin / ChangeMappedBase is executed the first time after anrnOracle login. To have "mapped base" changed automatically during anrnOracle login without a password, the following can be done:
rn
rnEdit the / etc / sudoers file with visudo:
rn
rnsu - root visudo
rnChange the entry in / etc / sudoers from:
rn
rnoracle ALL = / usr / local / bin / ChangeMappedBase
rnto read:
rn
rnoracle ALL = NOPASSWD: / usr / local / bin / ChangeMappedBase
rnMake sure bash executes / usr / local / bin / ChangeMappedBase during the login process. You can use eg ~ oracle / .bash_profile:
rn
rnsu - oracle echo "sudo / usr / local / bin / ChangeMappedBase">> ~ /. bash_profile
rnThe next time you login to Oracle, the base address for shared libraries will bet set automatically.
rn
rn $ ssh oracle @ localhost
rn
rnoracle @ localhost's password: Last login: Sun Apr 6 13:59:22 2003 from localhost
rn
rn $ cat / proc / $ $ / mapped_base; echo 268435456 $
rn
rn
rn
rn SGA starting from 1.25g to 0.3g is required to reduce recompilation oraclernprogram. Must be emphasized that, SGA and the starting point is the starting point for shared libraries mapped_ase related, SGA's starting point is greater than at least a starting point for shared libraries is more than 0.05g safety rn, otherwise the database will not be able to start or collapse.
rn
rn
oracle turn off rn
rn su - oracle
rn cd $ ORACLE_HOME / rdbms / lib
rn
rn modify the shared library file containing the definition of Address
rn
rn genksms-s 0x12000000> ksms.s
rn
rn well compiled object file
rn
rn make-f ins_rdbms.mk ksms.o
rn
rn recompile oracle executable file
rn make-f ins_rdbms.mk ioracle
rn
rn
rn As above redhat linux AS 2.1 version of oracle the use of the VLM is also relatively simple, and reference
rnhttp: / / www1.ap.dell.com/content / ... & l = zh & s = bsd
rnhttp: / / otn.oracle.com/global/cn/pub/notes/technote_rhel3.html
rn course, internet article on can have more for reference.
rn
rn I want to point out a problem which we encountered in practice a problem, that is, if the distribution of a large SGA, but did not use the VLM, almost near when 3g, leaving only around 20m left right rn . So that when a process hashrnjoin, because of U.S. pga_aggregate_target set to 1g, oracle default PGA single process can be achieved using rnpga_aggregate_target * 5% = 50M, then make the hash join in when the error
rn ORA-04030: out of process memory when trying to allocate 254476 bytes (hash-join subh, kllcqas: kllsltba)
rn our pga_aggregate_target reduced to adjust 400M Successful implementation of the query. VLM is not used because of the circumstances of a single process memory space must be at 3g distribution following, rn and the distribution of PGA belong to this category. If you are using the VLM has been assigned to the PGA above 4g virtual address, no longer have this problem. At this no longer too much of VLM, because rn is also relatively simple to use, up from the principle of expansion in terms of 32bitrn through the os to 36bit, oracle memory used to manage documents and to support the process of access to more than 4g part of the virtual memory. linux on the promotion of such usage has been the root cause of 64bitrnoracle are rarely because of its use, such as other sunOS / hp unix / AIX are widely used in the 64bit oracle, these methods also lose their value.
rn
rn the original reference
rnhttp: / / blog.csdn.net / biti_rainy / ... cle_20040726_1.aspx

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

ORA-19815: WARNING: db_recovery_file_dest_size

kods.net — Tue, 31 Mar 2009 09:47:09+0000

Point encountered an error today, marking what:
************************************************** **********************
ARC0: Failed to archive thread 1 sequence 1 (19809)
Wed Feb 25 13:53:32 2009
Errors in file / oradata/oracle/admin/ebaby/bdump/ebaby_arc0_4576.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************** **********************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************** **********************

db_recovery_file_dest_size defaults to 1G, I have to change to the 2G, but the day of a large amount of data is more obvious or a bit small, can be increased a little, the size can be formed in accordance with the day or week to determine the redo (no matter what time, to know their increase or reduce the are doing this)

Encounter this error when my only way to manually delete the corresponding LOG, but still can not work.

Error message and then look, follow the above tips to use rman to delete (Oracle is smart to do, the stakes ah)

At Oracle in the first confirmation:

sys@EBABY> SELECT substr(name, 1, 30) name, space_limit AS quota,
rn          space_used        AS used,
rn  2            space_reclaimable AS reclaimable,
rn  3        number_of_files   AS files
rn  4    FROM  v$recovery_file_dest ;
rn  5 
rnNAME
rn------------------------------------------------------------------------------------------------------------------------
rn
rn     QUOTA       USED RECLAIMABLE      FILES
rn---------- ---------- ----------- ----------
rn
rn/oradata/oracle/flash_recovery
rn2147483648 2098868224           0         43

43 documents have. USED space:

2098868224　可见差不多用完了。

按Ｏｒａｃｌｅ提示的：
 用rman 去干掉他们：

rman target /
[uniread] Loaded history (6 lines)

Recovery Manager: Release 10.2.0.1.0 - Production on Wednesday, February 25 13:50:25 2009

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

RMAN> crosscheck archivelog all;
rn
rn使用目标数据库控制文件替代恢复目录
rn分配的通道: ORA_DISK_1
rn通道 ORA_DISK_1: sid=137 devtype=DISK
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_7_4rnxq1wc_.arc 记录时间戳 =678028770
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_8_4ro2r62q_.arc 记录时间戳 =678033926
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_9_4row81gd_.arc 记录时间戳 =678060035
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_06/o1_mf_1_10_4rq6fglm_.arc 记录时间戳 =678103215
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_06/o1_mf_1_11_4rrf3dvl_.arc 记录时间戳 =678142830
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_12_4rs5q9sc_.arc 记录时间戳 =678168042
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_13_4rt2h0bd_.arc 记录时间戳 =678197473
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_14_4rvbhrpm_.arc 记录时间戳 =678238457
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_08/o1_mf_1_15_4rwro9mq_.arc 记录时间戳 =678285738
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_16_4ry3xxq1_.arc 记录时间戳 =678330046
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_17_4rzl40xq_.arc 记录时间戳 =678377345
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_18_4s0fr0tg_.arc 记录时间戳 =678405633
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_19_4s1fdk9k_.arc 记录时间戳 =678438034
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_20_4s2pxcgw_.arc 记录时间戳 =678480556
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_21_4s388kmz_.arc 记录时间戳 =678498322
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_11/o1_mf_1_22_4s4mk1ny_.arc 记录时间戳 =678542626
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_11/o1_mf_1_23_4s5phykd_.arc 记录时间戳 =678578431
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_24_4s6f4g5d_.arc 记录时间戳 =678601615
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_25_4s7s9mgy_.arc 记录时间戳 =678646836
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_26_4s8f474v_.arc 记录时间戳 =678667144
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_13/o1_mf_1_27_4s9jwfog_.arc 记录时间戳 =678703759
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_13/o1_mf_1_28_4sbwtkvy_.arc 记录时间戳 =678748755
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_29_4sc8zt8v_.arc 记录时间戳 =678761211
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_30_4sczzjz4_.arc 记录时间戳 =678784754
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_31_4sfdlj9m_.arc 记录时间戳 =678830417
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_15/o1_mf_1_32_4sgnz01x_.arc 记录时间戳 =678871777
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_15/o1_mf_1_33_4shqhx0x_.arc 记录时间戳 =678907134
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_34_4sk44frh_.arc 记录时间戳 =678952847
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_35_4sl79rxh_.arc 记录时间戳 =678988857
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_36_4slwdpxn_.arc 记录时间戳 =679010455
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_17/o1_mf_1_37_4smwdtdz_.arc 记录时间戳 =679043227
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_17/o1_mf_1_38_4so8h1yz_.arc 记录时间戳 =679088355
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_39_4soysqdt_.arc 记录时间戳 =679111224
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_40_4sq2bdyh_.arc 记录时间戳 =679147598
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_41_4sr53q5q_.arc 记录时间戳 =679183224
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_42_4ss1778k_.arc 记录时间戳 =679212008
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_43_4st4dql5_.arc 记录时间戳 =679248024
rn对归档日志的验证成功
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_44_4stsjowy_.arc 记录时间戳 =679269656
rn对归档日志的验证失败
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_20/o1_mf_1_45_4svwytt6_.arc 记录时间戳 =679305947
rn对归档日志的验证失败
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_20/o1_mf_1_46_4sx9vf2f_.arc 记录时间戳 =679351918
rn对归档日志的验证失败
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_21/o1_mf_1_47_4sxy0wtz_.arc 记录时间戳 =679372573
rn对归档日志的验证失败
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_21/o1_mf_1_48_4syvlf89_.arc 记录时间戳 =679402831
rn对归档日志的验证失败
rn存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_21/o1_mf_1_49_4t07o2n4_.arc 记录时间戳 =679447971
rn已交叉检验的 43 对象

RMAN>  delete expired archivelog all;

释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=137 devtype=DISK

已存档的日志副本列表
关键字     Thrd Seq     S 短时间              名称
------- ---- ------- - ------------------- ----
1       1    7       X 2009/02/05 03:13:08 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_7_4rnxq1wc_.arc
2       1    8       X 2009/02/05 13:19:29 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_8_4ro2r62q_.arc
3       1    9       X 2009/02/05 14:45:26 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_9_4row81gd_.arc
4       1    10      X 2009/02/05 22:00:33 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_06/o1_mf_1_10_4rq6fglm_.arc
5       1    11      X 2009/02/06 10:00:14 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_06/o1_mf_1_11_4rrf3dvl_.arc
6       1    12      X 2009/02/06 21:00:28 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_12_4rs5q9sc_.arc
7       1    13      X 2009/02/07 04:00:41 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_13_4rt2h0bd_.arc
8       1    14      X 2009/02/07 12:11:12 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_14_4rvbhrpm_.arc
9       1    15      X 2009/02/07 23:34:16 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_08/o1_mf_1_15_4rwro9mq_.arc
10      1    16      X 2009/02/08 12:42:17 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_16_4ry3xxq1_.arc
11      1    17      X 2009/02/09 01:00:45 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_17_4rzl40xq_.arc
12      1    18      X 2009/02/09 14:09:04 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_18_4s0fr0tg_.arc
13      1    19      X 2009/02/09 22:00:32 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_19_4s1fdk9k_.arc
14      1    20      X 2009/02/10 07:00:33 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_20_4s2pxcgw_.arc
15      1    21      X 2009/02/10 18:49:15 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_21_4s388kmz_.arc
16      1    22      X 2009/02/10 23:45:21 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_11/o1_mf_1_22_4s4mk1ny_.arc
17      1    23      X 2009/02/11 12:03:45 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_11/o1_mf_1_23_4s5phykd_.arc
18      1    24      X 2009/02/11 22:00:30 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_24_4s6f4g5d_.arc
19      1    25      X 2009/02/12 04:26:54 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_25_4s7s9mgy_.arc
20      1    26      X 2009/02/12 17:00:35 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_26_4s8f474v_.arc
21      1    27      X 2009/02/12 22:39:03 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_13/o1_mf_1_27_4s9jwfog_.arc
22      1    28      X 2009/02/13 08:49:17 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_13/o1_mf_1_28_4sbwtkvy_.arc
23      1    29      X 2009/02/13 21:19:13 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_29_4sc8zt8v_.arc
24      1    30      X 2009/02/14 00:46:50 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_30_4sczzjz4_.arc
25      1    31      X 2009/02/14 07:19:12 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_31_4sfdlj9m_.arc
26      1    32      X 2009/02/14 20:00:16 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_15/o1_mf_1_32_4sgnz01x_.arc
27      1    33      X 2009/02/15 07:29:36 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_15/o1_mf_1_33_4shqhx0x_.arc
28      1    34      X 2009/02/15 17:18:53 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_34_4sk44frh_.arc
29      1    35      X 2009/02/16 06:00:45 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_35_4sl79rxh_.arc
30      1    36      X 2009/02/16 16:00:56 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_36_4slwdpxn_.arc
31      1    37      X 2009/02/16 22:00:54 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_17/o1_mf_1_37_4smwdtdz_.arc
32      1    38      X 2009/02/17 07:07:06 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_17/o1_mf_1_38_4so8h1yz_.arc
33      1    39      X 2009/02/17 19:39:13 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_39_4soysqdt_.arc
34      1    40      X 2009/02/18 02:00:23 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_40_4sq2bdyh_.arc
35      1    41      X 2009/02/18 12:06:36 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_41_4sr53q5q_.arc
36      1    42      X 2009/02/18 22:00:23 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_42_4ss1778k_.arc
37      1    43      X 2009/02/19 06:00:07 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_43_4st4dql5_.arc
38      1    44      X 2009/02/19 16:00:23 /oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_44_4stsjowy_.arc

是否确定要删除以上对象 (输入 YES 或 NO)? yes
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_7_4rnxq1wc_.arc 记录时间戳 =678028770
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_8_4ro2r62q_.arc 记录时间戳 =678033926
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_05/o1_mf_1_9_4row81gd_.arc 记录时间戳 =678060035
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_06/o1_mf_1_10_4rq6fglm_.arc 记录时间戳 =678103215
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_06/o1_mf_1_11_4rrf3dvl_.arc 记录时间戳 =678142830
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_12_4rs5q9sc_.arc 记录时间戳 =678168042
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_13_4rt2h0bd_.arc 记录时间戳 =678197473
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_07/o1_mf_1_14_4rvbhrpm_.arc 记录时间戳 =678238457
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_08/o1_mf_1_15_4rwro9mq_.arc 记录时间戳 =678285738
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_16_4ry3xxq1_.arc 记录时间戳 =678330046
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_17_4rzl40xq_.arc 记录时间戳 =678377345
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_09/o1_mf_1_18_4s0fr0tg_.arc 记录时间戳 =678405633
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_19_4s1fdk9k_.arc 记录时间戳 =678438034
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_20_4s2pxcgw_.arc 记录时间戳 =678480556
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_10/o1_mf_1_21_4s388kmz_.arc 记录时间戳 =678498322
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_11/o1_mf_1_22_4s4mk1ny_.arc 记录时间戳 =678542626
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_11/o1_mf_1_23_4s5phykd_.arc 记录时间戳 =678578431
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_24_4s6f4g5d_.arc 记录时间戳 =678601615
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_25_4s7s9mgy_.arc 记录时间戳 =678646836
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_12/o1_mf_1_26_4s8f474v_.arc 记录时间戳 =678667144
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_13/o1_mf_1_27_4s9jwfog_.arc 记录时间戳 =678703759
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_13/o1_mf_1_28_4sbwtkvy_.arc 记录时间戳 =678748755
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_29_4sc8zt8v_.arc 记录时间戳 =678761211
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_30_4sczzjz4_.arc 记录时间戳 =678784754
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_14/o1_mf_1_31_4sfdlj9m_.arc 记录时间戳 =678830417
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_15/o1_mf_1_32_4sgnz01x_.arc 记录时间戳 =678871777
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_15/o1_mf_1_33_4shqhx0x_.arc 记录时间戳 =678907134
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_34_4sk44frh_.arc 记录时间戳 =678952847
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_35_4sl79rxh_.arc 记录时间戳 =678988857
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_16/o1_mf_1_36_4slwdpxn_.arc 记录时间戳 =679010455
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_17/o1_mf_1_37_4smwdtdz_.arc 记录时间戳 =679043227
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_17/o1_mf_1_38_4so8h1yz_.arc 记录时间戳 =679088355
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_39_4soysqdt_.arc 记录时间戳 =679111224
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_40_4sq2bdyh_.arc 记录时间戳 =679147598
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_18/o1_mf_1_41_4sr53q5q_.arc 记录时间戳 =679183224
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_42_4ss1778k_.arc 记录时间戳 =679212008
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_43_4st4dql5_.arc 记录时间戳 =679248024
已删除的存档日志
存档日志文件名 =/oradata/oracle/flash_recovery_area/EBABY/archivelog/2009_02_19/o1_mf_1_44_4stsjowy_.arc 记录时间戳 =679269656
38 EXPIRED 对象已删除

这下可to work.

Conclusion of the regular filing date the transfer value

redhat5.3 install oracle10g 2009-11-06 13:35:03
Recorded warning DBA: DBA needs to develop some habits 2009-06-18 20:27:33
Redo log files related to operation 2009-11-19 16:40:28

How to quickly kill a disproportionate amount of resources (CPU, memory) database process

kods.net — Tue, 31 Mar 2009 05:30:10+0000

A lot of time because of unusual or procedural errors leading to a particular process will use up a lot of system resources, the need to end these processes can usually be Kill the process using the following command:

alter system kill session 'sid,serial#';

However, this command is extremely slow release of resources, specifically can refer to: Oracle Research in Kill session.
More quickly in order to release resources, often we use the following steps to Kill the process:
1. First of all, kill the process at operating system level
2. Kill session in the database within
This process is usually quick to suspend the release of resources.

Today to face such a case, other friends in the database kill session, but no Long time effects:

[oracle@danaly ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 27 11:09:50 2005

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> select sid,username,status from v$session;

       SID USERNAME                       STATUS
---------- ------------------------------ --------
....
       154 SCOTT                          KILLED
...

30 rows selected.

That in accordance with the steps I mentioned earlier, first of all inquiries by the OS the process of the corresponding session number:

SQL> select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);
Enter value for sid: 154
old   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid)
new   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=154)

'KILL-9'||SPID
--------------------
kill -9 22702

SQL> !

Class at the operating system kill the process:

[oracle@danaly ~]$ ps -ef|grep 22702
oracle   22702     1  0 Oct25 ?        00:00:02 oracledanaly (LOCAL=NO)
oracle   12082 12063  0 11:12 pts/1    00:00:00 grep 22702
[oracle@danaly ~]$ kill -9 22702
[oracle@danaly ~]$ ps -ef|grep 22702
oracle   12088 12063  0 11:12 pts/1    00:00:00 grep 22702
[oracle@danaly ~]$ exit
exit

SQL> select sid,username,status from v$session;

       SID USERNAME                       STATUS
---------- ------------------------------ --------
...
       154 SCOTT                          KILLED
...

30 rows selected.

SQL> select sid,serial#,username from v$session where sid=154;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       154      56090 SCOTT

In the database once again kill the session, and specify the immediate options:

SQL> alter system kill session '154,56090' immediate;

System altered.

SQL> select sid,serial#,username from v$session where sid=154;

no rows selected

At this point the process was quickly cleared.

Oracle HowTo: How to quickly kill using too many resources (CPU, memory) database process 2009-11-05 21:02:56
Latch wait events 2009-07-07 14:46:04
Kill session in Oracle Research 2009-04-15 19:03:06
OS-level kill oracle session 2009-08-04 15:14:54
On the v $ resource_limit resources gcs_resources 2009-07-07 20:41:03

Oracle10g ASM maintenance of the database table space

kods.net — Tue, 31 Mar 2009 00:06:06+0000

1. Default naming Oracle when using ASM + OMF management, Oracle distribution file name will automatically create the appropriate file.
Our job to create table space can be simplified as follows:

create tablespace tablespace_name;

A command.
The following are the default Oracle naming examples:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jun 16 09:26:22 2005

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATADG/mmsdb/datafile/system.259.3
+DATADG/mmsdb/datafile/undotbs1.258.3
+DATADG/mmsdb/datafile/sysaux.257.3
+DATADG/mmsdb/datafile/users.256.3
+DATADG/mmsdb/datafile/platform.278.1
+DATADG/mmsdb/datafile/gateway.279.1

6 rows selected.

2. Since the definition of the name and file size if we want to create a custom file size and name, you can use the following method to create:

SQL> create tablespace cumail datafile '+DATADG/mmsdb/datafile/cumail'size 100m;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATADG/mmsdb/datafile/system.259.3
+DATADG/mmsdb/datafile/undotbs1.258.3
+DATADG/mmsdb/datafile/sysaux.257.3
+DATADG/mmsdb/datafile/users.256.3
+DATADG/mmsdb/datafile/platform.278.1
+DATADG/mmsdb/datafile/gateway.279.1
+DATADG/mmsdb/datafile/cumail

7 rows selected.

SQL> drop tablespace cumail including contents and datafiles;

Tablespace dropped.

3. ASSM managed to create the specified table space segment space required clause:

SQL> create tablespace cumail
  2  segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------------------------------
+DATADG/mmsdb/datafile/system.259.3
+DATADG/mmsdb/datafile/undotbs1.258.3
+DATADG/mmsdb/datafile/sysaux.257.3
+DATADG/mmsdb/datafile/users.256.3
+DATADG/mmsdb/datafile/platform.278.1
+DATADG/mmsdb/datafile/gateway.279.1
+DATADG/mmsdb/datafile/cumail.280.3

7 rows selected.

SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         LOCAL      MANUAL
UNDOTBS1                       LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO
PLATFORM                       LOCAL      MANUAL
GATEWAY                        LOCAL      MANUAL
CUMAIL                         LOCAL      AUTO

8 rows selected.

3. To change the file size

SQL> alter database datafile '+DATADG/mmsdb/datafile/cumail.280.3' resize 300m;

Database altered.

SQL>

4. If the property set the UNIFORM SIZE to some extent the need for property, it must show the designation.

SQL> create tablespace ticket extent management local uniform size 128k segment space management auto;

Tablespace created.


SQL> select name ,bytes/1024/1024 from v$datafile;

NAME                                     BYTES/1024/1024
---------------------------------------- ---------------
+DATADG/mmsdb/datafile/system.259.3                  450
+DATADG/mmsdb/datafile/undotbs1.258.3                 25
+DATADG/mmsdb/datafile/sysaux.257.3                  330
+DATADG/mmsdb/datafile/users.256.3                     5
+DATADG/mmsdb/datafile/platform.278.1               1000
+DATADG/mmsdb/datafile/gateway.279.1                1000
+DATADG/mmsdb/datafile/cumail.280.3                  300
+DATADG/mmsdb/datafile/ticket.281.1                  100

8 rows selected.

SQL> alter database datafile '+DATADG/mmsdb/datafile/ticket.281.1' resize 300m;

Database altered.

Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Oracle DBA database entirely routine maintenance manuals 2009-06-18 17:29:18
Oracle maintenance of commonly used SQL statement 2009-03-18 02:38:55
How to shut down and start Oracle10g RAC 2009-08-27 13:52:15
DBA scripts used 2009-06-18 09:52:14

Record two small test

kods.net — Mon, 30 Mar 2009 22:58:00+0000

1. SQL predicate in between ... and ... yes, including the border, the following is a small test:

SQL> select *

2 from (select rownum rn from dba_objects where rownum <6)

3 where rn between 2 and 4;

RN

----------

2

3

4

2. Unix search under the Oracle error:

[dwapp @ dbrac1 ~] $ oerr ora 30004

30004, 00000, "when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value"

/ / * Cause:

/ / * Action: Use another seperator which does not occur in any column value,

/ / Then retry.

On small test rownum 2009-04-02 16:34:21
Rownum about a small test 2009-04-02 14:00:46
Oracle 10g RMAN Learning Record 2010-01-17 08:23:18
append the test increases nologging 2009-07-02 02:50:01
Record of work - outside the connection and session blocking queries 2010-01-08 11:06:46

RMAN created physical standby

kods.net — Mon, 30 Mar 2009 22:49:29+0000

Monitor in the standby host to create the necessary directory structure and parameter files. Hot COPY after the main library to the standby, and on standby to restore.

+++++++++++++++++++++++++++++ Standby ++++++++++++++++++++++++++++++++++++++++++++++

C: \>% oracle_home% \ bin \ rman

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Mar 30 17:06:49 2009

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

RMAN> connect target /

connected to target database: lgx (not mounted)

RMAN> restore standby controlfile from 'P: \ backup02 \ CTL_0MKB7QQM_1_1';

Starting restore at 30-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 156 devtype = DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename = P: \ LGXDB \ CONTROL01.CTL
output filename = P: \ LGXDB \ CONTROL02.CTL
output filename = P: \ LGXDB \ CONTROL03.CTL
Finished restore at 30-MAR-09

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 30-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 156 devtype = DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile (s) to restore from backup set
restoring datafile 00001 to P: \ LGXDB \ SYSTEM01.DBF
restoring datafile 00002 to P: \ LGXDB \ UNDOTBS01.DBF
restoring datafile 00003 to P: \ LGXDB \ SYSAUX01.DBF
restoring datafile 00004 to P: \ LGXDB \ USERS01.DBF
channel ORA_DISK_1: reading from backup piece P: \ BACKUP02 \ 0KKB7QP7_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle = P: \ BACKUP02 \ 0KKB7QP7_1_1 tag = TAG20090330T164943
channel ORA_DISK_1: reading from backup piece P: \ BACKUP02 \ 0KKB7QP7_2_1
channel ORA_DISK_1: restored backup piece 2
piece handle = P: \ BACKUP02 \ 0KKB7QP7_2_1 tag = TAG20090330T164943
channel ORA_DISK_1: reading from backup piece P: \ BACKUP02 \ 0KKB7QP7_3_1
channel ORA_DISK_1: restored backup piece 3
piece handle = P: \ BACKUP02 \ 0KKB7QP7_3_1 tag = TAG20090330T164943
channel ORA_DISK_1: reading from backup piece P: \ BACKUP02 \ 0KKB7QP7_4_1
channel ORA_DISK_1: restored backup piece 4
piece handle = P: \ BACKUP02 \ 0KKB7QP7_4_1 tag = TAG20090330T164943
channel ORA_DISK_1: reading from backup piece P: \ BACKUP02 \ 0KKB7QP7_5_1
channel ORA_DISK_1: restored backup piece 5
piece handle = P: \ BACKUP02 \ 0KKB7QP7_5_1 tag = TAG20090330T164943
channel ORA_DISK_1: reading from backup piece P: \ BACKUP02 \ 0KKB7QP7_6_1
channel ORA_DISK_1: restored backup piece 6
piece handle = P: \ BACKUP02 \ 0KKB7QP7_6_1 tag = TAG20090330T164943
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 30-MAR-09

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------- -----
1 54.20M DISK 00:00:02 30-MAR-09
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20090330T115706
Piece Name: P: \ BACKUP02 \ 01KB79KI_1_1

List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 594698 30-MAR-09 606214 30-MAR-09
1 2 606214 30-MAR-09 608310 30-MAR-09
1 3 608310 30-MAR-09 611866 30-MAR-09
1 4 611866 30-MAR-09 613754 30-MAR-09
1 5 613754 30-MAR-09 613824 30-MAR-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------- -----
4 2.50K DISK 00:00:01 30-MAR-09
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20090330T115725
Piece Name: P: \ BACKUP02 \ 04KB79L5_1_1

List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 613824 30-MAR-09 613837 30-MAR-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------- -----
6 57.19M DISK 00:00:01 30-MAR-09
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20090330T130625
Piece Name: P: \ BACKUP02 \ 07KB7DMH_1_1

List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 594698 30-MAR-09 606214 30-MAR-09
1 2 606214 30-MAR-09 608310 30-MAR-09
1 3 608310 30-MAR-09 611866 30-MAR-09
1 4 611866 30-MAR-09 613754 30-MAR-09
1 5 613754 30-MAR-09 613824 30-MAR-09
1 6 613824 30-MAR-09 613837 30-MAR-09
1 7 613837 30-MAR-09 616271 30-MAR-09
1 8 616271 30-MAR-09 616338 30-MAR-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------- -----
9 3.00K DISK 00:00:01 30-MAR-09
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20090330T130645
Piece Name: P: \ BACKUP02 \ 0AKB7DN5_1_1

List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 616338 30-MAR-09 616351 30-MAR-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------- -----
11 57.30M DISK 00:00:02 30-MAR-09
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20090330T133353
Piece Name: P: \ BACKUP02 \ 0CKB7FA1_1_1

List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 594698 30-MAR-09 606214 30-MAR-09
1 2 606214 30-MAR-09 608310 30-MAR-09
1 3 608310 30-MAR-09 611866 30-MAR-09
1 4 611866 30-MAR-09 613754 30-MAR-09
1 5 613754 30-MAR-09 613824 30-MAR-09
1 6 613824 30-MAR-09 613837 30-MAR-09
1 7 613837 30-MAR-09 616271 30-MAR-09
1 8 616271 30-MAR-09 616338 30-MAR-09
1 9 616338 30-MAR-09 616351 30-MAR-09
1 10 616351 30-MAR-09 616963 30-MAR-09
1 11 616963 30-MAR-09 616967 30-MAR-09
1 12 616967 30-MAR-09 617061 30-MAR-09
1 13 617061 30-MAR-09 617095 30-MAR-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------- -----
14 2.50K DISK 00:00:01 30-MAR-09
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20090330T133413
Piece Name: P: \ BACKUP02 \ 0FKB7FAL_1_1

List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14 617095 30-MAR-09 617107 30-MAR-09

RMAN> sql 'alter database recover managed standby database disconnect from sessi
on ';

sql statement: alter database recover managed standby database disconnect from s
ession

RMAN>

+++++++++++++++++++++++++++++ Standby ++++++++++++++++++++++++++++++++++++++++++++++

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

Oracle database backup and recovery summary -exp/imp (Export and Import Bank and unloading equipment library)

kods.net — Mon, 30 Mar 2009 22:27:55+0000

1.1 basic commands

1. Getting Help

$ Exp help = y

$ Imp help = y

2. Three work

(1) interactive mode

$ Exp / / then prompted to enter the required parameters

(2) command-line mode

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

(3) parameters of papers

$ Exp parfile = username.par / / in the parameter file input parameters required

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

compress = n grants = y

file = / oracle / test.dmp full = y

3. Three models

(1) table, and will specify the data table export / import.

Export:

Export one or a few below:

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

Export of a certain part of the data tables

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

and col2 \ <... \ "

Import:

Into one or a few tables

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

(2) user mode, users will be assigned all the objects and data Export / Import.

Export:

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

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

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

Import:

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

commit = y ignore = y

(3) the way the whole database, the database of all objects in Export / Import Export:

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

Import:

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

1.2 Advanced options

1. Is separated into multiple files

To a number of fixed-size paper is derived: it is usually the larger amount of data in the table, a single dump file may be

File systems would exceed the limit

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

To a number of fixed-size paper into

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

2. Incremental Export / Import

/ / Oracle 9i is no longer supported exp after inctype

Must be SYS or SYSTEM before the implementation of the incremental export import

Incremental Export: consists of three types:

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

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

(2) "incremental" an incremental backup on Export to export the data after the change.

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

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

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

Incremental import:

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

One of:

SYSTEM: import system objects

RESTORE: import all the user object

3. S Y S DBA for Export / Import

1. For Oracle technical support

2. For table space transmission

Example:

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

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

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

4. Tablespace transmission (high speed)

Table space transmission is a new 8i Express between mobile data in the database as a solution is put on the format of a database data file attached to another database, rather than export the data into a dmp file, which in some cases are very effective, because the space of mobile data transmission tables as quickly as copy paper.

1. On the transfer table space has a number of rules (10g before):

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

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

? Source database and target database must be the same size block of data

? Aim database should not have the same name with the transfer of the table Table Spaces Spaces

? SYS object of the transfer should not

? Must transfer from the object set that contains

? There is some object, such as materialized views, function-based index can not be transmitted, etc.

(Endianness documents with cross-platform data files can be used to replace the first method of the document)

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

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

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

If there is no line selection, the table space that contains only tables of data, and are self-contained. For some non-self-packets

Containing the table space, data tables such as table space and index space can be transmitted together.

3. Briefly the use of these steps:

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

1. Set tablespace read-only (assuming the table space for the first name APP_Data and APP_Index)

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

2. Issued orders EXP

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

Should be noted that the above

SQL in order to perform at EXP, USERID three quotes must be in UNIX also must be taken to avoid "/" the use of

At 816 and after the operation must be used to sysdba

SQL at the command must be placed in the line (in this show because questions are on the two lines)

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

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

4. The local tablespace set to read and write

$ Alter tablespace app_data read write;

$ Alter tablespace app_index read write;

5. Aim at the database of additional data files (data files directly specify name)

(Table space should not exist, we must set up the corresponding user name or fromuser / touser)

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

6. Set up goals for reading and writing database table space

$ Alter tablespace app_data read write;

$ Alter tablespace app_index read write;

1.3 Optimization

1. To accelerate the speed of e x p

Increase large_pool_size, can improve the speed of exp manner of direct path (direct = y), data integration without a memory and checks. Set a larger buffer, if the export large object, a small buffer will fail.

ORACLE not export files for use on the drive not to export to NFS file system

UNIX Environment: piped directly into guided modes improve by imp / exp performance

2. To accelerate the speed of imp

Set up a indexfile, after the completion of import in the data at index

Import documents will be placed on different drives to increase DB_BLOCK_BUFFERS

Increase LOG_BUFFER

Archive run by non-ORACLE: ALTER DATABASE NOARCHIVELOG; set up a large table space and rollback segment, OFFLINE other rollback segment, the size of rollback segment table for the largest 1 / 2 to use COMMIT = N

Use ANALYZE = N

Into single-user mode

UNIX Environment: piped directly into guided modes improve by imp / exp performance

3.通过u ni x / Linux PIPE pipeline to speed up the e x p / imp speed

Export of data through the pipeline:

1. Mknod-p set up through the pipeline

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

2.通过exp and gzip export data to set up the pipeline and compression

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

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

gzip bitmap.dmp.gz

3. Export delete set up after the successful completion of the pipeline

$ Rm-rf / home / exppipe

Export script:

# # # UNIX under the ORACLE database to back up through the PIPE pipeline

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

trap "" 1 # nohup

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

export LOGFILE

DUMPDIR = / archlog_node1

export DUMPDIR

exec> $ LOGFILE 2> & 1

echo

echo 'Begin at' `date`

echo

# Clear old result file

cd $ DUMPDIR

if [-f exp.dmp.Z]

then

echo "clear old result file"

rm exp.dmp.Z

fi

# Make pipe

mkfifo exp.pipe

chmod a + rw exp.pipe

# Gain the dmp.Z file

compress exp.dmp.Z &

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

echo

echo 'exp end at' `date`

echo

# Rm pipe

rm exp.pipe

# Tar the dmp.Z file to tape

mt-f / dev/rmt/0 rew

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

echo

echo 'tar end at' `date`

echo

Generated through the pipeline to import documents:

1. Mknod-p set up through the pipeline

$ Mknod / home / exppipe p

2. Import files generated

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

gunzip / home / exppipe

3. Delete pipe

$ Rm-fr / home / exppipe

4. The entire library into the general steps

Note: At export when required, or other instrument through the toad extract source database to create the primary key and index script

1. Add to the whole database structure rows = n put lead into the

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

2. To enable business users to trigger the failure / delete the primary key and unique index

spool drop_pk_u.sql

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

from user_constraints

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

/

spool off

spool disable_trigger.sql

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

from user_triggers;

/

spool off

@ D r op _p k _ u. S ql

@ Di s a b l e _ t r i g g e r. S ql

3. To ignore = y full database import

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

4. Toad or other instrument through the extraction of the source database to create the primary key and index the script, in the target database to create the primary key

And index. So that triggers the entry into force.

1.4 Frequently Asked Questions

1. Character Set Problem

ORACLE settings are many languages around the world in order to support the language and character set, most of the language suggests that the monetary form, sorting methods and CHAR, VARCHAR2, C LOB, LONG field data show that effective. ORACLE set of multiple languages is the most important characteristics of both the National language settings and character sets, national language setting determines the interface or the language used in the type of prompt, the database character set determines the character set and save the data

(Such as text) when the encoding rules.

ORACLE set character set into the database character set and client character set environment settings. Client in the database,

Create database character set at the time set, and stored in the database props $ table.

At the client's character set is relatively easy, mainly the environment variables or registry entries NLS_ LANG, pay attention to the priority level NLS_LANG as follows: Parameter File
Use a little bit of skill, it can export / import in different database character set conversion on the data. Here

Required a 2-band file editing tool can be, such as uedit32. Open the Edit mode by dmp file exported by the

Take 2, 3 bytes of content, such as 00 01, first it is converted to hexadecimal number 10, for one, use the function

Get NLS_CHARSET_NAME the character set: SQL> select nls_charset_name (1) from dual; NLS_CHARSET_NAME (1)

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

Dmp file to know the character set for US7ASCII, if dmp file the required character set for

Into ZHS16GBK, you need to use the character set NLS_CHARSET_ID access to the code: SQL> select nls_charset_id ( 'zhs16gbk') from dual; NLS_CHARSET_ID ( 'ZHS16GBK')

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

Put 852 into hexadecimal number of 16 for 354, the 2,3-byte 00 01 into 03 54, that is, completed the character set from the dmp file to us7ascii conversion zhs16gbk In this way, then the file introduction dmp into the database character set zhs16gbk it.

2. Version problem

Exp / Imp a lot of time, you can use cross-version, such as in version 7 and version 8 export import between the data, but it must choose to do the right version of the rules as follows:

Always use the version of IMP to match the database version of the 816 if you want to import, use the Import tool 816.

Always use the EXP version of both the database matching the low version, such as at between 815 and 816 each guide, use the EXP instrument 815.

imp and exp up version should not compatible: imp lower version can import files generated by exp, exp should not import the high version of the document generation.

Database Backup and Recovery 2009-03-12 23:47:20
oracle data export / import (exp / imp) summary 2009-03-30 00:52:47
There are three standard Oracle database backup methods 2009-06-19 17:21:50
Export Oracle database data into the basic methods and regular backup script unix 2009-07-29 21:23:34
RMAN backup and recovery - RAC database backup and recovery environment 2009-06-26 02:10:01

The use of ORACLE in dbms_stats

kods.net — Mon, 30 Mar 2009 18:14:40+0000

can be estimated dbms_stats good statistical data (especially for the larger partition table), and to obtain better results, faster and finally work out the SQL implementation plan.

exec dbms_stats.gather_schema_stats (
ownname => 'SCOTT',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 15
)
Dbms_stats in order to fully understand the benefits of experience need to look closely at each one of the main pre-compiler directive (directive). Let us examine each of the following a directive, and understand how to use it for the cost-based SQL optimizer of the highest quality collection of statistical data.
parameter options

The use of four pre-set one way, this option can be set to control the way Oracle statistics:

gather - to re-analysis of the whole structure (Schema).
gather empty - only there is no statistical analysis of the table.
gather stale - just modify the volume of re-analysis of more than 10% of the table (these changes include the insertion, update and delete).
gather auto - NOT re-analysis of the current statistics, as well as statistical data expired (dirty) of the object. Note that the use gather auto combination is similar to gather stale and gather empty.
Note that gather stale or gather auto, require surveillance. If you implement an alter table xxx monitoring command, Oracle will use to track dba_tab_modifications view table changes. In this way, you know exactly, since an analysis on statistical data, the occurrence of the number of insert, update and delete operations.

estimate_percent options

estimate_percent parameters are a relatively new design, which allows Oracle to collect statistics at the dbms_stats data, automatic sampling is estimated to be the best percentage of a segment:
estimate_percent => dbms_stats.auto_sample_size

To verify the accuracy of automatic statistical sampling, you can view dba_tables sample_size out. A fun place is the use of automatic sampling at the time, Oracle will select a sample size of the percentage of 5-20. Remember, the better the quality of statistical data, CBO, the better the decision.

method_opt options
method_opt: for table - only tables
for all indexed columns - only the statistics shown in the table has index
for all indexes - only the analysis of statistics related to the index
for all columns

In particular, the dbms_stats parameters method_opt fit the data in the table and index changes in statistical data set. method_opt fit parameters necessary to determine which column histogram (histograms).

Some cases, the various values of the index will affect the distribution of CBO is to use an index or perform a full table scan of the decision-making. For example, if in the where clause values specified in the number of asymmetric, full-table scan it is more economical than the index visit.

If you have a high degree of tilt of the index (of some asymmetry in the value of the number of rows), Oracle can create a histogram statistics. But in the real world, the chances of such a situation is rather small. When using the CBO, the most common mistakes is one of at CBO statistics histogram introduce unnecessary. The light of experience, only the value of the requirements set out at the implementation of the plan must be amended before the histogram should be used.

Histogram in order to generate intelligent, Oracle for the preparation of the method_opt parameters dbms_stats. At method_opt clause, there are some important new options, including skewonly, repeat and auto:
method_opt => 'for all columns size skewonly'
method_opt => 'for all columns size repeat'
method_opt => 'for all columns size auto'

skewonly treatment options would be substantial time, because it wants to check each index value out of each distribution.

If an index dbms_stat all found out very uneven distribution, it will create a histogram for the index to help the cost-based SQL optimizer is to decide the index visit, or visit the full-table scan. For example, in an index, it is assumed that there is a bar at 50% of the line, such as shown in the list of B, then these lines in order to retrieve the whole table scan will be faster than the speed of scanning the index.

--************************************************ *************
- SKEWONLY option-Detailed analysis
--
- Use this method for a first-time analysis for skewed indexes
- This runs a long time because all indexes are examined
--************************************************ *************

begin
dbms_stats.gather_schema_stats (
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;

Re-analysis of statistical data, the use of repeat options, re-analysis of the mission will consume less resources. The use of repeat options (list C), only to re-histogram analysis of the existing index, histogram no longer search for other opportunities. Re-analysis of statistical data on a regular basis, you should take this approach.

--************************************************ **************
- REPEAT OPTION - Only reanalyze histograms for indexes
- That have histograms
--
- Following the initial analysis, the weekly analysis
- Job will use the "repeat" option. The repeat option
- Tells dbms_stats that no indexes have changed, and
- It will only reanalyze histograms for
- Indexes that have histograms.
--************************************************ **************
begin
dbms_stats.gather_schema_stats (
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;

Use alter table xxx monitoring; order to monitor the implementation Oracle table, the need to use the auto option dbms_stats. Such as D shown in the list, auto option in accordance with data distribution, and application access out of the way (for example, determined by monitoring the workload of a column) to create a histogram. The use of method_opt => 'auto' is similar to the option parameter in dbms_stats use gather auto.

begin
dbms_stats.gather_schema_stats (
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;

Statistical parameters of the collection of parallel degree
Oracle recommends setting the DEGREE parameter DBMS_STATS for DBMS_STATS.AUTO_DEGREE, permit Oracle in accordance with the parameters of the size of the object and parallel set of initialization parameters select the appropriate degree of parallelism.
Clustered indexes, domain indexes, bitmap indexes can not be connected in parallel to collect.

Dbms_stats analysis of how to use statistical information?
- Create a table to retain the history of statistical information

sql> exec dbms_stats.create_stat_table (ownname => 'scott', stattab => 'stat_table');

- Export the whole scheme of the statistical information

sql> exec dbms_stats.export_schema_stats (ownname => 'scott', stattab => 'stat_table');

- Analysis of scheme

Exec dbms_stats.gather_schema_stats (
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns',
degree => 6)

- Analysis

sql> exec dbms_stats.gather_table_stats (ownname => 'scott', tabname => 'work_list', estimate_percent => 10, method_opt => 'for all indexed columns');

- Analysis of the index

SQL> exec dbms_stats.gather_index_stats (ownname => 'crm2', indname => 'IDX_ADM_PERMISSION_PID_MID', estimate_percent =>'10 ', degree =>'4');

- If it is found that the Plan of Implementation wrong, delete the statistics table

SQL> dbms_stats.delete_table_stats (ownname => 'scott', tabname => 'work_list');

- Into the history of table statistics

sql> exec dbms_stats.import_table_stats (ownname => 'scott', tabname => 'work_list', stattab => 'stat_table');

- If, after analysis, the majority of the Plan of Implementation of the table are wrong, the need to back the whole scheme of the statistical information

sql> exec dbms_stats.import_schema_stats (ownname => 'scott', stattab => 'stat_table');

- Statistical information into the index

SQL> exec dbms_stats.import_index_stats (ownname => 'crm2', indname => 'IDX_ADM_PERMISSION_PID_MID', stattab => 'stat_table')

- Check whether the Successful Import

SQL> select table_name, num_rows, a.blocks, a.last_analyzed from all_tables a where a.table_name = 'WORK_LIST';

Analysis of the database (including all user objects and system objects): gather_database_stats
Analysis of the object of all users (including the table, index, cluster): gather_schema_stats
Analysis: gather_table_stats
Analysis of the index: gather_index_stats
Delete the database of statistical information: delete_database_stats
Delete the user program of statistical information: delete_schema_stats
Delete table statistics: delete_table_stats
Delete index statistics information: delete_index_stats
Delete statistical information: delete_column_stats
Set up information table statistics: set_table_stats
Index set of statistical information: set_index_stats
Statistical information setting out: set_column_stats

Oracle Database 10g from the start, Oracle database after the default in the creation of a mission called the timing GATHER_STATS_JOB for automated CBO statistics collection information.

The default automatic mission at 10:00-6:00 Evening and weekend days open throughout the day. DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC call the collection of statistical information.
First of all, the process of detection of loss of statistical information and the old object. Then determine the priority class, and then begin to carry out statistical information.

Following inquiries by the operation of this JOB:

select * from Dba_Scheduler_Jobs where JOB_NAME = 'GATHER_STATS_JOB'

In fact, at 10 o'clock with the operation of Job have a AUTO_SPACE_ADVISOR_JOB:

SQL> select JOB_NAME, LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME LAST_START_DATE
------------------------------ -------------------- --------------------
AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
FGR $ AUTOPURGE_JOB
PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC

However, the automation system has been affected a lot of the normal operation of 22:00 for most production systems are not free time.
And automatic analysis may lead to very serious competition latch, which may cause the database Hang or Crash.

It would be best so turn off the automatic capturing of statistical information

One of the ways:
exec dbms_scheduler.disable ( 'SYS.GATHER_STATS_JOB');
Auto-Recovery Analysis:
exec dbms_scheduler.enable ( 'SYS.GATHER_STATS_JOB');

Two Ways:
alter system set "_optimizer_autostats_job" = false scope = spfile;
alter system set "_optimizer_autostats_job" = true scope = spfile;
Pfile can directly modify the initialization parameter file, restart the database.

Detailed DBMS_STATS.GATHER_TABLE_STATS 2009-04-20 22:56:43
Dbms_stats analysis of how to use statistical information 2009-07-11 00:35:02
Statistical analysis: dbms_stats and analyze 2009-07-06 01:44:00
Analysis on the table 2010-01-22 08:09:21
dbms_stats.gather_table_stats and analyze table that small differences 2009-03-12 03:10:57

checking network configuration requirements

kods.net — Mon, 30 Mar 2009 17:55:14+0000

ORACLE11G test environment installed today, and checking network configuration requirements encountered Detect does not pass, the machine configuration is a static IP too, remember the PASS manual directly. Resolve this question is actually very simple to share the following way under me.

[root@linux ~]# vi /etc/hosts

#文件最前面加上一条
192.168.28.8 linux linux
#linux是主机名字

Then retry, detection through.

Oracle10g Network Configuration 2009-04-05 05:19:49
Oracle network configuration used in the sqlnet.ora, tnsnames.ora, listener.ora 2009-05-20 22:16:36
Oracle network configuration used in sqlnet.ora, tnsnames.ora, listener.ora 2009-05-20 14:33:15
Oracle network configuration using the sqlnet.ora, tnsnames.ora, listener.ora 2009-05-20 00:37:15
oracle upgrade steps 2009-11-18 04:58:14

Oracle deadlock related

kods.net — Mon, 30 Mar 2009 16:46:54+0000

ORACLE some were killed in the process, the status was set to "killed", but the lock does not release resources for a long time, sometimes it is no way but to restart the database. Now offers a way to solve this problem, that is, in the killing at ORACLE away, killed at OS level.

1. The following statement to query the object which was locked:

select object_name, machine, s.sid, s.serial #
from v $ locked_object l, dba_objects o, v $ session s
where l.object_id = o.object_id and l.session_id = s.sid;

2. The following statement to kill a process:
alter system kill session'24111 '; (one of the top 24,111 queries were out of sid, serial #)

【Note】 The above two steps can be Oracle's management console to perform.

3. If you use the above command to kill a process, the process was set to status "killed", but the resources locked for a long time have not been released, then again at os level to kill the process (thread), First of all, the implementation of the following statements to obtain the process (thread) number:
select spid, osuser, s.program
from v $ session s, v $ process p
where s.paddr = p.addr and s.sid = 24 (24 are above sid)

4. On the OS to kill the process (thread):
1) on unix, the implementation of identity with root command:
# kill -9 12345 (that is, Step 3 of the query spid)
2) windows (unix also applies to) orakill killed by thread, orakill are provided by an executable oracle command syntax is:
orakill sid thread
One of:
sid: to kill the process that belong to the instance name
thread: the thread is to kill the number, that is, Step 3 of the query spid.
Example: c:> orakill orcl 12345

ORA-00031: session marked for kill

Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.

Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

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 Data Import Export imp / exp command - to

kods.net — Mon, 30 Mar 2009 13:37:00+0000

Oracle Data Import Export imp / exp on oracle is equivalent to restore and backup data. exp command to put the data from a remote database server to the local exported dmp file, imp command dmp files can be put into the distance from the local database server. This function can be build using the same two databases, one for testing, one for official use.

Execution Environment: SQLPLUS.EXE or in DOS (command line) in the implementation of
DOS can be implemented in oracle 8i because of the installation directory is set to ora81BIN global path,
EXP.EXE the directory has been used with the implementation of IMP.EXE document import and export.
oracle using java prepared, SQLPLUS.EXE, EXP.EXE, IMP.EXE have the two documents may have been the type of paper packaging.
SQLPLUS.EXE call EXP.EXE, IMP.EXE wrapped by the class to complete import and export functions.

Here is an example of import and export.
Data Export:
1 will be derived entirely TEST database, user name, system password manager Export to D: daochu.dmp Medium
exp system / manager @ TEST file = d: daochu.dmp full = y
2 will be system users database table and export sys user
exp system / manager @ TEST file = d: daochu.dmp owner = (system, sys)
3 will be in the database table inner_notify, notify_staff_relat Export
exp aichannel / aichannel @ TESTDB2 file = d: datanewsmgnt.dmp tables = (inner_notify, notify_staff_relat)

4 will be in the database field in the table1 table filed1 to "00" Export data from the beginning
exp system / manager @ TEST file = d: daochu.dmp tables = (table1) query = "where filed1 like'00% '"

Derived above are commonly used for compression, not only put dmp file with winzip compression can be very good.
In the above command can also be followed by compress = y to achieve.

Data import
1 will be D: daochu.dmp in the TEST data into the database.
imp system / manager @ TEST file = d: daochu.dmp
imp aichannel / aichannel @ HUST full = y file = d: datanewsmgnt.dmp ignore = y
Above problem may be a bit, because some form already exists, then it is error, not to import the table.
Behind with ignore = y on it.
2 d: daochu.dmp import table table1
imp system / manager @ TEST file = d: daochu.dmp tables = (table1)

Basically, the above import and export good enough for us. A number of cases is to first delete the complete table, and then import.

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

Appendix 1:
Import data to the user to increase the operation of the first authority to start the sql * puls
Second, system / manager landed third, create user username IDENTIFIED BY password (if the user has been created, this step can be omitted)
Fourth, GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW,
DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE,
DBA, CONNECT, RESOURCE, CREATE SESSION TO user name of the fifth, run-cmd-enter the dmp file directory,
imp userid = system / manager full = y file =*. dmp
Or imp userid = system / manager full = y file = filename.dmp

Implementation of examples:
F: WorkOracle_Databackup> imp userid = test / test full = y file = inner_notify.dmp

Screen display
Import: Release 8.1.7.0.0 - Production on Thursday, February 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Derived from the conventional path by EXPORT: V08.01.07 documents have been created ZHS16GBK achieve ZHS16GBK NCHAR character set and character set import server uses UTF8 NCHAR Export Character Set (ncharset the possible conversion)
. AICHANNEL the object being imported into AICHANNEL
.. Is to import table "INNER_NOTIFY" 4 lines are ready to import the opening of constraints ...
Successful termination of import, but a warning.

Appendix Two:
Oracle does not allow direct changes in the owner table, using Export / Import can achieve this purpose.
First set up import9.par,
Then, use the following command: imp parfile = / filepath/import9.par
Example import9.par reads as follows:
FROMUSER = TGPMS
TOUSER = TGPMS2 (Note: the owner of the table FROMUSER replaced by TOUSER, FROMUSER and different users can TOUSER)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
BUFFER = 409600
file == / backup/ctgpc_20030623.dmp
log == / backup/import_20030623.log

Added:
1. Import and Export in order to add feedback = 1000 can display a process of growing to change the past "...", blinking cursor

Export Oracle database data into the basic methods and regular backup script unix 2009-07-29 21:23:34
Use Data Pump to export only the sequence of 2009-12-29 03:01:19
Oracle data between different users import and export 2009-08-17 08:10:34
Oracle EXP / IMP arguments detailed 2010-01-27 18:32:48
Oracle import and export 2009-09-17 14:27:14

CPU terror

kods.net — Mon, 30 Mar 2009 12:17:20+0000

500) this.width = 500; "border =" 0 ">
Now the production base of CPU, compared the terror, SQL tuning to have to point, and alas, some things to do, but. . . . .
Like this BLOG, to write, but had no time to write ....
Now the DBA, have been bogged down in the maintenance of the application. . . I am cold

Terror CPU 2009-03-30 10:35:18

2009/03

Copy the database using RMAN

Related Posts

oracle10g ASM transport_tablespace

Related Posts

Oracle10g ASM database table space maintenance

Related Posts

Checkpoint trigger conditions

Related Posts

oracle configuration

Related Posts

(To) oracle job application

Related Posts

ORACLE10G full version centos5 installed (the installation has passed)

ORACLE10G full version centos5 installed (the installation has passed)

Related Posts

Merge the use of (1)

Related Posts

SGA extended the principle of 32bit oracle

Related Posts

ORA-19815: WARNING: db_recovery_file_dest_size

Related Posts

How to quickly kill a disproportionate amount of resources (CPU, memory) database process

Related Posts

Oracle10g ASM maintenance of the database table space

Related Posts

Record two small test

Related Posts

RMAN created physical standby

Related Posts

Oracle database backup and recovery summary -exp/imp (Export and Import Bank and unloading equipment library)

Related Posts

The use of ORACLE in dbms_stats

Related Posts

checking network configuration requirements

Related Posts

Oracle deadlock related

Related Posts

Oracle Data Import Export imp / exp command - to

Related Posts

CPU terror

Related Posts