ORA-01555 Snapshot Too Old

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"

  FROM v$datafile a,

       v$tablespace b,

       dba_tablespaces c

 WHERE c.contents = 'UNDO'

   AND c.status = 'ONLINE'

   AND b.name = c.tablespace_name

   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

      "UNDO_BLOCK_PER_SEC"

  FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"

 FROM v$parameter

WHERE name = 'db_block_size';

Optimal Undo Retention

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       ROUND((d.undo_size / (to_number(f.value) *

       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"

  FROM (

       SELECT SUM(a.bytes) undo_size

          FROM v$datafile a,

               v$tablespace b,

               dba_tablespaces c

         WHERE c.contents = 'UNDO'

           AND c.status = 'ONLINE'

           AND b.name = c.tablespace_name

           AND a.ts# = b.ts#

       ) d,

       v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

              undo_block_per_sec

         FROM v$undostat

       ) g

WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size'

Optimal undo retention

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024)

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size'

Recommended unto_rentention :-

select BEGIN_TIME,END_TIME , TUNED_UNDORETENTION from v$undostat;

Number of time snapshot too old error found :-

select begin_time,ssolderrcnt from v$undostat;

Maximum time of query in Undo, clue is to set undo retention higher to this value :-

select begin_time,maxquerylen from v$undostat;

OCR recovery from CRS internal backup and export backup

Recovery case of OCR disk from ocr own backup.

$ crs_stat -t

Name           Type           Target    State     Host

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

ora.ibm.db     application    ONLINE    ONLINE    labtest02

ora....m1.inst application    ONLINE    ONLINE    labtest01

ora....m2.inst application    ONLINE    ONLINE    labtest02

ora....SM1.asm application    ONLINE    ONLINE    labtest01

ora....01.lsnr application    ONLINE    ONLINE    labtest01

ora....t01.gsd application    ONLINE    ONLINE    labtest01

ora....t01.ons application    ONLINE    ONLINE    labtest01

ora....t01.vip application    ONLINE    ONLINE    labtest01

ora....SM2.asm application    ONLINE    ONLINE    labtest02

ora....02.lsnr application    ONLINE    ONLINE    labtest02

ora....t02.gsd application    ONLINE    ONLINE    labtest02

ora....t02.ons application    ONLINE    ONLINE    labtest02

ora....t02.vip application    ONLINE    ONLINE    labtest02

$

$

$ ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          2

         Total space (kbytes)     :     307112

         Used space (kbytes)      :       3812

         Available space (kbytes) :     303300

         ID                       :  992671071

         Device/File Name         : /dev/rhdiskpower1

                                    Device/File integrity check succeeded

         Device/File Name         : /dev/rhdiskpower2

                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

$ orcconfig -showbackup

ksh: orcconfig:  not found

$ ocrconfig -showbackup

labtest02     2010/02/06 11:40:44     /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02     2010/02/06 07:40:44     /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02     2010/02/06 03:40:43     /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02     2010/02/05 07:40:41     /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02     2010/01/26 04:53:33     /u01/app/oracle/product/10.2.0/crs/cdata/crs

##################

Now we format ocr disk...

$ sudo su -

Password:

#

# pwd

/

# dd if=/dev/zero of=/dev/rhdiskpower1 bs=8192 count=25000

25000+0 records in

25000+0 records out

# dd if=/dev/zero of=/dev/rhdiskpower1 bs=8192 count=25000

25000+0 records in

25000+0 records out

# dd if=/dev/zero of=/dev/rhdiskpower2 bs=8192 count=25000

25000+0 records in

25000+0 records out

######################

$ crs_stat -t

Name           Type           Target    State     Host

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

ora.ibm.db     application    ONLINE    ONLINE    labtest02

ora....m1.inst application    ONLINE    ONLINE    labtest01

ora....m2.inst application    ONLINE    ONLINE    labtest02

ora....SM1.asm application    ONLINE    ONLINE    labtest01

ora....01.lsnr application    ONLINE    ONLINE    labtest01

ora....t01.gsd application    ONLINE    ONLINE    labtest01

ora....t01.ons application    ONLINE    ONLINE    labtest01

ora....t01.vip application    ONLINE    ONLINE    labtest01

ora....SM2.asm application    ONLINE    ONLINE    labtest02

ora....02.lsnr application    ONLINE    ONLINE    labtest02

ora....t02.gsd application    ONLINE    ONLINE    labtest02

ora....t02.ons application    ONLINE    ONLINE    labtest02

ora....t02.vip application    ONLINE    ONLINE    labtest02

$ uname -n

labtest01

$ srvctl stop listener -n labtest01

PRKH-1010 : Unable to communicate with CRS services.

  [OCR Error(Native: prsr_initCLSS:[21])]

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ ocrconfig -restore /u01/app/oracle/product/10.2.0/crs/cdata/crs/day.ocr

PROT-20: Insufficient permission to proceed. Require privileged user

###################

 with root privilage:-

# cd /u01/app/oracle/product/10.2.0/crs

# cd bin

# ./ocrconfig -restore /u01/app/oracle/product/10.2.0/crs/cdata/crs/day.ocr

# echo $?

0

# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

######################

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$  crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

Name           Type           Target    State     Host

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

ora.debs.db    application    ONLINE    ONLINE    labtest02

ora....debs.cs application    ONLINE    ONLINE    labtest01

ora....bs1.srv application    ONLINE    OFFLINE

ora....s1.inst application    ONLINE    OFFLINE

ora....s2.inst application    ONLINE    OFFLINE

ora....SM1.asm application    ONLINE    ONLINE    labtest01

ora....01.lsnr application    ONLINE    ONLINE    labtest01

ora....t01.gsd application    ONLINE    ONLINE    labtest01

ora....t01.ons application    ONLINE    ONLINE    labtest01

ora....t01.vip application    ONLINE    ONLINE    labtest01

ora....SM2.asm application    ONLINE    ONLINE    labtest02

ora....02.lsnr application    ONLINE    ONLINE    labtest02

ora....t02.gsd application    ONLINE    ONLINE    labtest02

ora....t02.ons application    ONLINE    ONLINE    labtest02

ora....t02.vip application    ONLINE    ONLINE    labtest02

ora....andh.db application    ONLINE    ONLINE    labtest01

ora....h1.inst application    ONLINE    OFFLINE

ora....h2.inst application    ONLINE    OFFLINE

$ ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          2

         Total space (kbytes)     :     307112

         Used space (kbytes)      :       5128

         Available space (kbytes) :     301984

         ID                       :  992671071

         Device/File Name         : /dev/rhdiskpower1

                                    Device/File integrity check succeeded

         Device/File Name         : /dev/rhdiskpower2

                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 6 14:44:27 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

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

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

SQL>

********************************************************************

From OCR export backup:

$ crs_stat -t

Name           Type           Target    State     Host

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

ora.debs.db    application    OFFLINE   OFFLINE

ora....debs.cs application    OFFLINE   OFFLINE

ora....bs1.srv application    ONLINE    OFFLINE

ora....s1.inst application    ONLINE    OFFLINE

ora....s2.inst application    ONLINE    OFFLINE

ora....SM1.asm application    ONLINE    ONLINE    labtest01

ora....01.lsnr application    ONLINE    ONLINE    labtest01

ora....t01.gsd application    ONLINE    ONLINE    labtest01

ora....t01.ons application    ONLINE    ONLINE    labtest01

ora....t01.vip application    ONLINE    ONLINE    labtest01

ora....SM2.asm application    ONLINE    ONLINE    labtest02

ora....02.lsnr application    ONLINE    ONLINE    labtest02

ora....t02.gsd application    ONLINE    ONLINE    labtest02

ora....t02.ons application    ONLINE    ONLINE    labtest02

ora....t02.vip application    ONLINE    ONLINE    labtest02

ora....andh.db application    OFFLINE   OFFLINE

ora....h1.inst application    ONLINE    OFFLINE

ora....h2.inst application    ONLINE    OFFLINE

$

$ ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          2

         Total space (kbytes)     :     307112

         Used space (kbytes)      :       5136

         Available space (kbytes) :     301976

         ID                       :  992671071

         Device/File Name         : /dev/rhdiskpower1

                                    Device/File integrity check succeeded

         Device/File Name         : /dev/rhdiskpower2

                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

$ ls -lt /u01

total 0

drwxr-xr-x    2 oracle   oinstall        256 Jan 20 16:20 cron_script

drwxrwx---    3 oracle   oinstall        256 Jan 12 16:17 app

drwxr-xr-x    2 root     system          256 Jan  7 16:10 lost+found

$ cd /u01/app

$ ls

oracle

$ mkdir ocr

$ ls

ocr     oracle

$ ls -lt

total 0

drwxr-xr-x    2 oracle   oinstall        256 Feb  6 15:09 ocr

drwxrwx---    3 oracle   oinstall        256 Jan 12 16:17 oracle

$ cd ocr

$ pwd

/u01/app/ocr

$ ocrconfig -export /u01/app/ocr/ocr-export.dmp

PROT-20: Insufficient permission to proceed. Require privileged user

######################

# pwd

/u01/app/oracle/product/10.2.0/crs/bin

# ./ocrconfig -export /u01/app/ocr/ocr-export.dmp

# dd if=/dev/zero of=/dev/rhdiskpower1 bs=8192 count=25000

25000+0 records in

25000+0 records out

# dd if=/dev/zero of=/dev/rhdiskpower2 bs=8192 count=25000

25000+0 records in

25000+0 records out

# ./ocrconfig -import /u01/app/ocr/ocr-export.dmp

# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

#################

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

Name           Type           Target    State     Host

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

ora.debs.db    application    OFFLINE   OFFLINE

ora....debs.cs application    OFFLINE   OFFLINE

ora....bs1.srv application    ONLINE    OFFLINE

ora....s1.inst application    ONLINE    OFFLINE

ora....s2.inst application    ONLINE    OFFLINE

ora....SM1.asm application    ONLINE    ONLINE    labtest01

ora....01.lsnr application    ONLINE    ONLINE    labtest01

ora....t01.gsd application    ONLINE    ONLINE    labtest01

ora....t01.ons application    ONLINE    ONLINE    labtest01

ora....t01.vip application    ONLINE    ONLINE    labtest01

ora....SM2.asm application    ONLINE    ONLINE    labtest02

ora....02.lsnr application    ONLINE    ONLINE    labtest02

ora....t02.gsd application    ONLINE    ONLINE    labtest02

ora....t02.ons application    ONLINE    ONLINE    labtest02

ora....t02.vip application    ONLINE    ONLINE    labtest02

ora....andh.db application    OFFLINE   OFFLINE

ora....h1.inst application    ONLINE    OFFLINE

ora....h2.inst application    ONLINE    OFFLINE

to check if oracle database is upgrade from 10g to 11g

Below script can the checked to see the database up gradation life cycle.

SQL> l

  1* select * from DBA_REGISTRY_HISTORY

SQL> /

ACTION_TIME                         ACTION               NAMESPACE            VERSION                      ID COMMENTS

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

16-NOV-09 04.22.18.257083 PM        UPGRADE              SERVER               11.1.0.7.0                      Upgraded from 11.1.0.6.0

06-JUN-10 01.36.36.363629 PM        APPLY                SERVER               11.1.0.7                      2 PSU 11.1.0.7.2

06-JUN-10 01.40.09.606668 PM        CPU                                                               6452863 view recompilation

25-MAY-11 05.59.56.069514 PM        VIEW INVALIDATE                                                   8289601 view invalidation

25-MAY-11 06.00.22.484425 PM        UPGRADE              SERVER               11.2.0.2.0                      Upgraded from 11.1.0.7.0

25-MAY-11 07.45.23.622169 PM        VIEW INVALIDATE                                                   8289601 view invalidation

25-MAY-11 07.45.39.435819 PM        UPGRADE              SERVER               11.2.0.2.0                      Upgraded from 11.1.0.7.0

7 rows selected.

SQL> SELECT version, prv_version,banner FROM sys.registry$ ;

VERSION              PRV_VERSION                    BANNER

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

11.2.0.2.0           11.1.0.6.0                     Oracle Database Catalog Views Release 11.2.0.2.0 - 64bit Production

11.2.0.2.0           11.1.0.7.0                     Oracle Database Packages and Types Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle Workspace Manager Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     JServer JAVA Virtual Machine Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle XDK Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle Database Java Packages Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle Expression Filter Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle Text Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle XML Database Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle Rules Manager Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle Multimedia Release 11.2.0.2.0 - Development

VERSION              PRV_VERSION                    BANNER

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

11.2.0.2.0           11.1.0.7.0                     OLAP Analytic Workspace Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Oracle OLAP API Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     OLAP Catalog Release 11.2.0.2.0 - Development

11.2.0.2.0           11.1.0.7.0                     Spatial Release 11.2.0.2.0 - Development

11.2.0.2.0                                          Oracle Enterprise Manager Release 11.2.0.2.0 - Development

3.2.1.00.12          3.0.1.00.12                    Oracle Application Express Release 3.2.1.00.12 - Development

11.1.0.6.0                                          OWB Release 11.1.0.6.0 - Production

18 rows selected.

SQL>

RMAN DUPLICATE COMMAND PITFALL

·         RMAN DUPLICATE: CONTROL FILE IS RESTORED NOT CREATED.

In Oracle Database 11.2, the RMAN 'Duplicate' command now RESTORES the backup controlfile to the auxiliary host rather than CREATE it. So we must ensure that there is a controlfile backup available to meet the duplicate time. Otherwise we may get the bellow error:

RMAN-03002: failure of Duplicate Db command at 07/23/2012 10:09:08

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

Here's test to run before starting your duplication, to verify whether you have a valid controlfile backup:

RMAN> run {

set until time "to_date('2012 SEP 03 07:37','YYYY MON DD HH24:MI')";

restore controlfile preview;}

Note: change the 'SET UNTIL TIME' to meet your duplicate requirements.

·         BCT(Block change tracking) issue when duplicating DB.

If BCT enable at production DB, we may get 'ORA-19755 Using RMAN Duplicate, Tries Open The Block Change Tracking File of Source DB'.

Workaround is to create a dummy file in same location as on Production to meet this requirement. We may also disable BCT for the time, duplicate process is active. Also check DOC ID: 1098638.1.

RMAN backup optimization

RMAN backup optimization is another feature to minimize backup size. Enabling this feature, RMAN BACKUP will skip backup of files that comes in certain condition or exactly identical to the file already backup up.

By Default it is set to OFF:

RMAN> SHOW BACKUP OPTIMIZATION;

RMAN configuration parameters for database with db_unique_name ORACLE are:

CONFIGURE BACKUP OPTIMIZATION OFF; # default

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

RMAN>

Below are the criteria when RMAN check which files can be skipped from the backup.

In case of datafile RMAN compares the DBID, checkpoint SCN, creation SCN, and RESETLOGS SCN and time as a datafile already in a backup. If they are identical then skip to take backup again.

In case of archived redo log RMAN compares thread, sequence number, and RESETLOGS SCN and time.

In case of backupset RMAN compares recid and stamp.

Backup Optimization only applies to below rman commands:

BACKUP DATABASE

BACKUP ARCHIVELOG with ALL or LIKE options

BACKUP BACKUPSET ALL

Optimization feature can be override anytime by using FORCE option with backup command.

BACKUP DATABASE FORCE;

Log: 

Starting backup at 08-SEP-12

current log archived

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3074.294.793279165;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3075.307.793284119;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3076.298.793286989;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3077.297.793289297;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3078.382.793291555;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3079.395.793293577;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3080.381.793295619;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3081.299.793297653;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3082.403.793299621;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3083.408.793301609;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3084.362.793303497;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3085.355.793305483;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3086.364.793307311;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3087.306.793309275;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3088.292.793311117;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3089.283.793312945;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3090.365.793314877;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3091.356.793316867;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3092.410.793317693;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3093.281.793319779;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3094.358.793322287;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3095.368.793325083;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3096.386.793326137;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3097.290.793328655;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3098.411.793332451;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3099.360.793338931;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3100.407.793343249;

log evidence of master node in RAC environment

Another log evidence of master node in RAC environment

2012-09-06 18:27:20.452: [  CRSRES][1090042176][ALERT] Remote start for `******.******1.inst` failed on member `*******`

2012-09-06 18:27:20.453: [    CRSD][1092143424] SM: recovery in process: 8

2012-09-06 18:27:20.453: [  CRSEVT][1092143424] Do failover for: ********

2012-09-06 18:27:20.457: [  OCRMSG][1092143424]prom_rpc: CLSC recv failure..ret code 7

2012-09-06 18:27:20.457: [  OCRMSG][1092143424]prom_rpc: possible OCR retry scenario

2012-09-06 18:27:20.458: [  OCRMAS][1085839680]th_master:13: I AM THE NEW OCR MASTER at incar 14. Node Number 2

ORA-20005: object statistics are locked

Today with an import script, we had received below error.

IMP-00017: following statement failed with ORACLE error 20005: IMP-00003: ORACLE error 20005 encountered IMP-00017: following statement failed with ORACLE error 20005: IMP-00003: ORACLE error 20005 encountered IMP-00017

When checking we find statistics are locked for those objects.

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked = 'ALL';

Solution is either to unlock the statistics the objects using dbms_stats package.

exec dbms_stats.unlock_schema_stats('schema_owner');

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Else to prevent import (imp) from locking the table's statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).