ORA-01545: rollback segment 'string' specified not available

01545, 00000, "rollback segment '%s' specified not available"
// *Cause: Either:
//         1) An attempt was made to bring a rollback segment online that is
//            unavailable during startup; for example, the rollback segment
//            is in an offline tablespace.
//         2) An attempt was made to bring a rollback segment online that is
//            already online.  This is because the rollback segment is
//            specified twice in the ROLLBACK_SEGMENTS parameter in the
//            initialization parameter file or the rollback segment is already
//            online by another instance.
//         3) An attempt was made to drop a rollback segment that is
//            currently online.
//         4) An attempt was made to alter a rollback segment that is
//            currently online to use unlimited extents.
//         5) An attempt was made to online a rollback segment that is
//            corrupted. This is because the rollback is specified in
//            _corrupted_rollback_segments parameter in initialization
//            parameter file.
// *Action: Either:
//         1) Make the rollback segment available; for example, bring an
//            offline tablespace online.
//         2) Remove the name from the ROLLBACK_SEGMENTS parameter if the name
//            is a duplicate or if another instance has already acquired the
//            rollback segment.
//         3) Bring the rollback segment offline first. This may involve
//            waiting for the active transactions to finish, or, if the
//            rollback segment needs recovery, discover which errors are
//            holding up the rolling back of the transactions and take
//            appropriate actions.
//         4) Same as 3).
//         5) Remove the name from the _corrupted_rollback_segments parameter.

발생 OS: SunOS 5.7
Oracle db version: 8.2.7.4

1. 복구 후 DB startup 시 에러 나타남

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> startup
ORACLE instance started.

Total System Global Area  919359164 bytes
Fixed Size                   102076 bytes
Variable Size             487653376 bytes
Database Buffers          429490176 bytes
Redo Buffers                2113536 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

얼럿로그: ORA-01545: rollback segment 'RBS1' specified not available

2. init파일에서 rollback_segments 주석처리 후 open

SQL> startup
ORACLE instance started.

Total System Global Area  919359164 bytes
Fixed Size                   102076 bytes
Variable Size             487653376 bytes
Database Buffers          429490176 bytes
Redo Buffers                2113536 bytes
Database mounted.
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oradata/rbs01.dbf'

3. v$recover_file 확인해서 파일별 복구 수행후 online

SQL> select * from v$recover_file;
SQL> recover datafile 8;
ORA-00279: change 377347436 generated at 03/06/2013 22:10:36 needed for thread 1
ORA-00289: suggestion : /oradata/archive/arch_1_37864.arc
ORA-00280: change 377347436 for thread 1 is in sequence #37864
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

SQL> alter database datafile 8 online;
Database altered.

4. rbs파일인 (여기선 3번) 3번파일 offline + recover + online 수행

SQL> alter database datafile 3 offline;
Database altered.

SQL> recover datafile 3;      
Media recovery complete.

SQL> alter database datafile 3 online;
Database altered.

5. db shutdown 후 init파일 rollback_segments 주석처리 제거하여 startup 후 정상 확인

※해당 상황은 필요 아카이브 로그가 전부 있는 상황이다.

Posted by neo-orcl
,

TNS-12516

TroubleShoot 2013. 1. 24. 12:35

가끔 리스너 로그에서 아래 에러를 볼 수 있다.

TNS-12516: TNS:listener could not find available handler with matching protocol stack
TNS-12516: TNS: 리스너가 일치하는 프로토콜 스택을 가진 처리기를 찾을 수 없습니다.

TNSNAMES.ORA가 제대로 설정되어있다면 PROCESS 리소스가 전부 차버려서 리스너가 서버 프로세스를 생성할 수 없을 경우 자주 발생한다.

얼럿로그에는 기록이 남지 않는다.
select * from v$resource_limit; 해봐서 수치를 확인해본다.

Posted by neo-orcl
,

2012.10.15

ORA-00845: MEMORY_TARGET not supported on this system

###11g에서 ###
sys@ORCL> alter system set memory_target=1013m;
alter system set memory_target=1013m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

그리고 memory_max_target과 memory_target은 현재 0 임

###변경(재시작 필요)###
sys@ORCL> alter system set memory_max_target=1013m scope=spfile;
System altered.

###재시작###
sys@ORCL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system

###해결###
Cause of the Problem
10g에서는 ASMM과 PGA자동이 나뉘어 있었지만
11g부터는 AMM이 가능하다. 이 값은 SGA_target + PGA_aggregate_target 값이다.
memory_max_target 로 설정한다.
리눅스 파일 시스템에서는 공유메모리가 /dev/shm 디렉토리에 마운트되어있어야한다. 크기는 memory_target이나 memory_max_target보다 커야 한다.
ORA-00845는 리눅스시스템에서 두가지 이유로 발생할수 있다.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

###/dev/shm 조회###
[oracle@centos5 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 753M     0  753M   0% /dev/shm

###/dev/shm 설정###
1G로 잡을 것이다.

As a root user,
[root@centos5 ~]# mount -t tmpfs shmfs -o size=1g /dev/shm

###/etc/fstab 수정###
vi /etc/fstab
shmfs /dev/shm tmpfs size=1g 0 추가

###확인###
sys@ORCL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             599789400 bytes
Database Buffers          461373440 bytes
Redo Buffers                5554176 bytes
Database mounted.
Database opened.

sys@ORCL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 1G
memory_target                        big integer 0
shared_memory_address                integer     0
sys@ORCL>
 
###memory_target 설정###
sys@ORCL> alter system set memory_target=1G;

System altered.

Posted by neo-orcl
,

테스트 환경에서 일어났던 일이다.
CentOS 5.6 64bit, LVM을 안쓰고 파티션만으로 진행했던 내용.

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    820      SYSTEM               ***     /oradata/orcl/system01.dbf
2    590      UNDOTBS1             ***     /oradata/orcl/undotbs01.dbf
3    460      SYSAUX               ***     /oradata/orcl/sysaux01.dbf
4    6        USERS                ***     /oradata/orcl/users01.dbf
5    100      EXAMPLE              ***     /oradata/orcl/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    75       TEMP                 32767       /oradata/orcl/temp01.dbf

RMAN> copy datafile 1 to '/dev/raw/raw1';

Starting backup at 27-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/orcl/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/27/2012 12:58:11
ORA-19504: failed to create file "/dev/raw/raw1"
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 3
--------------------------------------------------------------------------------------
[oracle@centos5 orcl]$ du -h system*
821M    system01.dbf
--------------------------------------------------------------------------------------
[root@centos5 ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-300, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-300, default 300): +850M
--------------------------------------------------------------------------------------
???????????????????????????????????? Why! What!
나는 820MB를 보고 raw1을 850MB정도를 잡았는데 왜 공간이 부족하다고 나오는지 이해하지 못하였다.
--------------------------------------------------------------------------------------
추가로 dd 결과
[oracle@centos5 orcl]$ dd if=/oradata/orcl/system01.dbf of=/dev/raw/raw1 bs=8k 
dd: writing `/dev/raw/raw1': No space left on device
104419+0 records in
104418+0 records out
855396864 bytes (855 MB) copied, 40.0186 seconds, 21.4 MB/s
--------------------------------------------------------------------------------------
추정되는 원인: fdisk시 +850M가 가져오는 결과가 이상한 것으로 판단
850 * 1024가 아닌 850 * 1000 으로 계산하는 것 같다. MB가 아닌 M만 써있는 것과
이번 결과들이 이 추정을 뒷받침해주고 있다.

[root@centos5 ~]# fdisk /dev/sdc1
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Command (m for help): p

Disk /dev/sdc1: 855 MB, 855396864 bytes
255 heads, 63 sectors/track, 103 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

     Device Boot      Start         End      Blocks   Id  System

855MB라고 써있으나 855396864/1024/1024 = 815.77001953125 로 820mb도 되지 않는다.
-------------------------------------------------------------------------------------
그럼 850 * 1024 * 1024 = 891289600 = 891M 다시 파티션해보았다.(이전 파티션은 다 삭제완료)
byte값인 891289600으로도 진행해 보았으나 value out of range 에러 나타난다. 아니 왜????

[root@centos5 ~]# fdisk /dev/sdc

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-300, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-300, default 300): +891289600
Value out of range.
Last cylinder or +size or +sizeM or +sizeK (1-300, default 300): +890M

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

이후로 정상 성공

'TroubleShoot' 카테고리의 다른 글

ORA-01545: rollback segment 'RBS1' specified not available  (0) 2013.03.08
TNS-12516  (0) 2013.01.24
ORA-00845: MEMORY_TARGET not supported... in LINUX  (0) 2013.01.17
11.2.0.1 shutdown hang  (0) 2012.12.21
32bit<->64bit 이관 후 추가 작업  (0) 2012.12.21
Posted by neo-orcl
,

11.2.0.1 shutdown hang

TroubleShoot 2012. 12. 21. 15:16

10.2.0.1에서만 자주 나던 셧다운 행이라고 생각했는데 11.2.0.1 버젼에서 나타나서 잠깐 당황했던 기억이 있다.
그것도 자주 행났던 윈도우 환경이 아닌 리눅스 환경에서.. Centos 5대였던거 같다.

다른 세션을 열어서 트레이스 파일을 보니 어느 특정 프로세스를 계속 죽이려고 하는데 실패하고 있었다.
ksukia: Attempt 9 to re-kill process OS PID=19145.
ksukia: killed 1 out of 1 processes.

*** 2012-12-28 15:48:24.552
ksukia: Starting kill, flags = 1
ksukia: killed 0 out of 1 processes.

*** 2012-12-28 15:48:25.553
ksukia: Starting kill, flags = 1
ksukia: killed 0 out of 1 processes.

*** 2012-12-28 15:48:26.554
ksukia: Starting kill, flags = 1
ksukia: killed 0 out of 1 processes.

*** 2012-12-28 15:48:27.555
ksukia: Starting kill, flags = 1
ksukia: killed 0 out of 1 processes.

*** 2012-12-28 15:48:28.556
ksukia: Starting kill, flags = 1
ksukia: Attempt 10 to re-kill process OS PID=19145.
ksukia: killed 1 out of 1 processes.

[oracle@centos5 trace]$ ps -ef | grep 19145
oracle   19145 18844  0 15:46 ?        00:00:00 [oracle] <defunct>
oracle   19310 19271  0 15:48 pts/1    00:00:00 grep 19145
[oracle@centos5 trace]$ kill -9 19145
[oracle@centos5 trace]$ ps -ef | grep 19145
oracle   19145 18844  0 15:46 ?        00:00:00 [oracle] <defunct>
oracle   19315 19271  0 15:49 pts/1    00:00:00 grep 19145
[oracle@centos5 trace]$ ps -ef | grep 18844
oracle   18844 18736  0 15:45 pts/2    00:00:00 sqlplus   as sysdba
oracle   19145 18844  0 15:46 ?        00:00:00 [oracle] <defunct>
oracle   19208 18844  0 15:47 pts/2    00:00:00 /bin/bash
oracle   19319 19271  0 15:49 pts/1    00:00:00 grep 18844
[oracle@centos5 trace]$ kill -9 18844

이렇게 하니 접속해있던 sys 유저로 접속했던 세션은 접속이 끊기지만 오라클 shutdown이 제대로 되어서 접속 가능하게 되었다.

 

 

Posted by neo-orcl
,

기존에 어떤 사이트에서 윈도우 64bit의 DB를 윈도우 32bit로 핫백업을 통해 데이터를 이관한 적이 있다.
데이터 이관을 완료 했는데 SQLGATE라는 프로그램으로 접속을 해보면
OCI: NO_DATA 같은 내용의 에러가 떨어졌다.(정확하지는 않음)
그래서 sqlplus로 접속하려고 해보니

conn scott/tiger
ERROR:
ORA-06553: PLS-801: 내부 오류 [56327]

패키지 DBMS_APPLICATION_INFO를 액세스하는데 오류입니다
연결되었습니다.

로그인은 되는데 PLS 에러가 나타났다.
찾아보니 해결은 64bit용 PLSQL을 32bit용으로 Recomplie 해야한다는 것.

SQL> spool recom.log
SQL> startup upgrade
SQL> @?/rdbms/admin/utlrip.sql

로그를 확인해서 에러가 없다면 OK

 

Posted by neo-orcl
,