oracle|unix| Create | data | Database CREATE DATABASE Although it's a very ordinary job, there are also some descriptions in Oracle's reference documentation, but there are many people in the world who do not, on the one hand, because there are very few opportunities for you to do, and when there is a chance for you to do it, May also rush to do not know how to do, this article will provide you with a complete reference.
Detailed steps are as follows:
The first step: Prepare for work
1. Environment variable:
ox1> more. Profile_oracyx
# This part of the proposal provides
Path=/data4/ora817/app/oracle/product/8.1.7/bin:/data4/ora817/bin::/usr/bin:.
Oracle_base=/data4/ora817/app/oracle
Oracle_sid=oracyx
Ora_nls33=/data4/ora817/app/oracle/product/8.1.7/ocommon/nls/admin/data
oracle_home=/data4/ora817/app/oracle/product/8.1.7
# This part is based on your environment, but some parameters are best written out when the database is built,
# such as: Ld_library_path, so that they will look more clearly.
Ld_library_path=/data4/ora817/app/oracle/product/8.1.7/lib:/usr/lib
Tns_admin=/data4/ora817/app/oracle/product/8.1.7/network/admin
user=ora817
nls_lang=american_america.zhs16cgb231280
logname=ora817
Ora_db=/data4/ora817/app/oracle/product/8.1.7/dbs
term=vt100
home=/data4/ora817
oracle_term=vt100
Shell=/bin/ksh
display=ox1:0.0
2. parameter file
ox1> pwd
/data4/ora817/app/oracle/product/8.1.7/dbs
ox1> CP Init.ora Initoracyx.ora
Ox1> VI Initoracyx.ora
#--------------------------BEGIN---------------------------------------------------------
# db_name is the only parameter that needs to be provided at the time the library is built,
# but usually the following parameters are partially adjusted
db_name = "Oracyx"
instance_name = Oracyx
Service_names = Oracyx
Control_files = ('/data4/ora817/oradata/oracyx/control1.ctl ', '/data2/oradata/or
Acyx/control2.ctl ', '/data5/oradata/oracyx/control3.ctl ')
open_cursors = 1000
Max_enabled_roles = 40
Db_block_buffers = 18800
Shared_pool_size = 204800000
Java_pool_size = 0
Log_checkpoint_interval = 1000000000
log_checkpoint_timeout = 1800
processes = 2000
Log_buffer = 2048000
Timed_statistics = True
Max_dump_file_size = 10000
Log_archive_start = True
log_archive_dest_1 = "Location=/data5/archive/oracyx"
Log_archive_format = Arch_%t_%s.arc
Global_names = False
Background_dump_dest =/data4/ora817/app/oracle/admin/oracyx/bdump
Background_core_dump = Partial
Core_dump_dest =/data4/ora817/app/oracle/admin/oracyx/cdump
Shadow_core_dump = None
User_dump_dest =/data4/ora817/app/oracle/admin/oracyx/udump
Db_block_size = 8192
Remote_login_passwordfile = Exclusive
Os_authent_prefix = ""
distributed_transactions = 1500
compatible = "8.1.7.0.0"
Sort_area_size = 1024000
Sort_area_retained_size = 1024000
Db_files = 400
Db_file_multiblock_read_count = 16
Parallel_max_servers = 8
lm_ress=900000
lm_locks=1500000
Session_cached_cursors = 20
Transactions_per_rollback_segment = 10
Db_block_lru_latches = 6
Instance_number = 1
Resource_limit=true
#--------------------------End-----------------------------------------------------------
Notably, the file name of the Oracle password file is strictly regulated: orapw[sid]. The default lookup order is: Find Orapwsid first, then find ORAPW, if there are two databases on the same machine, there must be one to indicate the SID for the sake of distinction.
Also, don't forget to confirm that Sqlnet.ora includes the following:
Sqlnet.authentication_services = (None)
Of course, you can also use the password file, then this step can be omitted.
4, Prepare the catalogue:
Oracle is really stupid and won't build subdirectories on its own, so we have to build the directory of all the data files before we start to build the library, and here are the directories:
/data2/oradata/oracyx
/data3/oradata/oracyx
/data4/ora817/oradata/oracyx/
/data5/oradata/oracyx
/data6/oradata/oracyx
/data7/oradata/oracyx
/data4/ora817/app/oracle/admin/oracyx/bdump
/data4/ora817/app/oracle/admin/oracyx/udump
/data4/ora817/app/oracle/admin/oracyx/cdump
Step two: Start building a library
1, the establishment of basic database files:
In this section, you will build a basic database file, including control file, system data file and online redo log file. The specific script is as follows:
CREATE DATABASE "Oracyx"
Controlfile Reuse
Maxdatafiles 254
Maxinstances 8
Maxloghistory 100
Maxlogmembers 2
Maxlogfiles 64
Character Set zhs16cgb231280
National Character Set zhs16cgb231280
DataFile '/data4/ora817/oradata/oracyx/system.dbf '
SIZE 240M Reuse Autoextend off
Disconnect
Spool off
Exit
Eof
#---------------------------------End----------------------------------------------------
2, create other table space
This section first makes appropriate modifications to the system tablespace, and then starts creating additional tablespaces, which are detailed in the following script:
REM ********** ALTER SYSTEM tablespace *********
ALTER tablespace SYSTEM
DEFAULT STORAGE (
INITIAL 640K NEXT 640K minextents 1
Maxextents Unlimited pctincrease 0);
ALTER tablespace SYSTEM MINIMUM EXTENT 64K;
REM ********** tablespace for tools**********
CREATE tablespace TOOLS datafile '/data4/ora817/oradata/oracyx/tools_01.dbf '
SIZE 4000M Reuse Autoextend off
MINIMUM EXTENT 512K
DEFAULT STORAGE (INITIAL 512K NEXT 512K minextents 8 maxextents 4096);
REM ********** tablespace for INDX1 **********
CREATE tablespace INDX1 datafile '/data2/oradata/oracyx/indx1_01.dbf '
SIZE 4000M Reuse Autoextend off
MINIMUM EXTENT 512K
DEFAULT STORAGE (INITIAL 512K NEXT 512K minextents 8 maxextents 4096);
REM ********** tablespace for user**********
CREATE tablespace USER1 datafile '/data6/oradata/oracyx/user1_01.dbf '
SIZE 4000M Reuse Autoextend off
MINIMUM EXTENT 512K
DEFAULT STORAGE (INITIAL 512K NEXT 512K minextents 8 maxextents 4096);
REM ********** tablespace for TEMP **********
CREATE tablespace TEMP datafile '/data7/oradata/oracyx/temp_01.dbf '
SIZE 4000M Reuse Autoextend off
MINIMUM EXTENT 512K
DEFAULT STORAGE (INITIAL 512K NEXT 512K minextents 8 maxextents 4096);
REM ********** tablespace for RBS **********
CREATE tablespace RBS datafile '/data3/oradata/oracyx/rbs1_1.dbf '
SIZE 4000M Reuse Autoextend off
MINIMUM EXTENT 512K
DEFAULT STORAGE (INITIAL 512K NEXT 512K minextents 8 maxextents 4096);
REM * * * * creating Six Rollback Segments ****************
CREATE public ROLLBACK SEGMENT rbs_01 tablespace RBS
STORAGE (INITIAL 10m NEXT 10m minextents OPTIMAL 200m);
ALTER ROLLBACK SEGMENT rbs_01 ONLINE;
CREATE public ROLLBACK SEGMENT rbs_02 tablespace RBS
STORAGE (INITIAL 10m NEXT 10m minextents OPTIMAL 200m);
ALTER ROLLBACK SEGMENT rbs_02 ONLINE;
CREATE public ROLLBACK SEGMENT rbs_03 tablespace RBS
STORAGE (INITIAL 10m NEXT 10m minextents OPTIMAL 200m);
ALTER ROLLBACK SEGMENT rbs_03 ONLINE;
CREATE public ROLLBACK SEGMENT rbs_04 tablespace RBS
STORAGE (INITIAL 10m NEXT 10m minextents OPTIMAL 200m);
ALTER ROLLBACK SEGMENT rbs_04 ONLINE;
CREATE public ROLLBACK SEGMENT rbs_05 tablespace RBS
STORAGE (INITIAL 10m NEXT 10m minextents OPTIMAL 200m);
ALTER ROLLBACK SEGMENT rbs_05 ONLINE;
CREATE public ROLLBACK SEGMENT rbs_06 tablespace RBS
STORAGE (INITIAL 10m NEXT 10m minextents OPTIMAL 200m);
ALTER ROLLBACK SEGMENT rbs_06 ONLINE;
Disconnect
Spool off
Exit
Eof
#-------------------End----------------------------------------------------
3. Create a data dictionary
In this section, you will invoke Oracle's own script to create the data dictionary. Where Catalog.sql is used to create views and system dynamic performance views relative to the system base table and their synonyms. At the same time, the script will run scripts to create other objects, primarily with a basic pl/sql environment (including PL/SQL declarations: data types, predefined exceptions, built-in processes and functions, SQL operations, etc.), auditing, import/export, Sql*loader, and installation options.
Catproc.sql is used to establish a PL/SQL usage environment. In addition, several pl/sql packages are created to extend the RDBMS functionality. The script also creates additional packages and views for some of the following RDBMS features: warnings, pipelines, log analysis, objects, large objects, advanced queues, replication options, and other built-in packages and options.
The above two are required to run, the other is based on the actual requirements, here for the use of a few to explain:
Caths.sql is used to install heterogeneous service Management Packs.
Helpbld.sql and Helpus.sql are used to create the tables that Sql*plus Help uses (to create the assist facility tables).
Another pupbld.sql, which needs to be run for a lower version, will appear with a "Product user profile does not exit" prompt at logon, pupbld.sql to build a pup (Product_user_ Profile) table to provide a product-level (Product_level) security mechanism based on user-level security.
Disconnect
Spool off
Exit
Eof
#------------------End----------------------------------------------------
4. Perfect treatment
In order to achieve better performance of the database, we also need to make further adjustments to the database, the example here is to modify the user's default table space and temporary table space, change the database for archive log mode of operation.
ALTER user sys temporary tablespace TEMP;
Alter USER system default Tablespace TOOLS;
Alter user system temporary tablespace TEMP;
Shutdown normal;
Startup Mount Pfile= '/data4/ora817/app/oracle/product/8.1.7/dbs/initoracyx.ora ';
ALTER DATABASE Archivelog;
ALTER DATABASE open;
Alter system archive log start;
alter system switch logfile;
Disconnect
Spool off
Exit
Eof
#------------------End----------------------------------------------------
Summarize:
Okay, so we're going to set up a new database, and I wonder if you're clear about the process right now? In our database of the logical structure are very familiar with the situation, the building process needs to focus on the physical storage of the database, and this and our specific application-related, not one or two words can intercede, I hope we can discuss together ...
Diao Chan
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
