Drop and Recreate Online Redolog File in oracle

Drop and Recreate Online Redolog Files
Method to drop and recreate online redolog files with 2 members to each group.

Firstly ORACLE will never allow you to drop the current ONLINE redolog file –

Ex :

SQL> select GROUP#,THREAD#,STATUS from v$log;
GROUP# THREAD# STATUS
———- ———- —————-
1 1 CURRENT
2 1 UNUSED
3 1 INACTIVE
4 1 INACTIVE

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) – cannot drop
ORA-00312: online log 1 thread 1: ‘/u03/oradata/testdb/redo01.log’

Now to drop and recreate online redolog files in the same directory –

SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/u50/oradata/test/redo4a.log
/u51/oradata/test/redo4b.log
/u50/oradata/test/redo3a.log
/u51/oradata/test/redo3b.log
/u50/oradata/test/redo2a.log
/u51/oradata/test/redo2b.log
/u50/oradata/test/redo1a.log
/u51/oradata/test/redo1b.log

8 rows selected.

Intention is to drop and recreate these online logs with a different size.

SQL> select GROUP#,THREAD#,STATUS from v$log;
GROUP# THREAD# STATUS
———- ———- —————-
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 1 INACTIVE

Begin dropping the redolog groups with STATUS=’INACTIVE’ – As mentioned earlier Oracle will not allow you to drop a current online redolog file.

sql > alter database drop logfile group 1;
cd /u50/oradata/test

rm redo1a.log

cd /u51/oradata/test

rm redo1b.log

Recreate group with both members –

sql > alter database add logfile group 1(‘/u50/oradata/test/log1a.ora’,’/u51/oradata/test/log1b.ora’)size 2048m;
Do the same for the other three groups –

To change the status of a log group from CURRENT to INACTIVE, simply switch a logfile with this command-

sql> alter system switch logfile;
Run the command 2 or 3 times if the group status does not change.

To check status of group remember the command is

sql > select GROUP#,THREAD#,STATUS from v$log;
Also make sure the logfile is physically removed from the disk with the rm command – if you don’t do this and try to recreate a logfile an error will appear similar to this –

Ex: You dropped log group 3 but did not remove the physical file redo03.log from disk /u03/oradata/test and tried to recreate the group.

SQL> alter database add logfile group3 ‘/u03/oradata/test/redo03.log’ size 50m;
alter database add logfile ‘/u03/oradata/test/redo03.log’ size 50m
*
ERROR at line 1:
ORA-01577: cannot add log file ‘/u03/oradata/test/redo03.log’ – file already
part of database

Enable and Disable constraints using Scripts

Below are scripts that we will use in our environment before import the data using impdp inorder to speed up the process by disabling the constraints and triggers.

CONSTRAINTS DISABLE
——————-

Set linesize 300

Spool COnstraints_disable.sql

select ‘alter table ‘ || owner || ‘.’ || table_name || ‘ disable constraint ‘ || constraint_name || ‘;’ from all_constraints where owner=’test_schema’;
CONSTRAINTS ENABLE
——————-

SQL> set linesize 300

SQL> spool Constraints_enable.sql

SQL> select ‘alter table ‘ || owner || ‘.’ || table_name || ‘ enable constraint ‘ || constraint_name || ‘;’ from all_constraints where owner=’test_schema’;

Trigger Disable
—————

SQL> Set linesize 300

SQL> spool trigger_disable.sql

select ‘alter trigger ‘ || owner || ‘.’ || trigger_name || ‘ disable; ‘ from all_triggers where owner=’test_schema’;
Trigger Enable
—————

SQL> Set linesize 300

SQL> spool trigger_enable.sql

select ‘alter trigger ‘ || owner || ‘.’ || trigger_name || ‘ enable; ‘ from all_triggers where owner=’test_schema’;
UNLOCK_TABLE_STATISTICS
—————————

SQL > set linesize 300

SQL> Unlock_statistics.sql

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS(”’||owner||”’,”’||table_name||”’);’ from dba_tab_statistics where stattype_locked is not null and owner in(‘test_schema’);

Session marked for killing

Even after we killed the session using “immediate” keyword and its still in the state session marked for killing.

SQL> Alter system kill session ‘374,1005’ immediate;

ora-00031 : Session marked for killing..

In this case get the PID corresponding to the SID and kill it at the OS level using kill -9

–Use the below query to get the PID —

select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid in(‘374’)
order by s.sid;

then kill the corresponding spid in the os level using kill -9 spid

Unix for DBA

Below are some of the basic unix commands which will be useful for Oracle DBA.

How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory

find . -print |grep -i test.sql

Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk ‘{ print $2 }’

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1=”`hostname`*$ORACLE_SID:$PWD>”

Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

Display the number of CPU’s in Solaris

psrinfo -v | grep “Status of processor”|wc -l

Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l

Aix : lsps -a

Total number of semaphores held by all instances on server

ipcs -as | awk ‘{sum += $9} END {print sum}’

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ‘<ID>’

Show mount points for a disk in AIX

lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Renaming tablespaces in Oracle

Renaming tablespaces in Oracle

Prior to Oracle 10g, there was no easy way to rename the tablespaces. One had to create a new tablespace, move all the objects from the old tablespace to the new tablespace and then drop the old tablespace along with its data files. This becomes a very tedious process and requires double the storage till the drop is done.

In 10g, Oracle introduced a new feature of renaming tablespace just like renaming a table. First, we will check for the existing tablespace name in the database by issuing the following query (make sure you are connected to the database with correct privileges):

SQL>SELECT name FROM v$tablespace WHERE tablespace_name = ‘TEST_TBS’;

NAME
—————
TEST_TBS

If you want to rename/delete a tablespace, it’s better to first unassign all quotas on that tablespace, because if you later create a tablespace with the same name, the old quotas are still active: (Check what all are the users using that tablespace and then unassign all the quotas)

SQL> alter user <username> quota 0 on <tablespace_name>;

Now we will rename it by issuing the following command.

SQL> ALTER TABLESPACE TEST_TBS RENAME TO TEST01_DATA;

Tablespace altered.

If you execute the first query again, it won’t return any record as tablespace is renamed to new name.

Few things to keep in mind when renaming the tablespaces:

• Rename tablespace is not supported for SYSTEM and SYSAUX tablespaces. Attempt to rename these tablespaces will result into an error.
• Database compatibility parameter (COMPATIBLE) in init.ora should be set to at least 10.0.0.0
• To rename a tablespace, the tablespace and its data files should be online.
• Temporary tablespace can be renamed as well.

When tablespace is being renamed, database updates the references in control file, data dictionary and online data file headers as they all reference the tablespace. One can verify that by querying the user_tables table to see the change in tablespace name for the objects which belong to older tablespace.