I am studying Oracle 9i on my own. I have never studied Oracle before, and I am having lots of doubts. I bought the Oracle Press Database Fundamentals I guide, but I guess it's for experenced DBAs. I want to create a database manually using personal Oracle 9i on Windows 2000. The following are some of my doubts:
1) How to configure envionment settings -- the book says some items needed to be set up in the machine configuration file or user configuration files. How do I go about it if I already have a database.
2) How to decide on your instance identifier (DB_NAME AND SID) before creating the database manually?
3) Where can I find catalog.sql and catproc.sql, and how do I run them?
4) For creating a database, do I have to connect as sys or system user?
5) How do I take backups?
I will be glad if you help me with my queries, and could you please suggest to me any books for beginners like me?
1)You answer to this question all depends on your platform. The Oracle documentation contains a section on platform specific information. I highly suggest you read this material to answer this question.
2) If you don't want to change anything, you can go with the default SID of "ORCL". The DB_NAME should be the same as the SID.
In the real world, we tend to give the SID a meaningful name. In our installation, we name the SID something meaningful which describes the database function. Then, we append one of the following: "P" for production databases, "D" for development databases, and "T" for test databases.
3) CATALOG and CATPROC are in the $ORACLE_HOME/rdbms/admin directory. You can run them by signing on to the database as SYS and issuing the following commands in SQL*Plus:
If you use the Database Configuration Assistant (DBCA) to create your database (highly recommended), then it will run these scripts for you.
4) You must connect as SYS AS SYSDBA to create the database. Or, you can CONNECT / AS SYSDBA. This is the same thing. You cannot connect as SYSTEM and create the database.
5) Backups and recovery is a complex topic that requires lots of study. The simplest thing to do is to bring down the database (SHUTDOWN IMMEDIATE), then backup the file system(s) that contain the database datafiles. You can use whatever backup method you want.
Here are a few books that I would recommend for beginners. Both can be found on Oracle Press:
Oracle Complete Reference by Koch & Loney
Oracle DBA Handbook by Loney
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.