FAQ of oracle8

最新推荐文章于 2021-04-04 08:13:50 发布

一起学IT技能

最新推荐文章于 2021-04-04 08:13:50 发布

阅读量663

点赞数

本文链接：https://blog.csdn.net/ququhouse/article/details/8802288

版权

1.1.1 EXP导出报错：EXP-00091

[问题描述]

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user OPS$ZBIASCP

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user OPS$ZBIASCP

About to export OPS$ZBIASCP's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export OPS$ZBIASCP's tables via Conventional Path ...

. . exporting table ABNORMALCHARGE

0 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table ACCESSSERVICEKEY

0 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table ACCITEMPROPERTY

0 rows exported

[问题分析]

问题产生的原因就是，导出的时候客户端的语言环境变量NLS_LANG和服务端的字符集设置不一致导致的；

日志中也有显示：

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

如何查看服务端和客户端的字符集呢？

查看服务端字符集：

SQL> select userenv('language') from dual;

查看oracle client端的字符集：（在unix平台下，就是环境变量NLS_LANG）

$echo $NLS_LANG

出现问题环境查看：
<21 rx66i84vm1 [oracle] :/opt/oracle>sqlplus / as sysdba

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

<2 rx66i84vm1 [zbiascp] :/home/zbiascp>echo $NLS_LANG

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

[解决方法]

找到NLS_LANG设置的地方，然后修改其值SIMPLIFIED CHINESE_CHINA.ZHS16GBK

为AMERICAN_AMERICA.AL32UTF8，保持客户端与服务端一致性；

1.1.2 如何统计一个表占用的数据库空间大小

[统计方法]

select segment_name,sum(bytes) FROM dba_segments WHERE owner ='USERDB' and segment_type ='TABLE' AND segment_name = 'TS_PRODUCTORDER';

select segment_name,sum(bytes) FROM dba_segments WHERE owner ='USERDB' and segment_type ='INDEX' AND segment_name = '%TS_PRODUCTORDER%';

SQL> col SEGMENT_NAME format a30;

SQL> select segment_name,sum(bytes)/1024 Space_K FROM dba_segments WHERE owner ='USERDB' and segment_type ='TABLE' group by segment_name order by Space_M DESC;

SEGMENT_NAME SPACE_K

------------------------------ ----------

TS_PRODORDERATTR 4096

TB_INTERACTLOG 3072

OCS_SUBSCRIBER_EXT 2048

TS_PRODUCTORDER 2048

TB_PRDSRVLOG 2048

CBE_SUBSCRIBER 2048

U_UVS_BASETAB 2048

CUSTOMER 960

ACCOUNT 640

NUMSERVICE 512

OCS_BFIRSTORDERHIS 448

1.1.3 如何设置Oracle以spfile替代pfile启动

[问题描述]

判断Oracle是从spfile还是pfile启动，可以通过下面SQL来判断：
SQL>select isspecified,count(*) from v$spparameter group by isspecified;
ISSPEC COUNT(*)
---------- --------
FALSE 294 --如果全是False说明是从pfile启动，如果既有true又有false说明是从spfile启动。

查看spfile路径参数配置：

1] % sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 24 17:17:07 2010

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

配置为空，而执行如下命令之后，修改spfile参数，也就不会生效；因为在spfile路径没有配置情况下，默认使用$ORACLE_HOME/dbs/initora11g.ora，这个文件是无法动态修改的；

[配置方法]

1)修改文件：$ORACLE_HOME/dbs/initora11g.ora
使文件中只包含一行：
SPFILE='/dev/vx/rdsk/vgora/lv_spfile';

2)SQLplus中执行创建SPfile.
sqlplus "/as sysdba"
SQL> create spfile='/dev/vx/rdsk/vgora/lv_spfile' from pfile.
SQL> shutdown immediate;
SQL> startup;

[spfile启动的好处]

spfile是一个二进制文件,支持动态修改Oracle参数,并能够同时在文件中修改,而pfile做为启动文件时,是不允许动态修改Oracle属性的;

1.1.4 oracle补丁版本号查看方法

opatch所在路径：$ORACLE_HOME/OPatch/

oracle@bmp1_1:~> opatch lsinventory -all

Invoking OPatch 11.1.0.8.1

Oracle Interim Patch Installer version 11.1.0.8.1

Oracle Home : /oracle/app/product/11g/db

Central Inventory : /oracle/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.8.1

OUI version : 11.1.0.7.0

OUI location : /oracle/app/product/11g/db/oui

Log file location : /oracle/app/product/11g/db/cfgtoollogs/opatch/opatch2010-05-24_09-05-41AM.log

Patch history file: /oracle/app/product/11g/db/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle/app/product/11g/db/cfgtoollogs/opatch/lsinv/lsinventory2010-05-24_09-05-41AM.txt

--------------------------------------------------------------------------------

List of Oracle Homes:

Name Location

OraDbHome1 /oracle/app/product/11g/db

Installed Top-level Products (2):

Oracle Database 11g 11.1.0.6.0

Oracle Database 11g Patch Set 1 11.1.0.7.0

There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch 9209238 : applied on Thu May 20 19:26:28 GMT+07:00 2010

Unique Patch ID: 12054701

Created on 7 Jan 2010, 01:47:50 hrs PST8PDT

1.1.1 机器无法安装oracle

错误提示信息如下：

监听无法启动，一启动就会产生core文件

请检查安装版本是否和操作系统版本的位数一致

1.1.2 非归档模式和归档模式之前的切换

1、sqlplus /nolog;

2、conn / as sysdba;

3、shutdown immediate;

4、startup mount;

执行完以后的步骤后，就要开始进行归档模式与非归档模式之间的转换了：

归档->非归档

5、alter database noarchivelog;

6、alter database archivelog;

7、archive log list;

检查是否成功：

在进行模式转换的时候会出现" ORA-00265: 要求实例恢复, 无法设置 ARCHIVELOG 模式"这样的错误，用以下方法可以轻松解决：

首先，关闭数据库：

shutdown immediate;

其次，以限制模式的方式启动数据库：

startup restrict;

接着，按照下面的步骤完成即可：(以下就是模式转换了)

shutdown immediate;

startup mount;

alter database archivelog(或者alter database noarchivelog);

archive log list;

注：

数据库运行在限制模式有时是很有用的，如要维护数据库的结构，或对数据库进行导入和导出等。可以以限制模式来启动数据库，这样就只有具有restricted session系统权限的用户才可以登陆数据库。

1.1.3 Ora-01589错误的处理方法

数据库强制停止后，启动报错，redolog文件损坏；经过重新创建控制文件后，再次启动，就报如下错误：

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 5478576128 bytes

Fixed Size 2163368 bytes

Variable Size 3472887128 bytes

Database Buffers 1979711488 bytes

Redo Buffers 23814144 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 1102326

Session ID: 1105 Serial number: 17

Oracle数据库联机日志文件丢失处理方法

试验一：用命令清空日志组方法

1、查看原来表中数据

SQL>; conn test/test

Connected.

SQL>; select * from test;

TEL

----------

2、插入新数据

SQL>; insert into test values(4);

1 row created.

SQL>; commit;

Commit complete.

SQL>;

3、正常关闭数据库

4、利用os command删除所有redo文件

5、启动数据库

SQL>; startup

ORACLE instance started.

Total System Global Area 353862792 bytes

Fixed Size 730248 bytes

Variable Size 285212672 bytes

Database Buffers 67108864 bytes

Redo Buffers 811008 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'

6、查看当前日志状态

SQL>; select * from v$log;

看来redo01.log不是当前日志，对于这类非当前日志可以直接clear,系统会重新自动生成一个redo文件

7、SQL>; alter database clear logfile group 1;

Database altered.

7、继续启动db

SQL>; alter database open;

alter database open

ERROR at line 1:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'

8、看来redo也得恢复，但是redo02是当前redo,直接clear是不行的

SQL>; alter database clear logfile group 2;

alter database clear logfile group 2

ERROR at line 1:

ORA-00350: log 2 of thread 1 needs to be archived

ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'

尝试clear unarchived logfile group ,报错：

SQL>; alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

ERROR at line 1:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

看来他是因为找不到这个文件，从有效的备份中cp一个过来看看

SQL>; host cp /T3/ORACLE/oradatabak/redo02* /T3/ORACLE/oradata/ORA9

SQL>; alter database clear unarchived logfile group 2;

Database altered.

搞定……….

9、按照oracle的某些做法也是可以的

SQL>; alter database clear unarchived logfile group 1 unrecoverable datafile;

Database altered.

10、但是对于非当前日志就都可以，下面看看redo03

SQL>; alter database clear logfile group 3;

Database altered.

结论：

如果数据库是正常shutdown,非当前日志都可以直接clear来重新生成，而且不丢失数据，因为正常关闭db,数据已经写入dbf文件了。唯独当前日志不可以，当前日志必须首先从有效的备份中拷贝一个日志文件过来，然后用

alter database clear unarchived logfile group n 或alter database clear unarchived logfile group n,除此之外，还可以用下面的方法来做

方法二：用cancel模式恢复数据库

前面的出错提示，步骤都一样，唯独恢复的方法不一样

SQL>; startup

ORACLE instance started.

Total System Global Area 353862792 bytes

Fixed Size 730248 bytes

Variable Size 285212672 bytes

Database Buffers 67108864 bytes

Redo Buffers 811008 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'

看看丢失了哪些redo

SQL>; host ls /T3/ORACLE/oradarta/ORA9/redo*

/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory

看来redo都丢了

直接recover

SQL>; recover database until cancel;

Media recovery complete.

这个时候redo还没有生成

SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*

/T3/ORACLE/oradata/ORA9/redo*: No such file or directory

启动数据库

SQL>; alter database open ;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL>; alter database open resetlogs;

Database altered.

（注意，这里必须用resetlogs,否则会错误的

SQL>; alter database open noresetlogs;

alter database open noresetlogs

ERROR at line 1:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'

SQL>;

Resetlogs其实就是根据控制文件让系统自动重新生成redo,如果noresetlog的话，就不会重新生成redo,缺少了文件，db自然无法启动）

SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*

/T3/ORACLE/oradata/ORA9/redo01.log /T3/ORACLE/oradata/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/redo03.log

SQL>;

检验

SQL>; select * from test.test;

TEL

----------

SQL>;

数据一点儿都没有丢失

结论：

如果数据库是正常关闭的，用recover database until cancel可以轻松恢复或者说重新建立所有的redo，不再区分是否是当前日志，而且由于正常关闭，不会丢失任何数据，唯一可能丢失的情况就是如果日志还没有归档

这种恢复方法由于要resetlogs,所以在恢复完成后，日志清零，以前的备份不再起作用，所以建议立即备份

SQL>; archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /T3/ORACLE/arch

Oldest online log sequence 0

Next log sequence to archive 1

Current log sequence 1

SQL>;

实验三：通过重新生成控制文件来恢复redo

前面的都一样，只是处理方法不一样

SQL>; startup

ORACLE instance started.

Total System Global Area 353862792 bytes

Fixed Size 730248 bytes

Variable Size 285212672 bytes

Database Buffers 67108864 bytes

Redo Buffers 811008 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'

SQL>; alter database backup controlfile to trace;

Database altered.

SQL>; shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>;

2、修改一下刚才生成的那个文件

CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS ARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log' SIZE 100M,

GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log' SIZE 100M,

GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log' SIZE 100M

-- STANDBY LOGFILE

DATAFILE

'/T3/ORACLE/oradata/ORA9/system01.dbf',

'/T3/ORACLE/oradata/ORA9/undotbs01.dbf',

'/T3/ORACLE/oradata/ORA9/cwmlite01.dbf',

'/T3/ORACLE/oradata/ORA9/drsys01.dbf',

'/T3/ORACLE/oradata/ORA9/example01.dbf',

'/T3/ORACLE/oradata/ORA9/indx01.dbf',

'/T3/ORACLE/oradata/ORA9/odm01.dbf',

'/T3/ORACLE/oradata/ORA9/tools01.dbf',

'/T3/ORACLE/oradata/ORA9/users01.dbf',

'/T3/ORACLE/oradata/ORA9/xdb01.dbf',

'/T3/ORACLE/oradata/ORA9/test01.dbf'

CHARACTER SET ZHS16GBK

;

另存为一个脚本，运行他

SQL>; @clone.sql

Control file created.

SQL>; alter database open resetlogs;

Database altered.

SQL>;

搞定……………

结论：这种方法的关键是重新创建控制文件，后面的步骤和前面的道理一样的

前面的三种方法都是假设db是正常关闭的，数据已经写入数据库文件中，所以不会由数据存在redo种，所以clear的话也不会有数据丢失

方法四：修改系统参数方法

1、插入数据

SQL>; select * from test;

TEL

----------

SQL>; insert into test values(5);

1 row created.

SQL>; commit;

Commit complete.

SQL>;

2、强行关闭

SQL>; shutdown abort

ORACLE instance shut down.

SQL>;

3、手工模拟删除redo

4、启动db

SQL>; startup

ORACLE instance started.

一起学IT技能

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
FAQ of oracle8

1.1.1 EXP导出报错：EXP-00091[问题描述]Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing
复制链接

扫一扫