|
关于数据库 启动 oracle的基本启动命令式 以命令行方式进入dos模式 c:你的当前路径>sqlplus /nolog # 以不登陆方式进入sqlplus sql>conn sys/oracle as sysdba # 以dba身份登陆数据库 sql>startup # 启动数据库 ORA-01081: 无法启动已在运行的 ORACLE --- 请首先关闭 #表示数据库已经启动 数据库startup的三个步骤：
数据库可以分布启动 sql>start nomount sql>alter database mount sql>alter database open 数据库在nomount的状态下启动的是数据库的实例： 它开始读参数文件（顺序为spfilesid.ora------spfile.ora-------initsid.ora------init.ora） 分配sga区 启动后台进程 打开altersid.log(告警文件)和trace files(追踪文件) 数据库在mount的状态下启动的是数据库的control file 通过参数文件定位并且打开控制文件，然后根据控制文件的内容获得数据文件和日志文件的名称和状态 数据库在open的状态下启动的是数据库的data file，redolog file，核对数据库的读一致性。 数据库启动的几种方法 1.startup pfile=’参数文件全路经’ # 根据参数文件启动数据库 2.startup restrict # 具备RESTRICTED SESSION权限的用户才能登陆数据库 例如： 首先以dba省份登陆数据库 sql>create user t1 identified by t1 # 建立t1用户 sql>grant restricted session,connect to t1 # 授予t1用户restricted session和connect权限 sql>create user t2 identified by t2 # 建立t2用户 sql>grant connect to t2 # 授予t2用户connect权限 关闭数据库 sql>startup restrict # 启动数据库到restrict状态 sql>conn t1/t1 # t1用户具有restricted session权限可以登陆 登陆成功 sql>conn t2/t2 # t2用户不具有restricted session权限不可以登陆 登陆失败 数据库可以在正常启动状态下更改到限制登陆状态 sql>alter system enable restricted session # 进入限制登陆状态 sql>alter system disable restricted session # 退出限制登陆状态 如果未授权restricted session权限的用户在进入限制登陆状态前已经登陆，dba可以把用户kill掉 sql>select sid,serial#,username from v$session
sql>alter system kill session ’10,10’ # 杀掉未授权的hr用户 3．startup read only/write 用户登陆到数据库就不再具有写的权限，用户可以进行查询，排序，可以使数据文件上线，下线，但是不会影响到控制文件，日志文件，跟踪文件，警告文件，审计文件的读与写。 9i中 UNDO 表空间损坏，如何恢复？
Errors in file d:\oracle\admin\cval\udump\cval_j000_1136.trc:
ORA-12012: 自动执行作业 30 出错
ORA-01115: 从文件 2 读取块时出现 IO 错误 (块 # 1287)
ORA-01110: 数据文件 2: 'D:\ORACLE\ORADATA\CVAL\UNDOTBS01.DBF'
ORA-27091: skgfqio: 无法进行 I/O 操作
ORA-27070: skgfdisp: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1117) 因为 I/O 设备错误，所以无法运行此项请求。
未归档模式。
有没有办法不重建数据库，简单一点的办法恢复？
先创建一个自动管理的回滚段,再修改oracle的参数undo_management='MANUAL',看看数据库能否正常使用,
如果可以的话,先删掉自动管理的回滚段重建就可以了.
1 删除undotbs01.dbf
2 连接到数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/esal/undotbs01.dbf'
3 查看rollback_segments
SQL> show parameter rollback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
max_rollback_segments integer 37
rollback_segments string
transactions_per_rollback_segment integer 5
4 修改初始化参数
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_retention=10800
undo_tablespace=UNDOTBS01
rollback_segments='SYSTEM'
5 启动数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/esal/undotbs01.dbf'
SQL> alter database /home/oracle/oradata/esal/undotbs01.dbf' offline;
alter database /home/oracle/oradata/esal/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database datafile '/home/oracle/oradata/esal/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> create spfile from pfile;
File created.
SQL> create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on;
create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on
*
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS1' already exists
SQL> drop tablespace undtotbs1;
drop tablespace undtotbs1
*
ERROR at line 1:
ORA-00959: tablespace 'UNDTOTBS1' does not exist
SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS01
SQL> alter system set undo_management=auto scope=both;
alter system set undo_management=auto scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set undo_management=auto scope=spfile;
alter system set undo_management=auto scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
#rollback_segments='SYSTEM'
"initxzh.ora" 99L, 2989C written
[oracle@WWW2 dbs]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jul 5 13:17:17 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/oracle@xzh as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
def
|