Check Executing SQL Statements
select /*+ T1A */ * from hr.employees;
select sql_id, executions, loads, sql_text from v$sql
where sql_text like '%T1A%';
SQL Explain Plan
select * from table (dbms_xplan.display_cursor('6npnynbfn50vs'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6npnynbfn50vs, child number 0
-------------------------------------
select /*+ T1A */ * from hr.employees
Plan hash value: 1445457117
-------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------
0 SELECT STATEMENT 3 (100)
1 TABLE ACCESS FULL EMPLOYEES 107 7276 3 (0) 00:00:01
-------------------------------------------------------------------------------
13 rows selected
Execution plan is case sensitive on SQL statements
Use Hash to store
select
u.sid,
substr(u.username,1,12) user_name,
s.sql_text
from
v$sql s,
v$session u
where
s.hash_value = u.sql_hash_value
and
sql_text not like '%from v$sql s, v$session u%'
order by
u.sid;
Redo Log
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- ---------------------- -------------------------
1 1 8 52428800 2 NO CURRENT 596545 25-JAN-09
2 1 6 52428800 2 NO INACTIVE 543104 24-JAN-09
3 1 7 52428800 2 NO INACTIVE 573428 25-JAN-09
alter system switch logfile;
alter system switch succeeded.
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- ---------------------- -------------------------
1 1 8 52428800 2 NO ACTIVE 596545 25-JAN-09
2 1 9 52428800 2 NO CURRENT 607813 26-JAN-09
3 1 7 52428800 2 NO INACTIVE 573428 25-JAN-09
Kill Sessions
Check Data Files
1* select file_name, tablespace_name, bytes, autoextensible from dba_data_files
SYS> /
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_4qm78bfj_.dbf
USERS 5242880 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_4qm78b7x_.dbf
SYSAUX 262144000 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_4qm78bd0_.dbf
UNDOTBS1 36700160 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_4qm78b7d_.dbf
SYSTEM 503316480 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_4qm7c00l_.dbf
EXAMPLE 104857600 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_4shhwf2w_.dbf
UNDOTBS2 20971520 NO
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_userstbs_4shj5fyy_.dbf
USERSTBS2 20971520 NO
/u01/app/oracle/oradata/ORCL/datafile/inventorytbs1
INVENTORY 5242880 NO
8 rows selected.
SYS>
Table Free Space
1* select table_name, pct_free, chain_cnt from user_tables where table_name like 'A%'
SYS> /
ATTRCOL$ 0 0
ARGUMENT$ 10 0
ACCESS$ 10 0
AUDIT$ 10 0
ATTRIBUTE$ 0 0
ASSOCIATION$ 10 0
APPROLE$ 10 0
AUD$ 10 0
AUX_STATS$ 10 0
AW$ 10 0
AW_OBJ$ 10 0
Check if FlashBack is ON
1* select name, current_scn, flashback_on from v$database
SYS> /
ORCL 782184 NO
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 109054196 bytes
Database Buffers 54525952 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS> alter database flashback on;
Database altered.
SYS>
Undo Transactions
select * from v$transactions
Others
select file#, checkpoint_change#, name from v$datafile
SYS> select name, open_mode from v$database;
ORCL READ WRITE
SYS> select * from v$instance;
1 orcl localhost.draco.com 10.2.0.1.0 15-FEB-09 OPEN NO 1 STOPPED ALLOWED
NO ACTIVE PRIMARY_INSTANCE NORMAL NO
1* select count(*) from v$fixed_table
SYS> /
1383
SYS> desc dict;
Name Null? Type
----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SYS> select segment_name, extents, blocks, bytes from user_segments where segment_name like '%INV%';
INVALIDATION_REGISTRY$ 1 8 65536
INVOICE 1 8 65536
I_INVALIDATION_REGISTRY$ 1 8 65536
INVPK 1 8 65536
SYS> select tablespace_name, extent_management, segment_space_management from dba_tablespaces;
SYSTEM LOCAL MANUAL
UNDOTBS1 LOCAL MANUAL
SYSAUX LOCAL AUTO
TEMP LOCAL MANUAL
USERS LOCAL AUTO
UNDOTBS2 LOCAL MANUAL
EXAMPLE LOCAL AUTO
TEMPTBS2 LOCAL MANUAL
USERSTBS2 LOCAL AUTO
INVENTORY LOCAL AUTO
INVOICE_PK LOCAL AUTO
11 rows selected.
SYS> select * from session_roles;
no rows selected
Monday, April 27, 2009
Useful Sql
$ORACLE_HOME\rdbms\admin\catalog.sql
Create views data dictionary information
$ORACLE_HOME\rdbms\admin\catproc.sql
Build procedural package
Tablespace
Create undo tablespace
1* create undo tablespace undotbs2 datafile size 20M
SYS> /
Tablespace created.
Create temp tablespace
1* create temporary tablespace temptbs2 tempfile size 5M
SYS> /
Tablespace created.
SYS> alter system set undo_management=AUTO scope=spfile;
System altered.
Change undo tablespace
SYS> alter system set undo_tablespace=UNDOTBS1 scope=spfile;
System altered.
Change temporary tablespace
SYS> alter system set undo_tablespace=UNDOTBS1 scope=spfile;
System altered.
Create users tablespace
1* create tablespace userstbs2 datafile size 20M
SYS> /
Tablespace created.
Change users tablespace
SYS> alter database default tablespace userstbs2;
Database altered.
Create tablespace
SYS> create tablespace invoice_pk datafile '/u01/app/oracle/oradata/ORCL/datafile/invoicetbs1' size 5M autoextend ON;
Tablespace created.
SYS> ed
Wrote file afiedt.buf
1 create table invoice
2 (
3 ID number constraint invpk primary key
4 using index
5 (create unique index invpk on invoice(id) tablespace invoice_pk),
6 invdate date,
7 description varchar2(200))
8* tablespace invoice_pk
SYS> /
Table created.
Resize Tablespace
SYS> alter database datafile '/u01/app/oracle/oradata/ORCL/datafile/invoicetbs1' resize 10M;
Database altered.
Set Tablespace read only
SYS> alter tablespace invoice_pk read only;
Tablespace altered.
Set Tablespace read write
1* alter tablespace invoice_pk read write
SYS> /
Tablespace altered.
Users
Create users
SYS> create user usera identified by oracle default tablespace INVENTORY temporary tablespace temp;
User created
SYS> grant create session to usera;
Grant succeeded.
SYS> alter user usera quota unlimited on inventory;
User altered.
SYS> grant unlimited tablespace to usera;
Grant succeeded.
Create OS authenticated users
SYS> show parameter prefix;
os_authent_prefix string ops$
SYS> create user ops$oracle identified externally;
User created.
SYS> grant grant any object privilege to ops$oracle;
Grant succeeded.
SYS> grant create any table to ops$oracle;
Grant succeeded.
Allow to create table on any schema
owner stay the same as the schema owner
Lock/Unlock users
1* alter user usera account lock
SYS> /
SYS> alter user usera account unlock;
User altered.
Primary Key Deferrable
DEFERRED
indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed.
INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking
1 create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially immediate,
3* partdesc varchar2(35) constraint partdesc_uq unique deferrable initially immediate)
SYS> /
Table created.
SYS> insert into inventory values (1,'A');
1 row created.
SYS> insert into inventory values (1,'B');
insert into inventory values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.PARTNO_PK) violated
1 create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially deferred,
3* partdesc varchar2(35) constraint partdesc_uq unique deferrable initially deferred)
SYS> /
Table created.
SYS> insert into inventory values (1,'A');
1 row created.
SYS> insert into inventory values (1,'B');
1 row created.
SYS> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYS.PARTNO_PK) violated
Check Log Switch
SYS> select sequence#, first_time from v$log_history;
1 23-JAN-09
2 23-JAN-09
3 23-JAN-09
4 23-JAN-09
5 23-JAN-09
6 24-JAN-09
7 25-JAN-09
8 25-JAN-09
9 26-JAN-09
10 15-FEB-09
11 25-FEB-09
12 01-MAR-09
13 02-APR-09
13 rows selected.
ArchiveLog Mode
Make sure you have enough disk space
archive log list;
SYS> Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
SYS>
1* select name, log_mode,open_mode from v$database
SYS> /
ORCL NOARCHIVELOG READ WRITE
SYS> alter database archivelog;
Database altered.
SYS> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SYS>
SYS> select name, log_mode,open_mode from v$database;
ORCL ARCHIVELOG MOUNTED
DB_Recovery_Size
SYS> show parameter db_recovery_file_dest_size;
db_recovery_file_dest_size big integer 8048M
Guarantee Undo Rentention
SQL> alter tablespace undotbs1 RENTENTION GUARANTEE
SQL> alter tablespace undotbs1 RENTENTION NOGUARANTEE
Show current user
SQL> show users;
Display current database
SQL> select * from global_name;
Create views data dictionary information
$ORACLE_HOME\rdbms\admin\catproc.sql
Build procedural package
Tablespace
Create undo tablespace
1* create undo tablespace undotbs2 datafile size 20M
SYS> /
Tablespace created.
Create temp tablespace
1* create temporary tablespace temptbs2 tempfile size 5M
SYS> /
Tablespace created.
SYS> alter system set undo_management=AUTO scope=spfile;
System altered.
Change undo tablespace
SYS> alter system set undo_tablespace=UNDOTBS1 scope=spfile;
System altered.
Change temporary tablespace
SYS> alter system set undo_tablespace=UNDOTBS1 scope=spfile;
System altered.
Create users tablespace
1* create tablespace userstbs2 datafile size 20M
SYS> /
Tablespace created.
Change users tablespace
SYS> alter database default tablespace userstbs2;
Database altered.
Create tablespace
SYS> create tablespace invoice_pk datafile '/u01/app/oracle/oradata/ORCL/datafile/invoicetbs1' size 5M autoextend ON;
Tablespace created.
SYS> ed
Wrote file afiedt.buf
1 create table invoice
2 (
3 ID number constraint invpk primary key
4 using index
5 (create unique index invpk on invoice(id) tablespace invoice_pk),
6 invdate date,
7 description varchar2(200))
8* tablespace invoice_pk
SYS> /
Table created.
Resize Tablespace
SYS> alter database datafile '/u01/app/oracle/oradata/ORCL/datafile/invoicetbs1' resize 10M;
Database altered.
Set Tablespace read only
SYS> alter tablespace invoice_pk read only;
Tablespace altered.
Set Tablespace read write
1* alter tablespace invoice_pk read write
SYS> /
Tablespace altered.
Users
Create users
SYS> create user usera identified by oracle default tablespace INVENTORY temporary tablespace temp;
User created
SYS> grant create session to usera;
Grant succeeded.
SYS> alter user usera quota unlimited on inventory;
User altered.
SYS> grant unlimited tablespace to usera;
Grant succeeded.
Create OS authenticated users
SYS> show parameter prefix;
os_authent_prefix string ops$
SYS> create user ops$oracle identified externally;
User created.
SYS> grant grant any object privilege to ops$oracle;
Grant succeeded.
SYS> grant create any table to ops$oracle;
Grant succeeded.
Allow to create table on any schema
owner stay the same as the schema owner
Lock/Unlock users
1* alter user usera account lock
SYS> /
SYS> alter user usera account unlock;
User altered.
Primary Key Deferrable
DEFERRED
indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed.
INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking
1 create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially immediate,
3* partdesc varchar2(35) constraint partdesc_uq unique deferrable initially immediate)
SYS> /
Table created.
SYS> insert into inventory values (1,'A');
1 row created.
SYS> insert into inventory values (1,'B');
insert into inventory values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.PARTNO_PK) violated
1 create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially deferred,
3* partdesc varchar2(35) constraint partdesc_uq unique deferrable initially deferred)
SYS> /
Table created.
SYS> insert into inventory values (1,'A');
1 row created.
SYS> insert into inventory values (1,'B');
1 row created.
SYS> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYS.PARTNO_PK) violated
Check Log Switch
SYS> select sequence#, first_time from v$log_history;
1 23-JAN-09
2 23-JAN-09
3 23-JAN-09
4 23-JAN-09
5 23-JAN-09
6 24-JAN-09
7 25-JAN-09
8 25-JAN-09
9 26-JAN-09
10 15-FEB-09
11 25-FEB-09
12 01-MAR-09
13 02-APR-09
13 rows selected.
ArchiveLog Mode
Make sure you have enough disk space
archive log list;
SYS> Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
SYS>
1* select name, log_mode,open_mode from v$database
SYS> /
ORCL NOARCHIVELOG READ WRITE
SYS> alter database archivelog;
Database altered.
SYS> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SYS>
SYS> select name, log_mode,open_mode from v$database;
ORCL ARCHIVELOG MOUNTED
DB_Recovery_Size
SYS> show parameter db_recovery_file_dest_size;
db_recovery_file_dest_size big integer 8048M
Guarantee Undo Rentention
SQL> alter tablespace undotbs1 RENTENTION GUARANTEE
SQL> alter tablespace undotbs1 RENTENTION NOGUARANTEE
Show current user
SQL> show users;
Display current database
SQL> select * from global_name;
Sqlplus Configuration
Config - $ORACLE_HOME/sqlplus/admin/glogin.sql
-- Personal settings
set pagesize 0
set linesize 190
define _editor=vi
set sqlprompt "&_user> "
-- Personal settings
set pagesize 0
set linesize 190
define _editor=vi
set sqlprompt "&_user> "
SharePoint Designer available as a free download
http://www.microsoft.com/downloads/details.aspx?FamilyID=baa3ad86-bfc1-4bd4-9812-d9e710d44f42&displaylang=en
Cannot add attachment to custom list form in SharePoint 2007
https://staceybailey.securespsites.com/blog/Lists/Posts/Post.aspx?ID=13
http://support.microsoft.com/default.aspx?scid=kb;EN-US;953271
http://msmvps.com/blogs/obts/archive/2007/04/14/802759.aspx
http://support.microsoft.com/default.aspx?scid=kb;EN-US;953271
http://msmvps.com/blogs/obts/archive/2007/04/14/802759.aspx
Schema
Varchar2 – update will use more extents
PK and unique key in Oracle will create index
Index
non unique
allow insert duplicate pk
commit ← check constraint, rollback
truncate table – DDL no rollback
bitmap index
sequence – cache default next 20 values
PK and unique key in Oracle will create index
Index
non unique
allow insert duplicate pk
commit ← check constraint, rollback
truncate table – DDL no rollback
bitmap index
sequence – cache default next 20 values
Profiles
SYS> show parameter resource;
resource_limit boolean FALSE
resource_manager_plan string
1* alter system set resource_limit=true
SYS> /
System altered.
resource_limit boolean FALSE
resource_manager_plan string
1* alter system set resource_limit=true
SYS> /
System altered.
Privileges
Object privileges
base on db object, table
grant … with grant option;
System privileges
create session
grant … with admin option;
grant to other people as well
base on db object, table
grant … with grant option;
System privileges
create session
grant … with admin option;
grant to other people as well
Users Role
SYSTEM
can connect only when database startup
cannot startup/shutdown recovery
SYSOPER
user public
startup/shutdown
complete recovery
cannot crete user/query table
can connect only when database startup
cannot startup/shutdown recovery
SYSOPER
user public
startup/shutdown
complete recovery
cannot crete user/query table
Shutdown Database
[oracle@localhost bdump]$ emctl stop dbconsole
TZ set to Asia/Macao
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.draco.com:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.
[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 29 22:06:34 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost bdump]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JAN-2009 22:08:13
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
SYS> shutdown abort;
ORACLE instance shut down.
Startup will apply redo log
TZ set to Asia/Macao
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.draco.com:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.
[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 29 22:06:34 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost bdump]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JAN-2009 22:08:13
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
SYS> shutdown abort;
ORACLE instance shut down.
Startup will apply redo log
Startup Database
[oracle@localhost bdump]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JAN-2009 22:09:01
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting //u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Log messages written to //u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.draco.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 29-JAN-2009 22:09:01
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File //u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.draco.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 29 22:09:11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost bdump]$ emctl start dbconsole
TZ set to Asia/Macao
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.draco.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ............................ started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl/sysman/log
[oracle@localhost bdump]$
SYS> startup nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
SYS>
use parameter file
start smon and other processes
not use control file
SYS> alter database mount;
Database altered.
Use control file
If start DB first, then listener, DB has to wait for timeout to connect to listener. Vice versa, DB will register listener automatically.
In nomount/mount mode, data dictionary is not available because Data file has not been read
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JAN-2009 22:09:01
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting //u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Log messages written to //u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.draco.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 29-JAN-2009 22:09:01
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File //u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.draco.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 29 22:09:11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost bdump]$ emctl start dbconsole
TZ set to Asia/Macao
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.draco.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ............................ started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl/sysman/log
[oracle@localhost bdump]$
SYS> startup nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
SYS>
use parameter file
start smon and other processes
not use control file
SYS> alter database mount;
Database altered.
Use control file
If start DB first, then listener, DB has to wait for timeout to connect to listener. Vice versa, DB will register listener automatically.
In nomount/mount mode, data dictionary is not available because Data file has not been read
Oracle Default Port
Listener – 1521
iSqlplus – 5560
DB Console - 1158
2nd DB Console – 5500
3rd DB Console – 5501
iSqlplus – 5560
DB Console - 1158
2nd DB Console – 5500
3rd DB Console – 5501
Check Oracle Process Running
oracle@localhost bdump]$ ps -ef | grep _orcl
oracle 2828 12099 2 21:43 pts/2 00:00:18 /u01/app/oracle/product/10.2.0/db_1/jdk/bin/java -server -Xmx256M -XX:MaxPermSize=96m -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 -Doracle.home=/u01/app/oracle/product/10.2.0/db_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl/sysman -DEMSTATE=/u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.draco.com_orcl/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.draco.com_orcl/config/java2.policy -Djava.security.properties=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/10.2.0/db_1 -Ddisable.checkForUpdate=true -Djava.awt.headless=true -jar /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.draco.com_orcl/config/server.xml
oracle 3843 1 3 21:56 ? 00:00:00 ora_j000_orcl
oracle 3847 11527 0 21:56 pts/1 00:00:00 grep _orcl
oracle 12099 1 0 17:54 pts/2 00:00:02 /u01/app/oracle/product/10.2.0/db_1/perl/bin/perl /u01/app/oracle/product/10.2.0/db_1/bin/emwd.pl dbconsole /u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl/sysman/log/emdb.nohup
oracle 16935 1 0 17:59 ? 00:00:00 ora_pmon_orcl
oracle 16937 1 0 17:59 ? 00:00:00 ora_psp0_orcl
oracle 16939 1 0 17:59 ? 00:00:00 ora_mman_orcl
oracle 16941 1 0 17:59 ? 00:00:03 ora_dbw0_orcl
oracle 16943 1 0 17:59 ? 00:00:08 ora_lgwr_orcl
oracle 16945 1 0 17:59 ? 00:00:03 ora_ckpt_orcl
oracle 16947 1 0 17:59 ? 00:00:06 ora_smon_orcl
oracle 16949 1 0 17:59 ? 00:00:00 ora_reco_orcl
oracle 16951 1 0 17:59 ? 00:00:03 ora_cjq0_orcl
oracle 16953 1 0 17:59 ? 00:00:05 ora_mmon_orcl
oracle 16955 1 0 17:59 ? 00:00:00 ora_mmnl_orcl
oracle 16957 1 0 17:59 ? 00:00:00 ora_d000_orcl
oracle 16959 1 0 17:59 ? 00:00:00 ora_s000_orcl
oracle 16996 1 0 17:59 ? 00:00:00 ora_qmnc_orcl
oracle 17103 1 0 17:59 ? 00:00:02 ora_q000_orcl
oracle 31941 1 0 21:04 ? 00:00:00 ora_q002_orcl
oracle 2828 12099 2 21:43 pts/2 00:00:18 /u01/app/oracle/product/10.2.0/db_1/jdk/bin/java -server -Xmx256M -XX:MaxPermSize=96m -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 -Doracle.home=/u01/app/oracle/product/10.2.0/db_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl/sysman -DEMSTATE=/u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.draco.com_orcl/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.draco.com_orcl/config/java2.policy -Djava.security.properties=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/10.2.0/db_1 -Ddisable.checkForUpdate=true -Djava.awt.headless=true -jar /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.draco.com_orcl/config/server.xml
oracle 3843 1 3 21:56 ? 00:00:00 ora_j000_orcl
oracle 3847 11527 0 21:56 pts/1 00:00:00 grep _orcl
oracle 12099 1 0 17:54 pts/2 00:00:02 /u01/app/oracle/product/10.2.0/db_1/perl/bin/perl /u01/app/oracle/product/10.2.0/db_1/bin/emwd.pl dbconsole /u01/app/oracle/product/10.2.0/db_1/localhost.draco.com_orcl/sysman/log/emdb.nohup
oracle 16935 1 0 17:59 ? 00:00:00 ora_pmon_orcl
oracle 16937 1 0 17:59 ? 00:00:00 ora_psp0_orcl
oracle 16939 1 0 17:59 ? 00:00:00 ora_mman_orcl
oracle 16941 1 0 17:59 ? 00:00:03 ora_dbw0_orcl
oracle 16943 1 0 17:59 ? 00:00:08 ora_lgwr_orcl
oracle 16945 1 0 17:59 ? 00:00:03 ora_ckpt_orcl
oracle 16947 1 0 17:59 ? 00:00:06 ora_smon_orcl
oracle 16949 1 0 17:59 ? 00:00:00 ora_reco_orcl
oracle 16951 1 0 17:59 ? 00:00:03 ora_cjq0_orcl
oracle 16953 1 0 17:59 ? 00:00:05 ora_mmon_orcl
oracle 16955 1 0 17:59 ? 00:00:00 ora_mmnl_orcl
oracle 16957 1 0 17:59 ? 00:00:00 ora_d000_orcl
oracle 16959 1 0 17:59 ? 00:00:00 ora_s000_orcl
oracle 16996 1 0 17:59 ? 00:00:00 ora_qmnc_orcl
oracle 17103 1 0 17:59 ? 00:00:02 ora_q000_orcl
oracle 31941 1 0 21:04 ? 00:00:00 ora_q002_orcl
Oracle Architecture
SPFile – binary file written to and read by the DB. Must not be edited manually. Default filename is $ORACLE_HOME/dbs/spfile
SYS> show parameter spfile;
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileorcl.ora
Text initialization parameter file – read by DB but not written to by the server. Default name is init
SYS> create pfile from spfile;
File created.
[oracle@localhost dbs]$ pwd
//u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@localhost dbs]$ ls -lrt
total 52
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-rw---- 1 oracle oinstall 1544 Jan 23 18:33 hc_orcl.dat
-rw-rw---- 1 oracle oinstall 24 Jan 23 18:33 lkORCL
-rw-r----- 1 oracle oinstall 1536 Jan 23 21:38 orapworcl
-rw-r--r-- 1 oracle oinstall 56 Jan 24 11:50 alert_orcl.log
-rw-r--r-- 1 oracle oinstall 1052 Feb 15 15:03 initorcl.ora
-rw-r----- 1 oracle oinstall 3584 Feb 15 15:04 spfileorcl.ora
[oracle@localhost dbs]$
Password file - $ORACLE_HOME/dbs/orapworcl
connect hr/hr@orcl, this will check the password in the system tablespace data dictionary
Control file – store database file location, redo log location, checkpoint. Save each file to separate disk for redundancy
Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5 (RHEL5)
Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5 (RHEL5)
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php
In this article I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5). The article is based on a server installation similar to this, with a minimum of 2G swap, secure Linux disabled and the following package groups installed:
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Development Libraries
Development Tools
Legacy Software Development
Server Configuration Tools
Administration Tools
Base
Legacy Software Support
System Tools
X Window System
Alternative installations may require more packages to be loaded, in addition to the ones listed below.
Download Software
Download the following software:
Unpack Files
Unzip the files:
unzip 10201_database_linux32.zip
You should now have a single directory containing installation files. Depending on the age of the download this may either be named "db/Disk1" or "database".
Hosts File
The /etc/hosts file must contain a fully qualified name for the server:
Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:
#kernel.shmall = 2097152 #kernel.shmmax = 2147483648 kernel.shmmni = 4096 # semaphores: semmsl, semmns, semopm, semmni kernel.sem = 250 32000 100 128 #fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default=262144 net.core.rmem_max=262144 net.core.wmem_default=262144 net.core.wmem_max=262144
Run the following command to change the current kernel parameters:
/sbin/sysctl -p
Add the following lines to the /etc/security/limits.conf file:
* soft nproc 2047 * hard nproc 16384 * soft nofile 1024 * hard nofile 65536
Add the following line to the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:
SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.
Setup
Install the following packages:
# From RedHat AS5 Disk 1 cd /media/cdrom/Server rpm -Uvh setarch-2* rpm -Uvh make-3* rpm -Uvh glibc-2* rpm -Uvh libaio-0* cd / eject # From RedHat AS5 Disk 2 cd /media/cdrom/Server rpm -Uvh compat-libstdc++-33-3* rpm -Uvh compat-gcc-34-3* rpm -Uvh compat-gcc-34-c++-3* rpm -Uvh gcc-4* rpm -Uvh libXp-1* cd / eject # From RedHat AS5 Disk 3 cd /media/cdrom/Server rpm -Uvh openmotif-2* rpm -Uvh compat-db-4* cd / eject
Create the new groups and users:
groupadd oinstall groupadd dba groupadd oper useradd -g oinstall -G dba oracle passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir -p /u01/app/oracle/product/10.2.0/db_1 chown -R oracle.oinstall /u01
Login as root and issue the following command:
xhost +
Edit the /etc/redhat-release file replacing the current release information (Red Hat Enterprise Linux Server release 5 (Tikanga)) with the following:
redhat-4
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME ORACLE_SID=TSH1; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi
Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=:0.0; export DISPLAY
Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:
./runInstaller
During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.
Post Installation
Edit the /etc/redhat-release file restoring the original release information:
Red Hat Enterprise Linux Server release 5 (Tikanga)
Edit the /etc/oratab file setting the restart flag for each instance to 'Y':
TSH1:/u01/app/oracle/product/10.2.0/db_1:Y
For more information see:
Hope this helps. Regards Tim...
Back to the Top.
Subscribe to:
Posts (Atom)