Loading...

Follow Helios's Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

I have an issue on 11.2.0.4 database (2 node RAC system) on AIX.  Our system is already around 25T and our full backup size almost 3,7Tb with using compress option.

Our number of datafiles are almost 1054.  We are taking our backup to disk using 16 channel. Total full backup duration is almost 4,5 hours.

Our backup duration became almost 7,8 hours last 3 days. I investigated backup log file and I noticed backup start 16 channels and that 16 channel backupsets job done almost 4:30 hours. After 16 channels backupsets  end,  one new channel opening and it keep taking backupsets by using 1 channel.

We investigate an issue with Oracle support. Here is the explanation of this  behavior

RMAN will try to balance the work load such that each channel processes the same amount of data.
17 backupsets are produced, each channel is processing 61-62 datafiles so you have ~ 1054 datafiles.
RMAN will initially take the no# files and divide by no# allocated channels – this gives 65.8.
The upper limit for filesperset is 64 so :1054/64 = 16.46 so rman will create 17 backupsets.
As you have 16 channels allocated the first 16 chs will process concurrently and the 17th backupset will inevitably run in isolation.

If you allocate 18 chs you will get filesperset of 59 and all 18 chs should process concurrently.

So We increased our number of allocate channels for backup and Issue has been resolved.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I have been faced with this problem on R12.1.3 2 node 11.2 RAC database on the AIX system. After closed instance 2, it started to gives ORA-17611: ksfd: file ” cannot be accessed, global open closed errors at alert.log file

After making some search We noticed that this is referring publish bug which will be fixed in12.1 (Future Release). Here is the possible solution for this error:

If the following check good, then these errors may be ignored:
1. You may ignore this error If errors are no longer occurring while the database instance is started, load tested, or during shutdowns.
2. Start up  ASM instance& mount all ASM disk groups.

SQL> ALTER DISK GROUP <disk group name> CHECK NOREPAIR;

Source:
ORA-17611 for ‘ksfd: file’ while upgrading database and during shutting down [ID 1302389.1]
Bug 8651110 – ORA-1014 / ORA-204 / ORA-202 / ORA-17611 during instance shutdown [ID 8651110.8]

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I have 11.2.0.4  2 node RAC system on AIX. Due to do security issue I can not get root password.

As you know, I need to close CRS by using crsctl stop crs commandTo can achieve run this command We need to have a root privileges user or root user’ password.

In this post I will share Which grant you need to can stop CRS instead of the Root user.

I have been made a request to can get related SUDO right for our oracle user(Owner of CRShome and DBhome).

Unix team has been made a request which paths or files should be accessed by oracle user with SUDO right to can stop and start crs.

We have been opened  Sr for our issue. Here is the answer:

Access to $GI_HOME/bin should be sufficient.

Note: if there are any issues to be reported about the Clusterware, such as startup, crs stop issues, that you need to be using root directly for supportability/diagnosis of that issue.

Also,

In future, if you going for an upgrade, make sure that rootupgrade.sh is executed as real root user – not through sudo

Referance: Things to Consider Before Upgrading to 11.2.0.3 Grid Infrastructure/ASM (Doc ID 1363369.1)

After to Unix team works, I could  stop/start CRS without any issue. The critical thing in here to do not forget to use the root user for the upgrade process.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I have faced with that issue on our 12.1.3 EBS system on AIX system with 2 node 11gR2 RAC databases.

During the full backup operation, I noticed that RMAN log mention below error message at the backup log file.

RMAN-03009: failure of backup command on ORA_DISK_3 channel at 03/29/2012 22:19:19
ORA-19566: exceeded limit of 0 corrupt blocks for file +ORADATA/PROD/datafile/apps_ts_xxcustom_data.308.752148073
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 03/29/2012 21:42:32
ORA-19566: exceeded limit of 0 corrupt blocks for file +ORADATA/PROD/datafile/apps_ts_xxcustom_data.395.741949465
RMAN-03009: failure of backup command on ORA_DISK_8 channel at 03/29/2012 22:18:24
ORA-19566: exceeded limit of 0 corrupt blocks for file +ORADATA/PROD/datafile/apps_ts_xxcustom_data.583.753881375

We have below error at alert.log:

 Hex dump of (file 440, block 1106642) in trace file /oracle11g/PROD/db/diag/rdbms/PROD/PROD1/trace/PROD1_ora_45744742.trc
 Corrupt block relative dba: 0x6e10e2d2 (file 440, block 1106642)
 Bad check value found during backing up datafile
 Data in bad block:
 type: 6 format: 2 rdba: 0x6e10e2d2
 last change scn: 0x08fc.fd857452 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x74520602
 check value in block header: 0x937c
 computed block checksum: 0x3a7
 Reread of blocknum=1106642, file=+ORADATA/PROD/datafile/apps_ts_xxcustom_data.716.771786733. found same corrupt data
 Reread of blocknum=1106642, file=+ORADATA/PROD/datafile/apps_ts_xxcustom_data.716.771786733. found same corrupt data
 Reread of blocknum=1106642, file=+ORADATA/PROD/datafile/apps_ts_xxcustom_data.716.771786733. found same corrupt data
 Reread of blocknum=1106642, file=+ORADATA/PROD/datafile/apps_ts_xxcustom_data.716.771786733. found same corrupt data
 Reread of blocknum=1106642, file=+ORADATA/PROD/datafile/apps_ts_xxcustom_data.716.771786733. found same corrupt data

After some search on Metalink and I followed Intermittent Ora-19566 When Using RMAN Backup [ID 549256.1] and then We first started to work with our storage team to can identified Issue is related with storage or not.

I noticed that issue is not with storage level. So We decided to go with SR1.

During SR session I followed below steps to identified why  I faced this corruption and how we can solve an issue in a short period.

Firstly While corruption happens Oracle started to fill in V$DATABASE_BLOCK_CORRUPTION dynamic view. So I query below view

select * from V$DATABASE_BLOCK_CORRUPTION ;

INST_ID FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
1 71 1.340.426 1 0 CHECKSUM
1 81 1.338.102 1 0 CHECKSUM
1 101 1.335.514 1 0 CHECKSUM
1 139 1.332.486 1 0 CHECKSUM
1 192 1.074.210 1 0 CHECKSUM
1 234 980.706 1 0 CHECKSUM
1 284 974.490 1 0 CHECKSUM
1 307 1.060.358 1 0 CHECKSUM
1 309 1.047.338 1 0 CHECKSUM
1 334 926.887 1 0 CHECKSUM
1 354 882.790 1 0 CHECKSUM
1 413 1.205.042 1 0 CHECKSUM
1 440 1.106.642 1 0 CHECKSUM
1 740 1.243.698 1 0 CHECKSUM

I was trying to check if the blocks are on free space or occupied by any object.

Even if the blocks were occupied by any object and if that object was dropped the block would still be in corrupted formatted unless Oracle reuses it.

If the blocks are indeed in free space then we use a document to manually format the block.

So the output of these queries is important.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#;
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1;
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

After using upper queries We run below queries:

SQL>Select file#,block# from v$database_block_corruption ;

From rman:

RMAN > Validate datafile <file no> block <Block no> ;
PS: You can use below sql's for your issues
select 'Validate datafile '|| file# || ' block '||block# ||';' FROM v$database_block_corruption;
select 'Select * from dba_free_space where file_id='|| file# || ' and '||block# ||' between block_id and block_id + blocks -1;' FROM v$database_block_corruption;

So our issue we just replaced the file no with file# and block no with block# ;

The above will run the validate only on that file and block and not entire database.

So we just started ti run those queries our first corrupted block
We just run for single file and output as below

rman connect target / ( Do not connect catalog)

RMAN > Validate datafile 71 block 1340426 ;
Validate datafile 71 block 1340426;
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
71 FAILED 0 0 1 0
File Name: +ORADATA/PROD/datafile/apps_ts_xxcustom_data.365.739618975
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data             0                  0
Index            1                  1
Other            0                  0

So this indicates the block is still corrupted.

Its showing failing block for index.

I run the following:

SQL>Select file#,block# from v$database_block_corruption where file#=71 ;
SQL>Select * from dba_free_space where file_id=71 and <block# value from above query> between block_id and block_id + blocks -1 ;

If this returns row then the block is in free space.

Query return:

APPS_TS_XXCUSTOM_DATA 71 1337856 24903680 3040 71

The good news is this block is in free space. Which mean when the index has dropped the block was still in the corrupted state but since it was not reused by any other segment oracle still believe’s it to be corrupt.

Once this block is reused oracle would autoformat this block.
So We can do a block recovery. By using the backups taken to tape or disk

As I mention previously We should first use below queries for other corrupted blocks

SQL>Select file#,block# from v$database_block_corruption ;

From rman

RMAN > Validate datafile <file no> block <Block no> ;

So replace the file no with file# and block no with block# ;

Once done

Select * from dba_free_space where file_id=71 and <block# value from above query> between block_id and block_id + blocks -1 ;

For example

V$database_block_corruption

2 81 1.338.102 1 0 CHECKSUM
2 101 1.335.514 1 0 CHECKSUM

Rman> backup validate datafile 81 block 1338102 ;
SQL>Select * from from dba_free_space where file_id=81 and 1338102 between block_id and block_id + blocks -1 ;

So corrupt blocks are not occupied by any objects. So My production database activities would not be affected.

However, since the blocks have still not been reused rman still considers them as corrupted. So what We have to do?

We should now go with the rman block recovery command.

Please note block recover command would restore archivelogs required for block recovery to disk for doing a block recovery of the affected block.

You can do the following for block recover:

A single command will recovery all blocks affected in v$database_block_corruption ;

Rman> run
{ allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
blockrecover corruption list ;
}

Or Alternate command is

Rman>recover corruption list ;

In these steps, We faced first block corruption recovery took 1 hour 06 min., second block corruption took almost 1 hour. We started to thing the recovery process could be more than 10 hours. But After 2th recover other block corruption took less than 5-10 min.

So the expected behavior is while recovering corruption list process end, dynamic view v$database_block_corruption will be updated. But We see that after rman complete process this view still show us there are corruption block available.

While We started to run validate command again view became empty

rman target / nocatalog
RMAN> Validate datafile 71 block 1340426;

So what is the cause of this corruption? What is a suggestion to prevent this error?

By default db_block_checksum is true.

When dbwr writes blocks to disk it does a xor of bytes within the blocks. Once the block is written to disk no oracle process modifies the blocks unless its re-read to buffer.
Before the block is read back to buffer the server process does a recomputation of the checksum value by doing xor of the bytes within the block.
This should match with the value stored in the block by dbwr process as no oracle process has modified the blocks.
If there is a mismatch in this value it clearly indicates some OS/hardware issue which has overwritten some contents of the blocks when they were on disk.

Also, any corruption by Oracle would be through redo changes. However, we see rman block recovery has gone fine.

Clearly indicating the redo changes vectors were good.You can set db_block_checking=typical …. however, these parameters would have some performance impact around 2 to 10%.

Or set DB_ULTRA_SAFE

Refer

New Parameter DB_ULTRA_SAFE introduce In 11g [ID 465130.1]

These parameters would help detect corruption at earlies in memory and prevent on occasion corrupted block from written to disk.

Again these might also have some performance impact around 2-10% depending on your application

References:
New Parameter DB_ULTRA_SAFE introduce In 11g [ID 465130.1]
Known Corruption issues caused by 3rd party Software Provider [ID 1323649.1]
Intermittent Ora-19566 When Using RMAN Backup [ID 549256.1]
Ext/Pub RMAN 9i Block-Level Media Recovery – Concept & [ID 144911.1 ]

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Oracle recommended using 3 control file for your database. We also need to duplicate our Control file due to do security issue.

In this post, I will try to explain how We can duplicate a control file when ASM is involved.

My environment is 2 Node 11.2.0.4 RAC system. My database SID is RAC00

Here are our steps:

1) Check the current control file:

SQL> select name from v$controlfile;

NAME
——————————————————————————–
+ORADATA/RAC00/controlfile/current.260.737948993

2) For can duplicate our control file our database must be down. So I am going to close my database first:

srvctl stop database -d RAC00

3) After my database down, log in one of my nodes. In my case I am using  node 1:

On node1:
Set DB env. than:

sqlplus “/as sysdba”
startup nomount

4) On node1 set ASM env. then check your current controlfile:

# asmcmd -p
ASMCMD> cd ORADATA/RAC00/CONTROLFILE/
ASMCMD [+ORADATA/RAC00/controlfile] > ls
Current.260.737948993
5) On node1 Set DB env. than:
rman target /
RMAN> restore controlfile to ‘+ORADATA’ from ‘+ORADATA/RAC00/controlfile/current.260.737948993’ ; << this will create 2th controlfile
RMAN> restore controlfile to ‘+ORADATA’ from ‘+ORADATA/RAC00/controlfile/current.260.737948993′ ; << this will create 3th controlfile
6) Checking those file create or not follow setp 4 and use ls command
# asmcmd -p
ASMCMD> cd ORADATA/RAC00/CONTROLFILE/
ASMCMD [+ORADATA/RAC00/controlfile] > ls
Current.260.737948993
CONTROLFILE UNPROT FINE May 01 18:00:00 Y current.1246.790368295
CONTROLFILE UNPROT FINE May 01 18:00:00 Y current.1247.790368311

As you can see We have new 2 controlfile

7) On node1 Set DB env. than:

sqlplus “/as sysdba”

SQL> alter system set control_files=’+ORADATA/RAC00/controlfile/current.260.737948993′,’+ORADATA/RAC00/controlfile/current.1246.790368295′,’+ORADATA/RAC00/controlfile/current.1247.790368311′ scope=spfile sid=’*’;

SQL> shutdown immediate

9) Start your db
srvctl start database -d RAC00

Reference:
How to duplicate a controlfile when ASM is involved [ID 345180.1]

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Oracle Database 19c (19.3.0) for Linux is available for download as of now from OTN and also eDelivery site.
19.3.0 for the database and GI are available as for Linux & Solaris.

You can download Oracle Database 19.3.0 on-prem for Linux now from the usual sources:

The download offering includes the zip, the RPM for the database, and Grid Infrastructure, the Client and more.

You can access the Oracle 19c Documentation from now on as well:

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I face with that error message on our 11.2.0.4 RAC database which is working on AIX Operating System.

I started to get ORA-00245: control file backup operation failed &RMAN-03009: failure of Control File and SPFILE Autobackup command on C1 channel xxx errors. I noticed that this error has appeared after I have been enabled block change tracking to can take Incremental backup on my system.

Our current RMAN setting is:

RMAN > CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ORADATA/SID/CONTROLFILE/snapcf_SID.f';

The Error message during Incremental(Level 1) is

RMAN-03009: failure of backup command on C1 channel at 04/04/2019 08:50:55
ORA-00245: control file backup operation failed
continuing other job steps, job failed will not be re-run

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 04/04/2019 08:53:52

RMAN-03009: failure of backup command on C13 channel at 04/04/2019 08:50:55
ORA-00245: control file backup operation failed

After making some search I get below metalink document:

RMAN Controlfile Must Reside on Shared Device for RAC database in 11G [ID 1263621.1]

So I changed our parameter as below and error has been stopped.

RMAN > CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘+ORADATA/SID/CONTROLFILE/snapcf_SID.f’;

Reference:
RMAN Controlfile Must Reside on Shared Device for RAC database in 11G [ID 1263621.1]
In RAC environment from 11.2 onwards Backup Or Snapshot controlfile needs to be in shared location [ID 1472171.1]
Top RMAN 11.2 issues [ID 1473914.1]

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

You may hit ORA-01000 maximum open cursors exceeded in your daily life.

So what is a cursor? Why you are hitting this error? In this post let us see inside of this error.

Cursors in Oracle

A cursor is a temporary work area created in the system memory when a SQL statement is executed. It can hold more than one row but can process only one row at a time. A set of rows the cursor holds is called an active set. The cursor might be used for retrieving data on a row-by-row basis like a looping statement.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

I have been checked my current settings as below:

SQL> show parameter cursor
Name       Type      Value
processes  integer   1500
SQL> show parameter session
Name       Type      Value
processes  integer   1655
SQL> show parameter process
Name       Type      Value
processes  integer   1500

So the question is How To Determine the SQL Expression That May Be Causing ORA-01000 on our system. There is a metalink note available for this question’s answer.

Here is the steps:

1. Find top 10 sessions which are currently opening most cursors.(In our example my open_cursors has been set to 1500)

# sqlplus / as sysdba
SQL> select * from ( select ss.value, sn.name, ss.sid
 from v$sesstat ss, v$statname sn
 where ss.statistic# = sn.statistic#
 and sn.name like '%opened cursors current%'
 order by value desc) where rownum < 11 ;

You may also go with the below query:

SQL> select c.sid as "OraSID",
c.address||':'||c.hash_value as "SQL Address",
COUNT(c.saddr) as "Cursor Copies"
from v$open_cursor c
group by
c.sid,
c.address||':'||c.hash_value
having COUNT(c.saddr) > 2
order by 3 DESC ;

Ps: Do not forget. That SQL for standalone database, If you have RAC you need to use gv$ instead of v$ views.

2. Identify session and SQL text by using below queries:

SQL> select A.SID as sid, a.status as status, A.EVENT as event, a.seconds_in_wait as wait, a.blocking_session as blk_sesn , a.prev_sql_id as SQL_ID from v$session a where a.sid='xxx'; << If you use first query
SQL> select SQL_FULLTEXT from v$sql where ADDRESS ||':'||HASH_VALUE = '<SQL Address>' ; << If you use second query

3. So now you have 2 option to can solve this error

a. Kill session if its INACTIVE status,
b. Increase this parameter dynamically.

If you go with option b than the issue is:

SQL> ALTER SYSTEM SET open_cursors = 3000 SCOPE=BOTH;  << If its standalone database
SQL> ALTER SYSTEM SET open_cursors = 3000 SCOPE=BOTH SID='*';  << If its RAC database

You can use event 1000 for additional diagnosis to can solve this problem. For details please check reference guide

References:
http://www.orafaq.com/node/758
How To Determine the SQL Expression That May Be Causing ORA-01000 [ID 1333600.1]

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

One of Our database size almost 35T and We need to back up this system twice in a day. Because of the backup duration time(We are using 16 channel and full backup time almost 2,5 hours) That’s why We decided to pass the Cumulative Incremental backup strategy for this system.

We take Level 0(full) backup on weekend and We take Level 1 Cumulative Incremental backup during working days.

We notice that after some days passed, Cumulative Incremental backup size rise up from 1T to 11T. It starts to work like full Level0 backup time duration and backup size.

We have been worked with Oracle Support. The reason for this is that there is a limit on the number of backups that BCT can store, these are 8 by default which means After 8 Incremental backup you need to get Level0 backup or you need to change hidden parameter _bct_bitmaps_per_file dynamically

The limit is set by the “_bct_bitmaps_per_file” parameter. This parameter sets the number of bitmaps to be stored for each datafile, and its default value is 8.  As I mentioned before, In order to avoid this issue one can either run less than 8 incremental backups or can increase the “_bct_bitmaps_per_file” parameter.

Please refer to:
How Many Incremental Backups Can Be Taken When BCT Is Enabled ? [ID 452455.1]

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I hit this issue while I was trying to rename table (or index) name on 11.2.0.4 database.

Symptoms is:

I use a simple command as following;

alter table [prev_tablename] rename to [last_tablename]

If We put into schema names to this command, We can deal with ORA-14047 error.

The error raises when I put the schema name in front of the last_tablename.

SQL> alter table hr.[prev_tablename] rename to hr.[last_tablename] >>>  Its give ORA-14047

Solution:

SQL> alter table hr.[prev_tablename] rename to [lasttable_name]
Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview