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'


Sunday, 6 April 2014

Oracle Database Upgrades and Migrations Metalink ID 1152016.1

Oracle Database Upgrades and Migrations Metalink  ID 1152016.1 

Oracle Upgrade note



Note 1561791.1 Upgrade / Downgrade Assistant: Oracle Database/Client
Note 419550.1   Different Upgrade Methods For Upgrading Your Database
Note 551141.1   Database Server Upgrade/Downgrade Compatibility Matrix
Note 730365.1   Oracle Database Upgrade Path Reference List
Note 455744.1   Best Practices to Minimize Downtime During Upgrade
Note 739485.1   How to estimate the time required to upgrade a database ?
Note 1515747.1 Oracle Database 12c Release 1 (12.1) Upgrade New Features
Note 1493645.1 Oracle Database 12c Release 1 (12.1) DBUA : Understanding New Changes With All New 12.1 DBUA 
Note 757245.1   Can you Upgrade RDBMS and Convert From 32-bit to 64-bit Binaries Directly on Linux or Windows based Intel Platforms Using the Database Upgrade Assistant (DBUA)
Note 1320966.1 Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results
Note 1392633.1 Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
Note 785351.1   Oracle 11gR2 Upgrade Companion
Note 601807.1   Oracle 11gR1 Upgrade Companion
Note 466181.1   10g Upgrade Companion

Oracle DBA

anuj blog Archive