Step : 1 Calculate total Size of tablespace
SELECT sum(bytes)/1024/1024 "TOTAL SIZE (MB)"
FROM dba_Data_files
Step : 2 Calculate Free Space in Tablespace
SELECT sum(bytes)/1024/1024 "FREE SPACE (MB)"
FROM dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
SELECT t2.total "TOTAL SIZE",
t1.free "FREE SPACE",
(t1.free/t2.total)*100 "FREE (%)" ,
(1-t1.free/t2.total)*100 "USED (%)"
FROM (SELECT sum(bytes)/1024/1024 free FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total FROM dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
CREATE TABLE db_growth
AS
SELECT *
FROM(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE% "
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
INSERT INTO db_growth
SELECT *
FROM (
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
CREATE VIEW v_db_growth
AS SELECT *
FROM
(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)
Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
INSERT INTO db_growth select *
FROM v_db_growth
Step : 8 Check everything goes fine.
SELECT * FROM db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
ALTER SESSION SET nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.submit(
jobno, 'BEGIN INSERT INTO db_growth SELECT * FROM v_db_growth;COMMIT:END;', sysdate, 'SYSDATE+ 24', TRUE);
COMMIT;
END;
PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
SELECT * FROM user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
EXEC DBMS_JOB.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 12 Finally all data populated in db_growth table
SELECT * FROM db_growth
Monday, September 22, 2008
Tuesday, August 26, 2008
How to Drop All Objects in a Schema in Oracle 10g
Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.
If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects
Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:
purge recyclebin;
This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:
select * from user_objects
If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects
Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:
purge recyclebin;
This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:
select * from user_objects
Tuesday, July 22, 2008
Importing the whole User Schema (All Objects Included) in SQL Plus from one user to another user with different name
Before Importing Create a user
SQL> create user jretail1 identified by jretail*****;
User created.
Grant privileges to the user
SQL> grant resource,connect,create view to jretail1;
Grant succeeded.
Then start the import process
SQL> $imp jretail1@test file=c:\jretail1120am.dmp log=c:\jretail1120am.log fromuser=jretail touser=jretail1;
Import: Release 10.1.0.2.0 - Production on Thu Oct 22 14:58:32 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by JRETAIL, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "ACCOUNTMASTER" 3203 rows imported
. . importing table "ACCOUNTOPENING" 4345 rows imported
. . importing table "AGEINGMASTER" 6 rows imported
. . importing table "APPLICATIONCONFIGURATION" 2 rows imported
. . importing table "BILLWISEADJUSTMENT" 462 rows imported
. . importing table "BILLWISEADJUSTMENTSCHEDULE" 462 rows imported
. . importing table "BUDGETMASTERDETAIL" 4 rows imported
. . importing table "BUDGETMASTERHEADER" 1 rows imported
. . importing table "CALENDARMASTER" 4 rows imported
. . importing table "CALENDARMONTHDETAIL" 0 rows imported
. . importing table "CHITMASTER" 10 rows imported
. . importing table "CHITMEMBER" 850 rows imported
. . importing table "CHITMEMBERINSTALLMENTDETAIL" 7107 rows imported
. . importing table "CHITRECEIPTDETAIL" 6351 rows imported
. . importing table "ITEMTYPEATTRIBUTESDETAIL" 2 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL>
SQL> create user jretail1 identified by jretail*****;
User created.
Grant privileges to the user
SQL> grant resource,connect,create view to jretail1;
Grant succeeded.
Then start the import process
SQL> $imp jretail1@test file=c:\jretail1120am.dmp log=c:\jretail1120am.log fromuser=jretail touser=jretail1;
Import: Release 10.1.0.2.0 - Production on Thu Oct 22 14:58:32 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by JRETAIL, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "ACCOUNTMASTER" 3203 rows imported
. . importing table "ACCOUNTOPENING" 4345 rows imported
. . importing table "AGEINGMASTER" 6 rows imported
. . importing table "APPLICATIONCONFIGURATION" 2 rows imported
. . importing table "BILLWISEADJUSTMENT" 462 rows imported
. . importing table "BILLWISEADJUSTMENTSCHEDULE" 462 rows imported
. . importing table "BUDGETMASTERDETAIL" 4 rows imported
. . importing table "BUDGETMASTERHEADER" 1 rows imported
. . importing table "CALENDARMASTER" 4 rows imported
. . importing table "CALENDARMONTHDETAIL" 0 rows imported
. . importing table "CHITMASTER" 10 rows imported
. . importing table "CHITMEMBER" 850 rows imported
. . importing table "CHITMEMBERINSTALLMENTDETAIL" 7107 rows imported
. . importing table "CHITRECEIPTDETAIL" 6351 rows imported
. . importing table "ITEMTYPEATTRIBUTESDETAIL" 2 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL>
Monday, June 23, 2008
Importing the whole User Schema (All Objects Included) in SQL Plus from one user to another user with the same name
Before Importing Create a user
SQL> create user visynapse identified by visynapse*******;
User created.
Grant privileges to the user
SQL> grant resource,connect,create view to visynapse;
Grant succeeded.
Then start the import process
SQL> $imp visynapse@test file=E:/uploaddb_19_10_09.dmp log=E:/implogvisyn.log;
Import: Release 10.1.0.2.0 - Production on Mon Oct 19 12:50:41 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Password:*****
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing VISYNAPSE's objects into VISYNAPSE
. . importing table "CALCULATION_SECURITY" 199 rows imported
. . importing table "CALCULATION_TABLE" 207 rows imported
. . importing table "COLOUR" 1 rows imported
. . importing table "CUBE" 8 rows imported
. . importing table "DASHBOARD" 2 rows imported
. . importing table "DASHBOARD_METRIC" 8 rows imported
. . importing table "DASH_TABLE" 3 rows imported
. . importing table "DATA_DICTIONARY" 48 rows imported
. . importing table "DIMENSION_26" 295 rows imported
. . importing table "DIMENSION_27" 432 rows imported
. . importing table "DIMENSION_28" 295 rows imported
. . importing table "DIMENSION_SECURITY" 19 rows imported
. . importing table "DOMAIN" 1 rows imported
. . importing table "FILTERS" 0 rows imported
. . importing table "GRAINS" 6148 rows imported
. . importing table "HEADER_FOOTER" 0 rows imported
. . importing table "KEY_PERFORMANCE_INDICATOR" 0 rows imported
. . importing table "KEY_PERFORMANCE_INDICATOR_CS" 0 rows imported
. . importing table "KPI_DIMENSION" 0 rows imported
. . importing table "KPI_DIMENSION_CS" 0 rows imported
. . importing table "LINKMETRIC" 0 rows imported
. . importing table "METRIC" 8 rows imported
. . importing table "METRIC_CALCULATION_SET" 207 rows imported
. . importing table "METRIC_CUBE_SET" 8 rows imported
. . importing table "REPORT_CHANNEL" 0 rows imported
. . importing table "REPORT_CONFIG" 23 rows imported
. . importing table "REPORT_HEADER" 0 rows imported
. . importing table "SCORECARD" 0 rows imported
. . importing table "SELECTION_DIMENSION" 0 rows imported
. . importing table "SELECTION_FILTERCRITERIA" 0 rows imported
. . importing table "SELECTION_FORMULA" 0 rows imported
. . importing table "SELECTION_SHAREDREPORT" 0 rows imported
. . importing table "SELECTION_TABLE" 0 rows imported
. . importing table "STATIC_REPORT_TABLE" 8 rows imported
. . importing table "SUMMARY_CONFIG" 0 rows imported
. . importing table "TIME_FREQUENCY" 6 rows imported
. . importing table "USER_DASHBOARD_SECURITY" 2 rows imported
. . importing table "USER_GROUP" 1 rows imported
. . importing table "USER_PROFILES" 1 rows imported
. . importing table "USER_TARGETS" 8 rows imported
. . importing table "VALUE_SECURITY" 0 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL>
SQL> create user visynapse identified by visynapse*******;
User created.
Grant privileges to the user
SQL> grant resource,connect,create view to visynapse;
Grant succeeded.
Then start the import process
SQL> $imp visynapse@test file=E:/uploaddb_19_10_09.dmp log=E:/implogvisyn.log;
Import: Release 10.1.0.2.0 - Production on Mon Oct 19 12:50:41 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Password:*****
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing VISYNAPSE's objects into VISYNAPSE
. . importing table "CALCULATION_SECURITY" 199 rows imported
. . importing table "CALCULATION_TABLE" 207 rows imported
. . importing table "COLOUR" 1 rows imported
. . importing table "CUBE" 8 rows imported
. . importing table "DASHBOARD" 2 rows imported
. . importing table "DASHBOARD_METRIC" 8 rows imported
. . importing table "DASH_TABLE" 3 rows imported
. . importing table "DATA_DICTIONARY" 48 rows imported
. . importing table "DIMENSION_26" 295 rows imported
. . importing table "DIMENSION_27" 432 rows imported
. . importing table "DIMENSION_28" 295 rows imported
. . importing table "DIMENSION_SECURITY" 19 rows imported
. . importing table "DOMAIN" 1 rows imported
. . importing table "FILTERS" 0 rows imported
. . importing table "GRAINS" 6148 rows imported
. . importing table "HEADER_FOOTER" 0 rows imported
. . importing table "KEY_PERFORMANCE_INDICATOR" 0 rows imported
. . importing table "KEY_PERFORMANCE_INDICATOR_CS" 0 rows imported
. . importing table "KPI_DIMENSION" 0 rows imported
. . importing table "KPI_DIMENSION_CS" 0 rows imported
. . importing table "LINKMETRIC" 0 rows imported
. . importing table "METRIC" 8 rows imported
. . importing table "METRIC_CALCULATION_SET" 207 rows imported
. . importing table "METRIC_CUBE_SET" 8 rows imported
. . importing table "REPORT_CHANNEL" 0 rows imported
. . importing table "REPORT_CONFIG" 23 rows imported
. . importing table "REPORT_HEADER" 0 rows imported
. . importing table "SCORECARD" 0 rows imported
. . importing table "SELECTION_DIMENSION" 0 rows imported
. . importing table "SELECTION_FILTERCRITERIA" 0 rows imported
. . importing table "SELECTION_FORMULA" 0 rows imported
. . importing table "SELECTION_SHAREDREPORT" 0 rows imported
. . importing table "SELECTION_TABLE" 0 rows imported
. . importing table "STATIC_REPORT_TABLE" 8 rows imported
. . importing table "SUMMARY_CONFIG" 0 rows imported
. . importing table "TIME_FREQUENCY" 6 rows imported
. . importing table "USER_DASHBOARD_SECURITY" 2 rows imported
. . importing table "USER_GROUP" 1 rows imported
. . importing table "USER_PROFILES" 1 rows imported
. . importing table "USER_TARGETS" 8 rows imported
. . importing table "VALUE_SECURITY" 0 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL>
Saturday, May 10, 2008
Exporting the whole User Schema (All Objects Included) in Oracle SQL Plus
SQL> $exp visynapse@uploaddb file=E:/uploaddb_19_10_09.dmp log=E:/uploadlog.log;
Export: Release 10.1.0.2.0 - Production on Mon Oct 19 11:18:48 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Password:******
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user VISYNAPSE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user VISYNAPSE
About to export VISYNAPSE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VISYNAPSE's tables via Conventional Path ...
. . exporting table CALCULATION_SECURITY 199 rows exported
. . exporting table CALCULATION_TABLE 207 rows exported
. . exporting table COLOUR 1 rows exported
. . exporting table CUBE 8 rows exported
. . exporting table DASHBOARD 2 rows exported
. . exporting table DASHBOARD_METRIC 8 rows exported
. . exporting table DASH_TABLE 3 rows exported
. . exporting table DATA_DICTIONARY 48 rows exported
. . exporting table DEFINITIONS_21 21 rows exported
. . exporting table DEFINITIONS_22 12 rows exported
. . exporting table DEFINITIONS_23 17 rows exported
. . exporting table DEFINITIONS_24 30 rows exported
. . exporting table DEFINITIONS_25 22 rows exported
. . exporting table DEFINITIONS_26 25 rows exported
. . exporting table DEFINITIONS_27 23 rows exported
. . exporting table DEFINITIONS_28 28 rows exported
. . exporting table DIMENSION_SECURITY 19 rows exported
. . exporting table DOMAIN 1 rows exported
. . exporting table FILTERS 0 rows exported
. . exporting table GRAINS 6148 rows exported
. . exporting table HEADER_FOOTER 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR_CS 0 rows exported
. . exporting table KPI_DIMENSION 0 rows exported
. . exporting table KPI_DIMENSION_CS 0 rows exported
. . exporting table LINKMETRIC 0 rows exported
. . exporting table METRIC 8 rows exported
. . exporting table METRIC_CALCULATION_SET 207 rows exported
. . exporting table METRIC_CUBE_SET 8 rows exported
. . exporting table REPORT_CHANNEL 0 rows exported
. . exporting table REPORT_CONFIG 23 rows exported
. . exporting table REPORT_HEADER 0 rows exported
. . exporting table SCORECARD 0 rows exported
. . exporting table SELECTION_DIMENSION 0 rows exported
. . exporting table SELECTION_FILTERCRITERIA 0 rows exported
. . exporting table SELECTION_FORMULA 0 rows exported
. . exporting table SELECTION_SHAREDREPORT 0 rows exported
. . exporting table SELECTION_TABLE 0 rows exported
. . exporting table STATIC_REPORT_TABLE 8 rows exported
. . exporting table SUMMARY_CONFIG 0 rows exported
. . exporting table TIME_FREQUENCY 6 rows exported
. . exporting table USER_DASHBOARD_SECURITY 2 rows exported
. . exporting table USER_GROUP 1 rows exported
. . exporting table USER_PROFILES 1 rows exported
. . exporting table USER_TARGETS 8 rows exported
. . exporting table VALUES_28_CONTROL 196 rows exported
. . exporting table VALUE_SECURITY 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
SQL>
Export: Release 10.1.0.2.0 - Production on Mon Oct 19 11:18:48 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Password:******
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user VISYNAPSE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user VISYNAPSE
About to export VISYNAPSE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VISYNAPSE's tables via Conventional Path ...
. . exporting table CALCULATION_SECURITY 199 rows exported
. . exporting table CALCULATION_TABLE 207 rows exported
. . exporting table COLOUR 1 rows exported
. . exporting table CUBE 8 rows exported
. . exporting table DASHBOARD 2 rows exported
. . exporting table DASHBOARD_METRIC 8 rows exported
. . exporting table DASH_TABLE 3 rows exported
. . exporting table DATA_DICTIONARY 48 rows exported
. . exporting table DEFINITIONS_21 21 rows exported
. . exporting table DEFINITIONS_22 12 rows exported
. . exporting table DEFINITIONS_23 17 rows exported
. . exporting table DEFINITIONS_24 30 rows exported
. . exporting table DEFINITIONS_25 22 rows exported
. . exporting table DEFINITIONS_26 25 rows exported
. . exporting table DEFINITIONS_27 23 rows exported
. . exporting table DEFINITIONS_28 28 rows exported
. . exporting table DIMENSION_SECURITY 19 rows exported
. . exporting table DOMAIN 1 rows exported
. . exporting table FILTERS 0 rows exported
. . exporting table GRAINS 6148 rows exported
. . exporting table HEADER_FOOTER 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR_CS 0 rows exported
. . exporting table KPI_DIMENSION 0 rows exported
. . exporting table KPI_DIMENSION_CS 0 rows exported
. . exporting table LINKMETRIC 0 rows exported
. . exporting table METRIC 8 rows exported
. . exporting table METRIC_CALCULATION_SET 207 rows exported
. . exporting table METRIC_CUBE_SET 8 rows exported
. . exporting table REPORT_CHANNEL 0 rows exported
. . exporting table REPORT_CONFIG 23 rows exported
. . exporting table REPORT_HEADER 0 rows exported
. . exporting table SCORECARD 0 rows exported
. . exporting table SELECTION_DIMENSION 0 rows exported
. . exporting table SELECTION_FILTERCRITERIA 0 rows exported
. . exporting table SELECTION_FORMULA 0 rows exported
. . exporting table SELECTION_SHAREDREPORT 0 rows exported
. . exporting table SELECTION_TABLE 0 rows exported
. . exporting table STATIC_REPORT_TABLE 8 rows exported
. . exporting table SUMMARY_CONFIG 0 rows exported
. . exporting table TIME_FREQUENCY 6 rows exported
. . exporting table USER_DASHBOARD_SECURITY 2 rows exported
. . exporting table USER_GROUP 1 rows exported
. . exporting table USER_PROFILES 1 rows exported
. . exporting table USER_TARGETS 8 rows exported
. . exporting table VALUES_28_CONTROL 196 rows exported
. . exporting table VALUE_SECURITY 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
SQL>
Saturday, April 19, 2008
Procedure for Installing Redhat and installing oracle 9i on RHEL4
Part I: Installing Linux
Minimum hardware requirement:
* Pentium IV processor
* 512MB of RAM
* minimum 40GB HARD DISK
1. Boot the server using the first CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The boot screen appears with the boot: prompt at the bottom of the screen.
* Select Enter to continue with a graphical install on the console. (For other installation methods and options, refer to the Red Hat Installation Guide.)
* The installer scans your hardware, briefly displays the Red Hat splash screen, and then begins a series of screen prompts.
3. Language Selection
* Accept the default.
4. Keyboard Configuration
* Accept the default.
5. Welcome Screen
* Click on Next.
6. Disk Partitioning Setup
* Do the disk partitioning as per the following
PARTITIONS MINIMUMSIZE
/ 12GB
/boot 100MB
/opt 4GB
/oracle 10GB
/tmp 3GB
swap double the size of RAM not more than 2GB
7. Boot Loader Configuration
* Accept the default.
8. Network Configuration
* It is usually best to configure database servers with a static IP address. To do so, click on Edit .
* A pop-up window appears. Uncheck the Configure using DHCP box, and enter the IP Address and Netmask for the server. Be sure that Activate on boot is checked, and click on OK .
* In the Hostname box, select manually and enter the hostname.
* In the Miscellaneous Settings box, enter the remaining network settings.
9. Firewall Configuration
* For the purposes of this walk-through, no firewall is configured. Select No firewall
* Select Disabled on the "Enable SELinux" drop down list.
* Click on Proceed when the "Warning - No Firewall" window appears.
10. Additional Language Support
* Accept the default.
11. Time Zone Selection
* Choose the time settings that are appropriate for your area. Setting the system clock to UTC is usually a good practice for servers. To do so, click on System clock uses UTC.
12. Set Root Password
* Enter a password for root, and enter it again to confirm.
13. Package Installation Defaults
* Select Customize software packages to be installed.
14. Package Group Selection
* Select only the package sets shown here and leave all others unselected.
* Desktop
o X Window System
o Gnome
* Applications
o Graphical Internet (optional)
* Servers
o Do not select anything in this group.
* Development
o Development Tools
* System
o Administration Tools
o System Tools
+ Add the package 'sysstat' by clicking on the Details link and selecting "sysstat - The sar an iostat system monitoring commands." from the Optional Packages list.
* Miscellaneous
o Do not select anything in this group.
* Click on Next to proceed.
15. Installing Packages
* Software will be copied to the hard disk and installed. Change disks as prompted.
16. Congratulations
* Remove the installation media from the system, and click on Reboot .
17. The system automatically reboots and presents a new welcome screen.
* Click on Next.
18. License Agreement
* Read the license agreement. If you agree to the terms, select Yes, I agree to the License Agreement and click on Next.
19. Date and Time
* Set the Date and Time.
* If you want to use an NTP server (recommended), select Enable Network Time Protocol and enter the name of the NTP server.
20. Display
* Accept the defaults or change as required.
21. Red Hat Login
* Enter your Red Hat Network login and password or create a new one.
22. System User
* Create an account for yourself.
* Do not create an account for oracle at this time. Creating the oracle account is covered later in this section.
23. Additional CDs
* Click on Next.
24. Finish Setup
* Click on Next.
25. A graphical login screen appears.
26. Congratulations! Your RHEL4 software is now installed.
27. Restart the system and logged in a root user, hence keeping the system ready to install Oracle 9i Release 2 on it.
II.Installation steps of oracle 9i:
Before Installing oracle, acquire the following softwares
1.Oracle 9i release 2(9.2.0.4.0) CD 1 OF 3.
2.Oracle 9i release 2(9.2.0.4.0) CD 2 of 3.
3.Oracle 9i release 2(9.2.0.4.0) CD 3 of 3.
4.Oracle 9i release 2(9.2.0.4.0) patch CD.
5.jdk1.5 update 6.
Download Oracle9i for Linux from the following web site:
http://otn.oracle.com/software/products/oracle9i/htdocs/linuxsoft.html
Download java for linux from the following website:
http://java.sun.com
The following steps are for installing oracle 9i on RHEL4.
1.Install JDK1.5 in /usr/java folder by using the following command.
* Create folder named javasoft on the Desktop.
* Open a new terminal using the following command:
right click on the Desktop and click Open terminal from the popup menu.
* Copy jdk-1_5_0_06-linux-i586.bin from the CD to /root/Desktop/javasoft folder, using the following commands.
Insert the CD containing jdk1.5 software
#mount /media/cdrom
*cp /media/cdrom/jdk-1_5_0_06-linux-i586.bin /root/Desktop/javasoft
*create a folder named java in the /usr folder.
#mkdir -p /usr/java
*Use the following commands to install jdk1.5
*cd /usr/java
*/root/Desktop/javasoft/jdk-1_5_0_06-linux-i586.bin
follow the steps as prompted to install the software.
*Create a symbolic link "jdk1.5" for easy accessibility using the following command:
#ln -s jdk-1_5_0_06 jdk1.5
*Do the permanent path setting for writing and executing the java programs without going to this folder every time.
#vi /etc/profile
Insert the following lines at the end of the file.
JAVA_HOME=/usr/java/jdk1.5
export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH
export PATH
*save and relogin as root user.
2.Open the terminal window on the linux desktop
3.Use the following commands to go to the home directory.
#cd /home
4.Insert the oracle 9i release CD no.1/3 to the CD Drive.
5.Mount the CD by the following command:
#mount /media/cdrom
6.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom
7.Use the following command to extract the software from CD 1 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk1.cpio note: once you do this a folder named Disk1 will be created in /home directory 8.Unmount the CD using the following command:
#umount /media/cdrom
9.Insert the oracle 9i release CD no.2/3 to the CD Drive.
10.Mount the CD by the following command:
#mount /media/cdrom
11.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom
12.Use the following command to extract the software from CD 2 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk2.cpio Note: once you do this a folder named Disk2 will be created in /home directory
13.Unmount the CD using the following command:
#umount /media/cdrom
14.Insert the oracle 9i release CD no.3/3 to the CD Drive.
15.Mount the CD by the following command:
#mount /media/cdrom
16.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom
17.Use the following command to extract the software from CD 3 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk3.cpio note: once you do this a folder named Disk3 will be created in /home directory 18.Unmount the CD using the following command:
#umount /media/cdrom
19.For patch use the following commands:
#mkdir 92040
20.Insert the patch CD to the CD Drive.
21.Mount the CD using following command:
#mount /media/cdrom
22.Use the following command to unzip the files
#unzip -d /home/92040 /media/cdrom/p3006854_9204_LINUX.zip
23.After issuing the above command, it will create a folder named as 3006854 in the /home/92040 directory. please confirm the same.
24.Issue the following commands to extract the p4198954 patch.
#unzip -d /home/92040 /media/cdrom/p4198954_40_LINUX.zip
you will able to see the following rpms within the specified folder.
1) compact-orcle-rhel4-1-0-5.i386.rpm
2) compact-libcwait-2.1-1.i386.rpm
As soon as you try to install these RPM's then you will receive dependency failure.
Use the following steps to avoid dependency failure:
1)Insert the RHEL4 CD no.3 to the CD Drive.
2)Mount the CD using the following command:
#mount /media/cdrom
#cd /media/cdrom/RedHat/RPMS
3)Execute the following RPMs
#rpm -ivh --aid libaio-devel-0.3.102-1.i386.rpm
#rpm -ivh --aid openmotif21-2.1.30-11.RHEL4.2.i386.rpm
4)After successful execution of the above RPMs, execute the RPMs already unzipped from the 4198954 patch.
#cd /home/92040
#rpm -ivh compat-oracle-rhel4-1.0.5.i386.rpm
#rpm -ivh compat-libcwait-2.1-1.i386.rpm
5)unmount the 3rd CD of RHEL4 using the following command
#unmount /media/cdrom
25.Configuring the Linux Kernel Parameters, update the /etc/sysctl.conf file,by adding the following lines towards the end of the file.
#vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=26214
save the file and relogin.
26.Create the Oracle Groups and User Account:
# groupadd oinstall
# groupadd dba
# groupadd oper
# useradd -g oinstall -G dba oracle
# id oracle
Set the password on the oracle account:
# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
27.Create Directories for oracle software.
# mkdir -p /oracle/app/oracle/product/9.2.0.4.0
# chown -R oracle:oinstall /oracle
# chmod -R 775 /oracle
28.Login as the oracle user and add the following lines at the end of the .bash_profile file:
#vi .bash_profile
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4.0; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH:/usr/java/jdk1.5/bin; export PATH
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=jay; export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
ORACLE_OEM_JAVARUNTIME=/usr/java/jdk1.5; export ORACLE_OEM_JAVARUNTIME
LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
THREADS_FLAG=native; export THREADS_FLAG
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
29.Save the .bash_profile file and relogin as oracle user.
30.Make sure the .bash_profile file ran correctly by issuing the following command:
$set | more
31.Login to Root using this command:
$su -
password:rootpassword
32.Execute the 3006854 patch using the following command:
#sh /home/92040/3006854/rhel3_pre_install.sh
33.Exit from the Root.
#exit
$(After typing exit you will receive the oracle login $ prompt)
34.Start the universal installer using the following command:
$/home/Disk1/runInstaller
note: This will start the universal installer. Follow the steps in the wizard to install oracle software.
NOTE: During the installation enter the appropriate ORACLE_HOME and name then continue as normal
1. Welcome Screen:
Click Next
2.Inventory Location:
"/oracle/app/oracle/oraInventory"and Click Next
3.Unix Group Name:
Use "oinstall" and click Next
When asked to run /tmp/orainstRoot.sh, run it before you click Continue
open a new terminal,by click the desktop
$su -
password:root password
#cd /tmp
#./orainstRoot.sh
once this execution is over and you get back the prompt, switch to the dialog box in the Universal Installer and continue next.
5.File Locations:
name:use default value
path:/oracle/app/oracle/product/9.2.0.4.0
6.Available Products:
Select "Oracle9i Database 9.2.0.1.0"
7.Installation Types:
Select "Enterprise Edition(2.84GB)
8.Database configuration:
Select "General Purpose"
9.Database Identification
Global Database Name:Antony
SID:Antony
and then Click Next
10.Database File Locations:
Directory for Database Files:/oracle/app/oracle/oradata
and then click Next
11.Database Character Set:
Use the default character set
and then click Next
12.Summary:
Start the Install
13.Setup privileges:
before completing the installation the universal installer will prompt another file to be executed from the root login.
follow the sequence commands to overcome this:
open the terminal
$su -
password:root password
#cd /oracle/app/oracle/product/9.2.0.4.0
#./root.sh
it will prompt for a path, press enter to keep it default.
after that press ok in the dialog box.
14.Configuration tools:
if any error comes, Simply ignore it.
15.Database Configuration Assistant:
enter password for sys and system and click OK
16. At the end of the installation, exit runInstaller.
Minimum hardware requirement:
* Pentium IV processor
* 512MB of RAM
* minimum 40GB HARD DISK
1. Boot the server using the first CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The boot screen appears with the boot: prompt at the bottom of the screen.
* Select Enter to continue with a graphical install on the console. (For other installation methods and options, refer to the Red Hat Installation Guide.)
* The installer scans your hardware, briefly displays the Red Hat splash screen, and then begins a series of screen prompts.
3. Language Selection
* Accept the default.
4. Keyboard Configuration
* Accept the default.
5. Welcome Screen
* Click on Next.
6. Disk Partitioning Setup
* Do the disk partitioning as per the following
PARTITIONS MINIMUMSIZE
/ 12GB
/boot 100MB
/opt 4GB
/oracle 10GB
/tmp 3GB
swap double the size of RAM not more than 2GB
7. Boot Loader Configuration
* Accept the default.
8. Network Configuration
* It is usually best to configure database servers with a static IP address. To do so, click on Edit .
* A pop-up window appears. Uncheck the Configure using DHCP box, and enter the IP Address and Netmask for the server. Be sure that Activate on boot is checked, and click on OK .
* In the Hostname box, select manually and enter the hostname.
* In the Miscellaneous Settings box, enter the remaining network settings.
9. Firewall Configuration
* For the purposes of this walk-through, no firewall is configured. Select No firewall
* Select Disabled on the "Enable SELinux" drop down list.
* Click on Proceed when the "Warning - No Firewall" window appears.
10. Additional Language Support
* Accept the default.
11. Time Zone Selection
* Choose the time settings that are appropriate for your area. Setting the system clock to UTC is usually a good practice for servers. To do so, click on System clock uses UTC.
12. Set Root Password
* Enter a password for root, and enter it again to confirm.
13. Package Installation Defaults
* Select Customize software packages to be installed.
14. Package Group Selection
* Select only the package sets shown here and leave all others unselected.
* Desktop
o X Window System
o Gnome
* Applications
o Graphical Internet (optional)
* Servers
o Do not select anything in this group.
* Development
o Development Tools
* System
o Administration Tools
o System Tools
+ Add the package 'sysstat' by clicking on the Details link and selecting "sysstat - The sar an iostat system monitoring commands." from the Optional Packages list.
* Miscellaneous
o Do not select anything in this group.
* Click on Next to proceed.
15. Installing Packages
* Software will be copied to the hard disk and installed. Change disks as prompted.
16. Congratulations
* Remove the installation media from the system, and click on Reboot .
17. The system automatically reboots and presents a new welcome screen.
* Click on Next.
18. License Agreement
* Read the license agreement. If you agree to the terms, select Yes, I agree to the License Agreement and click on Next.
19. Date and Time
* Set the Date and Time.
* If you want to use an NTP server (recommended), select Enable Network Time Protocol and enter the name of the NTP server.
20. Display
* Accept the defaults or change as required.
21. Red Hat Login
* Enter your Red Hat Network login and password or create a new one.
22. System User
* Create an account for yourself.
* Do not create an account for oracle at this time. Creating the oracle account is covered later in this section.
23. Additional CDs
* Click on Next.
24. Finish Setup
* Click on Next.
25. A graphical login screen appears.
26. Congratulations! Your RHEL4 software is now installed.
27. Restart the system and logged in a root user, hence keeping the system ready to install Oracle 9i Release 2 on it.
II.Installation steps of oracle 9i:
Before Installing oracle, acquire the following softwares
1.Oracle 9i release 2(9.2.0.4.0) CD 1 OF 3.
2.Oracle 9i release 2(9.2.0.4.0) CD 2 of 3.
3.Oracle 9i release 2(9.2.0.4.0) CD 3 of 3.
4.Oracle 9i release 2(9.2.0.4.0) patch CD.
5.jdk1.5 update 6.
Download Oracle9i for Linux from the following web site:
http://otn.oracle.com/software/products/oracle9i/htdocs/linuxsoft.html
Download java for linux from the following website:
http://java.sun.com
The following steps are for installing oracle 9i on RHEL4.
1.Install JDK1.5 in /usr/java folder by using the following command.
* Create folder named javasoft on the Desktop.
* Open a new terminal using the following command:
right click on the Desktop and click Open terminal from the popup menu.
* Copy jdk-1_5_0_06-linux-i586.bin from the CD to /root/Desktop/javasoft folder, using the following commands.
Insert the CD containing jdk1.5 software
#mount /media/cdrom
*cp /media/cdrom/jdk-1_5_0_06-linux-i586.bin /root/Desktop/javasoft
*create a folder named java in the /usr folder.
#mkdir -p /usr/java
*Use the following commands to install jdk1.5
*cd /usr/java
*/root/Desktop/javasoft/jdk-1_5_0_06-linux-i586.bin
follow the steps as prompted to install the software.
*Create a symbolic link "jdk1.5" for easy accessibility using the following command:
#ln -s jdk-1_5_0_06 jdk1.5
*Do the permanent path setting for writing and executing the java programs without going to this folder every time.
#vi /etc/profile
Insert the following lines at the end of the file.
JAVA_HOME=/usr/java/jdk1.5
export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH
export PATH
*save and relogin as root user.
2.Open the terminal window on the linux desktop
3.Use the following commands to go to the home directory.
#cd /home
4.Insert the oracle 9i release CD no.1/3 to the CD Drive.
5.Mount the CD by the following command:
#mount /media/cdrom
6.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom
7.Use the following command to extract the software from CD 1 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk1.cpio note: once you do this a folder named Disk1 will be created in /home directory 8.Unmount the CD using the following command:
#umount /media/cdrom
9.Insert the oracle 9i release CD no.2/3 to the CD Drive.
10.Mount the CD by the following command:
#mount /media/cdrom
11.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom
12.Use the following command to extract the software from CD 2 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk2.cpio Note: once you do this a folder named Disk2 will be created in /home directory
13.Unmount the CD using the following command:
#umount /media/cdrom
14.Insert the oracle 9i release CD no.3/3 to the CD Drive.
15.Mount the CD by the following command:
#mount /media/cdrom
16.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom
17.Use the following command to extract the software from CD 3 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk3.cpio note: once you do this a folder named Disk3 will be created in /home directory 18.Unmount the CD using the following command:
#umount /media/cdrom
19.For patch use the following commands:
#mkdir 92040
20.Insert the patch CD to the CD Drive.
21.Mount the CD using following command:
#mount /media/cdrom
22.Use the following command to unzip the files
#unzip -d /home/92040 /media/cdrom/p3006854_9204_LINUX.zip
23.After issuing the above command, it will create a folder named as 3006854 in the /home/92040 directory. please confirm the same.
24.Issue the following commands to extract the p4198954 patch.
#unzip -d /home/92040 /media/cdrom/p4198954_40_LINUX.zip
you will able to see the following rpms within the specified folder.
1) compact-orcle-rhel4-1-0-5.i386.rpm
2) compact-libcwait-2.1-1.i386.rpm
As soon as you try to install these RPM's then you will receive dependency failure.
Use the following steps to avoid dependency failure:
1)Insert the RHEL4 CD no.3 to the CD Drive.
2)Mount the CD using the following command:
#mount /media/cdrom
#cd /media/cdrom/RedHat/RPMS
3)Execute the following RPMs
#rpm -ivh --aid libaio-devel-0.3.102-1.i386.rpm
#rpm -ivh --aid openmotif21-2.1.30-11.RHEL4.2.i386.rpm
4)After successful execution of the above RPMs, execute the RPMs already unzipped from the 4198954 patch.
#cd /home/92040
#rpm -ivh compat-oracle-rhel4-1.0.5.i386.rpm
#rpm -ivh compat-libcwait-2.1-1.i386.rpm
5)unmount the 3rd CD of RHEL4 using the following command
#unmount /media/cdrom
25.Configuring the Linux Kernel Parameters, update the /etc/sysctl.conf file,by adding the following lines towards the end of the file.
#vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=26214
save the file and relogin.
26.Create the Oracle Groups and User Account:
# groupadd oinstall
# groupadd dba
# groupadd oper
# useradd -g oinstall -G dba oracle
# id oracle
Set the password on the oracle account:
# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
27.Create Directories for oracle software.
# mkdir -p /oracle/app/oracle/product/9.2.0.4.0
# chown -R oracle:oinstall /oracle
# chmod -R 775 /oracle
28.Login as the oracle user and add the following lines at the end of the .bash_profile file:
#vi .bash_profile
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4.0; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH:/usr/java/jdk1.5/bin; export PATH
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=jay; export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
ORACLE_OEM_JAVARUNTIME=/usr/java/jdk1.5; export ORACLE_OEM_JAVARUNTIME
LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
THREADS_FLAG=native; export THREADS_FLAG
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
29.Save the .bash_profile file and relogin as oracle user.
30.Make sure the .bash_profile file ran correctly by issuing the following command:
$set | more
31.Login to Root using this command:
$su -
password:rootpassword
32.Execute the 3006854 patch using the following command:
#sh /home/92040/3006854/rhel3_pre_install.sh
33.Exit from the Root.
#exit
$(After typing exit you will receive the oracle login $ prompt)
34.Start the universal installer using the following command:
$/home/Disk1/runInstaller
note: This will start the universal installer. Follow the steps in the wizard to install oracle software.
NOTE: During the installation enter the appropriate ORACLE_HOME and name then continue as normal
1. Welcome Screen:
Click Next
2.Inventory Location:
"/oracle/app/oracle/oraInventory"and Click Next
3.Unix Group Name:
Use "oinstall" and click Next
When asked to run /tmp/orainstRoot.sh, run it before you click Continue
open a new terminal,by click the desktop
$su -
password:root password
#cd /tmp
#./orainstRoot.sh
once this execution is over and you get back the prompt, switch to the dialog box in the Universal Installer and continue next.
5.File Locations:
name:use default value
path:/oracle/app/oracle/product/9.2.0.4.0
6.Available Products:
Select "Oracle9i Database 9.2.0.1.0"
7.Installation Types:
Select "Enterprise Edition(2.84GB)
8.Database configuration:
Select "General Purpose"
9.Database Identification
Global Database Name:Antony
SID:Antony
and then Click Next
10.Database File Locations:
Directory for Database Files:/oracle/app/oracle/oradata
and then click Next
11.Database Character Set:
Use the default character set
and then click Next
12.Summary:
Start the Install
13.Setup privileges:
before completing the installation the universal installer will prompt another file to be executed from the root login.
follow the sequence commands to overcome this:
open the terminal
$su -
password:root password
#cd /oracle/app/oracle/product/9.2.0.4.0
#./root.sh
it will prompt for a path, press enter to keep it default.
after that press ok in the dialog box.
14.Configuration tools:
if any error comes, Simply ignore it.
15.Database Configuration Assistant:
enter password for sys and system and click OK
16. At the end of the installation, exit runInstaller.
Monday, February 11, 2008
Creating and Maintaining a Password File in Oracle 10g
You can create a password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.
This section contains the following topics:
*
Using ORAPWD
*
Setting REMOTE_LOGIN_ PASSWORDFILE
*
Adding Users to a Password File
*
Maintaining a Password File
See Also:
*
"Using Password File Authentication"
*
"Selecting an Authentication Method for Database Administrators"
Using ORAPWD
The syntax of the ORAPWD command is as follows:
ORAPWD FILE=filename [ENTRIES=numusers]
[FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]
Command arguments are summarized in the following table.
Argument Description
FILE Name to assign to the password file. See your operating system documentation for name requirements. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
ENTRIES (Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE (Optional) If y, permits overwriting an existing password file.
IGNORECASE (Optional) If y, passwords are treated as case-insensitive.
NOSYSDBA (Optional) For Data Vault installations. See the Data Vault installation guide for your platform for more information.
There are no spaces permitted around the equal-to (=) character.
The command prompts for the SYS password and stores the password in the created password file.
Example
The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.
orapwd FILE=orapworcl ENTRIES=30
ORAPWD Command Line Argument Descriptions
The following sections describe the ORAPWD command line arguments.
FILE
This argument sets the name of the password file being created. You must specify the full path name for the file. If you supply only a file name, the file is written to the current directory. The contents of this file are encrypted, and the file cannot be read directly. This argument is mandatory.
The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. For name and location information for the Unix and Linux operating systems, see Administrator's Reference for UNIX-Based Operating Systems. For Windows, see Platform Guide for Microsoft Windows. For other operating systems, see your operating system documentation.
If you are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instance should point to the same password file.
Caution:
It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.
ENTRIES
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.
Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.
Caution:
When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.
FORCE
This argument, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to N.
IGNORECASE
If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.
See Also:
Oracle Database Security Guide for more information about case-sensitivity in passwords.
Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:
*
NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
*
EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
*
SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
This option is useful if you are administering multiple databases or a RAC database.
If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.
Note:
You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.
Adding Users to a Password File
When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.
A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.
Creating a Password File and Adding New Users to It
Use the following procedure to create a password and add new users to it:
1.
Follow the instructions for creating a password file as explained in "Using ORAPWD".
2.
Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)
Note:
REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.
3.
Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:
CONNECT SYS AS SYSDBA
4.
Start up the instance and create the database if necessary, or mount and open an existing database.
5.
Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.
Granting and Revoking SYSDBA and SYSOPER Privileges
If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:
GRANT SYSDBA TO oe;
Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM oe;
Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.
See Also:
Oracle Database Security Guide for more information on system privileges
Viewing Password File Members
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database. The columns displayed by this view are as follows:
Column Description
USERNAME This column contains the name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.
Maintaining a Password File
This section describes how to:
*
Expand the number of password file users if the password file becomes full
*
Remove the password file
Expanding the Number of Password File Users
If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and regrant the privileges to the users.
Replacing a Password File
Use the following procedure to replace a password file:
1.
Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
2.
Delete the existing password file.
3.
Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
4.
Follow the instructions in "Adding Users to a Password File".
Removing a Password File
If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform SYSDBA or SYSOPER database administration operations.
This section contains the following topics:
*
Using ORAPWD
*
Setting REMOTE_LOGIN_ PASSWORDFILE
*
Adding Users to a Password File
*
Maintaining a Password File
See Also:
*
"Using Password File Authentication"
*
"Selecting an Authentication Method for Database Administrators"
Using ORAPWD
The syntax of the ORAPWD command is as follows:
ORAPWD FILE=filename [ENTRIES=numusers]
[FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]
Command arguments are summarized in the following table.
Argument Description
FILE Name to assign to the password file. See your operating system documentation for name requirements. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
ENTRIES (Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE (Optional) If y, permits overwriting an existing password file.
IGNORECASE (Optional) If y, passwords are treated as case-insensitive.
NOSYSDBA (Optional) For Data Vault installations. See the Data Vault installation guide for your platform for more information.
There are no spaces permitted around the equal-to (=) character.
The command prompts for the SYS password and stores the password in the created password file.
Example
The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.
orapwd FILE=orapworcl ENTRIES=30
ORAPWD Command Line Argument Descriptions
The following sections describe the ORAPWD command line arguments.
FILE
This argument sets the name of the password file being created. You must specify the full path name for the file. If you supply only a file name, the file is written to the current directory. The contents of this file are encrypted, and the file cannot be read directly. This argument is mandatory.
The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. For name and location information for the Unix and Linux operating systems, see Administrator's Reference for UNIX-Based Operating Systems. For Windows, see Platform Guide for Microsoft Windows. For other operating systems, see your operating system documentation.
If you are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instance should point to the same password file.
Caution:
It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.
ENTRIES
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.
Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.
Caution:
When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.
FORCE
This argument, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to N.
IGNORECASE
If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.
See Also:
Oracle Database Security Guide for more information about case-sensitivity in passwords.
Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:
*
NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
*
EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
*
SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
This option is useful if you are administering multiple databases or a RAC database.
If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.
Note:
You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.
Adding Users to a Password File
When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.
A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.
Creating a Password File and Adding New Users to It
Use the following procedure to create a password and add new users to it:
1.
Follow the instructions for creating a password file as explained in "Using ORAPWD".
2.
Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)
Note:
REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.
3.
Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:
CONNECT SYS AS SYSDBA
4.
Start up the instance and create the database if necessary, or mount and open an existing database.
5.
Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.
Granting and Revoking SYSDBA and SYSOPER Privileges
If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:
GRANT SYSDBA TO oe;
Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM oe;
Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.
See Also:
Oracle Database Security Guide for more information on system privileges
Viewing Password File Members
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database. The columns displayed by this view are as follows:
Column Description
USERNAME This column contains the name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.
Maintaining a Password File
This section describes how to:
*
Expand the number of password file users if the password file becomes full
*
Remove the password file
Expanding the Number of Password File Users
If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and regrant the privileges to the users.
Replacing a Password File
Use the following procedure to replace a password file:
1.
Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
2.
Delete the existing password file.
3.
Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
4.
Follow the instructions in "Adding Users to a Password File".
Removing a Password File
If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform SYSDBA or SYSOPER database administration operations.
Subscribe to:
Posts (Atom)