2009/05

insert all

kods.net — Wed, 27 May 2009 22:05:02+0000

9i increased from the beginning insert all, the data can be inserted into multiple tables at the same time, this can greatly reduce the disk IO, as well as the length of the code.
rn

ADMIN @ orcl> create table inser_test_all (a varchar2 (20), b number);
rn
Table created.
rn
ADMIN @ orcl> begin
2 for i in 1 .. 10 loop
3 insert into inser_test_all values ( 'a' | | i, i);
4 end loop;
5 end;
6 /
rn
PL / SQL procedure successfully completed.
ADMIN @ orcl> create table inser_test_when1 (a varchar2 (20));
rn
Table created.
rn
ADMIN @ orcl> create table inser_test_when2 (b number);
rn
Table created.
rn
ADMIN @ orcl> create table inser_test (a varchar2 (20), b number);
rn
Table created.
rn
ADMIN @ orcl> insert all
2 into inser_test values ( 'number', b)
3 into inser_test values (a, 888)
4 selecdt a, b from inser_test_all;
selecdt a, b from inser_test_all
*
ERROR at line 4:
ORA-00928: missing SELECT keyword
rn

ADMIN @ orcl> ed
Wrote file afiedt.buf
rn
1 insert all
2 into inser_test values ( 'number', b)
3 into inser_test values (a, 888)
4 * select a, b from inser_test_all
ADMIN @ orcl> /
rn
20 rows created.
rn
ADMIN @ orcl> select * from inser_test;
rn
AB
-------------------- ----------
number 1
number 2
number 3
number 4
number 5
number 6
number 7
number 8
number 9
number 10
a1 888
rn
AB
-------------------- ----------
a2 888
a3 888
a4 888
a5 888
a6 888
a7 888
a8 888
a9 888
a10 888
rn
20 rows selected.
rn
ADMIN @ orcl>

rn
In fact, there can also be inserted into a different form, respectively. There is no insert all conditions
rn
Look at the following conditional insert all
rn

rn
> ed
Wrote file afiedt.buf
rn
1 insert all
2 when b> = 1 and b <= 5 then into inser_test_when1 values (a)
3 when b> = 6 and b <= 10 then into inser_test_when2 values (b)
4 * select a, b from inser_test_all
ADMIN @ orcl> /
rn
10 rows created.
rn

ADMIN @ orcl> select * from inser_test_when1;
rn
A
--------------------
a1
a2
a3
a4
a5
rn

ADMIN @ orcl> select * from inser_test_when2;
rn
B
----------
6
7
8
9
10
rn

rn

rn

Oracle deadlock related 2009-03-30 16:46:54
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
use sqlldr 1 Introduction 2009-03-27 19:39:01
An example of the use of TKPROF 2009-02-26 01:38:19

oracle order of 2 commonly used

kods.net — Sun, 24 May 2009 19:55:20+0000

5. Rights Management (DCL) statements

1.GRANT giving permission
Commonly used in the collection system has the following three rights:
CONNECT (basic connection), RESOURCE (Programming), DBA (database management)
Commonly used data objects have the following five rights:
ALL ON data object name, SELECT ON data object name, UPDATE ON data object name,
DELETE ON object name data, INSERT ON data object name, ALTER ON data object name

GRANT CONNECT, RESOURCE TO username;
GRANT SELECT ON table TO user name;
GRANT SELECT, INSERT, DELETE ON table TO user name 1, User Name 2;

2.REVOKE recovery authority

REVOKE CONNECT, RESOURCE FROM username;
REVOKE SELECT ON table FROM user name;
REVOKE SELECT, INSERT, DELETE ON table FROM user name 1, User Name 2;

Query the database Error No. 63:
select orgaddr, destaddr from sm_histable0116 where error_code ='63 ';

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

Query the database in the sum of a variety of error codes:
select error_code, count (*) from sm_histable0513 group by error_code order
by error_code;

If the database query statements for a single type of query statistics.
select sum (Successcount) from tbl_MiddleMt0411 where ServiceType2 = 111
select sum (successcount), servicetype from tbl_middlemt0411 group by servicetype

Original Address: http://www.cnoug.org/viewthread.php?tid=60293
/ / Create a control file to trace file command
alter database backup controlfile to trace;

/ / Add a new log file group statement
connect internal as sysdba
alter database
add logfile group 4
( '/ db01/oracle/CC1/log_ 1c . dbf ',
'/ db02/oracle/CC1/log_ 2c . dbf ') size 5M ;

alter database
add logfile member '/ db03/oracle/CC1/log_ 3c . dbf '
to group 4;
/ / MOUNT in the Server Manager and open a database:
connect internal as sysdba
startup mount ORA1 exclusive;
alter database open;

/ / Generate data dictionary
@ catalog
@ catproc

/ / In the init.ora of the location of backup database
log_archive_dest_1 = '/ db00/arch'
log_archive_dest_state_1 = enable
log_archive_dest_2 = "service = stby.world mandatory reopen = 60"
log_archive_dest_state_2 = enable
/ / The table to the user of the designation and management of space-related statements
create user USERNAME identified by PASSWORD
default tablespace TABLESPACE_NAME;
alter user USERNAME default tablespace TABLESPACE_NAME;
alter user SYSTEM quota 0 on SYSTEM;
alter user SYSTEM quota 50M on TOOLS;
create user USERNAME identified by PASSWORD
default tablespace DATA
temporary tablespace TEMP;
alter user USERNAME temporary tablespace TEMP;

/ / Re-designation of a data file size:
alter database
datafile '/ db05/oracle/CC1/data01.dbf' resize 200M ;

/ / Create an automatic extension of data files:
create tablespace DATA
datafile '/ db05/oracle/CC1/data01.dbf' size 200M
autoextend ON
next 10M
maxsize 250M ;

/ / In the table space automatically extended for an additional data file:
alter tablespace DATA
add datafile '/ db05/oracle/CC1/data02.dbf'
size 50M
autoextend ON
maxsize 300M ;

/ / Modify parameters:
alter database
datafile '/ db05/oracle/CC1/data01.dbf'
autoextend ON
maxsize 300M ;

/ / In the data files during the rename:
alter database rename file
'/ db01/oracle/CC1/data01.dbf' to
'/ db02/oracle/CC1/data01.dbf';

alter tablespace DATA rename datafile
'/ db01/oracle/CC1/data01.dbf' to
'/ db02/oracle/CC1/data01.dbf';

alter database rename file
'/ db05/oracle/CC1/redo01CC1.dbf' to
'/ db02/oracle/CC1/redo01CC1.dbf';

alter database datafile '/ db05/oracle/CC1/data01.dbf'
resize 80M ;

/ / Create and use the role:
create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
grant APPLICATION_USER to username;

/ / Rollback segment management
create rollback segment SEGMENT_NAME
tablespace RBS;

alter rollback segment SEGMENT_NAME offline;

drop rollback segment SEGMENT_NAME;

alter rollback segment SEGMENT_NAME online;
/ / Rollback segment on the specified services
commit;
set transaction use rollback segment ROLL_BATCH;
insert into TABLE_NAME
select * from DATA_LOAD_TABLE;
commit;

/ / Query the size of rollback segment and optimize the parameters
select * from DBA_SEGMENTS
where Segment_Type = 'ROLLBACK';
select N. Name, / * rollback segment name * /
S. OptSize / * rollback segment OPTIMAL size * /
from V $ ROLLNAME N, V $ ROLLSTAT S
where N. USN = S. USN;

/ / Recovery rollback segment
alter rollback segment R1 shrink to 15M ;
alter rollback segment R1 shrink;

/ / Example
set transaction use rollback segment SEGMENT_NAME

alter tablespace RBS
default storage
(initial 125K next 125K minextents 18 maxextents 249)

create rollback segment R4 tablespace RBS
storage (optimal 2250K);
alter rollback segment R4 online;

select Sessions_Highwater from V $ LICENSE;
grant select on EMPLOYEE to PUBLIC;

/ / Users and the role of
create role ACCOUNT_CREATOR;
grant CREATE SESSION, CREATE USER, ALTER USER
to ACCOUNT_CREATOR;

alter user THUMPER default role NONE;
alter user THUMPER default role CONNECT;
alter user THUMPER default role all except ACCOUNT_CREATOR;

alter profile DEFAULT
limit idle_time 60;

create profile LIMITED_PROFILE limit
FAILED_LOGIN_ATTEMPTS 5;
create user JANE identified by EYRE
profile LIMITED_PROFILE;
grant CREATE SESSION to JANE;

alter user JANE account unlock;
alter user JANE account lock;

alter profile LIMITED_PROFILE limit
PASSWORD_LIFE_TIME 30;

alter user jane password expire;

/ / Create the operating system user
REM Creating OPS $ accounts
create user OPS $ FARMER
identified by SOME_PASSWORD
default tablespace USERS
temporary tablespace TEMP;

REM Using identified externally
create user OPS $ FARMER
identified externally
default tablespace USERS
temporary tablespace TEMP;

/ / Implementation of ORAPWD
ORAPWD FILE = filename PASSWORD = password ENTRIES = max_users

create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
create role DATA_ENTRY_CLERK;
grant select, insert on THUMPER.EMPLOYEE to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.TIME_CARDS to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.DEPARTMENT to DATA_ENTRY_CLERK;
grant APPLICATION_USER to DATA_ENTRY_CLERK;
grant DATA_ENTRY_CLERK to MCGREGOR;
grant DATA_ENTRY_CLERK to BPOTTER with admin option;

/ / Set role
set role DATA_ENTRY_CLERK;
set role NONE;

/ / Recovery of the right to:
revoke delete on EMPLOYEE from PETER;
revoke all on EMPLOYEE from MCGREGOR;

/ / Recovery of the role:
revoke ACCOUNT_CREATOR from HELPDESK;

drop user USERNAME cascade;

grant SELECT on EMPLOYEE to MCGREGOR with grant option;
grant SELECT on THUMPER.EMPLOYEE to BPOTTER with grant option;
revoke SELECT on EMPLOYEE from MCGREGOR;

create user MCGREGOR identified by VALUES ' 1A 2DD3CCEE354DFA ';

alter user OPS $ FARMER identified by VALUES 'no way';

/ / Backup and recovery procedures for the use of export
exp system / manager file = expdat.dmp compress = Y owner = (HR, THUMPER)
exp system / manager file = hr.dmp owner = HR indexes = Y compress = Y
imp system / manager file = hr.dmp full = Y buffer = 64000 commit = Y

/ / Back-up table
exp system / manager FILE = expdat.dmp TABLES = (Thumper.SALES)
/ / Backup partition
exp system / manager FILE = expdat.dmp TABLES = (Thumper.SALES: Part1)

/ / Input example
imp system / manager file = expdat.dmp
imp system / manager file = expdat.dmp buffer = 64000 commit = Y

exp system / manager file = thumper.dat owner = thumper grants = N
indexes = Y compress = Y rows = Y
imp system / manager file = thumper.dat FROMUSER = thumper TOUSER = flower
rows = Y indexes = Y
imp system / manager file = expdat.dmp full = Y commit = Y buffer = 64000
imp system / manager file = expdat.dmp ignore = N rows = N commit = Y buffer = 64000

/ / Use the operating system backup command
REM TAR examples
tar-cvf / dev/rmt/0hc / db0 [1-9] / oracle/CC1
tar-rvf / dev/rmt/0hc / orasw/app/oracle/CC1/pfile/initcc1.ora
tar-rvf / dev/rmt/0hc / db0 [1-9] / oracle/CC1 / orasw/app/oracle/CC1/pfile/initcc1.ora

/ / Offline backup shell script
ORACLE_SID = cc1; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
. Oraenv
svrmgrl < connect internal as sysdba
shutdown immediate;
exit
EOF1
insert backup commands like the "tar" commands here
svrmgrl < connect internal as sysdba
startup
EOF2

/ / Set up in Server Manager to archivelog mode:
connect internal as sysdba
startup mount cc1;
alter database archivelog;
archive log start;
alter database open;

/ / Set up in Server Manager to archivelog mode:
connect internal as sysdba
startup mount cc1;
alter database noarchivelog;
alter database open;

select Name,
Value
from V $ PARAMETER
where Name like 'log_archive%';

/ / Online backup script
#
# Sample Hot Backup Script for a UNIX File System database
#
# Set up environment variables:
ORACLE_SID = cc1; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
. Oraenv
svrmgrl < connect internal as sysdba
REM
SYSTEM tablespace backup REM
REM
alter tablespace SYSTEM begin backup;
! tar-cvf / dev/rmt/0hc / db01/oracle/CC1/sys01.dbf
alter tablespace SYSTEM end backup;
REM
REM The SYSTEM tablespace has now been written to a
REM tar saveset on the tape device / dev/rmt/0hc. The
REM rest of the tars must use the "-rvf" clause to append
REM to that saveset.
REM
RBS tablespace backup REM
REM
alter tablespace RBS begin backup;
! tar-rvf / dev/rmt/0hc / db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM
DATA tablespace backup REM
REM For the purposes of this example, this tablespace
REM will contain two files, data01.dbf and data02.dbf.
REM The * wildcard will be used in the filename.
REM
alter tablespace DATA begin backup;
! tar-rvf / dev/rmt/0hc / db03/oracle/CC1/data0 *. dbf
alter tablespace DATA end backup;
REM
INDEXES tablespace backup REM
REM
alter tablespace INDEXES begin backup;
! tar-rvf / dev/rmt/0hc / db04/oracle/CC1/indexes01.dbf
alter tablespace INDEXES end backup;
REM
TEMP tablespace backup REM
REM
alter tablespace TEMP begin backup;
! tar-rvf / dev/rmt/0hc / db05/oracle/CC1/temp01.dbf
alter tablespace TEMP end backup;
REM
REM Follow the same pattern to back up the rest
REM of the tablespaces.
REM
REM
REM Step 2. Backup archive log files.
archive log stop
REM
REM Exit Server Manager, using the indicator set earlier.
exit
EOFarch1
#
# Record which files are in the destination directory.
# Do this by setting an environment variable that is
# Equal to the directory listing for the destination
# Directory.
# For this example, the log_archive_dest is
# / Db01/oracle/arch/CC1.
#
FILES = `ls / db01/oracle/arch/CC1/arch *. dbf`; export FILES
#
# Now go back into Server Manager and restart the
# Archiving process. Set an indicator (called EOFarch2
# In this example).
#
svrmgrl < connect internal
archive log start;
exit
EOFarch2
#
# Now back up the archived redo logs to the tape
# Device via the "tar" command, then delete them
# From the destination device via the "rm" command.
# You may choose to compress them instead.
#
tar-rvf / dev/rmt/0hc $ FILES
rm-f $ FILES
#
# Step 3. Backup control file to disk.
#
svrmgrl < connect internal
alter database backup controlfile to
'db01/oracle/CC1/CC1controlfile.bck';
exit
EOFarch3
#
# Backup control file to tape.
#
tar-rvf / dev/rmt/0hc / db01/oracle/CC1/CC1controlfile.bck
#
# End of hot backup script.
/ / Automatically generated script to start the backup
set pagesize 0 feedback off
select
'alter tablespace' | | Tablespace_Name | | 'begin backup;'
from DBA_TABLESPACES
where Status <> 'INVALID'
spool alter_begin.sql
/
spool off

/ / Automatically generated script to back up the end of
set pagesize 0 feedback off
select
'alter tablespace' | | Tablespace_Name | | 'end backup;'
from DBA_TABLESPACES
where Status <> 'INVALID'
spool alter_end.sql
/
spool off

/ / Backup script log file archiving.
REM See text for alternatives.
# Step 1: Stop the archiving process. This will keep
# Additional archived redo log files from being written
# To the destination directory during this process.
#
svrmgrl < connect internal as sysdba
archive log stop;
REM
REM Exit Server Manager using the indicator set earlier.
exit
EOFarch1
#
# Step 2: Record which files are in the destination
# Directory.

# Do this by setting an environment variable that is
# Equal to the directory listing for the destination
# Directory.
# For this example, the log_archive_dest is
# / Db01/oracle/arch/CC1.
#
FILES = `ls / db01/oracle/arch/CC1/arch *. dbf`; export FILES
#
# Step 3: Go back into Server Manager and restart the
# Archiving process. Set an indicator (called EOFarch2
# In this example).
#
svrmgrl < connect internal as sysdba
archive log start;
exit
EOFarch2
#
# Step 4. Back up the archived redo logs to the tape
# Device via the "tar" command, then delete them
# From the destination device via the "rm" command.
#
tar-rvf / dev/rmt/0hc $ FILES
#
# Step 5. Delete those files from the destination directory.
#
rm-f $ FILES
#
# End of archived redo log file backup script.

REM disk-to-disk backup
REM
REM Back up the RBS tablespace - to another disk (UNIX)
REM
alter tablespace RBS begin backup;
! cp / db02/oracle/CC1/rbs01.dbf / db10/oracle/CC1/backups
alter tablespace RBS end backup;
REM

REM mobile shell archive script log file
#
# Procedure for moving archived redo logs to another device
#
svrmgrl < connect internal as sysdba
archive log stop;
! mv / db01/oracle/arch/CC1 / db10/oracle/arch/CC1
archive log start;
exit
EOFarch2
#
# End of archived redo log directory move.

/ / Generate command to create control file
alter database backup controlfile to trace;

/ / Time to resume the example of
connect internal as sysdba
startup mount instance_name;
recover database until time ' 1999-08-07 : 14:40: 00 ' ;

/ / Create the restoration of Contents
rman rcvcat rman / rman @

/ / In the (UNIX) directory to create the restoration of
RMAN> create catalog tablespace rcvcat;

/ / In (NT) to create the restoration of directory
RMAN> create catalog tablespace "RCVCAT";

/ / Connect descriptor example
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))

/ / Listener.ora entry entry

/ / Listener.ora entry entry
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = loc.world)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = loc)
(ORACLE_HOME = / orasw / app / oracle / product / 8.1.5 .1)
)
)

/ / Tnsnames.ora entry
LOC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = loc)
(INSTANCE_NAME = loc)
)
)

/ / Set connection parameters (sql * net)
LOC = (DESCRIPTION =
(ADDRESS =
(COMMUNITY = TCP.HQ.COMPANY)
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))
/ / Parameter File Configuration Example
/ / Tnsnames.ora
HQ = (DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))

/ / Listener.ora
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = loc)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = loc)
(ORACLE_HOME = / orasw/app/oracle/product/8.1.5.1)
)
)

/ / Oracle8I tnsnames.ora
LOC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = loc)
(INSTANCE_NAME = loc)
)
)

/ / Use COPY database replication between
copy from
remote_username / remote_password @ service_name
to
username / password @ service_name
[append | create | insert | replace]
TABLE_NAME
using subquery;

REM COPY example
set copycommit 1
set arraysize 1000
copy from HR / PUFFINSTUFF @ loc --
create EMPLOYEE --
using --
select * from EMPLOYEE

/ / Monitor the management of
lsnrctl start
lsnrctl start my_lsnr
lsnrctl status
lsnrctl status hq

Monitor the process of inspection
ps-ef | grep tnslsnr
/ / Lsnrctl stop in the Monitor
set password lsnr_password
stop

/ / Lsnrctl set out in all the services
set password lsnr_password
services
/ / Start or stop the listener a NT
net start Oracle TNSListener
net stop Oracle TNSListener

/ / Tnsnames.ora document
fld1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = server1.fld.com) (PORT = 1521))
)
(CONNECT_DATA =
(SID = fld1)
)
)
/ / Operating system, network management

telnet host_name
ping host_name
/ etc / hosts file
130.110.238.109 nmhost
130.110.238.101 txhost
130.110.238.102 azhost arizona
/ / oratab table entry
loc: / orasw/app/oracle/product/8.1.5.1: Y
cc1: / orasw/app/oracle/product/8.1.5.1: N
old: / orasw/app/oracle/product/8.1.5.0: Y

/ / Create a control file to trace file command
alter database backup controlfile to trace;

/ / Add a new log file group statement
connect internal as sysdba
alter database
add logfile group 4
( '/ db01/oracle/CC1/log_ 1c . dbf ',
'/ db02/oracle/CC1/log_ 2c . dbf ') size 5M ;

alter database
add logfile member '/ db03/oracle/CC1/log_ 3c . dbf '
to group 4;
/ / MOUNT in the Server Manager and open a database:
connect internal as sysdba
startup mount ORA1 exclusive;
alter database open;

/ / Generate data dictionary
@ catalog
@ catproc

/ / In the init.ora of the location of backup database
log_archive_dest_1 = '/ db00/arch'
log_archive_dest_state_1 = enable
log_archive_dest_2 = "service = stby.world mandatory reopen = 60"
log_archive_dest_state_2 = enable
/ / The table to the user of the designation and management of space-related statements
create user USERNAME identified by PASSWORD
default tablespace TABLESPACE_NAME;
alter user USERNAME default tablespace TABLESPACE_NAME;
alter user SYSTEM quota 0 on SYSTEM;
alter user SYSTEM quota 50M on TOOLS;
create user USERNAME identified by PASSWORD
default tablespace DATA
temporary tablespace TEMP;
alter user USERNAME temporary tablespace TEMP;

/ / Re-designation of a data file size:
alter database
datafile '/ db05/oracle/CC1/data01.dbf' resize 200M ;

/ / Create an automatic extension of data files:
create tablespace DATA
datafile '/ db05/oracle/CC1/data01.dbf' size 200M
autoextend ON
next 10M
maxsize 250M ;

/ / In the table space automatically extended for an additional data file:
alter tablespace DATA
add datafile '/ db05/oracle/CC1/data02.dbf'
size 50M
autoextend ON
maxsize 300M ;

/ / Modify parameters:
alter database
datafile '/ db05/oracle/CC1/data01.dbf'
autoextend ON
maxsize 300M ;

/ / In the data files during the rename:
alter database rename file
'/ db01/oracle/CC1/data01.dbf' to
'/ db02/oracle/CC1/data01.dbf';

alter tablespace DATA rename datafile
'/ db01/oracle/CC1/data01.dbf' to
'/ db02/oracle/CC1/data01.dbf';

alter database rename file
'/ db05/oracle/CC1/redo01CC1.dbf' to
'/ db02/oracle/CC1/redo01CC1.dbf';

alter database datafile '/ db05/oracle/CC1/data01.dbf'
resize 80M ;

/ / Create and use the role:
create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
grant APPLICATION_USER to username;

/ / Rollback segment management
create rollback segment SEGMENT_NAME
tablespace RBS;

alter rollback segment SEGMENT_NAME offline;

drop rollback segment SEGMENT_NAME;

alter rollback segment SEGMENT_NAME online;
/ / Rollback segment on the specified services
commit;
set transaction use rollback segment ROLL_BATCH;
insert into TABLE_NAME
select * from DATA_LOAD_TABLE;
commit;

/ / Query the size of rollback segment and optimize the parameters
select * from DBA_SEGMENTS
where Segment_Type = 'ROLLBACK';
select N. Name, / * rollback segment name * /
S. OptSize / * rollback segment OPTIMAL size * /
from V $ ROLLNAME N, V $ ROLLSTAT S
where N. USN = S. USN;

/ / Recovery rollback segment
alter rollback segment R1 shrink to 15M ;
alter rollback segment R1 shrink;

/ / Example
set transaction use rollback segment SEGMENT_NAME

alter tablespace RBS
default storage
(initial 125K next 125K minextents 18 maxextents 249)

create rollback segment R4 tablespace RBS
storage (optimal 2250K);
alter rollback segment R4 online;

select Sessions_Highwater from V $ LICENSE;
grant select on EMPLOYEE to PUBLIC;

/ / Users and the role of
create role ACCOUNT_CREATOR;
grant CREATE SESSION, CREATE USER, ALTER USER
to ACCOUNT_CREATOR;

alter user THUMPER default role NONE;
alter user THUMPER default role CONNECT;
alter user THUMPER default role all except ACCOUNT_CREATOR;

alter profile DEFAULT
limit idle_time 60;

create profile LIMITED_PROFILE limit
FAILED_LOGIN_ATTEMPTS 5;
create user JANE identified by EYRE
profile LIMITED_PROFILE;
grant CREATE SESSION to JANE;

alter user JANE account unlock;
alter user JANE account lock;

alter profile LIMITED_PROFILE limit
PASSWORD_LIFE_TIME 30;

alter user jane password expire;

/ / Create the operating system user
REM Creating OPS $ accounts
create user OPS $ FARMER
identified by SOME_PASSWORD
default tablespace USERS
temporary tablespace TEMP;

REM Using identified externally
create user OPS $ FARMER
identified externally
default tablespace USERS
temporary tablespace TEMP;

/ / Implementation of ORAPWD
ORAPWD FILE = filename PASSWORD = password ENTRIES = max_users

create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
create role DATA_ENTRY_CLERK;
grant select, insert on THUMPER.EMPLOYEE to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.TIME_CARDS to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.DEPARTMENT to DATA_ENTRY_CLERK;
grant APPLICATION_USER to DATA_ENTRY_CLERK;
grant DATA_ENTRY_CLERK to MCGREGOR;
grant DATA_ENTRY_CLERK to BPOTTER with admin option;

/ / Set role
set role DATA_ENTRY_CLERK;
set role NONE;

/ / Recovery of the right to:
revoke delete on EMPLOYEE from PETER;
revoke all on EMPLOYEE from MCGREGOR;

/ / Recovery of the role:
revoke ACCOUNT_CREATOR from HELPDESK;

drop user USERNAME cascade;

grant SELECT on EMPLOYEE to MCGREGOR with grant option;
grant SELECT on THUMPER.EMPLOYEE to BPOTTER with grant option;
revoke SELECT on EMPLOYEE from MCGREGOR;

create user MCGREGOR identified by VALUES ' 1A 2DD3CCEE354DFA ';

alter user OPS $ FARMER identified by VALUES 'no way';

/ / Backup and recovery procedures for the use of export
exp system / manager file = expdat.dmp compress = Y owner = (HR, THUMPER)
exp system / manager file = hr.dmp owner = HR indexes = Y compress = Y
imp system / manager file = hr.dmp full = Y buffer = 64000 commit = Y

/ / Back-up table
exp system / manager FILE = expdat.dmp TABLES = (Thumper.SALES)
/ / Backup partition
exp system / manager FILE = expdat.dmp TABLES = (Thumper.SALES: Part1)

/ / Input example
imp system / manager file = expdat.dmp
imp system / manager file = expdat.dmp buffer = 64000 commit = Y

exp system / manager file = thumper.dat owner = thumper grants = N
indexes = Y compress = Y rows = Y
imp system / manager file = thumper.dat FROMUSER = thumper TOUSER = flower
rows = Y indexes = Y
imp system / manager file = expdat.dmp full = Y commit = Y buffer = 64000
imp system / manager file = expdat.dmp ignore = N rows = N commit = Y buffer = 64000

/ / Use the operating system backup command
REM TAR examples
tar-cvf / dev/rmt/0hc / db0 [1-9] / oracle/CC1
tar-rvf / dev/rmt/0hc / orasw/app/oracle/CC1/pfile/initcc1.ora
tar-rvf / dev/rmt/0hc / db0 [1-9] / oracle/CC1 / orasw/app/oracle/CC1/pfile/initcc1.ora

/ / Offline backup shell script
ORACLE_SID = cc1; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
. Oraenv
svrmgrl < connect internal as sysdba
shutdown immediate;
exit
EOF1
insert backup commands like the "tar" commands here
svrmgrl < connect internal as sysdba
startup
EOF2

/ / Set up in Server Manager to archivelog mode:
connect internal as sysdba
startup mount cc1;
alter database archivelog;
archive log start;
alter database open;

/ / Set up in Server Manager to archivelog mode:
connect internal as sysdba
startup mount cc1;
alter database noarchivelog;
alter database open;

select Name,
Value
from V $ PARAMETER
where Name like 'log_archive%';

/ / Online backup script
#
# Sample Hot Backup Script for a UNIX File System database
#
# Set up environment variables:
ORACLE_SID = cc1; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
. Oraenv
svrmgrl < connect internal as sysdba
REM
SYSTEM tablespace backup REM
REM
alter tablespace SYSTEM begin backup;
! tar-cvf / dev/rmt/0hc / db01/oracle/CC1/sys01.dbf
alter tablespace SYSTEM end backup;
REM
REM The SYSTEM tablespace has now been written to a
REM tar saveset on the tape device / dev/rmt/0hc. The
REM rest of the tars must use the "-rvf" clause to append
REM to that saveset.
REM
RBS tablespace backup REM
REM
alter tablespace RBS begin backup;
! tar-rvf / dev/rmt/0hc / db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM
DATA tablespace backup REM
REM For the purposes of this example, this tablespace
REM will contain two files, data01.dbf and data02.dbf.
REM The * wildcard will be used in the filename.
REM
alter tablespace DATA begin backup;
! tar-rvf / dev/rmt/0hc / db03/oracle/CC1/data0 *. dbf
alter tablespace DATA end backup;
REM
INDEXES tablespace backup REM
REM
alter tablespace INDEXES begin backup;
! tar-rvf / dev/rmt/0hc / db04/oracle/CC1/indexes01.dbf
alter tablespace INDEXES end backup;
REM
TEMP tablespace backup REM
REM
alter tablespace TEMP begin backup;
! tar-rvf / dev/rmt/0hc / db05/oracle/CC1/temp01.dbf
alter tablespace TEMP end backup;
REM
REM Follow the same pattern to back up the rest
REM of the tablespaces.
REM
REM
REM Step 2. Backup archive log files.
archive log stop
REM
REM Exit Server Manager, using the indicator set earlier.
exit
EOFarch1
#
# Record which files are in the destination directory.
# Do this by setting an environment variable that is
# Equal to the directory listing for the destination
# Directory.
# For this example, the log_archive_dest is
# / Db01/oracle/arch/CC1.
#
FILES = `ls / db01/oracle/arch/CC1/arch *. dbf`; export FILES
#
# Now go back into Server Manager and restart the
# Archiving process. Set an indicator (called EOFarch2
# In this example).
#
svrmgrl < connect internal
archive log start;
exit
EOFarch2
#
# Now back up the archived redo logs to the tape
# Device via the "tar" command, then delete them
# From the destination device via the "rm" command.
# You may choose to compress them instead.
#
tar-rvf / dev/rmt/0hc $ FILES
rm-f $ FILES
#
# Step 3. Backup control file to disk.
#
svrmgrl < connect internal
alter database backup controlfile to
'db01/oracle/CC1/CC1controlfile.bck';
exit
EOFarch3
#
# Backup control file to tape.
#
tar-rvf / dev/rmt/0hc / db01/oracle/CC1/CC1controlfile.bck
#
# End of hot backup script.
/ / Automatically generated script to start the backup
set pagesize 0 feedback off
select
'alter tablespace' | | Tablespace_Name | | 'begin backup;'
from DBA_TABLESPACES
where Status <> 'INVALID'
spool alter_begin.sql
/
spool off

/ / Automatically generated script to back up the end of
set pagesize 0 feedback off
select
'alter tablespace' | | Tablespace_Name | | 'end backup;'
from DBA_TABLESPACES
where Status <> 'INVALID'
spool alter_end.sql
/
spool off

/ / Backup script log file archiving.
REM See text for alternatives.
# Step 1: Stop the archiving process. This will keep
# Additional archived redo log files from being written
# To the destination directory during this process.
#
svrmgrl < connect internal as sysdba
archive log stop;
REM
REM Exit Server Manager using the indicator set earlier.
exit
EOFarch1
#
# Step 2: Record which files are in the destination
# Directory.

# Do this by setting an environment variable that is
# Equal to the directory listing for the destination
# Directory.
# For this example, the log_archive_dest is
# / Db01/oracle/arch/CC1.
#
FILES = `ls / db01/oracle/arch/CC1/arch *. dbf`; export FILES
#
# Step 3: Go back into Server Manager and restart the
# Archiving process. Set an indicator (called EOFarch2
# In this example).
#
svrmgrl < connect internal as sysdba
archive log start;
exit
EOFarch2
#
# Step 4. Back up the archived redo logs to the tape
# Device via the "tar" command, then delete them
# From the destination device via the "rm" command.
#
tar-rvf / dev/rmt/0hc $ FILES
#
# Step 5. Delete those files from the destination directory.
#
rm-f $ FILES
#
# End of archived redo log file backup script.

REM disk-to-disk backup
REM
REM Back up the RBS tablespace - to another disk (UNIX)
REM
alter tablespace RBS begin backup;
! cp / db02/oracle/CC1/rbs01.dbf / db10/oracle/CC1/backups
alter tablespace RBS end backup;
REM

REM mobile shell archive script log file
#
# Procedure for moving archived redo logs to another device
#
svrmgrl < connect internal as sysdba
archive log stop;
! mv / db01/oracle/arch/CC1 / db10/oracle/arch/CC1
archive log start;
exit
EOFarch2
#
# End of archived redo log directory move.

/ / Generate command to create control file
alter database backup controlfile to trace;

/ / Time to resume the example of
connect internal as sysdba
startup mount instance_name;
recover database until time ' 1999-08-07 : 14:40: 00 ' ;

/ / Create the restoration of Contents
rman rcvcat rman / rman @

/ / In the (UNIX) directory to create the restoration of
RMAN> create catalog tablespace rcvcat;

/ / In (NT) to create the restoration of directory
RMAN> create catalog tablespace "RCVCAT";

/ / Connect descriptor example
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))

/ / Listener.ora entry entry

/ / Create a control file to trace file command
alter database backup controlfile to trace;

/ / Add a new log file group statement
connect internal as sysdba
alter database
add logfile group 4
( '/ db01/oracle/CC1/log_ 1c . dbf ',

'/ db02/oracle/CC1/log_ 2c . dbf ') size 5M ;

alter database
add logfile member '/ db03/oracle/CC1/log_ 3c . dbf '
to group 4;
/ / MOUNT in the Server Manager and open a database:
connect internal as sysdba
startup mount ORA1 exclusive;
alter database open;

/ / Generate data dictionary
@ catalog
@ catproc

/ / In the init.ora of the location of backup database
log_archive_dest_1 = '/ db00/arch'
log_archive_dest_state_1 = enable
log_archive_dest_2 = "service = stby.world mandatory reopen = 60"
log_archive_dest_state_2 = enable
/ / The table to the user of the designation and management of space-related statements
create user USERNAME identified by PASSWORD
default tablespace TABLESPACE_NAME;
alter user USERNAME default tablespace TABLESPACE_NAME;
alter user SYSTEM quota 0 on SYSTEM;
alter user SYSTEM quota 50M on TOOLS;
create user USERNAME identified by PASSWORD
default tablespace DATA
temporary tablespace TEMP;
alter user USERNAME temporary tablespace TEMP;

/ / Re-designation of a data file size:
alter database
datafile '/ db05/oracle/CC1/data01.dbf' resize 200M ;

/ / Create an automatic extension of data files:
create tablespace DATA
datafile '/ db05/oracle/CC1/data01.dbf' size 200M
autoextend ON
next 10M
maxsize 250M ;

/ / In the table space automatically extended for an additional data file:
alter tablespace DATA
add datafile '/ db05/oracle/CC1/data02.dbf'
size 50M
autoextend ON
maxsize 300M ;

/ / Modify parameters:
alter database
datafile '/ db05/oracle/CC1/data01.dbf'
autoextend ON
maxsize 300M ;

/ / In the data files during the rename:
alter database rename file
'/ db01/oracle/CC1/data01.dbf' to
'/ db02/oracle/CC1/data01.dbf';

alter tablespace DATA rename datafile
'/ db01/oracle/CC1/data01.dbf' to
'/ db02/oracle/CC1/data01.dbf';

alter database rename file
'/ db05/oracle/CC1/redo01CC1.dbf' to
'/ db02/oracle/CC1/redo01CC1.dbf';

alter database datafile '/ db05/oracle/CC1/data01.dbf'
resize 80M ;
/ / Create and use the role:
create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
grant APPLICATION_USER to username;

/ / Rollback segment management
create rollback segment SEGMENT_NAME

tablespace RBS;

alter rollback segment SEGMENT_NAME offline;

drop rollback segment SEGMENT_NAME;

alter rollback segment SEGMENT_NAME online;
/ / Rollback segment on the specified services
commit;
set transaction use rollback segment ROLL_BATCH;
insert into TABLE_NAME
select * from DATA_LOAD_TABLE;
commit;

/ / Query the size of rollback segment and optimize the parameters
select * from DBA_SEGMENTS
where Segment_Type = 'ROLLBACK';
select N. Name, / * rollback segment name * /
S. OptSize / * rollback segment OPTIMAL size * /
from V $ ROLLNAME N, V $ ROLLSTAT S
where N. USN = S. USN;

/ / Recovery rollback segment
alter rollback segment R1 shrink to 15M ;
alter rollback segment R1 shrink;

/ / Example
set transaction use rollback segment SEGMENT_NAME

alter tablespace RBS
default storage
(initial 125K next 125K minextents 18 maxextents 249)

create rollback segment R4 tablespace RBS
storage (optimal 2250K);
alter rollback segment R4 online;

select Sessions_Highwater from V $ LICENSE;
grant select on EMPLOYEE to PUBLIC;

/ / Users and the role of
create role ACCOUNT_CREATOR;
grant CREATE SESSION, CREATE USER, ALTER USER
to ACCOUNT_CREATOR;

alter user THUMPER default role NONE;
alter user THUMPER default role CONNECT;
alter user THUMPER default role all except ACCOUNT_CREATOR;

alter profile DEFAULT
limit idle_time 60;

create profile LIMITED_PROFILE limit
FAILED_LOGIN_ATTEMPTS 5;
create user JANE identified by EYRE
profile LIMITED_PROFILE;
grant CREATE SESSION to JANE;

alter user JANE account unlock;
alter user JANE account lock;

alter profile LIMITED_PROFILE limit
PASSWORD_LIFE_TIME 30;

alter user jane password expire;

/ / Create the operating system user
REM Creating OPS $ accounts
create user OPS $ FARMER
identified by SOME_PASSWORD
default tablespace USERS
temporary tablespace TEMP;

REM Using identified externally

create user OPS $ FARMER
identified externally
default tablespace USERS
temporary tablespace TEMP;

/ / Implementation of ORAPWD
ORAPWD FILE = filename PASSWORD = password ENTRIES = max_users

create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
create role DATA_ENTRY_CLERK;
grant select, insert on THUMPER.EMPLOYEE to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.TIME_CARDS to DATA_ENTRY_CLERK;
grant select, insert on THUMPER.DEPARTMENT to DATA_ENTRY_CLERK;
grant APPLICATION_USER to DATA_ENTRY_CLERK;
grant DATA_ENTRY_CLERK to MCGREGOR;
grant DATA_ENTRY_CLERK to BPOTTER with admin option;

/ / Set role
set role DATA_ENTRY_CLERK;
set role NONE;

/ / Recovery of the right to:
revoke delete on EMPLOYEE from PETER;
revoke all on EMPLOYEE from MCGREGOR;

/ / Recovery of the role:
revoke ACCOUNT_CREATOR from HELPDESK;

drop user USERNAME cascade;

grant SELECT on EMPLOYEE to MCGREGOR with grant option;
grant SELECT on THUMPER.EMPLOYEE to BPOTTER with grant option;
revoke SELECT on EMPLOYEE from MCGREGOR;

create user MCGREGOR identified by VALUES ' 1A 2DD3CCEE354DFA ';

alter user OPS $ FARMER identified by VALUES 'no way';

/ / Backup and recovery procedures for the use of export
exp system / manager file = expdat.dmp compress = Y owner = (HR, THUMPER)
exp system / manager file = hr.dmp owner = HR indexes = Y compress = Y
imp system / manager file = hr.dmp full = Y buffer = 64000 commit = Y

/ / Back-up table
exp system / manager FILE = expdat.dmp TABLES = (Thumper.SALES)
/ / Backup partition
exp system / manager FILE = expdat.dmp TABLES = (Thumper.SALES: Part1)

/ / Input example
imp system / manager file = expdat.dmp
imp system / manager file = expdat.dmp buffer = 64000 commit = Y

exp system / manager file = thumper.dat owner = thumper grants = N
indexes = Y compress = Y rows = Y
imp system / manager file = thumper.dat FROMUSER = thumper TOUSER = flower
rows = Y indexes = Y
imp system / manager file = expdat.dmp full = Y commit = Y buffer = 64000
imp system / manager file = expdat.dmp ignore = N rows = N commit = Y buffer = 64000

/ / Use the operating system backup command
REM TAR examples

tar-cvf / dev/rmt/0hc / db0 [1-9] / oracle/CC1
tar-rvf / dev/rmt/0hc / orasw/app/oracle/CC1/pfile/initcc1.ora
tar-rvf / dev/rmt/0hc / db0 [1-9] / oracle/CC1 / orasw/app/oracle/CC1/pfile/initcc1.ora

/ / Offline backup shell script
ORACLE_SID = cc1; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
. Oraenv
svrmgrl < connect internal as sysdba
shutdown immediate;
exit
EOF1
insert backup commands like the "tar" commands here
svrmgrl < connect internal as sysdba
startup
EOF2

/ / Set up in Server Manager to archivelog mode:
connect internal as sysdba
startup mount cc1;
alter database archivelog;
archive log start;
alter database open;

/ / Set up in Server Manager to archivelog mode:
connect internal as sysdba
startup mount cc1;
alter database noarchivelog;
alter database open;

select Name,
Value
from V $ PARAMETER
where Name like 'log_archive%';

/ / Online backup script
#
# Sample Hot Backup Script for a UNIX File System database
#
# Set up environment variables:
ORACLE_SID = cc1; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
. Oraenv
svrmgrl < connect internal as sysdba
REM
SYSTEM tablespace backup REM
REM
alter tablespace SYSTEM begin backup;
! tar-cvf / dev/rmt/0hc / db01/oracle/CC1/sys01.dbf
alter tablespace SYSTEM end backup;
REM
REM The SYSTEM tablespace has now been written to a
REM tar saveset on the tape device / dev/rmt/0hc. The
REM rest of the tars must use the "-rvf" clause to append
REM to that saveset.
REM
RBS tablespace backup REM
REM
alter tablespace RBS begin backup;
! tar-rvf / dev/rmt/0hc / db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM
DATA tablespace backup REM

REM For the purposes of this example, this tablespace
REM will contain two files, data01.dbf and data02.dbf.
REM The * wildcard will be used in the filename.

REM
alter tablespace DATA begin backup;
! tar-rvf / dev/rmt/0hc / db03/oracle/CC1/data0 *. dbf
alter tablespace DATA end backup;
REM
INDEXES tablespace backup REM
REM
alter tablespace INDEXES begin backup;
! tar-rvf / dev/rmt/0hc / db04/oracle/CC1/indexes01.dbf
alter tablespace INDEXES end backup;
REM
TEMP tablespace backup REM
REM
alter tablespace TEMP begin backup;
! tar-rvf / dev/rmt/0hc / db05/oracle/CC1/temp01.dbf
alter tablespace TEMP end backup;
REM
REM Follow the same pattern to back up the rest
REM of the tablespaces.
REM
REM
REM Step 2. Backup archive log files.
archive log stop
REM
REM Exit Server Manager, using the indicator set earlier.
exit
EOFarch1
#
# Record which files are in the destination directory.
# Do this by setting an environment variable that is
# Equal to the directory listing for the destination
# Directory.
# For this example, the log_archive_dest is
# / Db01/oracle/arch/CC1.
#
FILES = `ls / db01/oracle/arch/CC1/arch *. dbf`; export FILES
#
# Now go back into Server Manager and restart the
# Archiving process. Set an indicator (called EOFarch2
# In this example).
#
svrmgrl < connect internal
archive log start;
exit
EOFarch2
#
# Now back up the archived redo logs to the tape
# Device via the "tar" command, then delete them
# From the destination device via the "rm" command.
# You may choose to compress them instead.
#
tar-rvf / dev/rmt/0hc $ FILES
rm-f $ FILES
#
# Step 3. Backup control file to disk.
#
svrmgrl < connect internal
alter database backup controlfile to
'db01/oracle/CC1/CC1controlfile.bck';

exit
EOFarch3
#
# Backup control file to tape.
#
tar-rvf / dev/rmt/0hc / db01/oracle/CC1/CC1controlfile.bck
#
# End of hot backup script.
/ / Automatically generated script to start the backup
set pagesize 0 feedback off
select
'alter tablespace' | | Tablespace_Name | | 'begin backup;'
from DBA_TABLESPACES
where Status <> 'INVALID'
spool alter_begin.sql
/
spool off

/ / Automatically generated script to back up the end of
set pagesize 0 feedback off
select
'alter tablespace' | | Tablespace_Name | | 'end backup;'
from DBA_TABLESPACES
where Status <> 'INVALID'
spool alter_end.sql
/
spool off

/ / Backup script log file archiving.
REM See text for alternatives.
# Step 1: Stop the archiving process. This will keep
# Additional archived redo log files from being written
# To the destination directory during this process.
#
svrmgrl < connect internal as sysdba
archive log stop;
REM
REM Exit Server Manager using the indicator set earlier.
exit
EOFarch1
#
# Step 2: Record which files are in the destination
# Directory.
# Do this by setting an environment variable that is
# Equal to the directory listing for the destination
# Directory.
# For this example, the log_archive_dest is
# / Db01/oracle/arch/CC1.
#
FILES = `ls / db01/oracle/arch/CC1/arch *. dbf`; export FILES
#
# Step 3: Go back into Server Manager and restart the
# Archiving process. Set an indicator (called EOFarch2
# In this example).
#
svrmgrl < connect internal as sysdba
archive log start;
exit
EOFarch2
#
# Step 4. Back up the archived redo logs to the tape
# Device via the "tar" command, then delete them

# From the destination device via the "rm" command.
#
tar-rvf / dev/rmt/0hc $ FILES
#
# Step 5. Delete those files from the destination directory.
#
rm-f $ FILES
#
# End of archived redo log file backup script.

REM disk-to-disk backup
REM
REM Back up the RBS tablespace - to another disk (UNIX)
REM
alter tablespace RBS begin backup;
! cp / db02/oracle/CC1/rbs01.dbf / db10/oracle/CC1/backups
alter tablespace RBS end backup;
REM

REM mobile shell archive script log file
#
# Procedure for moving archived redo logs to another device
#
svrmgrl < connect internal as sysdba
archive log stop;
! mv / db01/oracle/arch/CC1 / db10/oracle/arch/CC1
archive log start;
exit
EOFarch2
#
# End of archived redo log directory move.

/ / Generate command to create control file
alter database backup controlfile to trace;

/ / Time to resume the example of
connect internal as sysdba
startup mount instance_name;
recover database until time ' 1999-08-07 : 14:40: 00 ' ;

/ / Create the restoration of Contents
rman rcvcat rman / rman @

/ / In the (UNIX) directory to create the restoration of
RMAN> create catalog tablespace rcvcat;

/ / In (NT) to create the restoration of directory
RMAN> create catalog tablespace "RCVCAT";

/ / Connect descriptor example
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))

/ / Listener.ora entry entry
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
Posted on the net school students http://

(KEY = loc.world)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =

(SID_NAME = loc)
(ORACLE_HOME = / orasw / app / oracle / product / 8.1.5 .1)
)
)

/ / Tnsnames.ora entry
LOC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = loc)
(INSTANCE_NAME = loc)
)
)

/ / Set connection parameters (sql * net)
LOC = (DESCRIPTION =
(ADDRESS =
(COMMUNITY = TCP.HQ.COMPANY)
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))
/ / Parameter File Configuration Example
/ / Tnsnames.ora
HQ = (DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT DATA =
(SID = loc)))

/ / Listener.ora
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = loc)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = loc)
(ORACLE_HOME = / orasw/app/oracle/product/8.1.5.1)
)
)

/ / Oracle8I tnsnames.ora
LOC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)

(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = loc)
(INSTANCE_NAME = loc)
)
)

/ / Use COPY database replication between
copy from
remote_username / remote_password @ service_name
to
username / password @ service_name
[append | create | insert | replace]
TABLE_NAME
using subquery;

REM COPY example
set copycommit 1
set arraysize 1000
copy from HR / PUFFINSTUFF @ loc --
create EMPLOYEE --
using --
select * from EMPLOYEE

/ / Monitor the management of
lsnrctl start
lsnrctl start my_lsnr
lsnrctl status
lsnrctl status hq

Monitor the process of inspection
ps-ef | grep tnslsnr
/ / Lsnrctl stop in the Monitor
set password lsnr_password
stop

/ / Lsnrctl set out in all the services
set password lsnr_password
services
/ / Start or stop the listener a NT
net start Oracle TNSListener
net stop Oracle TNSListener

/ / Tnsnames.ora document
fld1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = server1.fld.com) (PORT = 1521))
)
(CONNECT_DATA =
(SID = fld1)
)
)
/ / Operating system, network management

telnet host_name
ping host_name
/ etc / hosts file
130.110.238.109 nmhost
130.110.238.101 txhost
130.110.238.102 azhost arizona
/ / oratab table entry
loc: / orasw / app / oracle / product / 8.1.5 .1: Y
cc1: / orasw/app/oracle/product/8.1.5.1: N
old: / orasw/app/oracle/product/8.1.5.0: Y

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

oracle commands commonly used 2

kods.net — Sun, 24 May 2009 17:45:55+0000

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

Database Backup Program

kods.net — Sun, 24 May 2009 15:38:01+0000

A foreword

As there will always be unfair to the community, people will always make mistakes, the program will always be the same BUG, the database is not a problem

Could have been avoided, a good backup is the key to the success of the project application.

Independent of the hard disk from the backup file saved, host and slave synchronization (DATA GUARD) to more than a single database instance

Load balancing (RAC), in accordance with the needs of different levels of different technologies, but its simply redundant data with human and material resources to improve data availability.

Strongly recommended to back up to other physical storage devices, such as another hard drive to ensure that media failure can occur smoothly resumed.

Second, tools for backup and recovery profile

1, commonly used imp / exp tools

We often use imp / exp tools provided by Oracle for the client to export the data to the dmp file, data transfer and storage methods, is a logical backup. 11gR2 has been abandoned in the exp, reservations imp, related to the operation of mercury by the data (Data Pump) of impdp / expdp replace.

Advantages as follows:

1, Using a simple.

2, Export you can specify a logical object (the user or the entire database or table)

Shortcomings as follows:

1, Can not be fully restored (Complete Recovery)

2, Can only backup data files (Data File)

3, Treasury-wide pressure to export the database

4, Larger space

2, Recovery Manager (RMAN)

Provided by the Oracle professional tool for backup and recovery is physical backup.

Advantages as follows:

1, Can be fully recovered and not fully restored

2, Can back up all database files (control files, server parameter files, archived log files, data files)

3, Control with powerful capabilities and scripting languages

4, Support for online hot backup

5, Support multi-level incremental backups

6, Support parallel backup and restore

7, Reduce the amount of backup data (compression, block-level backup)

8, Documents can be backed up to disk or tape

9, Block-level support for the restoration of online

10, may limit the rate to reduce the backup on the impact of system

11, and so on. . .

Three configuration RMAN

1, configuration archiving (ARCHIVELOG) mode

Database load (MOUNT) mode to open the following archive mode

SQL> ALTER DATABASE ARCHIVELOG;

Placed in the database archiving mode to prevent the redo log before being covered in the archive.

If in a non-filing (NOARCHIVELOG) mode, the database can only be restored to the last backup time, the time between the current changes will be lost data.

2, configure automatic backup control file

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN information stored in the control file (non-CATALOG mode), if the control file is lost without a backup if the recovery of the database will cause great difficulties

3, configuration control file record keep time

SQL> ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME = 45;

RMAN backup set of information stored in the control file of time, it is necessary to retain the strategy of greater than or equal to the restoration of the window.

4, configuration of the restoration of the window retention policies

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

The backup file over the date marked as RMAN will not (OBSOLETE):

RMAN> REPORT OBSOLETE;

Database file over the date of the report need backup RMAN:

RMAN> REPORT NEED BACKUP;

5, set the default save path of the backup file

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D: / BACKUP /% U'

6, the opening of fast incremental backups

In 9i in, RMAN incremental backup of all data files to be scanned to find out there is a change in the data block, and then back up.

At 10g , Add new features --- a block change tracking (Block Change Tracking), will change the data block recorded in a follow-up paper, so that when the incremental backups to avoid a full scan on the database.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'D: / BACKUP / BLOCK_CHANGE_TRACKING.TRC';

7, configure RMAN to optimize the use of (OPTIMIZATION) mode

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

RMAN optimization model to open automatically skip the document to meet certain conditions, the experiment shows that the optimization model can improve the performance of 10%

8, configure RMAN to back up the use of compression set

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

At 10g Increase in RMAN --- characteristics of a new backup set compression, the backup set can be reduced to 1 / 5 or 1 / 6, the price is about 10% increase in CPU consumption, you can choose to use according to the actual situation.

If conditions permit (multiple physical hard disk, multi-CPU) can also be set up in parallel with the above command to back up

Four multi-stage backup strategy

Backup is a multi-level recovery in order to reduce the time required to back up every day and reduce the time required for the system and have a good recovery. However, recovery time and backup time to have a trade-off. For example, as long as the beginning of a full backup and backup of all the documents in the archives have to ensure that the database can be restored to the latest state, but generally not so in fact carried out (because the recovery will require very, very long time) , multi-level backup is to solve this problem. The following is a proposed program.

Zero-order once a month to back up

Each week a level incremental backups

Make a difference every day a backup

When necessary (such as less than 24 hours is necessary to archive file system is full) backup archive

1, zero-level backup script

RMAN> RUN (

ALLOCATE CHANNEL 'DEV1' TYPE DISK;

ALLOCATE CHANNEL 'DEV2' TYPE DISK;

ALLOCATE CHANNEL 'DEV3' TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 TAG 'LV0' FORMAT 'D: / ORACLE / BACKUP /% U' DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

DELETE NOPROMPT OBSOLETE;

RELEASE CHANNEL DEV1;

RELEASE CHANNEL DEV2;

RELEASE CHANNEL DEV3;

)

2, an incremental backup script

RMAN> RUN (

ALLOCATE CHANNEL 'DEV1' TYPE DISK;

ALLOCATE CHANNEL 'DEV2' TYPE DISK;

ALLOCATE CHANNEL 'DEV3' TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE TAG 'LV 1C 'FORMAT' D: / ORACLE / BACKUP /% U 'DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

RELEASE CHANNEL DEV1;

RELEASE CHANNEL DEV2;

RELEASE CHANNEL DEV3;

)

3, a differential backup script

RMAN> RUN (

ALLOCATE CHANNEL 'DEV1' TYPE DISK;

ALLOCATE CHANNEL 'DEV2' TYPE DISK;

ALLOCATE CHANNEL 'DEV3' TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 TAG 'LV1D' FORMAT 'D: / ORACLE / BACKUP /% U' DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

RELEASE CHANNEL DEV1;

RELEASE CHANNEL DEV2;

RELEASE CHANNEL DEV3;

)

4, archiving file backup script

RMAN> RUN (

ALLOCATE CHANNEL 'DEV1' TYPE DISK;

ALLOCATE CHANNEL 'DEV2' TYPE DISK;

ALLOCATE CHANNEL 'DEV3' TYPE DISK;

BACKUP FILESPERSET 3 ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL DEV1;

RELEASE CHANNEL DEV2;

RELEASE CHANNEL DEV3;

)

Required daily amount of data to back up only changes in the volume of the day. Done to restore the most of a month as long as the restoration of zero-order three-level backup + +6 months level incremental backup differential backup + the day of the documents in the archives. If you can not accept this kind of recovery time, it can only reduce the zero-level time interval between the backup (for example, every week can be a zero-level backup, so that when the restoration of the need to restore only the amount of data a week). The system load is extremely low or even zero level for half a year to do a backup

Five management RMAN

RMAN through the REPORT, LIST, CROSSCHECK, DELETE command RMAN can manage.

1, if the manually delete the backup files, the need to implement cross-examination so that RMAN synchronization.

RMAN> CROSSCHECK BACKUP;

RMAN> CROSSCHECK ARCHIVELOG ALL;

Cross-examination would have been after the RMAN backup files manually to delete the status is set to expire (EXPIRED).

2, the report has not (OBSOLETE) and expired (EXPIRED) the backup file.

RMAN> REPORT OBSOLETE;

RMAN> LIST EXPIRED BACKUP;

3, do not delete (OBSOLETE) and expired (EXPIRED) the backup file.

RMAN> DELETE OBSOLETE;

RMAN> DELETE EXPIRED BACKUP;

Six recovery using RMAN

RMAN throughout the recovery process can be divided into restore (RESTORE) and recovery (RECOVER), their meaning is very different, one is the physical meaning of the reduction of paper and copy, a means to restore database consistency.

The recovery of the database is rather complicated, there are many different situations, we must understand the conditions of the structure of ORACLE database using different methods for processing. Here we will list only the most common recovery.

Prior to the restoration of the entire database must be in a state of the state of the database (MOUNT) state.

SQL> STARTUP MOUNT;

Then run RMAN commands to restore and recovery.

1, fully restored

1.1 and restore the entire database

RMAN>

RUN (

ALLOCATE CHANNEL 'DEV1' TYPE DISK;

ALLOCATE CHANNEL 'DEV2' TYPE DISK;

ALLOCATE CHANNEL 'DEV3' TYPE DISK;

RESTORE DATABASE;

RECOVER DATABASE;

RELEASE CHANNEL DEV1;

RELEASE CHANNEL DEV2;

RELEASE CHANNEL DEV3;

)

1.2 and restore the specified table space or data files

In the resumption of space before the table so that the table space in an offline state (OFFLINE state).

SQL> ALTER TABLESPACE TBS OFFLINE;

RMN recovery then.

RMAN>

RUN (

ALLOCATE CHANNEL 'DEV1' TYPE DISK;

ALLOCATE CHANNEL 'DEV2' TYPE DISK;

ALLOCATE CHANNEL 'DEV3' TYPE DISK;

RESTORE TABLESPACE TBS;

RECOVER TABLESPACE TBS;

RELEASE CHANNEL DEV1;

RELEASE CHANNEL DEV2;

RELEASE CHANNEL DEV3;

)

2, incomplete recovery

Incomplete recovery of more complex, broadly divided into about four categories.

1, Based on the recovery time (Time-based recovery)

2, Revocation based on the recovery (Cancel-based recovery)

3, Based on changes in the recovery (Change-based recovery)

4, Log sequence to restore (Log sequence recovery)

3, block-level recovery of

Online on the logic of RMAN can damage the data recovery block without affecting the operation of the database.

RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 3;

Seven quick resumption of the use of FLASHBACK

The following types of errors occur, they usually need to use back-up the implementation of the restoration:

• Media failure: database files due to physical problems lead to reading and writing of the document

• User error: the data in the database to change or be mistaken for误删除

For the use of RMAN media recovery, the use of user error recovery FLASHBACK.

1, 9i in FLASHBACK:

Query the database changes in the SCN

SQL> SELECT NAME, FIRST_CHANGE # FSCN, NEXT_CHANGE # NSCN, FIRST_TIME FROM V $ ARCHIVED_LOG;

To obtain the current SCN

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FSCN FROM DUAL;

The creation of the restoration of form, choose to return to the appropriate SCN

SQL> INSERT INTO TB_RECOVER SELECT * FROM TB AS OF SCN 1999999999;

2, 10g The FLASHBACK:

10g FLASHBACK to the enhanced and modified, through flashback, the user can complete it is impossible to restore many of the

, The current 10g Flashback of the following characteristics;

1, oracle falshback Database.

Characteristics allow the Flashback database through oracle statement, the database will be rolled before a point in time or

scn, the point in time without the need for the resumption of work!

2, oracle falshback table.

Characteristics allow the oracle statement through flashback table to table will be rolled before a point in time or on scn.

3, oracle falshback drop.

Characteristics allow the oracle to drop off the resumption of the table or index.

4, oracle falshback version query.

The characteristics of the table can be specified in a record period of time any changes!

5, oracle falshback transaction query

The properties can be restricted to users in a Service-level changes on the operation to check the database for diagnosis of the problem, sub -

Analysis of the performance of audit services.

Eight other high availability solutions (High Availability) Introduction

• Data Guard: to provide a comprehensive range of services that can be used to create, maintain, manage and monitor one or more standby databases

• Oracle Real Application Clusters (RAC): multiple instances from a single database access

• Oracle Streams: can spread and manage the data in the data stream, transaction processing and event

Day-to-day management of Oracle 10G RAC 2009-04-09 06:40:04
oracle database backup and restore 2009-04-03 18:35:52
Oracle9i database manually create specific steps 2009-04-02 13:49:25
RMAN created physical standby 2009-03-30 22:49:29

Unix environment explain fully optimized for Oracle database

kods.net — Sun, 24 May 2009 13:33:09+0000

Unix environment explain fully optimized for Oracle database

Today, the optimization has been to optimize the wait (waits) in transition, and the actual performance optimization in the emergence of the most fundamental points are concentrated in the IO, this is the most important aspects of performance, by the system waiting to be discovered in the Oracle database in less than , the operating system some of the irrational use of resources is a relatively good way, this article focus on the Unix environment.

First, through the operating system tools to check system status, such as CPU, memory, exchange, disk utilization, or the light of experience with the system compared to the normal state at the time of right, sometimes the system may appear to look at this may not be idle a normal state, because the CPU may be waiting for IO completion. In addition we should watch those occupied system resources (CPU, memory) process.

1, the operating system how to check whether there is the issue of IO? Tool used by sar, which is a more generic tool.

Rp1 # sar-u 2 10

That is, once every two seconds prosecution, a total of 20 times, of course, these are your decision.

Example return:

HP-UX hpn2 B.11.00 U 9000/800 08/05/03

18:26:32% usr% sys% wio% idle

Note: redhat in my view is that the results, I do not know the% system is the so-called% wio.

Linux 2.4.21 -20.ELsmp (YY075) 05/19/2005

10:36:07 AM CPU% user% nice% system% idle

10:36:09 AM all 0.00 0.00 0.13 99.87

10:36:11 AM all 0.00 0.00 0.00 100.00

10:36:13 AM all 0.25 0.00 0.25 99.49

10:36:15 AM all 0.13 0.00 0.13 99.75

10:36:17 AM all 0.00 0.00 0.00 100.00

10:36:17 AM CPU% user% nice% system% idle

10:36:19 AM all 0.00 0.00 0.00 100.00

10:36:21 AM all 0.00 0.00 0.00 100.00

10:36:23 AM all 0.00 0.00 0.00 100.00

10:36:25 AM all 0.00 0.00 0.00 100.00

% Usr which refers to the process used by the user the percentage of CPU resources,% sys was referring to the use of system resources, the percentage of CPU resources,% wio refers to the percentage of waiting for io to complete, which is worth a watch, % idle a percentage of that is idle. Wio out if a lot of value, such as more than 35% on your system IO bottlenecks, you spend a lot of CPU time to wait for IO completion. Idle system CPU busy little note. I like this example, we can see an average of 11 note wio no particular problem io, and my idle value is zero, that I have the CPU running at full capacity.

When your system is the issue of IO, you can solve from the following aspects:

* Contact the appropriate technical support to the operating system is optimized in this regard, such as hp-ux in a designated volume group of the strip and so on.

* Oracle to find the SQL statement unreasonable, their priority.

* Oracle in on frequent visits to build a reasonable addition to the index table, the table Then these sub-table so as not to visit the storage space to produce hot spots, and then there is a reasonable partition of the table.

when more than 80 percent utilization, then state your memory you should adjust, and generally have the following methods.

* Allocated to the memory Oracle will not use system memory more than the 1 / 2, the general security in the system memory for the benefit of 40%.

* Increased memory for the system.

* If you are a particularly large number of connections, you can use the MTS approach.

* Playing the whole patch to prevent the memory leak.

3, how to find the point with the Department to use resources, especially large and the implementation of Oracle's session of the statement.

Hp-unix can glance, top, IBM AIX can use topas, also can use the ps command. Through these procedures we can find points of system resources, especially a large number of these processes of the process, we can find the sql statement which the pid is running sql, the sql the best in the pl / sql developer, toad in the implementation of software to <> in your spid replaced spid it.

SELECT a.username, a.machine, a.program, a.sid, a.serial #,

a.status, c.piece, c.sql_text from v $ session a, v $ process b,

v $ sqltext c WHERE b.spid = 'ORCL' AND b.addr = a.paddr AND

a.sql_address = c.address (+) order BY c.piece

We will be able to get a look at the sql to look at whether it plans to follow the implementation of the index, its optimization to avoid scanning the entire table in order to reduce the IO wait, so as to speed up the pace of implementation of sentences.

Tip: SQL Optimization of doing, we often encounter the use of In the statement, then we must use to replace it exists, because Oracle is in dealing with In accordance with the way Or do, even if the use of the index will be very slow.

For example:

SELECT col1, col2, col3 FROM table 1 a

WHERE a.col1 not in (SELECT col1 FROM table2)

Can be replaced by:

SELECT col1, col2, col3 FROM table 1 a

WHERE not exists

(SELECT 'x' FROM table2 b

WHERE a.col1 = b.col1)

4, another useful script: Find the poor performance of the previous 10 sql.

SELECT * FROM (select PARSING_USER_ID, EXECUTIONS, SORTS,

COMMAND_TYPE, DISK_READS, sql_text FROM v $ sqlarea

order BY disk_reads DESC) where ROWNUM <10;

Second, the Oracle Server quickly found the cause of performance problems, we can resort to the view v $ session_wait to see the system, such as those in any session, the use of the number of IO. The following is provided for reference I Script:

Script Description: io accounting for a larger view of the running session.

SELECT se.sid, se.serial #, pr.SPID, se.username, se.status,

se.terminal, se.program, se.MODULE,, se.sql_address, st.event, st.

p1text, si.physical_reads,

si.block_changes FROM v $ session se, v $ session_wait st,

v $ sess_io si, v $ process pr WHERE st.sid = se.sid AND st.

sid = si.sid AND se.PADDR = pr.ADDR AND se.sid> 6 AND st.

wait_time = 0 AND st.event NOT LIKE '% SQL%' ORDER BY physical_reads DESC

To retrieve the results of a few notes:

1, I was waiting for each of the session has been scheduled in the order of physical time, because it is related to the actual IO.

2, you can determine whether it is reasonable to wait for the statement.

Select sql_address from v $ session where sid =;

Select * from v $ sqltext where address =;

The implementation of the above two statements could be the language of the session. You to use alter system kill session 'sid, serial #'; to kill the session.

3, should watch out about this event, which is the key to tuning one, following the event to do so often in order to brief Description:

a, buffer busy waits, free buffer waits by two parameters dbwr logo are the adequacy of the problems associated with large IO, v $ session_wait when the free buffer wait in the entry of little or no time on dbwr your system to use the process of decision not to adjust; free buffer wait a lot of entries, feel up your system must be very slow, then state your dbwr have enough, and it has become a wio generated a database of your performance bottleneck, this solution is as follows:

a.1 to increase the process of writing, at the same time to adjust the parameters db_block_lru_latches.

Example: to modify or add the following two parameters

db_writer_processes = 4

db_block_lru_latches = 8

a, 2 open asynchronous IO, IBM is much simpler in this respect, hp is some trouble, you can contact with Hp engineers.

b, db file sequential read, referring to the order of time, that is, full-table scan, which is that we should minimize some of the solution is to use the index, sql tuning, at the same time can increase this parameter db_file_multiblock_read_count.

c, db file scattered read, this parameter refers to read through the index, the same can be db_file_multiblock_read_count by increasing this parameter to improve performance.

d, latch free, with the bolt-related, and the need for special regulation.

e, other parameters can be inject special concept.

Optimization of other means appears to be mainly concentrated in the above SQL query, Oracle itself provides the optimizer.

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 learning some of operation (compared chaos)

kods.net — Sun, 24 May 2009 10:10:59+0000

1.windows command line to start sqlplus:
sqlplus-> user & pwd (to connect to orcal: user: system pwd: 000000)
2. See the current date: select sysdate from dual;
3. To create a user:
- method 1:
create user "username" identified by "pwd (not the head-digit)";
grant connect, resource to "username";
- method 2:
grant dba to "username" identified by "pwd";
conn "username" / "pwd";
4. Query the database name: select parameter domain;

***************************

2009-3-12
1.user: system pwd: 000000 rdb

- create a database
22: database configuration assistance. Name: rdb

2 - create a user
create user ht identified by ht;
grant create session, dba to ht;
commit;

3 - login rdb with the identification of ht
user: ht pwd: ht db: rdb

4 - create a table in rdb
create table company (
id integer primary key,
name varchar2 (50)
);

insert into company (id, name) values (1, 'ssdut');
insert into company (id, name) values (1, 'going');
commit; (********** donnot forget! *********)

5 - look the user's tables
select table_name, tablespace_name from user_tables;
---- from Internet:
select table_name from user_tables;
select table_name from all_tables;

6 - look the how many databases have?
orcale setup forder-> admin-> DB name == forder name

7 - look all users in database
select username from all_users;

8 - look the construct of a table
select column_id, column_name, data_type, data_length, data_precision,

data_scale, nullable, data_default from user_tab_columns where table_name

= 'Tablename' order by column_id;

9 - look current username:
show user

2009-3-13
1 - insert into a time (format:: timestamp 'year-month-day

hour: minute: second.millisecond)
insert into T_COMPANYINFO

(companycode, companyname, createtime, areacode) values

( 'aa','11 ', timestamp'2000-03-02 18:23:23.1234','0211 ');

2.
1.TYPE_FORWORD_ONLY, can only roll forward;
2.TYPE_SCROLL_INSENSITIVE, two-way scroll, but not up to date, that is, if the database

Modified data is not reflected in the in the ResultSet.
3.TYPE_SCROLL_SENSITIVE, two-way scrolling, and timely update of tracking databases in order to change

The data in the ResultSet.

4.ResultSet.CONCUR_READ_ONLY: This is the default value specified can not update the ResultSet
5.ResultSet.CONCUR_UPDATABLE: specified ResultSet can be updated

3.remote login the oracle
Net Configuration Assistance -> local net service name configuration -> server name: that is, the database name,

Hostname: remote host ip address
sql * plus login interface: user: admin pwd: admin host string: rdb
Log in dos command line: sqlplus -> admin @ rdb -> admin (format => used

Families: user @ database password: pwd)

2009-3-16
1.view the table structure
describe table_name;
desc table_name;
2.disconnect the database;
disconnect
relogin
connect
input your user name: admin # rdb
input your pwd: admin

3.
oracle jdbc location
C: \ Program Files \ Java \ jdk1.5.0_17 \ jre \ lib \ ext

oracle database basic use and parameters 2009-04-10 06:10:04
Oracle Database exp imp Import Export by user examples 2009-04-06 18:58:52
Detailed: database name. Instance name. ORACLE_SID. Database name 2009-04-06 07:22:20
use sqlldr 1 Introduction 2009-03-27 19:39:01
Oracle XDB relax resolve port conflict of 8080 2009-03-13 16:47:42

ORACLE common command 1

kods.net — Sun, 24 May 2009 05:39:31+0000

ORACLE common command

A, ORACLE startup and shut down
1, in the stand-alone environment
To start or shut down ORACLE system must first switch to the ORACLE user, as
su - oracle

a, start the ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> quit

b, shut down ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown
SVRMGR> quit

Oracle9i database start command:
$ Sqlplus / nolog

SQL * Plus: Release 9.2.0 .1.0 - Production on Fri Oct 31 13:53:53 2003

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup ^ C

SQL> startup
ORACLE instance started.

2, in the two-machine environment
To start or shut down ORACLE system must first switch to the root user, as
su - root

a, start the ORACLE system
hareg - y oracle

b, shut down ORACLE system
hareg - n oracle

What are the Oracle database start-up mode

Description:

There are several ways to start:
1, startup nomount
Non-installation boot, under the executable to start this way: the reconstruction of the control file and rebuild the database

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

2, startup mount dbname
Start the installation, under the executable to start this way:
Database log archiving,
Database media recovery,
So that online or offline data files,
Re-positioning data files, redo log files.

The implementation of "nomount", and then open the control file to confirm data files and online log file,
However, at this time not data files and log files to check to check.

3, startup open dbname
First implementation of "nomount", then "mount", then open the Redo log file, including all database files,
This way can access the data in the database.

4, startup, equivalent to the following three command
startup nomount
alter database mount
alter database open

5, startup restrict
Constraint start
In this way to start the database, but only allows users with certain access privileges
Non-privileged user access, we will have the following tips:
ERROR:
ORA-01035: ORACLE only with RESTRICTED SESSION allow users to use

6, startup force
Mandatory start-up mode
When the database is not closed, can be used to complete the database startup force the closure of
To close the database, and then the database command to start the implementation of the normal

7, startup pfile = parameter file name
Initialization parameter file with the start-up mode
Read parameter files first, and then set the parameter file to start the database
Cases: startup pfile = E: Oracleadminoradbpfileinit.ora

8, startup EXCLUSIVE
Second, the user how to effectively use the data dictionary
　 ORACLE database's data dictionary is an important part of one, it generated with the database created, with the database changes,
Reflected in the number of sys user tables and views. Data Dictionary of the English name is an uppercase character.

Data dictionary are user information, the user's permission information, all data object information, table constraints, statistical analysis, such as a database view.
We can not manually modify data dictionary information.

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

　　 dictionary of all data dictionary table name and explained that it has a synonym dict
dict_column 　　 All data dictionary table field names and explanations

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

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

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

SQL> select column_name, comments from dict_columns where table_name = 'USER_INDEXES';

And so on, you can easily know the name of the detailed data dictionary and explanation ORACLE not see the other documentation.

Below, by category, a number of user ORACLE data dictionary of commonly used methods of inquiry.

1, the user

Show the current user's default tablespace
SQL> select username, default_tablespace from user_users;

Show the current user's role
SQL> select * from user_role_privs;

Show the current user of the system privileges and table-level permissions
SQL> select * from user_sys_privs;
SQL> select * from user_tab_privs;

2, Table

See the table of all users
SQL> select * from user_tables;

Show the name of the table contains the log character
SQL> select object_name, object_id from user_objects
where instr (object_name, 'LOG')> 0;

See a table of the creation time
SQL> select object_name, created from user_objects where object_name = upper ( '& table_name');

See a table of size
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& table_name');

Show on the memory area ORACLE table
SQL> select table_name, cache from user_tables where instr (cache, 'Y')> 0;

3, index

View the index number and type of
SQL> select index_name, index_type, table_name from user_indexes order by table_name;

View index by the index field
SQL> select * from user_ind_columns where index_name = upper ( '& index_name');

View the index size
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& index_name');

4, serial number

Show serial number, last_number is the current value
SQL> select * from user_sequences;

5, view

See the name of view
SQL> select view_name from user_views;

See the select statement to create views
SQL> set view_name, text_length from user_views;
SQL> set long 2000; Description: text_length can view the value of setting the size of set long
SQL> select text from user_views where view_name = upper ( '& view_name');

6, synonyms

See the name synonymous with
SQL> select * from user_synonyms;

7, constraints

See a table of the Constraints
SQL> select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints where table_name = upper ( '& table_name');

SQL> select c.constraint_name, c.constraint_type, cc.column_name
from user_constraints c, user_cons_columns cc
where c.owner = upper ( '& table_owner') and c.table_name = upper ( '& table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;

8, memory function and the process of

View the status of functions and procedures
SQL> select object_name, status from user_objects where object_type = 'FUNCTION';
SQL> select object_name, status from user_objects where object_type = 'PROCEDURE';

View the function and process of source code
SQL> select text from all_source where owner = user and name = upper ( '& plsql_name');

Third, see the SQL database
1, see table space name and the size of

select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2, see table space name and the physical file size

select tablespace_name, file_id, file_name,
round (bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;

3, view the rollback segment name and size

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v $ rollstat v
Where r.segment_id = v.usn (+)
order by segment_name;

4, view the control file

select name from v $ controlfile;

5, view the log file

select member from v $ logfile;

6, view the use of table space

select sum (bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A. TABLESPACE_NAME, A. BYTES TOTAL, B. BYTES USED, C. BYTES FREE,
(B. BYTES * 100) / A. BYTES "% USED", (C. BYTES * 100) / A. BYTES "% FREE"
FROM SYS.SM $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C
WHERE A. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE_NAME;

7, view the database object library

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

8, see the database version

Select version FROM Product_component_version
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';

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

Select Created, Log_Mode, Log_Mode From V $ Database;
IV, ORACLE management of user connections

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

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

If you want to stop a connection with

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

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

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

Description: 21 is the sid of a number of connections

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

5, SQL * PLUS using
a, near to the SQL * Plus
$ sqlplus username / password

Quit SQL * Plus
SQL> exit

b, to help in the information under sqlplus
List of all SQL commands and SQL * Plus command
SQL> help
Are listed in order of a particular information
SQL> help command name

c, show the table structure DESCRIBE command
SQL> DESC table name

d, SQL * Plus commands in the Edit
Shows SQL commands buffer
SQL> L

Modify SQL command
First of all, to be correct line into the current row
SQL> n
CHANGE command changes with
SQL> c / old / new
Re-verify that the correct
SQL> L

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

e, call the external system editor
SQL> edit the file name
DEFINE command can use the system variable EDITOR to change the type of text editor in the paper are defined as follows login.sql his
DEFINE_EDITOR = vi

f, run the command file
SQL> START test
SQL> @ test

SQL * Plus statements used
a, the table to create, modify, delete
Create table orders the following format:
create table table name (column description list);

Table-based commands add a new column as follows:
ALTER TABLE table ADD (column description list)
Cases: In order to increase test Table 1 Age, used to store the age of
sql> alter table test
add (Age number (3));

Amend the definition of order-based list is as follows:
ALTER TABLE table name
MODIFY (column name data type)
Example: Count table test out the width to 10 characters long
sql> alter atble test
modify (County char (10));

b, a table will delete the form of statement is as follows:
DORP TABLE table;
For example: table delete to delete table data and table definition
sql> drop table test

c, table space to create, delete

6, ORACLE logical backup file of SH

Full backup of the SH file: exp_comp.sh

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

su - oracle-c "exp system / manager full = y inctype = complete file = / oracle / export / db_comp $ rq.dmp"

SH cumulative backup file: exp_cumu.sh

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

su - oracle-c "exp system / manager full = y inctype = cumulative file = / oracle / export / db_cumu $ rq.dmp"

Incremental backups of the SH file: exp_incr.sh

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

su - oracle-c "exp system / manager full = y inctype = incremental file = / oracle / export / db_incr $ rq.dmp"

root user crontab file
/ var / spool / cron / crontabs / root, add the following content

0 2 1 * * / oracle / exp_comp.sh

30 2 * * 0-5 / oracle / exp_incr.sh

45 2 * * 6 / oracle / exp_cumu.sh

Of course, this schedule can be based on different needs to change, and this is just one example.

7, ORACLE common SQL syntax and data objects

1. Data Control statements (DML) part of

1.INSERT (data table to record the statement insert)

INSERT INTO table (field names 1, Field Name 2, ... ...) VALUES (value 1, value 2, ... ...);
INSERT INTO table (field names 1, Field Name 2, ... ...) SELECT (field names 1, Field Name 2, ... ...) FROM another table;

String type field values must be enclosed in single quotation marks, for example: 'GOOD DAY'
If the field value contains single quotes' need to convert a string, we replace it into two single quotes''.
String types of field values over the length of the definition to be wrong, best to check the length before insertion.

Field values of date fields may be time for the current database system SYSDATE, precise to the second
Or date string into a function TO_DATE ( ' 2001-08-01 ',' YYYY-MM-DD ')
TO_DATE (), there is still a variety of date format, you can see ORACLE DOC.
Year - month - day hour: minute: second format YYYY-MM-DD HH24: MI: SS

INSERT maximum operational length of the string less than or equal to 4000 single-byte, if you want to insert a longer string, please consider using CLOB type of field,
ORACLE methods used in the DBMS_LOB own package.

If you use INSERT to automatically increase from 1 to start the serial number should be the establishment of a serial number
CREATE SEQUENCE serial number of the name (preferably table name + serial number tag) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
One of the biggest values to set the length field, if the definition of the serial number of automatic increase in NUMBER (6), max 999,999
INSERT statement to insert the field values as follows: the name of the serial number. NEXTVAL

2.DELETE (data table records to delete the phrase)

DELETE FROM table WHERE conditions;

Note: the deletion of records and can not be occupied in the release of ORACLE data block tablespace. Only to those who have been to delete the data blocks marked as unused.

If you sure you want to delete a large table of all records, TRUNCATE command can be used, it can block the release of the data table occupied space
TRUNCATE TABLE table;
This action can not be back.

3.UPDATE (modify table data recorded statement)

UPDATE table SET field name 1 = value 1, field 2 = value of 2, ... ... WHERE condition;

If the revised value of N there is no assignment or definition, will record the contents of the original clearance to NULL, before the revision of the best non-empty calibration;
Value of N more than the length of the definition to be wrong, best to check the length before inserting ..

Note:
A. The above SQL statement are added to the table row-level locks,
Confirmed after the completion of processing must be added the end of the order of things to come into effect COMMIT,
Otherwise, change is not necessarily written into the database.
If you want the withdrawal of these operations can be used to recover ROLLBACK command.

B. run the INSERT, DELETE and UPDATE statements about the former may be the best estimate of the scope of operation records,
It should be limited to the smaller (10,000 records) range. Otherwise ORACLE used a lot of things to deal with the back of the paragraph.
Procedures to respond to the loss of response to slow or even. If more than the number of records in the hundreds of thousands of these operations, which were sub-sub-sub-SQL statement completed
COMMIT to confirm things with which to deal with.
II. Data Definition (DDL) part of

1.CREATE (create tables, indexes, views, synonyms, procedures, functions, database links, etc.)

ORACLE field types are commonly used
Fixed length CHAR string
Variable-length VARCHAR2 string
NUMBER (M, N) the number of type M is the median total length, N is the length of a small number of
DATE date type

Create a table should not be smaller space on the front of the field may be empty on the back of the field

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

Create table fields can be added to the default value, for example, DEFAULT SYSDATE
This insertion and changes every time, do not process the operation of the field action can be time

Can create a table to add constraints to the field
For example, does not allow duplicate UNIQUE, keyword PRIMARY KEY

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

Change the name of the table
ALTER TABLE table 1 TO table 2;

In the table after adding a field
ALTER TABLE table name ADD paragraph description field names;

Amend the definition of exterior field description
ALTER TABLE table MODIFY of the fields described in paragraph names;

Add fields to the exterior condition
ALTER TABLE table name ADD CONSTRAINT constraint PRIMARY KEY (field names);
ALTER TABLE table name ADD CONSTRAINT constraint UNIQUE (field names);

On the table or remove the memory database
ALTER TABLE table CACHE;
ALTER TABLE table NOCACHE;

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

Delete table and all its constraints
DROP TABLE table CASCADE CONSTRAINTS;

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

TRUNCATE table;

III. Query (SELECT) part

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

Field names can be brought into the function
For example: COUNT (*), MIN (field names), MAX (field names), AVG (fieldname), DISTINCT (field names),
TO_CHAR (DATE field names, 'YYYY-MM-DD HH24: MI: SS')

NVL (EXPR1, EXPR2) function
Explained:
IF EXPR1 = NULL
RETURN EXPR2
ELSE
RETURN EXPR1

DECODE (AA, V1, R1, V2, R2 ....) function
Explained:
IF AA = V1 THEN RETURN R1
IF AA = V2 THEN RETURN R2
.. ...
ELSE
RETURN NULL

LPAD (char1, n, char2) function
Explained:
Char1 character n in accordance with the development of the median show that the shortage of char2-digit string with the replacement of the left side of the space

Field names can be between the arithmetic operations
For example: (1 * Field Name Field Name 1) / 3

Query can be nested
For example: SELECT ... ... FROM
(SELECT ... ... FROM table 1, [table 2, ... ...] WHERE conditions) WHERE condition 2;

Query results of the two can be set to operate
For example: and set UNION (removing duplicate records), and set UNION ALL (do not remove duplicate records), Difference Sets MINUS, intersection INTERSECT

Sub-query
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] GROUP BY field names 1
[HAVING conditions];

Between two or more table join queries

SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Fieldname [AND ... ...];

SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Field Name (+) [AND ... ...];

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

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

SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...]
ORDER BY field names 1, Field Name 2 DESC;

Fuzzy string comparison

INSTR (field names, 'string')> 0
Field Name LIKE 'string%' [ '% string%']

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

IV. ORACLE data objects in common (SCHEMA)

1. Index (INDEX)

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

A table is best not to index more than 3 (except special pages), the best single-field index, combined with an analysis of SQL statements implementation
Can also create a combination of multi-field index and the index based on function

ORACLE8.1.7 string of maximum length can be indexed for 1578 single-byte
ORACLE8.0.6 string of maximum length can be indexed as 758 single-byte

2. View (VIEW)

Of CREATE VIEW view AS SELECT .... FROM ... ..;
ALTER VIEW view name COMPILE;

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

3. Synonyms (SYNONMY)
CREATE SYNONYM synonym name FOR table;
CREATE SYNONYM synonym name FOR table @ database link name;

4. Database link (DATABASE LINK)
Database link CREATE DATABASE LINK name CONNECT TO username IDENTIFIED BY password USING 'database connection string';

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

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

Global database name can be identified with the following command
SELECT * FROM GLOBAL_NAME;

Query the remote database table
SELECT ... ... FROM table @ database link name;

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

solaris10 oracle10G EM config

kods.net — Sun, 24 May 2009 03:09:20+0000

solaris10 oracle10G EM config

su - oracle emctl status dbconsole (see conditions)
su - oracle emctl start dbconsole (Open Service)
su - oracle emctl stop dbconsole
su - oracle emca-config dbcontrol db-repos recreate (re-configuration management oracle10g EM)

bash-3.00 $ emca-config dbcontrol db-repos recreate

EMCA began 2009-5-21 18:28:16
EM Configuration Assistant 10.2.0.1.0 official version Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: ora10g
Ora10g database has been configured for the Database Control
You have chosen to configure Database Control, to manage the database ora10g
This will remove the existing configuration and default settings, and re-run the configuration whether or not to continue? [Yes (Y) / no (N)]:
Whether or not to continue? [Yes (Y) / no (N)]: y
Listener port number: 1521
SYS user's password:
DBSNMP the user's password:
SYSMAN user password:
Notification e-mail address (optional): z_wangshibo@boco.com.cn
Copies of the notice (SMTP) server (optional): boco.com.cn
-------------------------------------------------- ---------------

Has appointed the following settings

Database ORACLE_HOME ................ / export / home / oracle / pro

Database host name ................ sun450
Listener port number ................ 1521
Database SID ................ ora10g
E-mail address notification ............... z_wangshibo@boco.com.cn
Copies of the notice (SMTP) server ............... boco.com.cn

-------------------------------------------------- ---------------
Whether or not to continue? [Yes (Y) / no (N)]: y
2009-5-21 18:29:26 oracle.sysman.emcp.EMConfig perform
Message: this operation is recorded / export/home/oracle/pro/cfgtoollogs/emca/ora10g/emca_2009-05-21_06-28-16- afternoon. Log.
2009-5-21 18:29:34 oracle.sysman.emcp.util.DBControlUtil stopOMS
Information: Stopping Database Control (this may take some time) ...
2009-5-21 18:29:51 oracle.sysman.emcp.EMReposConfig dropRepository
Information: data files are deleted EM repository (this may take some time) ...
2009-5-21 18:36:26 oracle.sysman.emcp.EMReposConfig invoke
Information: the information has been successfully deleted archives
2009-5-21 18:36:26 oracle.sysman.emcp.EMReposConfig createRepository
Information: the information is to create EM repository (this may take some time) ...

2009-5-21 18:51:15 oracle.sysman.emcp.EMReposConfig invoke
Information: the information has been created archive

2009-5-21 18:51:42 oracle.sysman.emcp.util.DBControlUtil startOMS
Information: is to start Database Control (this may take some time) ...

2009-5-21 18:53:59 oracle.sysman.emcp.EMDBPostConfig performConfiguration
Information: Database Control has been the successful launch of
2009-5-21 18:53:59 oracle.sysman.emcp.EMDBPostConfig performConfiguration
Information: >>>>>>>>>>> Database Control URL for http://sun450:1158/em <<<<<<<<<<<
Has successfully completed the configuration of Enterprise Manager
EMCA at the end of 2009-5-21 18:53:59

-bash-3.00 # su - oracle emctl start dbconsole
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://sun450:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ..... started.
-------------------------------------------------- ----------------
Logs are generated in directory / export/home/oracle/pro/sun450_ora10g/sysman/log

-bash-3.00 # su - oracle emctl status dbconsole
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://sun450:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
-------------------------------------------------- ----------------
Logs are generated in directory / export/home/oracle/pro/sun450_ora10g/sysman/log

After opening oracle10g EM can oracl10g from WEB to manage the database, in the web where you can start the database and monitoring services.

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

solaris 10 oracle10g install

kods.net — Sun, 24 May 2009 02:02:42+0000

solaris 10 oracle10g install

This is my install process:

First of all, first download the ORACLE 10G Download address

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201sol64soft.html

Extract oracle 10g
Installation package will be replaced by a long file name sol 10g . cpio.gz
# cp sol 10g . cpio.gz / export / home / oracle
# Gunzip sol in the decompression 10g . cpio.gz
# cpio-ivmd < 10g . cpio

Users and groups must be built, oinstall and dba groups, oracle user
groupadd oinstall
groupadd dba
useradd-d / export / home / oracle-g oinstall-G dba-m oracle
passwd oracle (set password
The establishment of oracle base directory and database directory
mkdir / export / home / oracle (look at the situation, and sometimes will be automatically created)
mkdir / export / home / oracle / oradata
Change the directory owners and groups are
chown-R oracle: install / export / home / oracle
# chown-R oracle: install / export / home / oracle / oradata

Change the directory permissions
chmod-R 775 / export / home / oracle
# chmod-R 775 / export / home / oracle / oradata

Third, set kernel parameters
I adjusted in accordance with the following steps

cd / etc
cp system system.orig (a safe back-up)
vi system
Add the following:
set shmsys: shminfo_shmmax = 4294967296

set shmsys: shminfo_shmmin = 1

set shmsys: shminfo_shmmni = 200

set shmsys: shminfo_shmseg = 30

set semsys: seminfo_semmni = 100

set semsys: seminfo_semmsl = 510

set semsys: seminfo_semmns = 1520

set semsys: seminfo_semopm = 100

set semsys: seminfo_semvmx = 32767

set noexec_user_stack = 1 (to eliminate 2G Limit)

set noexec_user_stack_log = 1 (to eliminate 2G Limit)

New start

Fourth, set up oracle environment

After landing in his capacity as a root
su - oracle
Access to / export / home / oracle
Vi. Profile
Reads as follows:

ORACLE_BASE = / export / home / oracle
ORACLE_HOME = / export/home/oracle/product/10.0
ORACLE_SID = ora 10g
NLS_LANG = AMERICAN_AMERICA.ZHS16GBK
PATH = $ ORACLE_HOME / bin: / bin: / sbin: / usr / ccs / bin: / usr / local / bin LD_LIBRARY_PATH = $ ORACLE_HOME / lib: $ ORACLE_HOME / network / lib: / usr / local / lib: / usr / lib
DISPLAY = 127.0.0.1:0.0
TMPDIR = / var / tmp
export ORALCE_BASE ORACLE_HOME ORACLE_SID NLS_LANG PATH LD_LIBRARY_PATH DISPLAY TMPDIR
umask 022
# NLS_LANG = "simplified chinese_china.ZHS16GBK" (This is a Simplified Chinese)
To start the installation.
Run the installation file
To root the implementation xhost + - this step is very important not to implement this step-by-step installation of oracle in the oracle database users will not start Xwindows graphical interface.

# / usr / openwin / bin / xhost +
# Export DISPLAY
# Su - oracle

$. / runInstaller If this implementation / export/home/db/Disk1/run * will appear this error message:
[color = blue] Checking installer requirements ...

Checking operating system version: must be 5.6, 5.7, 5.8 or 5.9. Actual 5.10
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at

/ tmp / OraInstall 2004-10-26 _09-45-32AM/installActions 2004-10-26 _09-45-32AM.log
Show installActions 2004-10-26 _09-45-32AM.log [/ color]

$ Vi installActions 2004-10-26 _09-45-32AM.log
Using paramFile: / export/home/oracle/db/Disk1/install/oraparam.ini
Checking installer requirements ...
Checking operating system version: must be 5.6, 5.7, 5.8 or 5.9. Actual 5.10
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at / tmp / Or
aInstall 2004-10-26 _09-45-32AM/installActions 2004-10-26 _09-45-32AM.log

Want to solve this problem should be in / export/home/db/Disk1/install, the modified oraparam.ini oraparam.var

Amended to read as follows:
vi oraparam.ini

# You can customise error message shown for failure through CERTIFIED_VERSION_FAI
LURE_MESSAGE
Solaris = 5.6,5.7,5.8,5.9, 5.10 (that is to add 5.10)
Linux = redhat-2.1AS
# HPUX = B.11.00
Decunix = V 5.1A , V5.1
AIX = 5.1.0 .0

vi oraparam.var

# You can customise error message shown for failure through CERTIFIED_VERSION_FAI
LURE_MESSAGE
Solaris = 5.8,5.9, 5.10 (that is to add 5.10)
Windows = 4.0,5.0,5.1,5.2
Linux = redhat-2.1AS
HPUX = B.11.11
Decunix = V 5.1A , V5.1
AIX = 5.1.0 .0

Preservation, in the implementation. . / runInstaller on OK.

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

oracle under unix command

kods.net — Sun, 24 May 2009 01:30:28+0000

oracle under unix command

A, ORACLE startup and shut down
1, in a single environment in order to activate or deactivate the ORACLE system must first switch to the ORACLE user, as
su - oracle

a, start the ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> quit

b, shut down ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown
SVRMGR> quit

Oracle9i database start command:
$ Sqlplus / nolog

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

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup ^ C

SQL> startup
ORACLE instance started.

2, in the two-machine environment in order to activate or deactivate the ORACLE system must first switch to the root user, as
su - root

a, start the ORACLE system
hareg-y oracle

b, shut down ORACLE system
hareg-n oracle

What are the Oracle database start-up mode

Description:

There are several ways to start:
1, startup nomount
Non-installation boot, under the executable to start this way: the reconstruction of the control file and rebuild the database

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

2, startup mount dbname
Start the installation, under the executable to start this way:
Database log archiving,
Database media recovery,
So that online or offline data files,
Re-positioning data files, redo log files.

The implementation of "nomount", and then open the control file to confirm data files and online log file,
However, at this time not data files and log files to check to check.

3, startup open dbname
First implementation of "nomount", then "mount", then open the Redo log file, including all database files,
This way can access the data in the database.

4, startup, equivalent to the following three command
startup nomount
alter database mount
alter database open

5, startup restrict
Bound to start in this way to start the database, but only have a certain privilege allows users to access non-privileged user access, we will have the following tips:
ERROR:
ORA-01035: ORACLE only with RESTRICTED SESSION allow users to use

6, startup force
When start-up mode can not be forced to close the database, can be used to complete the startup force the closure of the database to close the database, and then the database command to start the implementation of the normal

7, startup pfile = parameter file name with initialization parameter file to read the start-up mode parameter files, parameter file and then restart the database were set up: startup pfile = Eracleadminoradbpfileinit.ora

8, startup EXCLUSIVE
Second, the user how to effectively make use of the ORACLE data dictionary data dictionary is an important part of the database, one that it generated with the database created, with the database changes,
Reflected in the number of sys user tables and views. Data Dictionary of the English name is an uppercase character.

Data dictionary are user information, the user's permission information, all data object information, table constraints, statistical analysis, such as a database view.
We can not manually modify data dictionary information.

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

dictionary of all data dictionary table name and explained that it has a synonym dict
dict_column all the data dictionary table field names and interpretation of

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

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

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

SQL> select column_name, comments from dict_columns where table_name = 'USER_INDEXES';

And so on, you can easily know the name of the detailed data dictionary and explanation ORACLE not see the other documentation.

Below, by category, a number of user ORACLE data dictionary of commonly used methods of inquiry.

1, the user

Show the current user's default tablespace
SQL> select username, default_tablespace from user_users;

Show the current user's role
SQL> select * from user_role_privs;

Show the current user of the system privileges and table-level permissions
SQL> select * from user_sys_privs;
SQL> select * from user_tab_privs;

2, Table

See the table of all users
SQL> select * from user_tables;

Show the name of the table contains the log character
SQL> select object_name, object_id from user_objects
where instr (object_name, 'LOG')> 0;

See a table of the creation time
SQL> select object_name, created from user_objects where object_name = upper ( '& table_name');

See a table of size
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& table_name');

Show on the memory area ORACLE table
SQL> select table_name, cache from user_tables where instr (cache, 'Y')> 0;

3, index

View the index number and type of
SQL> select index_name, index_type, table_name from user_indexes order by table_name;

View index by the index field
SQL> select * from user_ind_columns where index_name = upper ( '& index_name');

View the index size
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& index_name');

4, serial number

Show serial number, last_number is the current value
SQL> select * from user_sequences;

5, view

See the name of view
SQL> select view_name from user_views;

See the select statement to create views
SQL> set view_name, text_length from user_views;
SQL> set long 2000; Description: text_length can view the value of setting the size of set long
SQL> select text from user_views where view_name = upper ( '& view_name');

6, synonyms

See the name synonymous with
SQL> select * from user_synonyms;

7, constraints

See a table of the Constraints
SQL> select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints where table_name = upper ( '& table_name');

SQL> select c.constraint_name, c.constraint_type, cc.column_name
from user_constraints c, user_cons_columns cc
where c.owner = upper ( '& table_owner') and c.table_name = upper ( '& table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;

8, memory function and the process of

View the status of functions and procedures
SQL> select object_name, status from user_objects where object_type = 'FUNCTION';
SQL> select object_name, status from user_objects where object_type = 'PROCEDURE';

View the function and process of source code
SQL> select text from all_source where owner = user and name = upper ( '& plsql_name');

Third, see the SQL database
1, see table space name and the size of

select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2, see table space name and the physical file size

select tablespace_name, file_id, file_name,
round (bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;

3, view the rollback segment name and size

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v $ rollstat v
Where r.segment_id = v.usn (+)
order by segment_name;

4, view the control file

select name from v $ controlfile;

5, view the log file

select member from v $ logfile;

6, view the use of table space

select sum (bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A. TABLESPACE_NAME, A. BYTES TOTAL, B. BYTES USED, C. BYTES FREE,
(B. BYTES * 100) / A. BYTES "% USED", (C. BYTES * 100) / A. BYTES "% FREE"
FROM SYS.SM $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C
WHERE A. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE_NAME;

7, view the database object library

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

8, see the database version

Select version FROM Product_component_version
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';

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

Select Created, Log_Mode, Log_Mode From V $ Database;

IV, ORACLE management of user connections

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

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

If you want to stop a connection with

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

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

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

Description: 21 is the sid of a number of connections

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

5, SQL * PLUS using
a, near to the SQL * Plus
$ sqlplus username / password

Quit SQL * Plus
SQL> exit

b, in sqlplus information listed under the help of all SQL commands and SQL * Plus command
SQL> help
Are listed in order of a particular information
SQL> help command name

c, show the table structure DESCRIBE command
SQL> DESC table name

d, SQL * Plus command to display the SQL buffer editing command
SQL> L

SQL command to modify the first line to be corrected to the current row
SQL> n
CHANGE command changes with
SQL> c / old / new re-verify that the correct
SQL> L

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

e, call the external system editor
SQL> edit the file name can use the DEFINE command to set the system variable EDITOR to change the type of text editor in the paper are defined as follows login.sql his
DEFINE_EDITOR = vi

f, run the command file
SQL> START test
SQL> test

SQL * Plus statements used
a, the table to create, modify, create a table of command to delete the following format:
create table table name (column description list);

Table-based commands add a new column as follows:
ALTER TABLE table ADD (column description list)
Cases: In order to increase test Table 1 Age, used to store the age of
sql> alter table test
add (Age number (3));

Amend the definition of order-based list is as follows:
ALTER TABLE table name
MODIFY (column name data type)
Example: Count table test out the width to 10 characters long
sql> alter atble test
modify (County char (10));

b, a table will delete the form of statement is as follows:
DORP TABLE table;
For example: table delete to delete table data and table definition
sql> rop table test

c, table space to create, delete

6, ORACLE logical backup file of SH

Full backup of the SH file: exp_comp.sh

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

su - oracle-c "exp system / manager full = y inctype = complete file = / oracle / export / db_comp $ rq.dmp"

SH cumulative backup file: exp_cumu.sh

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

su - oracle-c "exp system / manager full = y inctype = cumulative file = / oracle / export / db_cumu $ rq.dmp"

Incremental backups of the SH file: exp_incr.sh

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

su - oracle-c "exp system / manager full = y inctype = incremental file = / oracle / export / db_incr $ rq.dmp"

root user crontab file
/ var / spool / cron / crontabs / root, add the following content

0 2 1 * * / oracle / exp_comp.sh

30 2 * * 0-5 / oracle / exp_incr.sh

45 2 * * 6 / oracle / exp_cumu.sh

Of course, this schedule can be based on different needs to change, and this is just one example.

7, ORACLE common SQL syntax and data objects

1. Data Control statements (DML) part of

1.INSERT (data table to record the statement insert)

INSERT INTO table (field names 1, Field Name 2, ... ...) VALUES (value 1, value 2, ... ...);
INSERT INTO table (field names 1, Field Name 2, ... ...) SELECT (field names 1, Field Name 2, ... ...) FROM another table;

String type field values must be enclosed in single quotation marks, for example: 'GOOD DAY'
If the field value contains single quotes' need to convert a string, we replace it into two single quotes''.
String types of field values over the length of the definition to be wrong, best to check the length before insertion.

Field values of date fields may be time for the current database system SYSDATE, precise to the second or the date into a string function TO_DATE ('2001-08-01 ',' YYYY-MM-DD ')
TO_DATE (), there is still a variety of date format, you can see ORACLE DOC.
Year - month - day hour: minute: second format YYYY-MM-DD HH24: MI: SS

INSERT maximum operational length of the string less than or equal to 4000 single-byte, if you want to insert a longer string, please consider using CLOB type of field,
ORACLE methods used in the DBMS_LOB own package.

If you use INSERT to automatically increase from 1 to start the serial number should be the establishment of a serial number
CREATE SEQUENCE serial number of the name (preferably table name + serial number tag) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
One of the biggest values to set the length field, if the definition of the serial number of automatic increase in NUMBER (6), max 999,999
INSERT statement to insert the field values as follows: the name of the serial number. NEXTVAL

2.DELETE (data table records to delete the phrase)

DELETE FROM table WHERE conditions;

Note: the deletion of records and can not be occupied in the release of ORACLE data block tablespace. Only to those who have been to delete the data blocks marked as unused.

If you sure you want to delete a large table of all records, TRUNCATE command can be used, it can block the release of the data table occupied space
TRUNCATE TABLE table;
This action can not be back.

3.UPDATE (modify table data recorded statement)

UPDATE table SET field name 1 = value 1, field 2 = value of 2, ... ... WHERE condition;

If the revised value of N there is no assignment or definition, will record the contents of the original clearance to NULL, before the revision of the best non-empty calibration;
Value of N more than the length of the definition to be wrong, best to check the length before inserting ..

Note:
A. The above SQL statement are added to the table row-level locks,
Confirmed after the completion of processing must be added the end of the order of things to come into effect COMMIT,
Otherwise, change is not necessarily written into the database.
If you want the withdrawal of these operations can be used to recover ROLLBACK command.

B. run the INSERT, DELETE and UPDATE statements about the former may be the best estimate of the scope of operation records,
It should be limited to the smaller (10,000 records) range. Otherwise ORACLE used a lot of things to deal with the back of the paragraph.
Procedures to respond to the loss of response to slow or even. If more than the number of records in the hundreds of thousands of these operations, which were sub-sub-sub-SQL statement completed
COMMIT to confirm things with which to deal with.

II. Data Definition (DDL) part of

1.CREATE (create tables, indexes, views, synonyms, procedures, functions, database links, etc.)

ORACLE field types are commonly used
Fixed length CHAR string
Variable-length VARCHAR2 string
NUMBER (M, N) the number of type M is the median total length, N is the length of a small number of
DATE date type

Create a table should not be smaller space on the front of the field may be empty on the back of the field

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

Create table fields can be added to the default value, for example, DEFAULT SYSDATE
This insertion and changes every time, do not process the operation of the field action can be time

Can create a table to add constraints to the field are not allowed to repeat such as UNIQUE, keyword PRIMARY KEY

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

Change the name of the table
ALTER TABLE table 1 TO table 2;

In the table after adding a field
ALTER TABLE table name ADD paragraph description field names;

Amend the definition of exterior field description
ALTER TABLE table MODIFY of the fields described in paragraph names;

Add fields to the exterior condition
ALTER TABLE table name ADD CONSTRAINT constraint PRIMARY KEY (field names);
ALTER TABLE table name ADD CONSTRAINT constraint UNIQUE (field names);

On the table or remove the memory database
ALTER TABLE table CACHE;
ALTER TABLE table NOCACHE;

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

Delete table and all its constraints
DROP TABLE table CASCADE CONSTRAINTS;

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

TRUNCATE table;

III. Query (SELECT) part

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

Field names such as can be brought into the function: COUNT (*), MIN (field names), MAX (field names), AVG (fieldname), DISTINCT (field names),
TO_CHAR (DATE field names, 'YYYY-MM-DD HH24: MI: SS')

NVL (EXPR1, EXPR2) function explained:
IF EXPR1 = NULL
RETURN EXPR2
ELSE
RETURN EXPR1

DECODE (AA, V1, R1, V2, R2 ....) function explained:
IF AA = V1 THEN RETURN R1
IF AA = V2 THEN RETURN R2

ELSE
RETURN NULL

LPAD (char1, n, char2) function explained:
Char1 character n in accordance with the development of the median show that the shortage of char2-digit string with the replacement of the left side of the space

Field names can be between the arithmetic operations such as: (1 * Field Name Field Name 1) / 3

For example, can be nested query: SELECT ... ... FROM
(SELECT ... ... FROM table 1, [table 2, ... ...] WHERE conditions) WHERE condition 2;

The results of two query operations such as the collection can be done: and set UNION (removing duplicate records), and set UNION ALL (do not remove duplicate records), Difference Sets MINUS, intersection INTERSECT

Sub-query
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] GROUP BY field names 1
[HAVING conditions];

Between two or more table join queries

SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Fieldname [AND ... ...];

SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Field Name (+) [AND ... ...];

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

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

SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...]
ORDER BY field names 1, Field Name 2 DESC;

Fuzzy string comparison

INSTR (field names, 'string')> 0
Field Name LIKE 'string%' [ '% string%']

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

IV. ORACLE data objects in common (SCHEMA)

1. Index (INDEX)

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

A table is best not to index more than 3 (except special pages), the best single-field index, combined with an analysis of SQL statements implementation
Can also create a combination of multi-field index and the index based on function

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

2. View (VIEW)

Of CREATE VIEW view AS SELECT .... FROM ... ..;
ALTER VIEW view name COMPILE;

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

3. Synonyms (SYNONMY)
CREATE SYNONYM synonym name FOR table;
CREATE SYNONYM synonym name FOR table @ database link name;

4. Database link (DATABASE LINK)
Database link CREATE DATABASE LINK name CONNECT TO username IDENTIFIED BY password USING 'database connection string';

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

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

Global database name can be identified with the following command
SELECT * FROM GLOBAL_NAME;

Query the remote database table
SELECT ... ... FROM table @ database link name;

5. Rights Management (DCL) statements

Giving permission 1.GRANT common set of system privileges are the following three:
CONNECT (basic connection), RESOURCE (Programming), DBA (database management)
Commonly used data objects have the following five rights:
ALL ON data object name, SELECT ON data object name, UPDATE ON data object name,
DELETE ON object name data, INSERT ON data object name, ALTER ON data object name

GRANT CONNECT, RESOURCE TO username;
GRANT SELECT ON table TO user name;
GRANT SELECT, INSERT, DELETE ON table TO user name 1, User Name 2;

2.REVOKE recovery authority

REVOKE CONNECT, RESOURCE FROM username;
REVOKE SELECT ON table FROM user name;
REVOKE SELECT, INSERT, DELETE ON table FROM user name 1, User Name 2;

Query the database Error No. 63:
select orgaddr, destaddr from sm_histable0116 where error_code ='63 ';

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

Query the database in the sum of a variety of error codes:
select error_code, count (*) from sm_histable0513 group by error_code order
by error_code;

If the database query statements for a single type of query statistics.
select sum (Successcount) from tbl_MiddleMt0411 where ServiceType2 = 111
select sum (successcount), servicetype from tbl_middlemt0411 group by servicetype

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

DBA should have the character

kods.net — Sat, 23 May 2009 07:14:17+0000

Each DBA path of growth is different, because no one is born with the DBA, each DBA are in their own career path through the inflection point to an accidental DBA and not through their own career planning. Now, a growing number of young technical staff of their own career development has done a very good planning, this can take a lot less of a detour.
As a detour away a lot of old DBA, my feeling is the growth of DBA is a shortcut to the road, and in this way grow, not all will be paid a reasonable return. Some people will go very easily, some people are exceptionally difficult. This is the opportunities related to a person, just like the inside of Jin Yong's martial arts novels, some people need to practice for several decades in order to make a breakthrough, and the heroine are often met through some chance, the Zhengguo Xiucheng. A DBA in the growth of the road, choose the correct way to join the right team, met the right mentors, and Jin Yong's martial arts inside the welded face or eat the ice-bed is as important as the cure-all panacea.

To become a good DBA, effort alone is not enough, there are many factors determine the success of the DBA. Individual's personality is very important for the DBA. First of all, DBA need to calm character, failing to fear, doing things orderly. DBA work experience in a wide range of challenges facing the challenges of time, thought it is very important before taking any action, in the absence of adequate grasp before, do not do any thing, this is the criteria for iron DBA. To develop such a style, calm personality from birth is essential. Of course, some styles can be acquired habit, but some natural enemies of character is the DBA.

I have encountered one thing, when a customer's system there are some problems, we need to assist the analysis, the issue of system performance to identify root causes. I arranged for a DBA tasks to do, and I told him that he is only allowed to observe the system, can not do anything. Early in the morning the next day, I just boot, there is incoming customer calls, saying that how the system can not be used. And so busy I have a buddy that night when the wake from the dream, I only know that he 100G last night for a table so close to one hundred percent of the sample analysis of the entire table. The analysis of 8:00 in the morning, when not yet been completed. Later I asked the DBA, why do such a foolish. He said he is not clear when he began to be very clear that the system can not do anything, but he found that this form may be the analysis of the proportion of inadequate samples, if the re-analysis can solve this problem, so He wanted to try, even though he knew that he should not do any action, but he has a dominant impulse is to want to try, which he has done a calculation, it is estimated that before the 9:00 am to complete the analysis done so.

The above example is a typical example, the DBA should know what to do, but under the domination of curiosity, he has done wrong, this is the personality deficiencies caused. DBA usually late at night to work independently at this time, some non-rational decisions is a闪念have often, when you recovered, you may find has cast a big mistake, everything is too late. As a DBA, the face of the production system, any non-rational may be your career terminated, steady DBA for the importance of character can be seen on the.

From the previous example we can learn is that, as a DBA, can not be too confident. Self-confidence is a DBA must have the quality, in this regard, often two extremes. The first is the extreme lack of self-confidence, which is almost all DBA industry have just entered the common problem of technical personnel, in the face of customers, did not dare speak aloud, afraid to express their point of view. Mainly from lack of self-confidence in the ability of their own uncertainty, says the wrong thing is always afraid of their own. My colleagues and I always said that as long as your strong technical than customers, then you are an expert is the expert you, you said is correct. Just getting started for the DBA, the establishment of a strong self-confidence is crucial, but this process is very long, according to each person's different abilities and character, often to a few years time. A small number of DBA in a short period of time to establish self-confidence, which depends mainly on the understanding that this person, if he is able to quickly and customers from the exchanges that the level of customers, then he may be able to quickly establish a technology based on their own the capacity of self-confidence. Lack of self-confidence will become a door to your success in the outside of the DBA, so the establishment of full self-confidence is to be the best first step in DBA.

And compared to a lack of confidence, excessive self-confidence is very dangerous, lack of self-confidence is very easy to underestimate the customer does not have too much harm, and excessive self-confidence is the culprit of the majority of errors. DBA is always a lot of the old saying that DBA longer, less timid. Application system is very complex and can not一定之规. So the old DBA always suspected their own to determine whether entirely accurate. Excessive self-confidence and often appear in the new long-term DBA is found in a number of projects successfully completed, the self-confidence is also very fast growth, self-confidence is a good thing in general growth, but growth in the self-confidence, while maintaining the necessary care is very important.

In addition to stable and self-confidence, DBA the next character is the team spirit necessary. Modern society is no longer the Knights on their own times, and do anything in the work of a team. Teamwork can greatly reduce the intensity of DBA work, to alleviate the pressure DBA. As a DBA, should not be feeling lonely, because it is a company, a team in doing this. DBA in the field can be obtained through various channels, as well as other corporate headquarters team of experienced support team. Under such circumstances, will not encounter any problems feel powerless. Another aspect of team spirit is reflected in the team learning. Team learning can also speed up the growth of DBA, the so-called team learning is the same when we go to school, a group of people learning together. And school is a different era, DBA team to learn more efficient. For example, there are currently three points need to learn the knowledge, if the individual learning, the need for 3 months. Well, if there is a team, 3 individuals with learning, can be completed within one month learning a knowledge point, and then through the one month that we can exchange ideas, then 2 months to learn the team 3 months to achieve the effect of individual learning.

The spirit of seeking truth is to become an essential quality of senior DBA at any time, must be used to treat the spirit of seeking truth. At present, information on the DBA full of the Internet, in the learning process, we must not blindly believe that others, everything should be carefully sought, it is best able to carry out such experiments. I met many friends, have problems, whether or not it is important to test the production of the Treasury or the Treasury, are directly to the Internet to find some solutions, to find relevant information, the lack of a serious screening, they hurry to the operation, and finally led to very serious consequences. Dared to question the spirit of everything is very valuable. DBA of the learning process is an ongoing challenge, continuing the process of seeking truth, only with all the attitude of suspicion to learn before they can continue to explore the depth to reach the highest realm.

Oracle is willing to buy 7.4 billion US dollars from Sun 2009-04-21 10:20:46
Zoning on the Oracle 2009-04-15 00:52:26
The first part (4) based on the communication process (I) 2009-04-09 18:56:48
oracle basic concepts and terminology 2009-04-06 22:17:39
Oracle database used to maintain day-to-day command 2009-04-06 16:46:33

Oracle Exp-00003 on the solution to the problem

kods.net — Fri, 22 May 2009 22:19:02+0000

Oracle Exp-00003 on the solution to the problem

The day before yesterday in the lead when the data have taken place in the Exp-00003 error, before encountered such a problem, the solution is to the database server on the local guide, problem solving, but this is from oracle 9.2.0.1.0 Export the client (and the database server is 10.2.0.1.0), the requirements of the export with export 9.2.0.1.0, because the DMP file to export into 9.2.0.1.0, so that this machine derived method does not work, find the appropriate documentation after issue is resolved, the following solution for the records.

D: \ hanganbuild> exp hangyun / hangyun @ HANGAN100 file = c: \ 0425.dmp tables = sys_news

Export: Release 9.2.0.1.0 - Production on Wednesday, April 25 13:24:34 2007

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

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ZHS16GBK have been derived AL16UTF16 NCHAR character set and character set

About to export specified tables by conventional path ...
.. Are derived table SYS_NEWS
EXP-00003: not found in paragraph (4,99363) definition of export storage to terminate, but a warning.

Encountered such a situation there are two solutions: one is the above-mentioned to the server to export the machine, but at this time and the DMP file server on the local version of the EXPORT line.

The second way is to change the database server-side view of the oracle system definition:

To view specific: EXU9TNE

The view at the time of the initial location for the creation of: $ ORACLE_HOME / rdbms / admin / catexp.sql Lane

Initial is defined as:

CREATE OR REPLACE VIEW EXU9TNE
(tsno, fileno, blockno, length)
AS
SELECT ts #, segfile #, segblock #, length
FROM sys. Uet $
WHERE ext # = 1

Sys user to log on the database server to re-establish the view:

CREATE OR REPLACE VIEW EXU9TNE
(tsno, fileno, blockno, length)
AS
SELECT ts #, segfile #, segblock #, length
FROM sys. Uet $
WHERE ext # = 1
UNION ALL select "TSNO", "FILENO", "BLOCKNO", "LENGTH" from sys.exu9tneb

Is to increase attention to the above statement marked red

And then try the next should be OK.

D: \ hanganbuild> exp hangyun / hangyun @ HANGAN100 file = c: \ 0425.dmp tables = sys_news

Export: Release 9.2.0.1.0 - Production on Wednesday, April 25 13:36:31 2007

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

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ZHS16GBK have been derived AL16UTF16 NCHAR character set and character set

About to export specified tables by conventional path ...
.. Are derived table SYS_NEWS 4999 line was derived
In the absence of warning, to terminate the export.

(Note: Oracle's system is better not to easily view changes to the Executive after the operation, it is best to revert to the original view)

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
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

See the "direct search results for the empty" view

kods.net — Fri, 22 May 2009 22:13:41+0000

Direct access to a number of view will not return any results. There are two reasons for this situation would lead to the occurrence of:

view the use of ou shielding, the client query, it is necessary to simulate the operation of the application of user login before they can carry out its inquiries. (Such as purchase order summary view)
The solution: run fnd_global.APPS_INITIALIZE () to initialize the environment, and then be able to query the.
v iew of the client the impact of language environment, not inquiries, the general view in the establishment of the statement, there will be XXX = userenv ( 'LANG') these conditions.
The solution: run the statement in the query window: ALTER session SET NLS_LANGUAGE = American; (in accordance with the language of your application environment to determine what language film).

oracle set order to introduce 2009-05-20 15:09:33
Summary of the partition table 2009-04-21 18:21:19
The second part (15) practice to create table space-level replication environment (1) to initialize the environment 2009-04-09 04:28:38
Oracle to create data table space, the user, authorization, table, index, cluster 2009-04-06 06:17:29
Oracle to create data table space, the user, authorization, tables, indexes, clusters 2009-04-06 06:08:26

Oracle Exp-00003 on the question of solution

kods.net — Fri, 22 May 2009 21:30:55+0000

Oracle Exp-00003 on the solution to the problem

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
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

The use of EBS fnd_global.apps_initialize

kods.net — Fri, 22 May 2009 20:43:48+0000

Prototype: fnd_global.apps_initialize (user_ID, Responsibility_id,

Responsibility_application_id);

Role: the conversation in the database set up global variables, and user-summary information.

Parameters was:

Parameters of a User ID

select user_id

from fnd_user

where user_name like '% OPERATIONS%'; - ID of OPERATIONS: 1318

Parameter b, duties ID (responsibility id)

select RESPONSIBILITY_ID, APPLICATION_ID, RESPONSIBILITY_KEY
from fnd_responsibility
- where APPLICATION_ID = 140
where RESPONSIBILITY_KEY like '% ASSETS_VISION_OPERATIONS%';

Parameter c, on behalf of the duties of their applications (application) number

SQL access to the above

Run:
BEGIN
fnd_global.APPS_INITIALIZE (youruesr_id, yourresp_id, yourresp_appl_id);
END;

Test:

select fnd_profile.value ( 'GL_SET_OF_BKS_ID') FROM DUAL;

fnd_profile.value ( 'USER_ID') - corresponds to just user_id

Oracle Database Exp / Imp tools for performance tuning 2009-04-06 11:12:24
Database tuning problem (parameter binding, Histogram Analysis) 2009-04-03 23:53:10
setting to discuss filing oracle10g 2009-04-02 05:19:08
LISTENER and EZCONNECT 2009-04-01 21:42:20
An example of the use of TKPROF 2009-02-26 01:38:19

oracle install faq

kods.net — Fri, 22 May 2009 06:48:38+0000

6, Appendix ◎ installation procedures are shown in "box □"
This is because the installation procedure for Oracle to support the poor Chinese, we use in English can be installed, before running the installation

# su - oracle
$ export LANGUAGE = en_US
$ export LANG = en_US

◎ oranavi in the use of tools if there are problems in the installation, the next on how to continue?
Do not oranavi many tools to guide the use of Oracle's installation process, which will lead to the environment under the Oracle user configuration confusion, the correct approach is:

# su - oracle
$ export LANGUAGE = en_US
$ export LANG = en_US
$ / mnt / cdrom / runInstall &

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

Oracle network configuration used in the sqlnet.ora, tnsnames.ora, listener.ora

kods.net — Wed, 20 May 2009 22:16:36+0000

Oracle network configuration used in the sqlnet.ora, tnsnames.ora, listener.ora file Note: The article and related content from the network from finishing the book, so only the sharing of knowledge, to help.

listener.ora, tnsnames.ora and sqlnet.ora This document is the relationship between three network configuration oracle of three key documents are on the $ ORACLE_HOME \ network \ admin directory. Listener.ora which is server-side and database-related, while the tnsnames.ora and sqlnet.ora files that not only two related to the server-side, the main thing is to work closely and client relations.

A client set up

1.sqlnet.ora

The adoption of the document to determine how to find a connection in the connection string appears

Sample file:
# Sqlnet.ora Network Configuration File: F: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# Install "Software Only", this file wont exist and without the native
# Authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME, ONAMES, EZCONNECT)

# NAMES.DEFAULT_DOMAIN = oracle.com

Content Description:

SQLNET.AUTHENTICATION_SERVICES = (NTS) - that the use of the OS authentication, the database server, you can use sqlplus / as sysdba. General this configuration is ok in the windows, and in the unix environment there may be some problems, and generally can be removed under unix this configuration.

NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME, ONAMES) - that will be the first to make use of analytical tnsnames; tnsnames analytic if not, will be used to resolve hostname; If the hostname can not resolve, will be used to resolve onames; the final analysis the use of EZCONNECT. For example, we enter the client: sqlplus zidi / zidi @ changbai, then the client will first look at the tnsnames.ora file orcl records. If there is no corresponding record is trying to bring as a host name orcl through network channels to resolve its IP address and then connect to this IP on global_name = changbai This example, of course, I am not a host name orcl.

Commented out the NAMES.DEFAULT_DOMAIN = changbai.com-- that the domain name using the default changbai.com, if tnsnames.ora configuration in the corresponding resolution, if the original database alias is oralocal (that is, the network service name), then When this parameter is enabled after tnsnames to change the configuration oralocal.changbai.com. When using tnsping or sqlplus login, simply write in front of the alias, the system will automatically add the back to resolve the domain name.

2.tnsnames.ora

The document on the client machine, the client access the database record of the local configuration, in fact, the definition of network services, sqlnet.ora only when there is "NAMES.DIRECTORY_PATH = (TNSNAMES)" when such a word, that is, the client analysis connection string in the order of TNSNAMES when try to use the document.

Sample file:
# Tnsnames.ora Network Configuration File: F: \ oracle \ product \ 10.2.0 \ db_1 \ NETWORK \ ADMIN \ tnsnames.ora
# Generated by Oracle configuration tools.

HUINAMN = network service name
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 108.108.108.108) (PORT = 1521)) using the agreement to access the database, the database host computer name or IP address, port number database
)
(CONNECT_DATA =
(SID = HN) database instance name, the premise is that the database is connected to Oracle8 or earlier
)
)
CHANGBAI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 208.208.208.208) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CB) database services, or global database name
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED) using a dedicated server mode to connect, it needs to do the pattern matching server, if there is no server on the basis of the automatic adjustment mode
(SERVICE_NAME = orcl)
)
)

Second, set up server-side
listener.ora

It is the listener process listener configuration file. On the listener process is not to say, the acceptance of remote access to the database and transferred to the oracle application server process. So if not using a remote connection, listener process is not necessary to close the listener if the same process and will not affect the already existing database connection.

Sample file:
# Listener.ora Network Configuration File: F: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER = here LISTENER process definition example for the provision of services which
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F: \ oracle \ product \ 10.2.0 \ db_1)
(PROGRAM = extproc)
)
)

LISTENER = listener name, a database can have more than one listener
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1)) listener monitoring the agreement, IP, port, etc.
(ADDRESS = (PROTOCOL = TCP) (HOST = dabeng) (PORT = 1521))
)
)

The above example is one of the most simple example, but it is also the most common. The process of a listener for a database instance (SID) to provide services.

Third, sum up

The three documents mentioned above to pass through the graphical configuration tools to complete the configuration: Database Configuration Assistant, Net Configuration Assistant

The specific configuration will try my best, and then look at the configuration file, so there will be a general structure is that when you type sqlplus zidi / zidi @ changbai time

1. Sqlnet.ora look at the name query to resolve the way, found TNSNAME
2. Tnsnames.ora file query, from the inside looking orcl records, and to find the host name, port and service_name
3. If the listener process, there is no problem, then the process of establishing a connection with the listener.
4. According to a different server models such as the dedicated server mode or shared server mode, listener action to take next. The default is dedicated server mode, there is no problem if the client is connected to the database on the server process.
5. At this time the network connection has been established, listener process is completed the mission.

Understanding from another angle:
changbai is that we used to connect the remote database service name, but ultimately must be translated into host: port: service_name this connection string, and this transformation can be understood as the process of translation, and translation in order to succeed, you must choose a good translation method, it is our sqlnet.ora translation method used to select which NAMES.DIRECTORY_PATH = (TNSNAMES) is the name we have chosen analytical method; tnsnames.ora is a translation method - the local name resolution method configuration file, just like the method we have chosen the dictionary, the dictionary file is the same as our store with all the services which correspond to the connection string name; As listener.ora is responsible for accept our request for a responsible person - connecting address configuration document, which includes connecting the connection address of responsible person, but also can be placed through his connection to allow the database information. This constitutes a network architecture of Oracle.

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

Dump Systemstat Error

kods.net — Wed, 20 May 2009 20:50:48+0000

The production database hang, try to get the systemstat

++++++++++++++++++

% oracle_home% \ bin \ sqlplus-prelim / as sysdba

oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266
oradebug dump systemstate 266
++++++++++++++++++

Then, it got a error in alert log

++++++++++++++++++

System State dumped to trace file
Tue May 19 13:16:12 2009
Errors in file d: \ oracle \ product \ 10.2.0 \ admin \ fspdc \ udump \ fspdc_ora_12612.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [kgxAolDump +208] [PC: 0x50C15B6] [ADDR: 0x88] [UNABLE_TO_READ] []
++++++++++++++++++

It caused by a bug.

The solution from metalink

Doc ID:

417,879.1

Cause

Issue was filed as Bug 5892959 and was closed as a duplicate of unpublished bug 5730231.

Solution

The issue is not resolved in any known patches. The below workaround was provided:

Workaround
~~~~~~~~~~~~
Connect / as sysdba (without-prelim)

-OR -

Force the variable PGA to be initialized
eg:
sqlplus-prelim '/ as sysdba'
oradebug setmypid
oradebug unlimit;
oradebug call ksmcpg
oradebug dump systemstate 10

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

RHEL AS 4 under manual in ORACLE 10g database on the bare equipment operation

kods.net — Wed, 20 May 2009 20:34:30+0000

Have been used in the previous DBCA to create database using DBCA, however bare in the use of equipment always pass, so want to pass the database manually to the database of the way built on the bare device. Through the manual to understand database structure of ORACLE database .
Assumption here has been able to install the linux operating system, ORACLE software, it is no longer detailed software installation steps. Can refer to:
ORACLE 10G for RHEL AS 4 install (http://blog.chinaunix.net/u/28641/showart_1087570.html)
In the cited article, the attention here to only install the software, not the establishment of databases.

After the completion of the installation of oracle software to start manually the database based on the bare device.
Related to the environment:
ORACLE_BASE: / opt / oracle
ORACLE_HOME: / opt/oracle/product/10.2.0/ora10g
ORACLE_SID: ora10g

1. The establishment of the relevant directory

- Diagnosis of Contents
[oracle @ rhel47 ~] $ mkdir-p $ ORACLE_BASE/admin/ora10g/adump
[oracle @ rhel47 ~] $ mkdir-p $ ORACLE_BASE/admin/ora10g/bdump
[oracle @ rhel47 ~] $ mkdir-p $ ORACLE_BASE/admin/ora10g/cdump
[oracle @ rhel47 ~] $ mkdir-p $ ORACLE_BASE/admin/ora10g/udump
[oracle @ rhel47 ~] $ mkdir-p $ ORACLE_BASE/admin/ora10g/pfile

- Data file storage directory
[oracle @ rhel47 ~] $ mkdir-p $ ORACLE_BASE/oradata/ora10g

2. To create data files bare equipment

- Create a logical volume.
[root @ rhel47 ~] # lvcreate-L 608M-n lvsystem vg00
[root @ rhel47 ~] # lvcreate-L 408M-n lvsysaux vg00
[root @ rhel47 ~] # lvcreate-L 108M-n lvtemp vg00
[root @ rhel47 ~] # lvcreate-L 108M-n lvundotbs vg00
[root @ rhel47 ~] # lvcreate-L 60M-n lvredoa1 vg00
[root @ rhel47 ~] # lvcreate-L 60M-n lvredoa2 vg00
[root @ rhel47 ~] # lvcreate-L 60M-n lvredoa3 vg00

- Bare of equipment, in order to reboot the system after the entry into force, the following command to be added to / etc / rc.local file.
[root @ rhel47 vg00] # raw / dev/raw/raw1 / dev/mapper/vg00-lvsystem
[root @ rhel47 vg00] # raw / dev/raw/raw2 / dev/mapper/vg00-lvsysaux
[root @ rhel47 vg00] # raw / dev/raw/raw3 / dev/mapper/vg00-lvtemp
[root @ rhel47 vg00] # raw / dev/raw/raw4 / dev/mapper/vg00-lvundotbs
[root @ rhel47 vg00] # raw / dev/raw/raw5 / dev/mapper/vg00-lvredoa1
[root @ rhel47 vg00] # raw / dev/raw/raw6 / dev/mapper/vg00-lvredoa2
[root @ rhel47 vg00] # raw / dev/raw/raw7 / dev/mapper/vg00-lvredoa3

- Change is the main bare equipment: a group, to take effect after the restart, the following command into / etc / rc.local file.
[root @ rhel47 raw] # chown oracle: dba / dev / raw / raw *

- Data files in the directory link to the bare device.
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw1 rlvsystem
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw2 rlvsysaux
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw3 rlvtemp
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw4 rlvundotbs
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw5 rlvredoa1
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw6 rlvredoa2
[oracle @ rhel47 ora10g] $ ln-s / dev/raw/raw7 rlvredoa3

3. To create a document ORACLE parameters $ ORACLE_HOME/dbs/initora10g.ora

Copy $ ORACLE_HOME / dbs / init.ora to $ ORACLE_HOME/dbs/initora10g.ora (that is, ora $ (ORACLE_SID). Ora). As a result of its own version of the init.ora low, it is recommended to use the init.ora with other documents, such as the establishment of a database using dbca generated init $ (ORACLE_SID). ora file as a template.
Amended to read as follows parameters.
db_name = ora10g
shared_pool_size = 100000000
control_files = (/ opt/oracle/oradata/ora10g/control01.ctl, / opt/oracle/oradata/ora10g/control02.ctl, / opt/oracle/oradata/ora10g/control03.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS

4. Create a password file

[oracle @ rhel47 ~] $ orapwd file = $ ORACLE_HOME/dbs/orapwora10g password = root123 entries = 5 force = y

5. Ora10g.sql script file to create database

- Note the use of reuse parameters, shut down automatically extended.
[oracle @ rhel47 ~] $ cat>> ora10g.sql < CREATE DATABASE ora10g
LOGFILE
GROUP 1 ( '/ opt/oracle/oradata/ora10g/rlvredoa1') SIZE 50M,
GROUP 2 ( '/ opt/oracle/oradata/ora10g/rlvredoa2') SIZE 50M,
GROUP 3 ( '/ opt/oracle/oradata/ora10g/rlvredoa3') SIZE 50M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
NOARCHIVELOG
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/ opt/oracle/oradata/ora10g/rlvsystem' SIZE 600M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/ opt/oracle/oradata/ora10g/rlvsysaux' SIZE 400M REUSE AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/ opt/oracle/oradata/ora10g/rlvtemp' SIZE 100M REUSE AUTOEXTEND OFF
UNDO TABLESPACE "UNDOTBS"
DATAFILE '/ opt/oracle/oradata/ora10g/rlvundotbs' SIZE 100M REUSE AUTOEXTEND OFF;

EOF

6. The implementation of database

oracle @ rhel47 ~] $ sqlplus / nolog

SQL * Plus: Release 10.2.0.1.0 - Production on Sat Feb 7 13:01:13 2009

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

SQL> connect sys/root123 as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 134217728 bytes
Fixed Size 1218100 bytes
Variable Size 125831628 bytes
Database Buffers 4194304 bytes
Redo Buffers 2973696 bytes
SQL> start ora10g.sql

Database created.

7. To create data dictionary, PACKAGE package.

- Are still under the SYS user, the two statements must spend a longer period of time, need to be patient .....................
SQL> start / opt/oracle/product/10.2.0/ora10g/rdbms/admin/catalog.sql

SQL> start / opt/oracle/product/10.2.0/ora10g/rdbms/admin/catproc.sql

Done here, to manually install on essentially completed. The following to manually configure and monitor DBCONSOLE

8. Configured to monitor

Copy $ (ORACLE_HOME) / network / admin / samples / tnsname.ora to $ (ORACLE_HOME) / network / admin / increase / modify the relevant information.
ORA10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.80.47) (PORT = 1521))
)
(CONNECT_DATA =
(SID = ora10g)
(SERVER = DEDICATED)
)
)

Copy $ (ORACLE_HOME) / network / admin / samples / sqlnet.ora to $ (ORACLE_HOME) / network / admin / increase / modify the relevant information.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH = (TNSNAMES)

Then, through the command to start listening.
[oracle @ rhel47 ~] $ lsnrctl start
Confirmed that the normal start.

9. DBCONSOLE configuration

DBCONSOLE needed to configure this tool emca ..
The establishment of the Treasury, the relevant information here, including the need for ORACLE_SID, listening port number, SYS and SYSMAN user password.
[oracle @ rhel47 ~] $ emca-repos create

Configuration database, where the relevant information, including the need for ORACLE_SID, listening port number, SYS, DBSNMP and SYSMAN user password.
[oracle @ rhel47 ~] $ emca-config dbcontrol db

Here .. take some time after the completion of, dbconsole automatically start .. using the following command to check.
[oracle @ rhel47 ~] $ emctl status dbconsole

Http://rhel47:1158/em the way through (OEM) access control ..

(OVER)

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

"In-depth Analysis of Oracle" Book

kods.net — Wed, 20 May 2009 19:20:10+0000

"In-depth Analysis of Oracle" Book

The first time to buy books related DBA point of view!
Reading down the breath, there are still many views. In general, the more successful this book. Particularly in the author's actual experience. If some improvement, from the perspective of the book, as summed up the contents of the arrangement, the cognitive perspective to further extend the number of unique,
Quality will have improved.

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

2009/05

insert all

Related Posts

oracle order of 2 commonly used

Related Posts

oracle commands commonly used 2

Related Posts

Database Backup Program

Related Posts

Unix environment explain fully optimized for Oracle database

Related Posts

Oracle learning some of operation (compared chaos)

Related Posts

ORACLE common command 1

Related Posts

solaris10 oracle10G EM config

Related Posts

solaris 10 oracle10g install

Related Posts

oracle under unix command

Related Posts

DBA should have the character

Related Posts

Oracle Exp-00003 on the solution to the problem

Oracle Exp-00003 on the solution to the problem

Related Posts

See the "direct search results for the empty" view

Related Posts

Oracle Exp-00003 on the question of solution

Oracle Exp-00003 on the solution to the problem

Related Posts

The use of EBS fnd_global.apps_initialize

Related Posts

oracle install faq

Related Posts

Oracle network configuration used in the sqlnet.ora, tnsnames.ora, listener.ora

Related Posts

Dump Systemstat Error

Cause

Solution

Related Posts

RHEL AS 4 under manual in ORACLE 10g database on the bare equipment operation

1. The establishment of the relevant directory

2. To create data files bare equipment

3. To create a document ORACLE parameters $ ORACLE_HOME/dbs/initora10g.ora

4. Create a password file

5. Ora10g.sql script file to create database

6. The implementation of database

7. To create data dictionary, PACKAGE package.

8. Configured to monitor

9. DBCONSOLE configuration

Related Posts

"In-depth Analysis of Oracle" Book

Related Posts