Welcome to Paul Chuks Oracle dba Professional

  Problem:   Omitted "+" when adding datafile in a tablespace in +DG_DATA diskgroup and it mistakenly added it to the filesystem. below is the command that created the problem: alter tablespace SYSAUX add datafile 'DG_DATA' size 500m autoextend on next 5G maxsize 30G; select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX'; system@ORA01 > select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX';    FILE_ID FILE_NAME                                                                   BYTES ONLINE_STA ---------- ---------------------------------------------------------------------- ---------- ----------          2 +DG_ORA/ora01/ora_sysaux            ...

 How to quickly resolve archiver error in oracle database 11g, 12c, 19c ORA-00257: archiver error. Connect internal only, until freed. First check the location and size of the archive log location with below command: SQL>  show parameter recovery NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                 string      +FRA db_recovery_file_dest_size            big integer 1000G recovery_parallelism                 integer     0 As you can see, the locations is +FRA inside ASM storage and the size is 1TB. Next check the free space you have available in your ASM location with below query: SQL> select name , total_mb/1...

When audit directory adump get filled, it prevents application from further connection and throw below error : ERROR: ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 28: No space left on device Additional information: 9925 ORA-02002: error while writing to audit trail ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 28: No space left on device Additional information: 9925 At this stage, as a DBA, you need to urgently resolve the issue because everyone will be calling that they cannot access the database. The first thing to do is to check if the file syste is filled up which might not show you 100% because is only the size defined for audit location is reached when you run df -h. However, the inode of the filesystem has reached 100% and that is why it cannot create further login audit files and so could not allow anyone login to the database until the space is freed. To find out if the inode is filled, run df -ih command on the linux syste...

https://drive.google.com/file/d/0BwyzpvMqgJ_3YlItckpZWlNaY0E/view?usp=sharing

EM 12c : Agent Raises Periodically Alerts for Target Agent : Internal error detected: java.lang.IllegalStateException:oracle.sysman.gcagent.target.interaction.execution.ConfigStateMgr:798 (Doc ID 1458858.1)

imp \"/ as sysdba\" parfile=imp_filename_dec2014.par file=/filesystem_path/SID/filename_in_dec2014.dmp log=/filesystem_path/SID/imp_filename_dec2014.log fromuser=USERA touser=USERB

The following steps will help you revive an un-responsive KVM on your linux work station.... fixing an unresponsive VM..... 1. Launch a terminal from the linux session... and logon using sudo 2. make sure the VM file is running.. by running #virsh list --all 3. If the VM is not listed under the running VMs, then go to /etc/libvirt/qemu and check if xml file for the affected VM is there 4. check /var/lib/libvirt/qemu/save/ for a copy of the affected vm (vm_name.save) and delete it 5. run virsh start (vm_name) This five short steps can help you save your VM...

Use this query to get the password of the user in the database: select user#, name,password from SYS.user$ where name = '<USERNAME>'; Then use this below to reset the password alter user <username> identified by values '<PASSWORD>';

when getting error while starting RAC db,  and all the raw device for storage has the right permission and +ASM is up but db refuse to start with below error: oradb@HOSTNAME01:/dev $ ls -lrt asm-disk* lrwxrwxrwx 1 root root 3 Mar  6 20:43 asm-disk3 -> sde lrwxrwxrwx 1 root root 3 Mar  6 20:43 asm-disk2 -> sdc lrwxrwxrwx 1 root root 3 Mar  6 20:43 asm-disk5 -> sdg lrwxrwxrwx 1 root root 3 Mar  6 20:43 asm-disk4 -> sdf lrwxrwxrwx 1 root root 3 Mar  6 20:48 asm-disk1 -> sdd oradb@HOSTNAME01:/dev $    INST_ID GROUP_NUMBER NAME                      State ---------- ------------ ------------------------- -----------          1            1 DG_01                ...

SUMMARY ======== "DG01" diskgroup cannot be mounted due to one disk member is missing: ====================================================== SQL> ALTER DISKGROUP DG01 MOUNT /* asm agent *//* {1:16801:2} */ NOTE: cache registered group DG01 number=1 incarn=0x2929b317 NOTE: cache began mount (first) of group DG01 number=1 incarn=0x2929b317 NOTE: Assigning number (1,1) to disk (/dev/asm-disk5) Sat Feb 14 06:41:49 2015 ERROR: no read quorum in group: required 1, found 0 disks NOTE: cache dismounting (clean) group 1/0x2929B317 (DG01) NOTE: messaging CKPT to quiesce pins Unix process pid: 7194, image: oracle@SLSLMEDIDBOP01 (TNS V1-V3) NOTE: dbwr not being msg'd to dismount NOTE: lgwr not being msg'd to dismount NOTE: cache dismounted group 1/0x2929B317 (DG01) NOTE: cache ending mount (fail) of group DG01 number=1 incarn=0x2929b317 NOTE: cache deleting context for group DG01 1/0x2929b317 GMON dismounting group 1 at 18 for pid 29, osid 7194 NOTE: Dis...

SOLUTION for rman error: RMAN-06429: RCVCAT database is not compatible with this version of RMAN $ rman target / catalog rman/********@CATDB Recovery Manager: Release 11.2.0.3.0 - Production on Sat Feb 14 18:05:00 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database:XXX (DBID=607******5) connected to recovery catalog database recovery catalog is partially upgraded to 11.02.00.03 RMAN> crosscheck archivelog all; ORACLE error from recovery catalog database: ORA-06550: line 1, column 41: PLS-00201: identifier 'DBMS_RCVMAN.GETPACKAGEVERSION' must be declared ORA-06550: line 1, column 34: PL/SQL: Statement ignored recovery catalog is partially upgraded to 11.02.00.03 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of crosscheck comman...

When preparing to taking export backup of application tables from a target database(source) and importing to another database(destination), You need to do the following: 1. You need to create directory where the dump file to be created with reside. CREATE DIRECTORY DUMPDIR AS '<filesystem_path with enough space>'; example: CREATE DIRECTORY DUMPDIR AS '/u01/export/dumpdir'; 2. Grant required permission to the directory. GRANT READ, WRITE ON DIRECTORY DUMPDIR TO <USERNAME>; 3. Select the properties of the objects/tables to be exported in the source database. select owner,segment_name,segment_type,tablespace_name, sum(bytes/1024/1024) from dba_segments where segment_name IN ('table_name','object_name','table_name','table_name'...) group by owner,segment_name,segment_type,tablespace_name ; example: --------------------------------------------------------------------------------------------------------------------------------- OWNE...

This script is handy when you are having space challenge in your storage either +asm or filesyste. First connect to the target database and then to the catalog database. crosscheck archivelog all; resync catalog; run { allocate channel c1 TYPE 'sbt_tape'; allocate channel c2 TYPE 'sbt_tape'; allocate channel c3 TYPE 'sbt_tape'; backup archivelog all delete input; release channel c1; release channel c2; release channel c3; }

SQL> show parameter db_files NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_files                             integer     200 SQL> select count(*) from v$datafile;   COUNT(*) ----------        200 This issue result as a result of datafiles in the database has reached the number of db_files parameter in the database. To resolve this, follow the below steps: First, take the backup of the spfile with the below command: cd $ORACLE_HOME/dbs $ cp -p spfileSID.ora spfileSID.ora_11082014 Also create a b...

How to resolve error ORA-15032,ORA-15031,ORA-15025,ORA-15056 when adding a disk to asm. SQL> ALTER DISKGROUP ASM_FRA ADD DISK '/dev/dm-51'; ALTER DISKGROUP ASM_FRA ADD DISK '/dev/dm-51' * ERROR at line 1: ORA-15032: not all alterations performed ORA-15031: disk specification '/dev/dm-51' matches no disks ORA-15025: could not open disk "/dev/dm-51" ORA-15056: additional error message Linux-x86_64 Error: 13: Permission denied Additional information: 42 Additional information: 1927546192 I wanted to add  the following disk: (/dev/dm-51,/dev/dm-52,/dev/dm-53) First check the permissions on this files after the Unix admin has provision it. $ls -lrt /dev/dm* brw-rw---- 1 root disk 253, 19 Jan 31 13:46 /dev/dm-19 brw-rw---- 1 root disk 253, 53 Mar 2 21:04 /dev/dm-53 brw-rw---- 1 root disk 253, 52 Mar 2 21:04 /dev/dm-52 brw-rw---- 1 root disk 253, 51 Mar 2 21:04 /dev/dm-51 brw-rw---- 1 root disk 253, 46 Mar 2 21:12 /dev/dm-46 brw-r...

CLEANING APPLICATION CONNECTIONS WHEN MAXIMUM NUMBER OF SESSIONS EXCEEDED. When users can't login to the database or their processes are hanging and probably getting this error: ERROR at line 1: ORA-12801: error signaled in parallel query server PZ99, instance <hostname>:<SID> (2) ORA-00018: maximum number of sessions exceeded Follow the below steps to resolve immediately so that users can connect to database: 1. login as a privilege OS user (example oracle owner) in the node having the issue. 2. run this command to show all the processes of application connection handles to the database: $ ps -ef|grep LOCAL=NO 3. Use this command to clean up connections so that users can connect: ps -ef|grep LOCAL=NO| awk '{print $2}'| xargs -i{} kill -9 {}

Script to get the USER CREATION SCRIPT in the database: set escape on select 'create user ' || U.username || ' identified ' || DECODE(password, NULL, 'EXTERNALLY', ' by values ' || '''' || password || '''' ) || chr(10) || 'default tablespace ' || default_tablespace || chr(10) || 'temporary tablespace ' || temporary_Tablespace || chr(10) || ' profile ' || profile || chr(10) || 'quota ' || decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) || ' on ' || default_tablespace || decode (account_status,'LOCKED', ' account lock', 'EXPIRED', ' password expire', 'EXPIRED \& LOCKED', ' account lock password expire', null) ||';' "Create user scripts" from dba_users U, dba_ts_quotas Q where U.username = '<USERNAME>' and U.username=Q.username(+) and U.default_tablespace=Q.tables...

exec dbms_job.broken( , FALSE); exec dbms_job.run( ); Example: exec dbms_job.broken(111033, FALSE); exec dbms_job.run(111033);

To set up your SQLPATH environmental variable. 1. From run cmd, type regedit and press enter. 2. Under SOFTWARE, expand it, then select ORACLE, expand it, select KEY_Oraclient10g_Home1. 3. On the right pane, double click the SQLPATH. 4. Then add the path for the location of your sql on the hard disk example . 5. And press enter or OK.