Monthly Archives: February 2014

FTP file transfer with an automated bash script


#!/bin/bash HOST=’your.ftp.site’ USER=’remoteusername’ PASSWD=’remotepasswd’ ftp -n -v $HOST << EOT ascii user $USER $PASSWD prompt cd upload ls -la bye EOT

ORA-02065 when change max_dump_file_size


SQL> alter system set max_dump_file_size=4000M; alter system set max_dump_file_size=4000M * ERROR at line 1: ORA-02065: illegal option for ALTER SYSTEM But this one will work: SQL> alter system set max_dump_file_size=’4000M’; System altered.

Create 11g rman catalog


killing expdp/impdp job


step 1:  take the job_name from below views select * from USER_DATAPUMP_JOBS; select * from DBA_DATAPUMP_SESSIONS; step 2: try to attach to that job_name which you find out in step1. oracle@aa: /u02/backups # impdp system/***** attach=test_DUMP11(job_name) step 3:  stop job immediate impdp> STOP_JOB=IMMEDIATE impdp>kill_job

Creating a Recovery Catalog


$ sqlplus /nolog CONNECT SYS/passwd@OCM AS SYSDBA; CREATE TABLESPACE rman DATAFILE ‘/u02/app/oradata/rman/rman01.dbf’ size 100m; Create the Recovery Catalog Owner in the new database (OCM) CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman; SQL> GRANT connect, resource, recovery_catalog_owner TO rman; $ rman catalog rman/passwd@OCM Recovery Manager: Release 10.2.0.3.0 – Production on […]

List Failure, Advise Failure and Repair Failure with Oracle 11g


Oracle 11g / RMAN List Failure


The 11g version of RMAN has a useful feature for diagnosing and repairing any failures. Find any database failures: RMAN> list failure; Determine manual or automatic repair: RMAN> advise failure; From the output of the advise, try to manually fix the failure. If this fails to work, then try the repair command: RMAN> repair failure;

Read-Only Tables in Oracle 11g (practic test)


SQL> create table test (id number ,name varchar2(12)); Table created. SQL> insert into test values (1,’ramil’); 1 row created. SQL> insert into test values (2,’rashad’); 1 row created. SQL> insert into test values (3,’amil’); 1 row created. SQL> insert into test values (4,’eldar’); 1 row created. SQL> commit ; Commit complete. SQL> select * from […]

How to fix ORA-01654 unable to extend index in tablespace


When you get an error like: How to fix ORA-01654 unable to extend index in tablespace, then this article is for you!. The problem in Oracle is that when you delete a record, Oracle will leave it blank. That’s a real waste of space when you delete thousands of records. So deleting records will not […]

How to Apply Opatch on Oracle RAC


1: Check Oracle service status [oracle@rac-node1 ~]$crs_stat -t Name Type Target State Host ———————————————————— ora….SM1.asm application ONLINE ONLINE dbsr…ode1 ora….E1.lsnr application ONLINE ONLINE dbsr…ode1 ora….de1.gsd application ONLINE ONLINE dbsr…ode1 ora….de1.ons application ONLINE ONLINE dbsr…ode1 ora….de1.vip application ONLINE ONLINE dbsr…ode1 ora….SM2.asm application ONLINE ONLINE dbsr…ode2 ora….E2.lsnr application ONLINE ONLINE dbsr…ode2 ora….de2.gsd application ONLINE ONLINE dbsr…ode2 ora….de2.ons […]

Compile INVALIDS of any SCHEMA


Earlier to this, I used the following commands to compile INVALID objects of any database 1. @?/rdbms/admin/utlrp 2. exec utl_recomp.recomp_parallel(threads=>5) 3. alter package/ procedure compile … comand I found one more utility that will help us to compile the invalids of any particular schema exec dbms_utility.compile_schema(‘<schema_name>’, FAlSE); Ex; exec dbms_utility.compile_schema(‘SCOTT’, FAlSE); We used “FALSE” as […]

Error 1031 received logging on to the standby


Suppressing further error logging of LOG_ARCHIVE_DEST_2. Fri Nov 6 08:45:53 2009 Error 1031 received logging on to the standby Suppressing further error logging of LOG_ARCHIVE_DEST_2. Fri Nov 6 08:50:53 2009 Error 1031 received logging on to the standby Suppressing further error logging of LOG_ARCHIVE_DEST_2. Fri Nov 6 08:55:53 2009 Error 1031 received logging on to […]

How to merge patches in ORACLE APPS R12/11i


Step 1. Create two directory “source” and “dest”  cd home  mkdir source  mkdir dest  Step 2. Unzip patch into source directory.  cd home  $ ls -ltr pABC.zip  -rw-r–r– 1 egaprt appseeg 0 Aug 18 06:06 pABC.zip  unzip pABC.zip -d source  Step 3: Now, Merge the patch  admrgpch -s source -d dest -merge_name ABC_merge  Step 4: […]

Read – only Standby and Active Data Guard feature in Oracle Data Guard


Steps to Switch standby database in Read – Only mode :- SQL> SHUTDOWN IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size                  2213384 bytes Variable Size           […]

Oracle Software Patching Using OPatch – Interim Patches


OPatch is an Oracle utility that assists you to apply interim patches to Oracle’s rdbms software. You can find opatch utility in $ORACLE_HOME/Opatch directory. For example, let’s apply patch number 8943287  to our 10.2.0.5 database. Applying Patch: 1- Backup Oracle Home directory. $ tar -cf ora10g.tar ora10g 2- Download patch file p8943287_10205_Linux-x86-64.zip via metalink. And copy it to […]

Password Verify Function (test)


bash-3.2$ pwd /u01/app/oracle/product/11.2.0.2/db_1/rdbms/admin bash-3.2$ sqlplus / as sysdba: SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 10 21:14:12 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @?/rdbms/admin/utlpwdmg.sql; Function created. Profile altered. […]

Profiles and Password Verify Function – Oracle 11g


Verify Function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. In Oracle Database 11g, verify_fnction_11g function could be found on password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin. At the end of the script […]

SQL command to find size of Oracle database


######## datafiles size ########## SQL> select sum(bytes/1024/1024/1024) from dba_data_files; ######## actual database size ######### SQL> select sum(bytes/1024/1024/1024) from dba_segments;

ORA-00020: maximum number of processes (600) exceeded


bash-3.2$ export ORACLE_SID=ANARDB bash-3.2$ bash-3.2$ rman Recovery Manager: Release 11.2.0.3.0 – Production on Mon Feb 10 09:00:00 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connect target /; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== ORA-00020: maximum number of processes (600) exceeded RMAN> RMAN> RMAN> exit […]

How to install Statspack in an Oracle database