RAM&KRISHNA BLOG

ERROR: ORA-07391: sftopn: fopen error, unable to open text file. - SOLUTION - Specify file locations & try to create the file again. Like below SQL> create pfile='/backup/expdp/initdev.ora' from spfile='/oracle/product/10.2.0/db_1/dbs/spfiledev.ora'; File created. Thank You !

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG

ERROR: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 12.34 GB ORA-31693: Table data object "TEST"."TEST_SEAT" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01466: unable to read data - table definition has changed^@ SOLUTION: Wait for Some Time & start the export again. The Issue will get resloved.

The below are the steps. I have followed. The database is in no archive log mode. E:\Documents and Settings\KRISHNA>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 29 22:55:36 2014 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select name from v$database; NAME --------- RISHI SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\USERS01.DBF E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSAUX01.DBF E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\UNDOTBS01.DBF E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSTEM01.DBF Here I will be changing USERS01.DBF file location. SQL> select open_mode,log_mode from v$database; OPEN_MODE  LOG_MODE ---------- ------------ READ WRITE

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes Database Buffers          436207616 bytes Redo Buffers          

Recently I faced the below error, while exporting & importing schema stats from Prod - Uat database. SQL> BEGIN dbms_stats.import_schema_stats  ('TESTU','TESTP_STATS'); END; * ERROR at line 1: ORA-20000: Unable to set values for column TEST_COLUMN: does not exist or insufficient privileges ORA-06512: at "SYS.DBMS_STATS", line 7346 ORA-06512: at "SYS.DBMS_STATS", line 7373 ORA-06512: at "SYS.DBMS_STATS", line 8038 ORA-06512: at line 1 SOLUTION: 1. First I have tried giving the ANALYZE permission to the user. GRANT ANALYZE ANY TO "SYSTEM"; But after giving the above permission, still that issue is not resloved. 2. Now I have tried deleting that column from the temporary stats table. Which will create as part of export & import stats. SQL> desc testp_stats;  Name                                      Null?    Type  ----------------------------------------- -------- -------------------------

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. SQL>  select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------        

1. Display the number of CPUs             cat /proc/cpuinfo|grep processor|wc –l 2. Show top CPU%                          ps aux|sort -n +2 3. Display top-10 CPU consumers          ps aux|sort -rn +2|head -10 4. Shutdown server as root                /sbin/shutdown -r now 5. Kill all xxx processes                pkill [-9] “xxx” 6. Show swap paging space                /sbin/swapon -s 7. Show Linux syslog errors              tail /var/log/messages 8. See held memory segments              ipcs -m 9. Show Linux system parms               sysctl -a 10. Linux command history files           history|more 11. Huge pages ---------- grep -i huge /proc/meminfo 12. CPU INFO ------------ cat /proc/cpuinfo | grep processor | awk '{a++} END {print a}'  13. ps -ef | grep tns ==> To get current running listeners in a server 14. Clear a file using /dev/null in Linux $ cat /dev/null > file.txt - Example

                                                    FLASHBACK TECHNOLOGY 1     Here will see how to enable the flashback mode & create a restore point/restore as well in an ORACLE database. if the database running  noarchivelog mode. Points to know           -------------------------------------------------------------------------------------------------------- FlashBack technology is oracle 10G new feature. To enable the flashback technology the database has to be in Archivelog mode. Using flashback technology we can create a RESTORE point and also we can restore any tables and users dropped.  Here we are doing whole Flashback database.         ------------------------------------------------------------------------------------------------------------ As I mentioned above. To enable flashback feature the database has to be in archivelog mode. Using the below query we can check whether a database running archivelog mode (OR) not. connect to

Duplicating a database using Recovery Manager  (RMAN) NOTE:  Here host name is same for both source & Target db's Following are the steps that we need to perform. Source Database :PRMY001 Duplicate Database Name: DPRMY001 -------------------------------------------------------------------------------------------------------------------------- STEP 1:  Create password for destination database.  In $ORACLE_HOME/dbs location like below. orapwd file=$ORACLE_HOME/dbs/orapwdprmy001 password=dprmy001 entries=3 force=y -------------------------------------------------------------------------------------------------------------------------- STEP 2:  Create necessary folders for destination duplicate database. [oracle@oradba dprmy001]$ ls    > List all files/Directories in  present DIR adump bdump cdump dpdump pfile scripts udump [oracle@oradba dprmy001]$ pwd  > Shows present working DIR /u01/app/oracle/admin/dprmy001 ------------------------------