Category: Databases

I was running an export to get an estimate of the size of the dump file for the data in a list of tables. The parameter file contained the below options:

job_name=dean_expdp_data
estimate_only=yes
CONTENT=DATA_ONLY
DIRECTORY=DEAN_DATA_PUMP
PARALLEL=4
exclude=statistics
exclude=DB_LINK
exclude=tablespace
exclude=DIRECTORY
schemas=SCHEMA1,SCHEMA2,SCHEMA3
exclude=table:"IN('BIG_TABLE_01','BIG_TABLE_02')"
LOGFILE=dean_expdp_data.log

Ran the command

expdp \'/ as sysdba \' parfile=dean_expdp_data.par

and got error

[oracle@pdcigusprdb1 datapump]$ expdp \'/ as sysdba \' parfile=dean_expdp_data.par

Export: Release 12.1.0.2.0 - Production on Fri Nov 17 19:01:54 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."DEAN_EXPDP_DATA":  "/******** AS SYSDBA" parfile=dean_expdp_data.par 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE_DATA:"SCHEMA"."TABLE_NAME"] 
ORA-31600: invalid input value  for parameter TABLE_EXISTS_ACTION in function KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x75a5a7aa0     27116  package body SYS.KUPW$WORKER
0x75a5a7aa0     11286  package body SYS.KUPW$WORKER
0x75a5a7aa0     20583  package body SYS.KUPW$WORKER
0x75a5a7aa0     15739  package body SYS.KUPW$WORKER
0x75a5a7aa0     10613  package body SYS.KUPW$WORKER
0x75a5a7aa0     13357  package body SYS.KUPW$WORKER
0x75a5a7aa0     17209  package body SYS.KUPW$WORKER
0x75a5a7aa0      5235  package body SYS.KUPW$WORKER
0x75a5a7aa0     12091  package body SYS.KUPW$WORKER
0x75a5a7aa0      2081  package body SYS.KUPW$WORKER
0x7174e4340         2  anonymous block

KUPC$QUEUE_INT.SEND
KUPC$QUEUE_INT.SEND
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
In procedure CREATE_TABLE_DATA_OBJECT_ROWS
In function GATHER_PARSE_ITEMS
In function CHECK_FOR_REMAP_NETWORK
Nothing to remap
In procedure BUILD_OBJECT_STRINGS
In function PROCESS_TABLE_EXISTS_ACTION
In procedure DETERMINE_FATAL_ERROR with ORA-31600: invalid input value  for parameter TABLE_EXISTS_ACTION in function KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION
Job "SYS"."DEAN_EXPDP_DATA" stopped due to fatal error at Fri Nov 17 19:01:56 2017 elapsed 0 00:00:02

According to Oracle Support “DataPump Export (EXPDP) Fails With Errors ORA-39126 ORA-31600 (Doc ID 1508068.1)”

use CONTENT=ALL instead of CONTENT=DATA_ONLY when using ESTIMATE_ONLY=Y parameter

I changed parameter file and ran the export and got the error

Export: Release 12.1.0.2.0 - Production on Fri Nov 17 19:42:50 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: "SYS.DEAN_EXPDP_DATA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object

As the previous export had failed, I had to clean up dba_datapump_jobs:

SQL> SELECT owner_name, job_name, operation, job_mode, 
  2     state, attached_sessions 
  3     FROM dba_datapump_jobs 
   where owner_name='SYS' and state='NOT RUNNING'
ORDER BY 1,2; 

  4    5  
OWNER_NAME JOB_NAME         OPERATION  JOB_MODE STATE         ATTACHED_SESSIONS
SYS        DEAN_EXPDP_DATA  EXPORT     SCHEMA   NOT RUNNING                   0

Drop the table:

SQL> drop table DEAN_EXPDP_DATA;

Table dropped.

SQL> 

Reran the export and got the estimate. However, it sort of defeated the purpose as CONTENT=ALL includes the meta data in the size of the export.

Total estimation using BLOCKS method: 596.3 MB
Job "SYS"."DEAN_EXPDP_DATA" successfully completed at Fri Nov 17 19:47:31 2017 elapsed 0 00:00:02

Prior to 12c, the process to rename/relocate a data file consisted off

-  taking the tablespace offline
-  renaming the datafiles using OS commands
-  issuing an 
     ALTER TABLESPACE data_01 
           RENAME DATAFILE '/u01/oradata/data_01.dbf' TO '/u02/oradata/data_01.dbf';
-  bring the table space online

With 12c, this is much easier. You can accomplish this with:

ALTER DATABASE MOVE DATAFILE '/u01/oradata/data_01.dbf' TO '/u02/oradata/data_01.dbf';

One way to drop a RAC database without using DBCA.

This happened to be a smaller non-production database and I had the luxury of taking an export before I dropped the database. All the users had already moved to the new database.

Create a data pump directory (if you do not already have one).

CREATE or replace DIRECTORY dean_data_pump AS '/backup/UATDB/DATAPUMP';

Perform an export with the command:

cd /backup/UATDB/DATAPUMP
nohup expdp \'/ as sysdba \' directory=DEAN_DATA_PUMP dumpfile=UATDB_full_2017_09_18.dmp logfile=UATDB_full_2017_09_18.log full=yes &

Monitor the nohup.out file for errors.

Check the current status of the database. This is a 2 node RAC and is currently running on both nodes

[uatsrvr2-UATDB] srvctl status database -d UATDB
Instance UATDB1 is running on node uatsrvr1
Instance UATDB2 is running on node uatsrvr2

Stop all nodes of the RAC

srvctl stop database -d UATDB

Repeat the status check

[uatsrvr2-UATDB] srvctl status database -d UATDB
Instance UATDB1 is not running on node uatsrvr1
Instance UATDB2 is not running on node uatsrvr2

Start SQL*Plus to change the cluster_database parameter

[uatsrvr2-UATDB2] sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 10:35:26 2017

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 4810256384 bytes
Fixed Size                  226136 bytes
Variable Size            13421810 bytes
Database Buffers         3456106 bytes
Redo Buffers                97070 bytes
Database mounted.
SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Bring up the database in exclusive mode with restricted session

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 4810256384 bytes
Fixed Size                  2261368 bytes
Variable Size            1342181000 bytes
Database Buffers         3456106496 bytes
Redo Buffers                9707520 bytes
Database mounted.

Check if the database is in restricted session and cluster is off and then exit from SQL*Plus

SQL> select logins,parallel from v$instance;

LOGINS     PAR
---------- ---
RESTRICTED NO

SQL> exit

Connect to the database via RMAN

[uatsrvr2-UATDB2] rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 10:43:07 2017

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

connected to target database: UATDB2 (DBID=407684853, not open)

Drop the database, including all the backups

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "UATDB" and DBID is 407684853

Do you really want to drop all backups and the database (enter YES or NO)? yes

using target database control file instead of recovery catalog
allocated channel: ORA_1
channel ORA_DISK_1: SID=2 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
3535    3535    1   1   AVAILABLE   DISK        /backup01/UATDB/backups/UATDB_db_20170911_eise5da4_1_1.bus
.
.
Many more lines of output
.
.
database name is "UATDB" and DBID is 407684853
database dropped

RMAN> 

Remove the configuration from SRVCTL

[uatsrvr2-UATDB] srvctl remove database -d UATDB
Remove the database UATDB? (y/[n]) y
[uatsrvr2-UATDB] 

The following steps will have to be performed on all nodes of the RAC. Edit the /etc/oratab and remove the entry for the database.

If auditing is turned on, delete the audit files from the audit destination.

Delete any CRON entries, trace files, password files, etc.

A fellow DBA supported an application that had a process that compiled a number of packages every night as part of a scheduled script. Sample command:

ALTER PACKAGE schema.package_name COMPILE PACKAGE;
 
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
 

Over the last couple of days this script began to fail every night. I used the below SQL to identify the blocking session:

select    * 
from      gv$active_session_history
where     sample_time > sysdate -  (interval '1' day)
  and     sql_opname = 'ALTER PACKAGE'
  and not blocking_Session is null
order by  sample_time
;

This SQL returned the information identifying the blocks with the sample time exactly matching the script’s scheduled run time. I used the sample time, blocking session id, session serial and instance id and ran the below SQL:

select * 
from   gv$active_session_history
where sample_time between 
                  to_date('07-AUG-17 08.01 PM',
                          'dd-mon-yy hh.mi pm') 
                  and 
                  to_date('07-AUG-17 09.32 PM',
                          'dd-mon-yy hh.mi pm')
  and  session_id      = 
  and session_serial# = 
  and inst_id         = 
order by sample_time
;

This provided me the last sql_id that the blocking session had executed.

select  * 
from    gv$sql
where   inst_id = 
  and   sql_id in 'sql id'
;

The last SQL that the blocking session had executed happened to be a select. However, working on the assumption that it had previously performed a DML, I cancelled the session and the ALTER PACKAGES script was able to execute successfully.

The below MS Word Document contains the steps to add disk groups to ASM via ASMCA.
Adding disk groups to ASM

I needed to extract the DDL for a few profiles. This DDL was a quick and easy way to do it:

select dbms_metadata.get_ddl('PROFILE',a.profile) from dba_profiles a where a.profile = '' and rownum = 1;