Search This Blog

Total Pageviews

Monday, 7 April 2014

unix search text string in file

unix search text string in file
Finding all files containing a text string in linux


backup !!!
find /opt/commvault/ -name backup.out -mtime 2 -exec grep -il "databasename" {} \;


find /home/anujs/Documents -type f -exec grep -il "/tickets" {} \;


find /home/oracle -type f -exec grep -il "192.168.0.17" {} \;

find /u01/app/oracle/diag/rdbms/irac/irac1/trace -type f -exec grep -il "undo_tablespace" {} \;

if you know the file type

find . -type f -name "*.ext" -exec grep -il "hello" {} \


find /u01/app/oracle/diag/rdbms/irac/irac1/trace -type f -name "*.log" -exec grep -il "undo_tablespace" {} \;

find . -type f -exec grep -il "hdp" {} \;


rm -rf  `ls -l | grep 'Apr  2' | tr -s ' ' | cut -d ' ' -f9`

find /u01/app/oracle/diag/tnslsnr/***/listener/alert -ctime +5 -exec rm -f {} \;

via grid
15 * * * * find /u01/app/grid/diag/asm/*/*/alert  -name 'log_*.xml'  -mtime +1 -exec rm -f {} \; &> /dev/null
20 * * * * find /u01/app/oracle/diag/tnslsnr/*/listener/alert  -name 'log_*.xml'  -mtime +4 -exec rm -f {} \; &> /dev/null
05 * * * * find /u01/app/*/grid/rdbms/audit -name '*.aud' -ctime +7 -exec rm -f {} \; &> /dev/null
25 * * * * find /u01/app/grid/diag/crs/*/crs/trace  -name 'log_*.xml'  -mtime +1 -exec rm -f {} \; &> /dev/null


20 * * * * find /u01/app/oracle/diag/tnslsnr/*/listener/trace  -name 'listener*.log'  -mtime +4 -exec rm -f {} \; &> /dev/null

via oracle
05 * * * * find /u01/app/oracle/admin/*/adump -name '*.aud' -ctime +7 -exec rm -f {} \; &> /dev/null

35 * * * * find /u01/app/oracle/diag/rdbms/*/*/trace \( -name '*.trc' -o -name '*.trm' \) -type f -mtime +2 -exec rm -f {} \; &> /dev/null
25 * * * * find /u01/app/oracle/diag/rdbms/*/*/  -name 'log_*.xml'  -mtime +1 -exec rm -f {} \; &> /dev/null

05 * * * * /u01/app/oracle/diag/tnslsnr/*/listener/alert -name '*.xml' -ctime +7 -exec rm -f {} \; &> /dev/null
05 * * * * find /u01/app/oracle/diag/tnslsnr/*/listener/alert -name '*.xml' -ctime +7 -exec rm -f {} \; &> /dev/null

find /u01/app/oracle/admin/*/adump -name '*.aud' -ctime +7 -exec rm -f {} \; &> /dev/null

du -max /anuj | sort -hr | head -30
find /anuj -type f -size +100M -exec du -ch '{}' + | sort -hr | head -50

 find /u01/app/oracle/diag/rdbms/*/*/trace -type f -size +20000k -exec ls -lh {} \; 2> /dev/null   | awk '{ print $NF ": " $5 }'  | sort -hrk 2,2
/bin/rm: Argument list too long
find $HOME -maxdepth 1 -type f -name "*.jpg" -exec rm "{}" \;
ls | head -n 1000 | xargs rm


15 * * * * find /u01/app/oracle/admin/*/adump -name '*.aud' -ctime +1 -exec rm -f {} \;
# Purge Trace files
30 * * * * find /u01/app/oracle/diag/rdbms/*/*/trace \( -name '*.trc' -o -name '*.trm' \) -type f -ctime +1 -exec rm -f {} \; &> /dev/null
20 * * * * find /u01/app/oracle/diag/rdbms/*/*/  -name 'log_*.xml'  -ctime +1 -exec rm -f {} \; &> /dev/null


find /u01/app/oracle/diag/rdbms/*/*/trace \( -name '*.trc' -o -name '*.trm' \) -type f -mtime +7 -exec rm -f {} \; &> /dev/null
find /u01/app/oracle/diag/rdbms/anuj/anuj/trace -type f -mtime +15 -exec rm -f {} \;

use below 
 already has .gz suffix -- unchanged		
		 
	find /u01/app/oracle/diag/rdbms/irac/irac1/trace/ -mtime +1 -type f ! -name '*.gz' -print0 -exec gzip -9 '{}' \;
	 
	 
	 find /u01/app/oracle/diag/rdbms/*/*/trace \( -name '*.trc' -o -name '*.trm' \) -mtime +1 -type f ! -name '*.gz' -print0 -exec gzip -9 '{}' \;


 
find . -xdev -type f ! -name "*.trc" -newermt 2024-10-10 !  -newermt 2024-10-16 -ls | egrep -v 'May'
 
 
find . -xdev -type f ! -name "*.trc" -newermt 2024-10-10 !  -newermt 2024-10-16 -ls -exec gzip {} \;
 
 
find . -xdev -type f -size +100M -print | xargs ls -lh | sort -k5,5 -h -r | head

find . -xdev -type f -ls|sort -nr -k 7,7
$find . -xdev -type f -size +10G  -exec du -hs {} \;
$find . -xdev -type f -size +1M  -exec du -hs {} \;
 zgrep
# Grep text patterns from files within compressed file (equivalent to grep -Z).
# More information: <https://manned.org/zgrep>.

# Grep a pattern in a compressed file (case-sensitive):
zgrep pattern path/to/compressed/file

# Grep a pattern in a compressed file (case-insensitive):
zgrep -i pattern path/to/compressed/file

# Output count of lines containing matched pattern in a compressed file:
zgrep -c pattern path/to/compressed/file

# Display the lines which don’t have the pattern present (Invert the search function):
zgrep -v pattern path/to/compressed/file

# Grep a compressed file for multiple patterns:
zgrep -e "pattern_1" -e "pattern_2" path/to/compressed/file

# Use extended regular expressions (supporting `?`, `+`, `{}`, `()` and `|`):
zgrep -E regular_expression path/to/file

# Print 3 lines of [C]ontext around, [B]efore, or [A]fter each match:
zgrep -C|B|A 3 pattern path/to/compressed/file

$
20,000 kilobytes (20 megabytes) in the user’s home directory (~) find ~ -type f -size +20000k -exec ls -lh {} \; 2> /dev/null | awk '{ print $NF ": " $5 }' | sort -hrk 2,2
==
find /home/oracle -name 'TKT0' -print -exec zip /dumps/TKT0_`hostname`.zip {} \;

find /ux/log/alert -type f -exec grep -r "ORA-01555" {} \;



 <txt>ORA-01555 caused by SQL statement below (SQL ID: 0kv0t7aj8yu67, Query Duration=16206 sec, SCN:
 <txt>ORA-01555 caused by SQL statement below (SQL ID: 8ru41zauaub1n, Query Duration=14790 sec, SCN:
 <txt>ORA-01555 caused by SQL statement below (SQL ID: 20fr6cgpnzgcy, Query Duration=15270 sec, SCN:



w
find . -type f -name "opr_dia0*" -print -exec zip /home/oracle/odsprdc_dia.zip {} \;


===

to delete files 
rm -rf `find -maxdepth 1 -type f -exec ls -l {} + | grep '11:54' | awk '{ print $9 }'`

to delete Dir
rm -rf `find  -type d -exec ls -l {} + | grep '11:54' | awk '{ print $9 }'`


==================



Find all files in the current directory: 
find . -type f 

Find all files with a specific extension in the current directory: 
find . -name "*.txt"

Find all files modified within the last 24 hours: 
find . -mtime -1 

Find all files larger than 100MB: 
find . -size +100M 

Find all files with a specific name and execute a command on them: 
find . -name "example.txt" -exec cat {} \; 

Find all files with a specific permission: 
find . -perm 777 

Find all files that are empty: 
find . -empty 

Find all files that are owned by a specific user: 
find / -user myusername 

Find all files that are in a specific group: 
find / -group mygroupname 

Find all files that are older than a certain date: 
find / -type f -newermt "2022-01-01" 

Find all directories in the current directory: 
find . -type d 

Find all files with a specific name and delete them: 
find . -name "temp*" -delete 

Find all files with a specific name and move them to a different directory: 
find . -name "*.log" -exec mv {} /var/log/ \; 

Find all files that are larger than a certain size and print their size:
find . -size +1G -exec ls -lh {} \; 

Find all files that are older than a certain number of days and print their names: find . -mtime +30 -exec ls {} \; 

Find all files that have been accessed within the last 7 days and print their names: find . -atime -7 -exec ls {} \; 

Find all files that match a specific pattern and copy them to a different directory: find . -name "*.txt" -exec cp {} /backup/ \; 

Find all files that are symbolic links and print their target: 
find . -type l -exec ls -l {} \; 

Find all files that have been modified within the last hour and print their names: find . -cmin -60 -exec ls {} \; 

Find all files that have been modified within the last week and compress them: find . -mtime -7 -exec gzip {} \; 

Find all files that have been modified within the last 30 days and have a specific string in their  content: 
find . -mtime -30 -exec grep -l "string" {} \; 

Find all files that have been modified within the last 30 days and have a specific string in their  content and delete them: 
find . -mtime -30 -exec grep -l "string" {} \; -delete 

Find all files that are larger than a certain size and have a specific string in their content and  move them to a different directory: 
find . -size +1G -exec grep -l "string" {} \; -exec mv {}  /new_folder/ \; 

Find all files that are smaller than a certain size and have a specific string in their content and  print their name and size:
find . -size -100M -exec grep -l "string" {} \; -exec ls -lh {}  \; 

Find all files that have been modified within the last year and have a specific string in their  content and compress them with tar: 
find . -mtime -365 -exec grep -l "string" {} \; -exec tar -czf {}.tar.gz {} \; 

Find all files that have been accessed within the last month and have a 
specific string in their  content and print their name and last access time: 
find . -atime -30 -exec grep -l "string" {} \; -exec stat -c "%n  last accessed on %x" {} \; 

Find all files that have been modified within the last week and have a specific string in their  content 
and mail them to a specific email address: 
find . -mtime -7 -exec grep -l "string" {} \; -exec mail -s "Files with specific string" email@example.com < {} \; 

Find all files that have been modified within the last 30 days and have a specific string in their  content 
and run a specific command on them: 
find . -mtime -30 -exec grep -l "string" {} \; -exec your_command {} \; 

Find all files that have been modified within the last year and have a specific string in their  content 
and rename them: 
find . -mtime -365 -exec grep -l "string" {} \; -exec mv {}  {}_newname \; 

Find all files that have a specific string in their content and have been modified within the last  year, 
and change their permission: 
find . -mtime -365 -exec grep -l "string" {} \; -exec chmod 755 {} \; 

Find all files that have been modified within the last year and have a specific string in their  content 
and also have a specific file extension and print their name and path: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec stat -c "%n path: %h" {} \;

Find all files that have a specific string in their content and have been modified within the last  year 
and also have a specific file extension and change their owner: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec chown new_owner {} \; 

Find all files that have a specific string in their content and have been modified within the last  year 
and also have a specific file extension and change their group: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec chgrp new_group {} \; 

Find all files that have been modified within the last year and have a specific string in their  content 
and also have a specific file extension and print their name and last access time: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec stat -c "%n last accessed on %x" {} \; 

Find all files that have been modified within the last year and have a specific string in their  content 
and also have a specific file extension and move them to a different directory: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec mv {} /new_folder/ \; 

Find all files that have been modified within the last year and have a specific string in their  content 
and also have a specific file extension and compress them with tar: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec tar -czf {}.tar.gz {} \; 
find . -type f -exec grep -il "ORA-XXX" {} \; | xargs tar czf 04025trc.tar.gz

Find all files that have been modified within the last year and have a specific string in their  
content and also have a specific file extension and mail them to a specific email address: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec mail -s "Files with specific string" email@example.com < {}  \; 

Find all files that have a specific string in their content and have been modified within the 
last  year and also have a specific file extension and rename them: 
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec mv {} {}_newname \; 

Find all files that have been modified within the last year and have a specific string in their  content 
and also have a specific file extension and change their permission:
find . -mtime -365 -name "*.txt" -exec grep -l "string" {} \; - exec chmod 755 {} \; 

find . "*.trc" -exec ls -l {} + 

for full path 
find . "*.trc" -exec realpath {} \;

Oracle 11g- Recovering from Loss of All Control Files on ASM 


Recovering from Loss of All Control Files 
Recovery: All Control Files Missing
All Control Files Missing
Re-create lost controlfile


I have deleted all control files and re-created using trace file.

I have deleted all control files and re-created using trace file.

SQL> show parameter control

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
control_file_record_keep_time        integer                           7
control_files                        string                            +DATA/vihaan/controlfile/curre
                                                                       nt.262.844101027, +DATA/vihaan
                                                                       /controlfile/current.263.84410
                                                                       1029
control_management_pack_access       string                            DIAGNOSTIC+TUNING


NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
control_file_record_keep_time        integer                           7
control_files                        string                            +DATA/vihaan/controlfile/current.262.844101027, +DATA/vihaan/controlfile/current.263.844101029
control_management_pack_access       string                            DIAGNOSTIC+TUNING



Check is  database in archive log mode 


SQL> archive log list ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/vihaan/archivelog
Oldest online log sequence     36
Next log sequence to archive   38
Current log sequence           38
SQL> alter system switch logfile ;

System altered.

SQL>  archive log list ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/vihaan/archivelog
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39


SQL> alter database backup controlfile to trace ;

Database altered.

Location of Trace file 

/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace

-rw-r----- 1 oracle asmadmin   7002 Apr  7 10:18 vihaan_ora_5567.trc  <<<<<<<----
-rw-r----- 1 oracle asmadmin 269546 Apr  7 10:18 alert_vihaan.log


cat vihaan_ora_5567.trc

Trace file /u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/vihaan_ora_5567.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      unknown
Release:        2.6.18-348.1.1.el5
Version:        #1 SMP Tue Jan 22 16:24:03 EST 2013
Machine:        i686
Instance name: vihaan
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 5567, image: oracle@unknown (TNS V1-V3)


*** 2014-04-07 10:13:24.382
*** SESSION ID:(1.5) 2014-04-07 10:13:24.382
*** CLIENT ID:() 2014-04-07 10:13:24.382
*** SERVICE NAME:(SYS$USERS) 2014-04-07 10:13:24.382
*** MODULE NAME:(sqlplus@unknown (TNS V1-V3)) 2014-04-07 10:13:24.382
*** ACTION NAME:() 2014-04-07 10:13:24.382

kwqmnich: current time::  9: 13: 23: 0
kwqmnich: instance no 0 repartition flag 1
kwqmnich: initialized job cache structure

*** 2014-04-07 10:13:25.194
kwqinfy: Call kwqrNondurSubInstTsk

*** 2014-04-07 10:18:55.674
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="vihaan"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=+DATA/vihaan/archivelog'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "VIHAAN" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/vihaan/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/vihaan/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/vihaan/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/vihaan/system01.dbf',
  '+DATA/vihaan/sysaux01.dbf',
  '+DATA/vihaan/undotbs01.dbf',
  '+DATA/vihaan/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_694825248.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_811404903.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813933977.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813953696.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_844101203.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/vihaan/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/vihaan/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/vihaan/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/vihaan/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/vihaan/system01.dbf',
  '+DATA/vihaan/sysaux01.dbf',
  '+DATA/vihaan/undotbs01.dbf',
  '+DATA/vihaan/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_694825248.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_811404903.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813933977.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813953696.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_844101203.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/vihaan/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--


================================

Do some activity for testing ....


SQL> create table anuj as select sysdate s_date from dual ;

Table created.

SQL> alter session set nls_date_format='dd-mm-yyyy hh:mi:ss ';

Session altered.

SQL> select * from anuj ;

S_DATE
--------------------
07-04-2014 10:24:36


SQL> archive log list ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/vihaan/archivelog
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39


SQL> alter system switch logfile ;

System altered.

SQL> archive log list ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/vihaan/archivelog
Oldest online log sequence     38
Next log sequence to archive   40
Current log sequence           40

=================================================

I am going to delete the control file 

as a grid user 

[grid@unknown ~]$ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     10236     8704                0            8704              0             N  DATA/
ASMCMD [+] > cd DATA/
ASMCMD [+DATA] > ls -s
Block_Size  Blocks  Bytes  Space  Name
                                  ASM/
                                  VIHAAN/
ASMCMD [+DATA] > cd VIHAAN/
ASMCMD [+DATA/VIHAAN] > ls -s
Block_Size  Blocks      Bytes      Space  Name
                                          CONTROLFILE/
                                          DATAFILE/
                                          ONLINELOG/
                                          TEMPFILE/
                                          archivelog/
                                          redo01.log => +DATA/VIHAAN/ONLINELOG/group_1.261.844089885
                                          redo02.log => +DATA/VIHAAN/ONLINELOG/group_2.284.844089885
                                          redo03.log => +DATA/VIHAAN/ONLINELOG/group_3.256.844089885
                                          sysaux01.dbf => +DATA/VIHAAN/DATAFILE/SYSAUX.266.844089693
                                          system01.dbf => +DATA/VIHAAN/DATAFILE/SYSTEM.265.844089693
                                          temp01.dbf => +DATA/VIHAAN/TEMPFILE/TEMP.260.844089891
                                          undotbs01.dbf => +DATA/VIHAAN/DATAFILE/UNDOTBS1.257.844089693
                                          users01.dbf => +DATA/VIHAAN/DATAFILE/USERS.258.844089693
ASMCMD [+DATA/VIHAAN] > cd CONTROLFILE/
ASMCMD [+DATA/VIHAAN/CONTROLFILE] > ls -s
Block_Size  Blocks     Bytes     Space  Name
     16384     627  10272768  16777216  current.262.844101027
     16384     627  10272768  16777216  current.263.844101029


You can not delete the control file while database is running ...

ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm current.262.844101027 current.263.844101029
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.262.844101027' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.263.844101029' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm -rf current.262.844101027 current.263.844101029
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.262.844101027' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.263.844101029' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm -f current.262.844101027
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.262.844101027' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)


so i have to shutdown the database ..

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.


deleted the control file ..

ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm -rf current.262.844101027 current.263.844101029
ASMCMD [+DATA/VIHAAN/CONTROLFILE] > ls -s
ASMCMD-08002: entry 'CONTROLFILE' does not exist in directory '+DATA/VIHAAN/'


SQL> startup ;
ORACLE instance started.

Total System Global Area  640286720 bytes
Fixed Size                  1338420 bytes
Variable Size             448791500 bytes
Database Buffers          184549376 bytes
Redo Buffers                5607424 bytes
ORA-00205: error in identifying control file, check alert log for more info



in alter log file error 

SUCCESS: diskgroup DATA was mounted
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/vihaan/controlfile/current.263.844101029'
ORA-17503: ksfdopn:2 Failed to open file +DATA/vihaan/controlfile/current.263.844101029
ORA-15012: ASM file '+DATA/vihaan/controlfile/current.263.844101029' does not exist
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/vihaan/controlfile/current.262.844101027'
ORA-17503: ksfdopn:2 Failed to open file +DATA/vihaan/controlfile/current.262.844101027
ORA-15012: ASM file '+DATA/vihaan/controlfile/current.262.844101027' does not exist
ORA-205 signalled during: ALTER DATABASE   MOUNT...
Mon Apr 07 10:37:40 2014
Checker run found 2 new persistent data failures



Start Recovery 


SQL> shutdown immediate ;
ORA-01507: database not mounted


ORACLE instance shut down.



SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  640286720 bytes
Fixed Size                  1338420 bytes
Variable Size             448791500 bytes
Database Buffers          184549376 bytes
Redo Buffers                5607424 bytes



SQL> select name from v$controlfile;

no rows selected


Now Create Control file 

CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/vihaan/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/vihaan/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/vihaan/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/vihaan/system01.dbf',
  '+DATA/vihaan/sysaux01.dbf',
  '+DATA/vihaan/undotbs01.dbf',
  '+DATA/vihaan/users01.dbf'
CHARACTER SET WE8MSWIN1252
;

SQL> select name from v$controlfile;

no rows selected


SQL> CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/vihaan/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/vihaan/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/vihaan/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '+DATA/vihaan/system01.dbf',
 14    '+DATA/vihaan/sysaux01.dbf',
 15    '+DATA/vihaan/undotbs01.dbf',
 16    '+DATA/vihaan/users01.dbf'
 17  CHARACTER SET WE8MSWIN1252
 18  ;

Control file created.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/vihaan/controlfile/current.263.844252891
+DATA/vihaan/controlfile/current.262.844252891


SQL> alter database open resetlogs;

Database altered.


SQL> alter session set nls_date_format ='dd-mm-yyyy hh24:mi:ss' ;

Session altered.

SQL>  select * from anuj ;

S_DATE
-------------------
07-04-2014 10:24:36



set linesize 200
SQL> r
  1  select a.group#,a.member,b.status from v$logfile a, v$log b
  2* where a.group#=b.group#

    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- ------------------------------------------------
         3 +DATA/vihaan/redo03.log                            UNUSED
         2 +DATA/vihaan/redo02.log                            UNUSED
         1 +DATA/vihaan/redo01.log                            CURRENT





from grid user check control files


[grid@unknown ~]$ asmcmd -p
ASMCMD [+] > cd data
ASMCMD [+data] > ls -lt

Type  Redund  Striped  Time             Sys  Name
                                        Y    ASM/
                                        Y    VIHAAN/
ASMCMD [+data] > cd VIHAAN/
ASMCMD [+data/VIHAAN] > ls -lt


Type       Redund  Striped  Time             Sys  Name
                                             Y    CONTROLFILE/
                                             Y    DATAFILE/
                                             Y    ONLINELOG/
                                             Y    TEMPFILE/
                                             N    archivelog/
                                             N    redo01.log => +DATA/VIHAAN/ONLINELOG/group_1.261.844089885
                                             N    redo02.log => +DATA/VIHAAN/ONLINELOG/group_2.284.844089885
                                             N    redo03.log => +DATA/VIHAAN/ONLINELOG/group_3.256.844089885
                                             N    sysaux01.dbf => +DATA/VIHAAN/DATAFILE/SYSAUX.266.844089693
                                             N    system01.dbf => +DATA/VIHAAN/DATAFILE/SYSTEM.265.844089693
                                             N    temp01.dbf => +DATA/VIHAAN/TEMPFILE/TEMP.260.844089891
                                             N    undotbs01.dbf => +DATA/VIHAAN/DATAFILE/UNDOTBS1.257.844089693
                                             N    users01.dbf => +DATA/VIHAAN/DATAFILE/USERS.258.844089693


ASMCMD [+data/VIHAAN] > cd CONTROLFILE/
ASMCMD [+data/VIHAAN/CONTROLFILE] > ls -s
Block_Size  Blocks     Bytes     Space  Name
     16384     615  10076160  16777216  Current.262.844252891
     16384     615  10076160  16777216  Current.263.844252891



SQL> create pfile from spfile ;
File created.

My init file cat initvihaan.ora


vihaan.__db_cache_size=184549376
vihaan.__java_pool_size=4194304
vihaan.__large_pool_size=4194304
vihaan.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
vihaan.__pga_aggregate_target=276824064
vihaan.__sga_target=364904448
vihaan.__shared_io_pool_size=0
vihaan.__shared_pool_size=163577856
vihaan.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/vihaan/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/vihaan/controlfile/current.263.844252891','+DATA/vihaan/controlfile/current.262.844252891'#Oracle managed file
*.db_block_size=8192
*.db_domain=''
*.db_name='vihaan'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)'
*.log_archive_dest_1='location=+data/vihaan/archivelog'
*.memory_target=641728512
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


Oracle DBA

anuj blog Archive