oracle 笔记(续1)

oracle工具如下：
Oracle Universal Installer(OUI):Used to install,upgrade,or remove software components，Based on a Java engine.
Oracle Database Configuration Assistant:A grphical user interface tool that interacts with the OUI,or can be used independently,to create,delete,or modify a database.
SQL*PLUS:A utility to access data in an Oracle database.
Oracle Enterprise Manager:A graphical interface used to administer,monitor,and tune one or more databases.
1.Oracle Universal Installer
2.Database Configuration Assistant.
3.Database Upgrade Assistant
4.Oracle Net Manager
5.Oracle Enterprise Manager
6.SQL*PLUS
7.Recovery Manager
8.Oracle Secure Backup
9.Data Pump
10.SQL*Loader

Starting the OUI:
To start Oracle Universal Installer on UNIX:$ ./runInstaller(必须在图形界面运行，命令行界面运行不了，还有最好不要用root用户安装，新建一个oracle用户去运行此程序。./ 是因为linux系统是在path找命令)
To start Oracle Universal Installer on NT:Start > Programs > Oracle Installation > Products > Universal Installer
Non-Interactive Installation(非交互式安装，需要有一个交互文件)。
Allows for no user interaction,Response files:Templates must be edited,Text files contain variables and values,Parameters are customized.
To start Universal Installer in non-interactive mode: ./runInstaller -responsefile myrespfile -silent.(在runInstaller同级目录中有个response目录，里面有response文件模版，可以参考)

Oracle DBCA：
You use the Oracle Database Configuration Assistant to :Create a database,Configure database options,Delete a database,Manage templates.
DB Administrator Users:
Users SYS and SYSTEM are create automatically,During database creation,Granted the DBA role.
User SYS,Owner of the database data dictionary,Default password:change_on_install(从9.2.0版本后就不设置缺省口令了)
User SYSTEM,Owner of additional internal tables and views used by Oracle tools,default password:manager.

SQL*PLUS - IMPORTANT!!!!!!!!
An Oracle tool providing:Capability to interact with and manipulate the database,Ability to start up and shut down the database,create and run queries,add rows,modify data,and write customized reports.
A subset of the standard SQL language with specific add ons.
Connecting to SQL*PLUS:
$sqlplus /nolog
sql>connect / as sysdba
 Connected to an idle instance
sql>startup --启动数据库
sql>select * from v$sga_dynamic_components;
$lsncrl start --启动监听(一般情况都是先启动监听，在去启动DB)

Initializetion Parameter Files:
Entries are specific to the instance being started.Two types of parameter:Explicit(显示的):Having an entry in the file,Implicit(隐式的):No entry within the file,but assuming the Oracle default values.Multiple initialization parameter files can exist.Changes to entries int file take effect based on the type of initialization parameter file used:Static parameter file,PFILE,Persistent server parameter file,SPFILE.

sql>show parameter sga;--查看parameter以sga开头的

PFILE --initSID.ora:
Text file,Modified with an operating system editor,Modifications made manually,Changes take effect on the next start up,Only opened during instance start up,Default location is $ORACLE_HOME/dbs.
$env | grep ORACLE_HOME --查看ORACLE_HOME环境变量
ORACLE_SID和SID(Site Identifier,站点标示符)
    tom大师语录>>If you're unfamiliar with the term SID or ORACLE_SID,a full definition is called for.The SID is a site identifier.It and ORACLE_HOME(where the Oracle software is installed) are hashed together in UNIX  to create a unique key name for attaching an SGA.If your ORACLE_SID or ORACLE_HOME is not set correctly,you'll get the ORACLE NOT AVAILABLE error,since you can't attach to a shared memory segment that is indentified by this unique key.On Windows,shared memory isn't used in the same fashion as UNIX,but the SID is still important.You can have more than one database on the same ORACLE_HOME,so you need a way to uniquely identify each one,along with their configuration files.

init{ORACLE_SID}.ora参数文件中的参数：db_name,db_block_size,control_file参数。
SPFILE-spfileSID.ora:Binary file,Maintained by the Oracle server,Always resides on the server side(其存储在服务器上，避免了pfile存储在客户端的弊端),Ability to make changes persistent across shut down and start up,Can self-tune parameter values,Can have Recovery Manager support backing up to the initialization parameter file.
Creating an SPFILE：
(1)sql > create spfile='$ORACLE_HOME/dbs/spfile{ORACLE_SID}.ora' from pfile='$ORACLE_HOME/dbs/init{ORACLE_SID}.ora';
(2)sql > create spfile from pfile;
可以在instance启动前或者instance启动后创建。
$string spife | more oracle --查看二进制文件。

Modify parameters in spfile
Alter xtsystem set parameter=value --deferred参数是指下次启动时候改变起作用。
sql>alter system set fast_start_mttr_target=250;--修改参数，内存和spfile参数文件中此参数值都改变，默认是both。如果是pfile的话，只能改memory中的参数值。
sql>alter system set fast_start_mttr_target=250 comment='hello world';--comment选项是加注释
sql>desc v$parameter;--存储参数的v$视图
sql>alter system set undo_tablespace=undo2;
sql>alter system reset undo_suppress_errors scope=both sid='*';--reset为缺省值。注意参数选项，学着查看联机文档。
spfile和pfile比较：
1：An spfile can be backed-up with RMAN(RMAN cannot backup pfile)
2:Reduce human errors.The spfile is maintained by the server.Parameters are checked before changes are accepted.
3:Eliminate configuration problems(no need to have a local pfile if you want to start Oracle from a remote machine)
4:Easy to find --stored in a central location.

Order of precedence:spfileSID.ora>Default spfile(spfile.ora)>initSID.ora>default pfile
只能指定pfile启动DB：startup pfile=$ORACLE_HOME/dbs/initDBA1.ora
如果想用spfile，可以在pfile文件中写：spfile=/database/startup/spfileDBA1.ora这样就是通过pfile找到了spfile。如下：
$mv spfileding.ora test --移动当前文件夹中的spfile到test中
$rm -f initding.ora --删除当前文件夹的pfile
$cp test/spfileding.ora ./spfileabc.ora --从test中copy spfile
$vi ding.ora--创建一个pfile
spfile=$ORACLE_HOME/dbs/spfileabc.ora--编辑pfile引用spfile
$sqlplus /nolog
sql>conn / as sysdba
sql>startup pfile=$ORACLE_HOME/dbs/ding.ora--这样就以pfile启动oracle instance了，所用的pfile中引用了spfile。

Starting Up a Database
shutdown nomount mount open 四个状态(状态不可逆，只进不退)
$sqlplus / as sysdba
sql> conn / as sysdba
sql>!ps -ef | grep oracle --这个时候可以看到启动了一个连接进程
sql>!ipcs --可以看到没有分配内存
sql>startup nomount--(读取参数文件，启动instance(分配内存和启动后台进程)到nomount状态)
sql>!ipcs--现在可以看到共享内存了，但是数据库只是nomount状态
sql>show parameter db_name;
sql>show parameter db_block_size;--到现在只能看到一部分参数
sql>alter database mount;--mount就是把一个数据库和instance挂接起来，Oracle Database then reads the control files to get the names of the database's datafiles and redo log files.现在数据库还是关闭状态，只有管理员可以访问控制，如做一些备份恢复等维护工作，普通用户不能访问。
sql>alter database open;--All files opened as described by the control file for this instance.具体过程去读文档。

sql>alter database db01 mount;--from nomount to mount
sql>alter database db01 open read only;--启动数据库到只读状态

Use the startup command to restrict access to a database:
sql> startup restrict;--受限模式，特权用户可以用
Use the alter system command to place an instance in restricted mode:
sql> alter system enable restricted session;--受限模式

sql>create user ding identified by ding;--创建用户
sql>grant connect,resource to ding;--授权用户
sql>alter system enable restricted session;--打开受限模式
，这时普通用户就连不上了
sql>select saddr,sid,serial#，username from V$session;--查看session
sql>alter system kill session 'sid,serial#';--踢出用户，其中sid和serial#应该替换为上面sql语句中查询到的具体值。

打开为只读模式
startup mount.   alter database open read only.--这个模式中用户只能查询，不能修改
sql>startup mount;
sql>alter database open read only;--打开为只读模式

关闭数据库：Close Database：写sga数据到数据文件和日志文件，关闭在线数据文件和日志文件，at this point,the database is closed and inaccessible for normal operations.The control files remain open after a database is closed bu still mounted。Unmount a Database：这时instance还在。Shut Down an Instance：关闭实例，收回sga和终止后台进程。

关闭数据库：
shutdown abort：相当断电
shutdown immdeiate：最常用，强制终止会话，事务，回滚和提交
shutdown transactional：等待事务提交。
shutdown normal：只是不允许新连接，会等待会话，等待事务，不强制checkpoint。
一致性关闭(干净关闭)：shutdown normal/transactional/immediate.
非一致性关闭(不干净关闭):shutdown abort / instance failure/startup force.
sql>select addr,status from v$transaction;--查看当前系统有没有活动的事务。

Diagnostic Files:
          Contain information about significant events encountered,Used to resolve problems,Used to better manage the database on a day-to-day basis. Several types exist:alertSID.log file,Background trace files,User trace files.
    Background>SID_processname_PID.trc(db01_qmn0_3783.trc)(每个进程都会有个trace文件)
User Trace Files>SID_ora_PID.trc(db01_ora_3627.trc)
      Alert Log File>alertSID.log file:Records the commands,Records results of major events,Used for day-to-day operational information,Used for diagnosing database errors. Each entry has a time stamp associated with it,Must be managed by DBA,Location defined by BACKGROUND_DUMP_DEST.
Background Trace Files>
        Log errors detected by any background process,Are used to diagnose and troubleshoot errors.Create when a background process encounters an error.Location defined by BACKGROUND_DUMP_DEST.(有错误时候才写相关进程trace文件)
User trace files>
        produced by the user process,Can be generated by a server process,Contain statistics for traced SQL statements,Contain user error messages.Created when a user encounters user session errors.Location is defined by USER_DUMP_DEST.Size defined by MAX_DUMP_FILE_SIZE.
Enable/Disable User Tracing>
        (1)Session level:Using the alter session command:alter session set sql_trace=true(如果在当前会话执行了此语句，sql>show parameter sql_trace--&gt查询到的值还是false，但是set语句已经执行成功了，只是查的时候不显示true)
Executing DBMS procedure:dbms_system.set_sql_trace_in_session.
        (2)Instance level:Setting the initialization parameter:sql_trace=true.一般是在session上设置，如果在instance上设置的话，每个用户都产生trace文件这样在用户量大的时候会挤爆硬盘。一般不再instance上打开sql_trace。

  如果spfile或者pfile丢了：可以通过alert日志文件创建参数文件。把里面的参数copy出来，该加单引号的加单引号，该加括号的加括号--这样一个参数文件就创建好了。
sql>select username,account_status from dba_users;--查看所有用户状态。
sql>alter user hr account unlock;--解锁hr用户。
sql>alter user hr identified by hr;--设置密码。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------(待续2)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29117696/viewspace-1072658/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29117696/viewspace-1072658/