/usr/bin/find: Argument list too long error

When /usr/bin/find: Argument list too long error

USE THIS:

find /u01/app/oracle/diag/rdbms/TEST/TEST/trace/ -name '*.trm'  -mtime +30 -exec /bin/rm {} \;

INSTEAD OF THIS!!!

find /u01/app/oracle/diag/rdbms/TEST/TEST/trace/*.trc +30 -exec /bin/rm -rf {} \;

READ ONLY tables in oracle 11g

In Oracle 11g, we can change the table to READ ONLY mode which doesn't allow any DML over it.

Below is a tested scenario.

SQL> alter table scott.emp read only;

Table altered.

SQL> select

owner,

table_name,

read_only from dba_tables

where owner='SCOTT'

and table_name='OBJECTS';

OWNER TABLE_NAME READ_ONLY

---------- --------------- ---------------

SCOTT EMP YES

SQL> update scott.emp

set owner='SCOTT';

update objects

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "EMP"."SCOTT"

SQL> delete from scott.emp where empno = 7369;

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP"

Now change the mode of table to read write again.

SQL> alter table scott.emp read write;

Table altered.

SQL> select owner,table_name,read_only from dba_tables where owner='SCOTT'

2 and table_name='EMP';

OWNER TABLE_NAME READ_ONLY

---------- --------------- ---------------

SCOTT EMP NO

SQL> truncate table SCOTT.EMP;

Table truncated.

Log all database errors to a table

drop trigger log_errors_trig;
drop table   log_errors_tab;

create table log_errors_tab (
 error     varchar2(30),
 timestamp date,
 username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
 process   varchar2(8),
 program   varchar2(48));

create or replace trigger log_errors_trig 
 after servererror on database
declare
 var_user     varchar2(30);
 var_osuser   varchar2(30);
 var_machine  varchar2(64);
 var_process  varchar2(8);
 var_program  varchar2(48);
begin
 select username, osuser, machine, process, program
 into   var_user, var_osuser, var_machine, var_process, var_program
 from   sys.v_$session
 where  audsid = userenv('sessionid');

 insert into log_errors_tab
   values(dbms_standard.server_error(1),sysdate,var_user,
          var_osuser,var_machine,var_process,var_program);
end;
/

Problem: ORA-10631 errors encountered during "SHRINK SPACE" operation

OEM was nice the other day and offered some Segment Adviser Recommendations to shrink a few tables that had become fragmented. However, when I ran the shrink job, it failed.

PROBLEM:

• "ALTER TABLE my_test_table SHRINK SPACE;" results in "ORA-10631: SHRINK clause should not be specified for this object"

SOLUTION:

• Tables that have function-based indexes cannot be compacted using the SHRINK clause
• Apparently, someone forgot to inform OEM of this fact (thanks the bogus recommendations! :-)
• See MetaLink Bug No.:5968539

Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT   dt.owner, dt.table_name,
         (CASE
            WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
            ELSE 'N'
         END) AS can_shrink
    FROM dba_tables dt,
         (SELECT   table_name, COUNT(*) cnt
              FROM dba_indexes di
             WHERE index_type LIKE 'FUNCTION-BASED%'
          GROUP BY table_name) ind
   WHERE dt.table_name = ind.table_name(+)
     AND dt.table_name NOT LIKE 'AQ$%'
     AND dt.table_name NOT LIKE 'BIN$%'
     AND dt.owner = 'NADVI'
ORDER BY 1, 2;

SED – Search and replace string:

If you want to change all occurrences of developer to administrator in the oracle.txt file in the grep example, enter this:

Content of oracle.txt

Oracle database developer

SQL database developer

MYSQL database developer

sed 's/developer/administrator/g' oracle.txt

See the changes below after running SED.

oracle database administrator

SQL database administrator

MYSQL database administrator

In the quoted string, the "s" means substitute, and the "g" means make a global change. You can also leave off the "g" (to change only the first occurrence on each line) or specify a number instead (to change the first n occurrences on each line).

Searching and replacing several strings on a file:

example:

sed 's/-//g' test.txt |sed 's/"//g' | sed 's/;//g' | sed 's/\[//g'

Take care of any escape character using additional ‘\’ while searching for ‘[‘

Temp Tablespace recreate to resize / Shrink

In 11g, there is a new SQL command that can be used to shrink temporary tablespaces. This command can be used to shrink only locally managed temporary tablespaces :

ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

The SHRINK SPACE clause allows a user to shrink a temporary tablespace, whereas SHRINK TEMPFILE allows the shrink of a temporary file. The optional KEEP clause defines the lower bound that a tablespace can be shrunk to. It is the opposite for MAXSIZE for auto extensible tablespace. If it is not specified, the system will try to shrink as much as possible, as long as the other storage attibutes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option.  

Below steps are OK for any Oracle Versions Release: 8.1.5 to 11.2


1. Create another temp tablespace with moderate size on temp tablespace datafile location

CREATE  TEMPORARY TABLESPACE "TEMP2"

TEMPFILE '/mylocation/temp/temp02.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT  100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

2. Make the new TEMP tablespace as default for database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Drop old big default tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

4. Re create that big TBS with new size

CREATE

TEMPORARY TABLESPACE "TEMP" TEMPFILE '/mylocation/temp/temp01.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT  100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

5.  Make the resized TEMP TBS as default for Database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

6. Drop second temp tablespace.

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

While trying to drop first temp tbs, if users are still using that tablespace you won't be able to drop it. 


Following is the work around:

1. Find out the all the sessions that are not active and have an entry in V$sort_usage.
You can do it by

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

2. Kill those session Using
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually. where SID_NUMBER and SERIAL#NUMBER is found in step 


3. Drop old tablespace 

Create password file for SYS

Cd to $ORACLE_HOME/dbs

orapwd file=orapw password=<>same as SYS> entries=10

Recreate EM DBCONSOLE :

Remove the following directories from your filesystem:
/
/oc4j/j2ee/OC4J_DBConsole__

If the dbcontrol is upgraded from lower version, for example, from
10.2.0.3 to 10.2.0.4, then the following directory also needs to be removed from the file system.
/.upgrade
/oc4j/j2ee/OC4J_DBConsole__.upgrade

NOTE:
On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it

Alternatively on Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete '
- where is the DB Control service name (typically: OracleDBConsole)

OR use the command,

'nmesrvops delete '
- where is the DB Control service name (typically: OracleDBConsole)

Also available from Microsoft is the delsrv.exe command. (Free download from Microsoft)



Step 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYS
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);

Step 2: Drop the DB Control Repository Objects

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;


to create the dbconsole you need to:

ORACLE_HOME/bin/emca -config dbcontrol db -repos create

Rename ASM Diskgroups

Rename diskgroup option is a new feature of 11gR2  but can be done for 10g & 11gR1 as well.
It requires installing 11gR2 SIHA Grid Infrastructure software only installation.

cd <11gr2 grid home>/bin
use renamedg tool to rename 10g or 11gR1 diskgroup.
Diskgroup cannot be renamed under following conditions :

- the Diskgroup is mounted
- the Diskgroup is being used by CSS
- Diskgroup contains offline disks

1) First of all, the desired diskgroup needs to be dismounted (from each node if this is a RAC configuration):

# asmcmd umount DATA

2) Verify that the desired diskgroup was dismounted:

# asmcmd lsdg

3) Then execute the rename statement:

# renamedg phase=both dgname=DATA newdgname=ORACLE_DATA1 verbose=true

4) Finally, mount the renamed diskgroup and validate that it was mounted (on each node if this is a RAC configuration):

# asmcmd mount ORACLE_DATA1

# asmcmd lsdg