Oracle 学习笔记之一

手工建库

oracle@suse:~> export ORACLE_BASE=/opt/oracle
oracle@suse:~> export ORACLE_HOME=/opt/oracle/ora920
oracle@suse:~> export ORACLE_SID=test
oracle@suse:~> export ORACLE_TERM=xterm
oracle@suse:~> export LANG=zh_CN.GB18030
oracle@suse:~> NLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG
oracle@suse:~> DATA_HOME=/data; export DATA_HOME

oracle@suse:~> orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=10 password=test123
create folder
oracle@suse:~> mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{bdump,cdump,udump,pfile,create,logs}
oracle@suse:~> ls $ORACLE_BASE/admin/$ORACLE_SID
cdump create bdump logs pfile udump
oracle@suse:~>mkdir -p $DATA_HOME/test/archive

oracle@suse:~> vi $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora
*.background_dump_dest='/opt/oracle/admin/test/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/data/test/control01.ctl','/data/test/control02.ctl','/data/test/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/test/cdump'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_cache_size=218103808
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=318767104
*.db_name='test'
*.db_recycle_cache_size=486539264
*.db_writer_processes=4
*.disk_asynch_io=true
*.dml_locks=10000
*.enqueue_resources=10000
*.fast_start_mttr_target=300
*.filesystemio_options='asynch'
*.global_names=FALSE
*.hash_area_size=10485760
*.hash_join_enabled=TRUE
*.instance_name='test'
*.java_pool_size=52428800
*.job_queue_processes=10
*.large_pool_size=52428800
######################Archive mode###############
*.log_archive_dest_1='location=/data/test/archive'
*.log_archive_dest_2='service=DBSTANDBY reopen=300'
*.log_archive_format='arch_%t_%s.arc'
*.log_archive_start=TRUE
##############################################
*.log_buffer=1048576
*.log_checkpoints_to_alert=TRUE
*.open_cursors=2000
*.open_links=30
*.parallel_adaptive_multi_user=true
*.parallel_automatic_tuning=true
*.parallel_max_servers=8
*.parallel_min_servers=4
*.pga_aggregate_target=524288000
*.processes=500
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=300
*.sga_max_size=1782579200
*.shared_pool_size=486539264
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/test/udump'
*.utl_file_dir='/opt/oracle/admin/test/logs'
*.workarea_size_policy='auto'


oracle@suse:~>sqplplus "/ as sysdba"
SQL>startup pfile='$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora' nomount
启动有问题请查看bdump,udump下的trc信息。

vi $ORACLE_BASE/admin/$ORACLE_SID/creadte/createdb.sql
CREATE DATABASE test
LOGFILE
GROUP 1 ('$DATA_HOME/test/log01_01.log') SIZE 50M,
GROUP 2 ('$DATA_HOME/test/log02_01.log') SIZE 50M,
GROUP 3 ('$DATA_HOME/test/log03_01.log') SIZE 50M,
GROUP 4 ('$DATA_HOME/test/log04_01.log') SIZE 50M,
GROUP 5 ('$DATA_HOME/test/log05_01.log') SIZE 50M
DATAFILE '$DATA_HOME/test/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M MAXSIZE 32767M
extent management local
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '$DATA_HOME/test/temp01.dbf' SIZE 1000M
AUTOEXTEND ON NEXT 10M MAXSIZE 32767M
UNDO TABLESPACE UNDOTBS1 datafile '$DATA_HOME/test/undotbs01.dbf' SIZE 2000M AUTOEXTEND ON NEXT 10M
MAXSIZE 32767M
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16;

SQL>@/u01/oracle/admin/sid/create/createdb.sql

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/catclust.sql
SQL> alter user system identified by systempwd;
SQL> conn system/systempwd;
SQL>@?/sqlplus/admin/pupbld.sql(以system身份登陆)
SQL>conn / as sysdba;
SQL>create spfile from pfile='$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora' ;
*****pfile中有Archive mode可省以下操作
SQL>alter system set log_archive_format='arch_%t_%s.arc' scope=spfile;
SQL>alter system set log_archive_dest_1='location=/data/sid/archive';
SQL>alter system set log_archive_start=true scope=spfile;
*****
SQL>shutdown immediate;
SQL> conn / as sysdba;
SQL>startup mount;
SQL>alter database archivelog/noarchivelog;
SQL>alter system archive log start;
SQL>alter database open;
SQL>alter system archive log current;

# LISTENER.ORA Network Configuration File: /u01/oracle/ora920/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryDB )(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/ora920)
(SID_NAME = test)
)
)