解决ORA-01031错误

来源：互联网发布：达内科技陪网络营编辑：程序博客网时间：2024/05/02 02:14

--快过年了同事都闪了呵呵我还在坚守岗位
--有时间就做做练习——用同事机器(之前用administrator建的用户xep 并属于administrator组远程操作)
c:/>show user
USER 为"SCOTT"
c:/>create table te (id number,name varchar2(128),describe varchar2(30));

表已创建。

1.c:/>insert into te select object_id,object_name,'test' from user_objects;
--最好用sys_objects 行数比较多
c:/>desc user_segments;
名称是否为空? 类型
----------------------------------------- -------- -----------------------

SEGMENT_NAME                                       VARCHAR2(81)
PARTITION_NAME                                     VARCHAR2(30)
SEGMENT_TYPE                                       VARCHAR2(18)
TABLESPACE_NAME                                    VARCHAR2(30)
BYTES                                              NUMBER
BLOCKS                                             NUMBER
EXTENTS                                            NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENTS                                        NUMBER
MAX_EXTENTS                                        NUMBER
PCT_INCREASE                                       NUMBER
FREELISTS                                          NUMBER
FREELIST_GROUPS                                    NUMBER
BUFFER_POOL                                        VARCHAR2(7)

2.c:/>select * from user_segments where segment_name like 'TE';

SEGMENT_NAME
---------------------------------------------------------------------------

PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ -------------------------

     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
---------- ---------- ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_
----------- ------------ ---------- --------------- -------
TE
                               TABLE              USERS
     65536         16          1          65536                       1
2147483645                       1               1 DEFAULT
--可能我们的数据会不一样（blocks,bytes..等）
--重复执行 1. 用2.查看变化
--大概插入了约600多万行数据时机器挂了 shutdown 也不好用了只好重启机器了
--启动后出现以下情况
SQL> conn sys/sys as sysdba
ERROR:
ORA-01031: insufficient privileges
查看 %oracle_home/NETWORK/ADMIN/sqlnet% SQLNET.AUTHENTICATION_SERVICES= (NTS) --os认证
--baidu一下
connect / as sysdba is current (Oracle 8i, and later) syntax which replaces the older connect internal syntax, and has exactly the same function.

This syntax is used to connect to the Oracle database with very powerful privileges. In particular, it allows the user to stop and start the database.

When it is used, Oracle does not check the password held for the user in the database's data-dictionary but instead checks that the current operating system userid is defined in the ORA_DBA group (if the OS is Windows NT/2000) or the "dba" group (if the OS is Unix). If the user is in this group, then he is allowed to connect. If he isn't in this group, he will get a ORA-01031: insufficient privileges error message, or an Enter password prompt.

The reason the data dictionary password is not used is that Oracle needs some mechanism of checking that the user is sufficiently authorised to start the database which works even when the database is down. Being a member of this group is sufficient proof that you are authorised to stop and start the database, since only the system administrator (Administrator on Windows, root on Unix) can add users to this group.

The connect / as sysdba technique can therefore be used as a method of logging in as SYS or SYSTEM when you've forgotten both their passwords: Use connect / as sysdba to connect as SYS, (assuming that your operating system userid is in the Windows ORA_DBA or Unix dba group), then change the SYS and SYSTEM passwords.
--原来远程登录的用户没有在dba用户组中手动加上就好

SQL> conn sys/sys as sysdba
已连接。
SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             247466860 bytes
Database Buffers          360710144 bytes
Redo Buffers                2940928 bytes
数据库装载完毕。
ORA-16038: 日志 2 序列号 81 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO02.LOG'

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------------------------

IS_
---
3 ONLINE
D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO03.LOG
NO

2 ONLINE
D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO02.LOG
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------------------------

IS_
---

1 STALE ONLINE
D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO01.LOG
NO

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
第 1 行出现错误:
ORA-00262: 当前日志 1 (关闭线程 1 的日志) 无法切换
ORA-00312: 联机日志 1 线程 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO01.LOG'
ORA-00350: 日志 2 (实例 mydb 的日志, 线程 1) 需要归档
ORA-00312: 联机日志 2 线程 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO02.LOG'

SQL> shutdowm immediate
SP2-0734: 未知的命令开头 "shutdowm i..." - 忽略了剩余的行。

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             247466860 bytes
Database Buffers          360710144 bytes
Redo Buffers                2940928 bytes
数据库装载完毕。
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
第 1 行出现错误:
ORA-00262: 当前日志 1 (关闭线程 1 的日志) 无法切换
ORA-00312: 联机日志 1 线程 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO01.LOG'
ORA-00350: 日志 2 (实例 mydb 的日志, 线程 1) 需要归档
ORA-00312: 联机日志 2 线程 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/MYDB/REDO02.LOG'

SQL> alter database clear unarchived logfile group 2;

数据库已更改。

SQL> alter database clear unarchived logfile group 3;

数据库已更改。

SQL> startup
ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它
SQL> alter database open;

数据库已更改。

--查询表中数据表内数据为空因为一直没commit 上面把未归档的文件也都清了属正常