Wikipedia

Search results

Postgres 12: DBA Life Just Got Better!



Postgres Getting Bigger and Stronger











Congratulations everyone for Postgres 12 release on October-3 2019.
It’s magic when like-minded people from all over the world come together and work towards a common goal to make Postgres better every single day.
Thank you for all the community members (Core members, developers, testers, build management and documentation team) to make this possible.
Postgres 12 has some really cool features for the end-user. Some of them are a major enhancement and some with minor changes in existing functionality. It’s going to benefit the open-source database world in a lot of different ways. But What a Database Admin would gain out of Postgres 12?
Below are some features which I am willing to use to make Database operations better and easier.
1.   Partitioning Improvements: Partitioning performance enhancements, including improved query performance on tables with thousands of partitions, improved insertion performance with INSERT and COPY, and the ability to execute ALTER TABLE ATTACH PARTITION without blocking queries. Also, there is an improvement for foreign keys to reference partitioned tables.
2.   Checksum Control: pg_checksums can enable/disable page checksums (used for detecting data corruption) in an offline cluster.- With an earlier version of Postgres, the only option was to reinitialize the database using initdb.
3.   Reindex Concurrently: Being a DBA indexing database without downtime(write) has been an issue. REINDEX CONCURRENTLY can rebuild an index without blocking writes to its table. Well this features also comes with a cost. Let me explain. Usually, PostgreSQL locks the table whose index is rebuilt against writes and performs the entire index build with a single scan of the table. Rest of the transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index rebuild is finished. This is a prob for a Production system. A very large tables can take many hours to be indexed, and even for smaller tables, an index rebuild can lock out writers for periods that are unacceptably long for a production system.
PostgreSQL supports rebuilding indexes with minimum locking of writes. This method is invoked by specifying the CONCURRENTLY option of REINDEX. When this option is used, PostgreSQL must perform two scans of the table for each index that needs to be rebuilt and wait for termination of all existing transactions that could potentially use the index. This method requires more total work than a standard index rebuild and takes significantly longer to complete as it needs to wait for unfinished transactions that might modify the index. As it allows normal operations, this method is useful for rebuilding indexes in a production environment. Of course, the extra CPU, memory and I/O load imposed by the index rebuild may slow down other operations.
4. Reporting information: Progress reports statistics for CREATE INDEXREINDEXCLUSTERVACUUM FULL, and pg_checksums
CREATE INDEX and REINDEX operations: use pg_stat_progress_create_index system view.
for CLUSTER and VACUUM FULL: use pg_stat_progress_cluster
5. Enhance security with MFA: Multi-factor authentication, using the clientcert=verify-full option combined with an additional authentication method in pg_hba.conf.
There has been some changes to improve existing funtionality
1.   psql command \dP to list partitioned tables and indexes.
2.   Time-based server parameters to use units of microseconds
3.   fractional input for integer server parameters.
set work_mem = ‘30.1GB’
4.   wal_recycle and wal_init_zero server parameters to control WAL file recycling.
5.   VACUUM to skip index cleanup
This change adds a VACUUM command option INDEX_CLEANUP as well as a table storage option vacuum_index_cleanup. The use of this option reduces the ability to reclaim space and can lead to index bloat, but it is helpful when the main goal is to freeze old tuples.
6.   vacuumdb to select tables for vacuum based on their wraparound horizon
The options are –min-xid-age and –min-mxid-age.
7.   pg_upgrade to use the file system’s cloning feature.
8.   –exclude-database option to pg_dumpall.
I hope you could use some of the features to solve some of real-time problems. I would love to hear back from you about the features which can solve your database problems.


ORA-00354 – (Oracle Hidden Parameter : _allow_resetlogs_corruption)

NOTE: This is One of the Hidden Oracle ParametersDO NOT use it until and unless instructed by Oracle Support. You are trying to start your DATABASE, you found the aforementioned error i.e. ORA-00354.

.Required Action: Perform recovery with a good version of the redo log file or perform cancel-based recovery up to, but not including, the corrupted redo log file.
What will happen, if you don’t have Valid Backup? There is a Workaround via which you can at least recover your Database to some extent:
SQL> select * from v$log;
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 897612315 generated at 10/19/2005 16:54:18 needed for thread 1 ORA-00289:
suggestion : /opt/oracle/oradata/conner/archive/1_160.dbf
ORA-00280: change 897612315 for thread 1 is in sequence #160
Specify log: {=suggested | filename | AUTO | CANCEL} cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:’/opt/oracle/oradata/conner/system01.dbf’
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
SQL> shutdown immediate;
SQL> startup
Fri Jun 10 16:30:25 2005
alter database open resetlogs
Fri Jun 10 16:30:25 2005
RESETLOGS is being done without consistency checks. This may result in a corrupted database.
The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 240677200 Resetting resetlogs
activation ID 3171937922 (0xbd0fee82)
NOTE : I would strongly recommend to immediately take Logical Backup (either Using Data Pump , the conventional one) and Create New Database)

PMON Terminating the Instance Due To Error 822


Click to add to FavoritesTo BottomTo Bottom

In this Document
Symptoms
Changes
Cause
Solution
References


APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

On : 12.1.0.2 version, RDBMS

Database crashed and the following error observed in the alert log file.
ERROR
-----------------------
Thu Nov 16 20:31:48 2017
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Thu Nov 16 20:31:49 2017
Instance Critical Process (pid: 6, ospid: 16813, MMAN) died unexpectedly
Thu Nov 16 20:31:49 2017
System state dump requested by (instance=1, osid=16803 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /apps/oracle/diag/rdbms/<dbname>/<instance_name>/trace/<instance_name>_diag_16817_20171116203149.trc
Thu Nov 16 20:31:49 2017
PMON (ospid: 16803): terminating the instance due to error 822

CHANGES


CAUSE

The collected OS error log shows "Out of memory" and "Free swap = 0kB" errors, and it proved that mman process got killed due to "Out of memory" problem:
Nov 16 20:31:45 <osname> kernel: Free swap = 0kB <<< Free swap = 0kB
Nov 16 20:31:45 <osname> kernel: Out of memory: Kill process 16813 (ora_mman_<instance_name>) score 112 or sacrifice child <<< Out of memory

SOLUTION

Engage OS admin to investigate the memory shortage problem.
The OOM killer, a feature enabled by default, is a self protection mechanism employed by the Linux kernel when under severe memory pressure. Please refer the below document for more details:
Linux: Out-of-Memory (OOM) Killer (Doc ID 452000.1)
-- Reference (Oracle Doc ID 2342018.1)

Restoring Control Files

Restoring Control Files
This section contains the following topics:

Restore Lost Copy of a Multiplexed Control File
Restore Control File from Backup After Loss of All Current Control Files
Create New Control File After Losing All Current and Backup Control Files

Restore Lost Copy of a Multiplexed Control File
Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure.

Copying a Multiplexed Control File to a Default Location
If the disk and file system containing the lost control file are intact, then you can simply copy one of the intact control files to the location of the missing control file. In this case, you do not have to alter the CONTROL_FILES initialization parameter setting.


To replace a damaged control file by copying a multiplexed control file:

If the instance is still running, then shut it down:

SHUTDOWN ABORT

Correct the hardware problem that caused the media failure. If you cannot repair the hardware problem quickly, then proceed with database recovery by restoring damaged control files to an alternative storage device, as described in "Copying a Multiplexed Control File to a Nondefault Location".

Use an intact multiplexed copy of the database's current control file to copy over the damaged control files. For example, to replace bad_cf.f with good_cf.f, you might enter:

% cp /oracle/good_cf.f /oracle/dbs/bad_cf.f

Start a new instance and mount and open the database. For example, enter:

STARTUP

Copying a Multiplexed Control File to a Nondefault Location:

Assuming that the disk and file system containing the lost control file are not intact, then you cannot copy one of the good control files to the location of the missing control file. In this case, you must alter the CONTROL_FILES initialization parameter to indicate a new location for the missing control file.

To restore a control file to a nondefault location:

If the instance is still running, then shut it down:

SHUTDOWN ABORT

If you cannot correct the hardware problem that caused the media failure, then copy the intact control file to alternative locations. For example, to copy a good version of control01.dbf to a new disk location you might issue:

% cp $ORACLE_HOME/oradata/trgt/control01.dbf /new_disk/control01.dbf

Edit the parameter file of the database so that the CONTROL_FILES parameter reflects the current locations of all control files and excludes all control files that were not restored. Assume the initialization parameter file contains:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/bad_disk/control02.dbf'

Then, you can edit it as follows:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/new_disk/control02.dbf'

Start a new instance and mount and open the database. For example:

STARTUP

Restore Control File from Backup After Loss of All Current Control Files

Use the following procedures to restore a backup control file if a permanent media failure has damaged all control files of a database and you have a backup of the control file. When a control file is inaccessible, you can start the instance, but not mount the database. If you attempt to mount the database when the control file is unavailable, then you receive this error message:
ORA-00205: error in identifying control file, check alert log for more info 

You cannot mount and open the database until the control file is accessible again. If you restore a backup control file, then you must open RESETLOGS.
As indicated in the following table, the procedure for restoring the control file depends on whether the online redo logs are available.

Table 18-1 Scenarios When Control Files Are Lost
Status of Online LogsStatus of DatafilesRestore Procedure
AvailableCurrentIf the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the filename of the online logs containing the changes in order to open the database. After recovery, open RESETLOGS.
UnavailableCurrentIf the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open RESETLOGS (when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file).
AvailableBackupRestore a backup control file, perform complete recovery, and then open RESETLOGS.
UnavailableBackupRestore a backup control file, perform incomplete recovery, and then open RESETLOGS.


Restoring a Backup Control File to the Default Location

If possible, restore the control file to its original location. In this way, you avoid having to specify new control file locations in the initialization parameter file.
To restore a backup control file to its default location:
  1. If the instance is still running, shut it down:
    SHUTDOWN ABORT
    
    
  2. Correct the hardware problem that caused the media failure.
  3. Restore the backup control file to all locations specified in the CONTROL_FILES parameter. For example, if ORACLE_HOME/oradata/trgt/control01.dbf and ORACLE_HOME/oradata/trgt/control02.dbf are the control file locations listed in the server parameter file, then use an operating system utility to restore the backup control file to these locations:
    % cp /backup/control01.dbf ORACLE_HOME/oradata/trgt/control01.dbf
    % cp /backup/control02.dbf ORACLE_HOME/oradata/trgt/control02.dbf
    
    
  4. Start a new instance and mount the database. For example, enter:
    STARTUP MOUNT 
    
    
  5. Begin recovery by executing the RECOVER command with the USING BACKUP CONTROLFILE clause. Specify UNTIL CANCEL if you are performing incomplete recovery. For example, enter:
    RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
    
    
  6. Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance crashed.
    For example, assume that you see the following:
    ORA-00279: change 55636 generated at 11/08/2002 16:59:47 needed for thread 1
    ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc
    ORA-00280: change 55636 for thread 1 is in sequence #111
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    
    You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):
    ORACLE_HOME/oradata/redo01.dbf
    Log applied.
    Media recovery complete.
    
    
    If the online logs are inaccessible, then you can cancel recovery without applying them. If all datafiles are current, and if redo in the online logs is required for recovery, then you cannot open the database without applying the online logs. If the online logs are inaccessible, then you must re-create the control file, using the procedure described in "Create New Control File After Losing All Current and Backup Control Files".
  7. Open the database with the RESETLOGS option after finishing recovery:
    ALTER DATABASE OPEN RESETLOGS;
    

Restoring a Backup Control File to a Nondefault Location

If you cannot restore the control file to its original place because the media damage is too severe, then you must specify new control file locations in the server parameter file. A valid control file must be available in all locations specified by the CONTROL_FILES initialization parameter. If not, then the database prevents you from the mounting the database.
To restore a control file to a nondefault location:
Follow the steps in "Restoring a Backup Control File to the Default Location", except after step 2 add the following step:
Edit all locations specified in the CONTROL_FILES initialization parameter to reflect the new control file locations. For example, if the control file locations listed in the server parameter file are as follows, and both locations are inaccessible:
CONTROL_FILES='/oracle/oradata/trgt/control01.dbf',
              '/oracle/oradata/trgt/control01.dbf'

Then, you can edit the initialization parameter file as follows:
CONTROL_FILES='/good_disk/control01.dbf','/good_disk/control02.dbf'


Create New Control File After Losing All Current and Backup Control Files

If all control files have been lost in a permanent media failure, but all online redo log members remain intact, then you can recover the database after creating a new control file. The advantage of this tactic is that you are notrequired to open the database with the RESETLOGS option.
Depending on the existence and currency of a control file backup, you have the options listed in the following table for generating the text of the CREATE CONTROLFILE statement. Note that changes to the database are recorded in the alert_SID.log, so check this log when deciding which option to choose.

Table 18-2 Options for Creating the Control File
If you . . .Then . . .
Executed ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS after you made the last structural change to the database, and if you have saved the SQL command trace outputUse the CREATE CONTROLFILE statement from the trace output as-is.
Performed your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACEbefore you made a structural change to the databaseEdit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect the change. For example, if you recently added a datafile to the database, then add this datafile to the DATAFILE clause of the CREATE CONTROLFILE statement.
Backed up the control file with the ALTER DATABASE BACKUP CONTROLFILE TO filenamestatement (not the TO TRACE option)Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run ALTERDATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, then edit the trace to reflect the change.
Do not have a control file backup in either TO TRACE format or TO filename formatExecute the CREATE CONTROLFILE statement manually (refer to Oracle Database SQL Reference).
Note:
If your character set is not the default US7ASCII, then you must specify the character set as an argument to the CREATE CONTROLFILE statement. The database character set is written to the alert log at startup. The character set information is also recorded in the BACKUP CONTROLFILE TO TRACE output.
To create a new control file:
  1. Start the database in NOMOUNT mode. For example, enter:
    STARTUP NOMOUNT
    
    
  2. Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option (refer to Table 18-2 for options). The following example assumes that the character set is the default US7ASCII:
    CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
         MAXLOGFILES 32
         MAXLOGMEMBERS 2
         MAXDATAFILES 32
         MAXINSTANCES 16
         MAXLOGHISTORY 1600
    LOGFILE
         GROUP 1 (
           '/diska/prod/sales/db/log1t1.dbf',
           '/diskb/prod/sales/db/log1t2.dbf'
         )  SIZE 100K
         GROUP 2 (
           '/diska/prod/sales/db/log2t1.dbf',
           '/diskb/prod/sales/db/log2t2.dbf'
         )  SIZE 100K,
    DATAFILE
         '/diska/prod/sales/db/database1.dbf',
         '/diskb/prod/sales/db/filea.dbf';
    
    
    After creating the control file, the instance mounts the database.
  3. Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):
    RECOVER DATABASE
    
    
  4. Open the database after recovery completes (RESETLOGS option not required):
    ALTER DATABASE OPEN;
    
    
  5. Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf:
    ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;










Materialized view privileges to avoid "ORA-01031: insufficient privileges while create Mview"


Materialized view privileges to avoid "ORA-01031: insufficient privileges while create Mview"

Prerequisites 


The privileges required to create a materialized view should be granted directly rather than through a role.

To create a materialized view in your own schema:

You must have been granted the CREATE MATERIALIZED VIEW system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.

You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create a materialized view in another user's schema:

You must have the CREATE ANY MATERIALIZED VIEW system privilege.

The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example, if the master tables are on a remote database) and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.

To create the materialized view with query rewrite enabled, in addition to the preceding privileges:

If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema.

If you are defining the materialized view on a prebuilt container (ON PREBUILT TABLE clause), then you must have the SELECT privilege WITH GRANT OPTION on the container table.

The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the master table and index of the materialized view or must have the UNLIMITED TABLESPACE system privilege.


When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

How to calculate Undo tablespace size and Undo Retention


How to calculate Undo tablespace size and Undo Retention


Overview

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

However, it is worth to tune the following important parameters

i) The size of the UNDO tablespace

ii) The UNDO_RETENTION parameter

Calculate UNDO_RETENTION  for given UNDO Tabespace

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!

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_SIZE
———-
209715200

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’;

DB_BLOCK_SIZE [Byte]
——————–
                4096



Optimal Undo Retention

209’715’200 / (3.12166667 * 4’096) = 16’401 [Sec]

Using Inline Views, you can do all in one query!

Needed UNDO Size in MB:

SELECT ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((TO_NUMBER(e.value)*TO_NUMBER(f.value)*g.undo_block_per_sec)/(1024*1024),2) "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';

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Optimal UNDO Retention in seconds:

SELECT ROUND(d.undo_size/(1024*1024),2) "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';

The previous query may return a “NEEDED UNDO SIZE” that is less than the “ACTUAL UNDO SIZE”. If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

UNDO_BLOCK_PER_SEC
——————

        3.12166667

ORA-31623: a job is not attached to this session via the specified handle




ORA-31623: a job is not attached to this session via the specified handle - Below error while import was initiating:


Issue:-

Import: Release 11.2.0.4.0

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

Username: /as sysdba

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

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

Cause :-

Above issue can be caused if any registry component is invalid or Oracle is not able to provide sufficient memory to Datapump job.

Solution :-

Crosschecked the DBA _REGISTRY , all components were in VALID state.

Checked the SGA component allocated to database and saw that only SGA_MAX_TARGET is mentioned, SGA_TARGET is 0 . PFB below :-

Datapump use streams pool memory parameter to allocate memory to jobs, if SGA is manually sized then allocate some memory to streams_pool_size

SQL> alter system set streams_pool_size=128M scope=both;

IF ASMM/AMM is used then perform below steps :-

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1280M
sga_target                           big integer 0

Increased the value of SGA target to 4GB  [varies based on your SGA size]

SQL> alter system set sga_max_size=4g scope=spfile;

System altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size            4043309960 bytes
Database Buffers          201326592 bytes
Redo Buffers               28884992 bytes
Database mounted.
Database opened.

SQL> alter system set sga_target=4g;

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 4G
sga_target                           big integer 4G

Tried import after this and it completed without any errors.

I hope this article help you

Thanks

Also, refer - How to resolve the Data Pump error ORA-31623 UDE-31623 (a job is not attached to this session via the specified handle) ? (Doc ID 1907256.1)

How to Drop UNDO Tablespace

It is not an easy task to drop the undo tablespace. Once I have to delete the undo tablespace due to some reason and I find that it is not straight forward to delete the undo tablespace. I got the following error while dropping the error :

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS                               D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
UNDOTBS1                       D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
SYSAUX                             D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
SYSTEM                            D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
EXAMPLE                          D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

As the error indicate that the undo tablespace is in use so i issue the following command.

SQL> alter tablespace undotbs1  offline;
alter tablespace undotbs1  offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace.

Therefore, to drop undo  tablespace, we have to perform the following steps:

 1.) Create new undo tablespace
 2.) Make it default tablespace and undo management manual by editing parameter file and restart it.
 3.) Check the all segment of old undo tablespace to be offline.
 4.) Drop the old tablespace.
 5.) Change undo management to auto by editing parameter file and restart the database

Step 1 : Create Tablespace   :  Create undo tablespace undotbs2    

SQL> create undo tablespace UNDOTBS2 datafile  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'  size 100M;
Tablespace created.

Step 2 : Edit the parameter file

SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.

SQL> alter system set undo_management=MANUAL scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             360711792 bytes
Database Buffers           58720256 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.

SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

Step 3: Check the all segment of old undo tablespace to be offline

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------ ------------------------------ ------------------------------ ----------------
SYS                 SYSTEM                                     SYSTEM                            ONLINE
PUBLIC       _SYSSMU10_1192467665$          UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU1_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU2_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU3_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU4_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU5_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU6_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU7_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU8_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU9_1192467665$           UNDOTBS1                       ONLINE
PUBLIC      _SYSSMU12_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU13_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU14_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU15_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU11_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU17_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU18_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU19_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU20_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU16_1304934663$          UNDOTBS2                        OFFLINE

21 rows selected.

If anyone the above segment is online then change it status to offline by using below command. '

SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

Step  5 : Change undo management to auto and restart the database

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             364906096 bytes
Database Buffers           54525952 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.

SQL> show parameter undo_tablespace
NAME                                       TYPE        VALUE
------------------------------------   ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2