/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

DROP QUEUE TABLES

While trying to drop a user with QUEUE table objects; it won't let you drop with below warnings.

ORA-00604: error occurred at recursive SQL level 1
ORA-24170: SCOTT.TEST_QUEUE_R is created by AQ, cannot be dropped directly
ORA-06512: at "SYS.DBMS_RULE_ADM", line 158
ORA-06512: at line 1

It requires to drop QUEUE tables first from user and then can be dropped.

--FIND THE QUEUE TABLES BY LOGGING as USER SCOTT.

select * from dba_tables
where table_name like 'AQ%'
and tablespace_name = 'USERS'

-- Grant execute on DBMS_AQDM to the user SCOTT having queue tables.

GRANT EXECUTE ON DBMS_AQADM to SCOTT


-- LOGIN AS SCOTT DROP QUEUE TABLES

select * from user_tables
where table_name like 'AQ%'

exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_TEST_IAI_NR',force => TRUE);

--- NOW DROP SCOTT as SYS

Alternate link for Metalink

Today I was facing issues accessing metalink (https://support.oracle.com/). Seems the site is down.

Then I found the below alternate link on oracle forum that worked.

https://supporthtml.oracle.com/ep/faces/index.jspx

Hopefully this would work for any future unplanned outage!!

RMAN Recover Error ORA-01547

When attempting to perform a point-in-time recovery from a hot backup you receive an ORA-01547 followed by ORA-01195 and ORA-01110: 

SQL> recover database until time '2004-02-23:05:00:00' using backup controlfile;

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u02/oradata/scr10/system01.dbf'

Reason: 

The most likely reason is that you have specified a time that occurred  while the backup was taking place.

FIX: 

Either restore an earlier backup and roll it forward, or specify a time that occurred after the last file in the current backup was written. If the backup was written to disk (rman or manual) a simple 'ls -ltr' in the directory containing the backup files will give you a rough idea of the earliest possible restore point.

Rename or Move Datafiles and Logfiles

 To rename or relocate datafiles in the SYSTEM tablespace one must use option II, 'Renaming or Moving a Datafile with the Database Shut Down', because you cannot take the SYSTEM tablespace offline.

I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN

To do this you must follow these steps:

1. Determine how many datafiles are associated with the tablespace.

       > SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES

                 WHERE TABLESPACE_NAME = '';

2. Keep tablespace OFFLINE before moving in OS LEVEL

      > ALTER TABLESPACE  SCOTT_TABLESPACE OFFLINE;

   * At this point the tablespace is not accessible to users.

3. Go to OS box and run move command

MV /u02/oradata/scott_data.dbf /u03/oradata/datafile/scott_data.dbf

4. Once moving done on OS level, run followings:

* This updates the entry for the datafile(s) in the  controlfile.

       > ALTER DATABASE RENAME FILE

                 '/u02/oradata/scott_data.dbf ' TO '/u03/oradata/datafile/scott_data.dbf’;

     

5. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

       > ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;

 6. You can also verify that the controlfile has been updated by doing the

    Following:

        > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This will produce a readable copy of the contents of your controlfile

    which will be placed in your user_dump_dest directory.

    Optionally, you can query V$DATAFILE, which gets information from the

    controlfile as well.

II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN

1. If the database is up, shut it down.

2. Copy the datafile to the new name/location at operating system level.

3. Mount the database.

        > STARTUP MOUNT                                                          

         

   This command will read the control file but will not mount the datafiles.   

4. Rename the file inside Oracle.

        > ALTER DATABASE RENAME FILE

                 '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

                 TO

                 '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

   Do this for all the datafiles that were renamed or moved at the operating

   system level.

5. Open the database.

        > ALTER DATABASE OPEN;

6. Query v$dbfile to confirm that the changes made were correct.

        > SELECT * FROM V$DBFILE;

7. Remove the datafile(s) from the old location at the operating system level.

 

III. RENAME AND OR MOVE A LOGFILE

1. Shutdown the database. 

2. Copy the logfile to the new name/location at operating system level.

3. Mount the database.

        > STARTUP MOUNT

4. Rename the file.

       > ALTER DATABASE RENAME FILE

                 '/FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG'

                 TO

                 '/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG';

5. Open the database.

        > ALTER DATABASE OPEN;

6. Remove the logfile(s) from the old location at the operating system level.