ORA-20005: object statistics are locked (stattype = ALL)

The table statistics can be locked from the further update / from auto agthering of stats by using dbms_stats package.  When you get this error you can check whether your table is locked on further statistics updation using following query. The stattype_locked should be null to update the statistics.

 

SQL> select table_name, stattype_locked from dba_tab_statistics where table_name=’EMP’;

TABLE_NAME STATT
—————————— —–
EMP ALL

You can unlock the stattype_locked using the following statement.

SQL> exec dbms_stats.unlock_table_stats(‘SCOTT’, ‘EMP’);

SQL> select table_name, stattype_locked from dba_tab_statistics where table_name=‘EMP’;

TABLE_NAME                     STATT

—————————— —

 

EMP

Now you will be able to generate the statistics.

Taking Oracle 12 c R1 Database backup to AWS cloud S3

STEP 01 : CREATE THE REQUIRED DIRECTORIES AND DOWNLOAD THE OSBWS (Oracle Secure Backup Cloud Module) from below URL :

http://www.oracle.com/technetwork/products/secure-backup/secure-backup-s3-484709.html

[oracle@vmlinux1 ~]$ cd /u01/app/oracle
[oracle@vmlinux1 oracle]$ mkdir -p software/osbws

copy the downloaded OSBWS software zip file to the above location :

[oracle@vmlinux1 osbws]$ ls -ltr
total 2668
-rwxrw-rw- 1 oracle oinstall 2728797 Jan 7 13:30 osbws_installer.zip

Unzip the downloaded zip file

[oracle@vmlinux1 osbws]$unzip osbws_installer.zip

[oracle@vmlinux1 osbws]$ ls -ltr
total 5484
-rw-r–r– 1 oracle oinstall 11298 Sep 1 20:27 osbws_readme.txt
-rw-r–r– 1 oracle oinstall 2867360 Nov 28 00:03 osbws_install.jar
-rwxrw-rw- 1 oracle oinstall 2728797 Jan 7 13:30 osbws_installer.zip

STEP 02: Create directory for the secure Oracle wallet. The Oracle wallet will be created by the OSBWS INSTALLER and used to store your AWS S3 credentials.

[oracle@vmlinux1 osbws]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@vmlinux1 osbws]$ mkdir -p $ORACLE_HOME/dbs/osbws_wallet

STEP 03 : INSTALL ORACLE SECURE BACKUP CLOUD MODULE for S3

You need to have AWS account SID and KEY , along with that you need to have OTN username and password

Create a script that will invoke the OSB Cloud Module for Amazon S3 installer and provide all the inputs

[oracle@vmlinux1 osbws]$ pwd
/u01/app/oracle/software/osbws
[oracle@vmlinux1 osbws]$ vi osbws_install.sh
java -jar osbws_install.jar \
-AWSID ********************************** \
-AWSKey *******************************************************\
-otnUser ********@gmail.com \
-otnPass ************ \
-walletDir $ORACLE_HOME/dbs/osbws_wallet \
-libDir $ORACLE_HOME/lib \
-configFile $ORACLE_HOME/dbs/osbwsorcl.ora

[oracle@vmlinux1 osbws]$ chmod +x osbws_install.sh
[oracle@vmlinux1 osbws]$ ./osbws_install.sh
Oracle Secure Backup Web Service Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
AWS credentials are valid.
Oracle Secure Backup Web Service wallet created in directory /u01/app/oracle/product/12.1.0/dbhome_1/dbs/osbws_wallet.
Oracle Secure Backup Web Service initialization file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/osbwsorcl.ora created.
Downloading Oracle Secure Backup Web Service Software Library from file osbws_linux64.zip.
Download complete.

STEP 04 : MODIFY RMAN CONFIGURATION

Manually create the following symbolic link for the default media management library before performing backups using the SBT interface.

ln -s $ORACLE_HOME/lib/libosbws.so $ORACLE_HOME/lib/libobk.so

[oracle@vmlinux1 lib]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sun Jan 7 22:15:28 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1492211347)

RMAN> configure channel device type sbt parms
2> ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/osbwsorcl.ora)’;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libosbws12.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/osbwsorcl.ora)’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/osbwsorcl.ora)’;
new RMAN configuration parameters are successfully stored

STEP 05 : TEST MEDIA MANAGEMENT SOFTWARE

[oracle@vmlinux1 osbws]$ sbttest osbws_readme.txt -dbname orcl
The sbt function pointers are loaded from libobk.so library.
— sbtinit succeeded
— sbtinit (2nd time) succeeded
sbtinit: vendor description string=Oracle Secure Backup
sbtinit: Media manager is version 12.2.0.1
sbtinit: Media manager supports SBT API version 2.0
sbtinit: allocated sbt context area of 1072 bytes
— sbtinit2 succeeded
— regular_backup_restore starts …………………………..
— sbtbackup succeeded
write 100 blocks
— sbtwrite2 succeeded
— sbtclose2 succeeded
sbtinfo2: SBTBFINFO_NAME=osbws_readme.txt
sbtinfo2: SBTBFINFO_COMMENT=Oracle Secure Backup Web Services Library
sbtinfo2: SBTBFINFO_METHOD=stream
sbtinfo2: SBTBFINFO_ORDER=random access
sbtinfo2: SBTBFINFO_SHARE=multiple users
sbtinfo2: SBTBFINFO_LABEL=s3.amazonaws.com/oracle-data-gsssat-1
— sbtinfo2 succeeded
— sbtrestore succeeded
file was created by this program:
seed=431998581, blk_size=16384, blk_count=100
read 100 buffers
— sbtread2 succeeded
— sbtclose2 succeeded
— sbtremove2 succeeded
— regular_backup_restore ends …………………………..
— sbtcommand succeeded
proxy copy is not supported
— sbtend succeeded
*** The SBT API test was successful ***
[oracle@vmlinux1 osbws]$

STEP 06: PERFORMA ORACLE DATABASE BACKUP TO AWS CLOUD

Note : To secure the backup in the cloud encrypt the backup before taking the backup :

RMAN > set encryption on for all tablespaces algorithm ‘*******’ identified by myPassword only;

[oracle@vmlinux1 scripts]$ cat rman_testdb1.cmd
run {
allocate channel ch1 type sbt;
allocate channel ch2 type sbt;
allocate channel ch3 type sbt;
allocate channel ch4 type sbt;

backup as compressed backupset database;
sql ‘alter system archive log current’;
backup as compressed backupset archivelog all not backed up;
backup current controlfile;

release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}

[oracle@vmlinux1 scripts]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sun Jan 7 22:41:39 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1492211347)

RMAN> @rman_testdb1.cmd

RMAN> run {
2> allocate channel ch1 type sbt;
3> allocate channel ch2 type sbt;
4> allocate channel ch3 type sbt;
5> allocate channel ch4 type sbt;
6>
7> backup as compressed backupset database;
8> sql ‘alter system archive log current’;
9> backup as compressed backupset archivelog all not backed up;
10> backup current controlfile;
11>
12> release channel ch1;
13> release channel ch2;
14> release channel ch3;
15> release channel ch4;
16> }
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=56 device type=SBT_TAPE
channel ch1: Oracle Secure Backup Web Services Library VER=12.2.0.1

allocated channel: ch2
channel ch2: SID=50 device type=SBT_TAPE
channel ch2: Oracle Secure Backup Web Services Library VER=12.2.0.1

allocated channel: ch3
channel ch3: SID=61 device type=SBT_TAPE
channel ch3: Oracle Secure Backup Web Services Library VER=12.2.0.1

allocated channel: ch4
channel ch4: SID=62 device type=SBT_TAPE
channel ch4: Oracle Secure Backup Web Services Library VER=12.2.0.1

Starting backup at 07-JAN-18
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.258.964430517
channel ch1: starting piece 1 at 07-JAN-18
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.257.964430481
channel ch2: starting piece 1 at 07-JAN-18
channel ch3: starting compressed full datafile backup set
channel ch3: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORCL/61E1DCBBD0A608ECE053330948361085/DATAFILE/sysaux.271.964430891
channel ch3: starting piece 1 at 07-JAN-18
channel ch4: starting compressed full datafile backup set
channel ch4: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/ORCL/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.266.964430619
channel ch4: starting piece 1 at 07-JAN-18
channel ch4: finished piece 1 at 07-JAN-18
piece handle=04so431g_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch4: backup set complete, elapsed time: 00:10:35
channel ch4: starting compressed full datafile backup set
channel ch4: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORCL/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.267.964430619
channel ch4: starting piece 1 at 07-JAN-18
channel ch3: finished piece 1 at 07-JAN-18
piece handle=03so431g_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch3: backup set complete, elapsed time: 00:11:00
channel ch3: starting compressed full datafile backup set
channel ch3: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORCL/61E1DCBBD0A608ECE053330948361085/DATAFILE/system.270.964430891
channel ch3: starting piece 1 at 07-JAN-18
channel ch2: finished piece 1 at 07-JAN-18
piece handle=02so431g_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch2: backup set complete, elapsed time: 00:11:36
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.260.964430563
channel ch2: starting piece 1 at 07-JAN-18
channel ch2: finished piece 1 at 07-JAN-18
piece handle=07so43n8_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch2: backup set complete, elapsed time: 00:00:25
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.259.964430563
channel ch2: starting piece 1 at 07-JAN-18
channel ch2: finished piece 1 at 07-JAN-18
piece handle=08so43o1_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch2: backup set complete, elapsed time: 00:00:15
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA/ORCL/61E1DCBBD0A608ECE053330948361085/DATAFILE/users.273.964430903
channel ch2: starting piece 1 at 07-JAN-18
channel ch2: finished piece 1 at 07-JAN-18
piece handle=09so43og_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch2: backup set complete, elapsed time: 00:00:15
channel ch4: finished piece 1 at 07-JAN-18
piece handle=05so43lb_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch4: backup set complete, elapsed time: 00:05:16
channel ch3: finished piece 1 at 07-JAN-18
piece handle=06so43m4_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch3: backup set complete, elapsed time: 00:05:21
channel ch1: finished piece 1 at 07-JAN-18
piece handle=01so431g_1_1 tag=TAG20180107T224151 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch1: backup set complete, elapsed time: 00:21:11
Finished backup at 07-JAN-18

Starting Control File and SPFILE Autobackup at 07-JAN-18
piece handle=c-1492211347-20180107-00 comment=API Version 2.0,MMS Version 12.2.0.1
Finished Control File and SPFILE Autobackup at 07-JAN-18

sql statement: alter system archive log current

Starting backup at 07-JAN-18
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=1 STAMP=964825499
channel ch1: starting piece 1 at 07-JAN-18
channel ch2: starting compressed archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=2 STAMP=964825499
channel ch2: starting piece 1 at 07-JAN-18
channel ch2: finished piece 1 at 07-JAN-18
piece handle=0cso44cr_1_1 tag=TAG20180107T230459 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch2: backup set complete, elapsed time: 00:00:15
channel ch1: finished piece 1 at 07-JAN-18
piece handle=0bso44cr_1_1 tag=TAG20180107T230459 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch1: backup set complete, elapsed time: 00:00:36
Finished backup at 07-JAN-18

Starting backup at 07-JAN-18
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 07-JAN-18
channel ch1: finished piece 1 at 07-JAN-18
piece handle=0dso44dv_1_1 tag=TAG20180107T230535 comment=API Version 2.0,MMS Version 12.2.0.1
channel ch1: backup set complete, elapsed time: 00:01:45
Finished backup at 07-JAN-18

Starting Control File and SPFILE Autobackup at 07-JAN-18
piece handle=c-1492211347-20180107-01 comment=API Version 2.0,MMS Version 12.2.0.1
Finished Control File and SPFILE Autobackup at 07-JAN-18

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

RMAN> **end-of-file**

RMAN>

 

WARNING OGG-00706 Failed to add supplemental log group on table

GGSCI (nracm1 as gguser@source) 28> add trandata scott.account

Logging of supplemental redo log data is already enabled for table scott.ACCOUNT.

2015-12-16 04:06:58 WARNING OGG-00706 Failed to add supplemental log group on table scott.ACCOUNT due to ORA-32588: supplemental logging attribute primary key exists SQL ALTER TABLE “scott”.”ACCOUNT” ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS /* GOLDENGATE_DDL_REPLICATION */.

SOLUTION :

GGSCI (nracm1 as gguser@source) 29> delete trandata scott.account

Logging of supplemental redo log data disabled for table scott.ACCOUNT.
TRANDATA for scheduling columns has been disabled on table ‘scott.ACCOUNT’.
GGSCI (nracm1 as gguser@source) 30> add trandata scott.account

Logging of supplemental redo data enabled for table scott.ACCOUNT.
TRANDATA for scheduling columns has been added on table ‘scott.ACCOUNT’.
GGSCI (nracm1 as gguser@source) 31>

INSTALLING ORACLE 12C GOLDEN GATE

1. UNZIP THE SOFTWARE

2. GO TO Disk1

3. RUN runInstaller

4. You will get GUI, you can install GG 12 BOTH ON 11G DATABASE AND 12C DATABASE :

5. CLICK NEXT BY SELECTING YOUR DATABASE , HERE YOU NEED TO SPECIFY THE GG HOME LOCATION AND THE MANAGER PORT :

6. CLICK NEXT :

7. CLICK NEXT :

8. INSTALLATION COMPLETE , CLOSE THE WINDOW:

Configuring ASM failed with the following message: ORA-00845: MEMORY_TARGET not supported on this system

ERROR DURING INSTALLATION :
Configuring ASM failed with the following message:
Configuring HA resource failed. The following error occurred:
PRCR-1079 : Failed to start resource ora.asm
CRS-5017: The resource action “ora.asm start” encountered the following error:
ORA-00845: MEMORY_TARGET not supported on this system
. For details refer to “(:CLSN00107:)” in “/u01/app/grid/diag/crs/nracm1/crs/trace/ohasd_oraagent_grid.trc”.

CRS-2674: Start of ‘ora.asm’ on ‘nracm1’ failed

SOLUTION :

Starting with Oracle Database 12c, If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.

Increase the size of MEMORY_TARGET and RESTART THE INSTALLATION.

Thanks

Oracle Grid Infrastructure is not configured properly. ASMCA needs Oracle Grid Infrastructure to configure ASM.

SOLUTION:

export ORACLE_HOME=/u01/app/11.2.0/grid_1

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

CLUVFY HEALTHCHECK

From oracle 11g R2 we have a new feature in oracle cluvfy , It will generate the HEALTHCHECK report in html format FOR RAC .

$mkdir -p /tmp/clu_tmp

$cluvfy comp healthcheck -collect cluster -html -save -savedir /tmp/clu_tmp

 

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode(During RMAN COLD BACKUP)

RMAN-03009: failure of backup command on c3 channel at 05/15/2012 18:49:11
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel c4: starting compressed full datafile backupset
channel c4: specifying datafile(s) in backupset

There is at least one file that is in need of recovery.
A COLD backup for a database running in NOARCHIVELOG mode has to be a CONSISTENT backup as it cannot be recovered after restore.

To confirm, mount the database and run the following SQL:

SQL >select count(1),fuzzy from v$datafile_header group by fuzzy;

COUNT(1) FUZ
——————– —
92 YES
SQL>Recover database ;

SQL >select count(1),fuzzy from v$datafile_header group by fuzzy;

COUNT(1) FUZ
——————– —
92 NO

 

Now take the backup of the database (RMAN COLD BACKUP)

 

Reference : Doc ID 373828.1

CATPROC Oracle Database Packages and Types INVALID After upgrade

STEP 01:

For validating the catproc the following steps needs to be executed:

Step 01 a  –
SQL> shutdown immediate;

Step 01 b –
SQL> startup upgrade;

Step 01 c  –

conn / as sysdba
spool catalogproc.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
Column comp_id format a10
Column comp_name format a35
Column version format a12
Column status format a10
select comp_id, comp_name, version, status from dba_registry;
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE STATUS<>’VALID’ ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS;
spool off

Step 01 d –
SQL> shutdown immediate;
SQL> startup;

STEP 02:

If step 01 doesn’t works :

Reference note:782735.1

Execute the following script repeatedly while connected as SYS AS SYSDBA and perform the indicated operation until it returns ‘CATPROC can be validated now’.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = ‘CATPROC’;
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line(‘Please compile Invalid object ‘||object_name||’
Object_id ‘||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘CATPROC can be validated now’ );
end;
/

Once all of the dependent objects have been recompiled, validate CATPROC by executing the following command:
execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

In my case even after running this CATPROC did not become VALID . So when I ran the below query

column object_name format a25

SQL > select object_name, object_type, owner from dba_objects
where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
—————————— ——————- ——————————
PLAN_TABLE                         TABLE                                            SYS
PLAN_TABLE                         SYNONYM                                     PUBLIC
TOAD_PLAN_TABLE          TABLE                                             DORIS
BMC_SQLEX_PLAN_TABLE_S02   TABLE                            SYSTEM
PLAN_TABLE                        TABLE                                             SYSTEM
SQL_PLAN_TABLE_TYPE   TYPE                                             SYS
SQL_PLAN_TABLE_TYPE   SYNONYM                                  PUBLIC
PLAN_TABLE$                           TABLE                                      SYS
TOAD_PLAN_TABLE             SYNONYM                                 PUBLIC

 

This issue is caused by existence of :
– table SYS.PLAN_TABLE$
– public synonym PLAN_TABLE for the SYS.PLAN_TABLE$.
– and possibly (not always present) table SYS.PLAN_TABLE

In order to prevent the issue from occurring it is necessary to drop before starting the upgrade process. :
– the SYS owned table PLAN_TABLE$
– the public synonym PLAN_TABLE
– the SYS owned table PLAN_TABLE if it exists

As the SYS user, drop the PLAN_TABLE$, the public synonym PLAN_TABLE and if it exists
the table PLAN_TABLE in SYS schema (SYS.PLAN_TABLE is not always there) :

If you receive the following error while attempting to drop the plan table:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

ensure that the cursor_sharing parameter is set to EXACT.

This can be done at the session level using ‘alter system set cursor_sharing=exact scope=memory;’
SQL> drop table plan_table$;

Table dropped.

SQL> drop public synonym plan_table;

Synonym dropped.

if SYS.PLAN_TABLE exists :

drop table plan_table;

SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS

Then re-create the PLAN_TABLE
$ sqlplus ‘/ as sysdba’
SQL> @?/rdbms/admin/catplan.sql — creates a public plan table as a global temporary table accessible from any schema
SQL> @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
SQL> @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
SQL> @?/rdbms/admin/prvtspa.plb — reload dbms_sqlpa (Note use prvtspao.plb if prvtspa.plb is not present).
SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
PLAN_TABLE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS
PLAN_TABLE$ TABLE SYS

ORA-01157: cannot identify/lock data file 202 – see DBWR trace file

ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 – see DBWR trace file
ORA-01110: data file 202: ‘/u10/oradata/orcl01/temp_02.dbf’

STEP 01: Drop the particular datafile:

SQL>alter database tempfile ‘/u10/oradata/orcl01/temp_02.dbf’ drop;

STEP 02 : Add the temp file :

SQL>ALTER TABLESPACE TEMP1 add tempfile ‘/u10/oradata/orcl01/temp_02.dbf’ size 10G;