Pages

Wednesday, November 15, 2017

Find And Replace In Vi editor


Syntax for Text Substitution inside the vi editor is,

:[range]s[ubstitute]/{pattern}/{string}/[Flags] [count]

Following are the possible Flags

[g] -> replace all occurances in the line.
[c] -> Confirm each substitution
[i] -> make case insensitive

Case 1: Replace a text with another in the whole file

:%s/oldText/newText/g

where %s indicates all lines in the file.

Case 2: Replace a text within a single line

:s/oldText/newText/g

No range was specified therefore it replaces text in the single line.

:s/I/oldText/newText/g

where I indicates case insensitive

Case 3: Replace a text within a range of lines

:1,10s/oldText/newText/g

replaces text between line 1 and 10

Case 4: Replace text in only the first N lines

:s/oldText/newText/g 5

replaces the text in 5 lines from the current cursor position.

Case 5: Replace the whole word and not the partial word

:s/\<his\>/her/

The standard replace will make the word history with herstory. Enclose the word with < and > to ensure the search is for the whole word

and not for partial one.

Case 6: Interactive Find and Replace

:%s/oldText/newText/gc

This will prompt for confirmation.

replace with newText (y/n/a/q/l/^E/^Y)?

y -> replace the highlighted word. After replace moves to the next word.
n -> does not replace the highlighted word. Moves to the next word.
a -> replace all the highlighted words.
l -> replaces the current highlighted word and terminates the find and replace.



pwd
/export/home/oratest/CLONE_24Jul2022

vi Db_config_bkp.sql

Add below contents in your file.


SQL>@Db_config_bkp.sql


Prompt
Prompt  === copying init.ora and spfiles  ===
Prompt  ========================================
Prompt

host cp $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/oraInst.loc /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/root.sh  /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/*.env  /export/home/oratest/CLONE_24Jul2022

host cp -R $ORACLE_HOME/network/admin/* /export/home/oratest/CLONE_24Jul2022

spool /export/home/oratest/CLONE_24Jul2022/Precloneinfo.log

alter database backup controlfile to trace as '/export/home/oratest/CLONE_24Jul2022/ctrl_$ORACLE_SID.sql';

create pfile='/export/home/oratest/CLONE_24Jul2022/initpfile.ora' from spfile;

Prompt
Prompt  === dblinks  ===
Prompt  ========================================
Prompt

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

set lines 200
set pages 200
col DB_LINK for a50
col USERNAME for a20
col HOST for a50
select * from dba_db_links;

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
 ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
 ||'connect to ' || L.USERID || ' identified by '
 ||L.PASSWORD||' using ''' || L.host || ''''
 ||chr(10)||';' TEXT
 FROM sys.link$ L, sys.user$ U
 WHERE L.OWNER# = U.USER#;

Prompt
Prompt  === dba_directories  ===
Prompt  ========================================
Prompt

set pages 800
set lines 300
col DIRECTORY_NAME for a40
col DIRECTORY_PATH for a90
select * from dba_directories;

set pages 800
select 'create or replace directory '||DIRECTORY_NAME||' as '''||DIRECTORY_PATH||''';' from dba_directories;


archive log list

show parameter backgr

Prompt
Prompt  === temp_files  ===
Prompt  ========================================
Prompt

set lines 200
col FILE_NAME for a80;
select file_name,tablespace_name,bytes/1024/1024,status from dba_temp_files;

Prompt
Prompt  === data_files  ===
Prompt  ========================================
Prompt

select file_name,tablespace_name,bytes/1024/1024,status from dba_data_files;

Prompt
Prompt  === control_files  ===
Prompt  ========================================
Prompt
select name from v$controlfile;


Prompt
Prompt  === redolog_files  ===
Prompt  ========================================
Prompt
col MEMBER for a60
set pages 800
set lines 200
select * from v$logfile;

select * from v$log;


Prompt
Prompt  === all directories of db CRD files  ===
Prompt  ========================================
Prompt
select substr(name,1,instr(name,'/',-1)) from v$datafile
union
select substr(name,1,instr(name,'/',-1)) from v$tempfile
union
select substr(member,1,instr(member,'/',-1)) from v$logfile
union
select substr(name,1,instr(name,'/',-1)) from v$controlfile;


Prompt
Prompt  === status of CRD files  ===
Prompt  ========================================
Prompt
select distinct status from v$datafile
union
select distinct status from v$tempfile
union
select distinct status from v$logfile
union
select distinct status from v$controlfile;

Prompt
Prompt  === size and count of datafiles on each mount  ===
Prompt  ========================================
Prompt
set pages 200
set lines 300
col DATAFILES_DIR for a50
select substr(name, 1, instr(name, '/', -1)) datafiles_DIR, count(1) datafiles_count,sum(bytes/1024/1024/1024) size_in_GB from v$datafile group by rollup (substr(name, 1, instr(name, '/', -1))) order by 1;

Prompt
Prompt  === size and count of tempfiles on each mount  ===
Prompt  ========================================
Prompt
set pages 200
set lines 300
col TEMPFILES_DIR for a50
select substr(name, 1, instr(name, '/', -1)) tempfiles_DIR, count(1) tempfiles_count,sum(bytes/1024/1024/1024) size_in_GB from v$tempfile group by rollup (substr(name, 1, instr(name, '/', -1))) order by 1;

Prompt
Prompt  ===all file locations of db ===
Prompt  ========================================
Prompt
select distinct substr(name, 1, instr(name, '/', -1)) || '  -- DATAfiles' from v$datafile union
select distinct substr(name, 1, instr(name, '/', -1)) || '  -- TEMPfiles' from v$tempfile union
select distinct substr(member, 1, instr(member, '/', -1)) || '  -- LOGfiles' from v$logfile union
select distinct substr(name, 1, instr(name, '/', -1)) || '  -- Controlfiles' from v$controlfile;


select node_name from apps.fnd_nodes;

show parameter pfile

show parameter backgr

show parameter utl

Prompt *****Public synonym backup before import***********
set long 900000
select dbms_metadata.get_ddl(object_type=>'SYNONYM',name=>synonym_name,schema=>'PUBLIC') from all_synonymswhere owner='PUBLIC' and table_owner not in ('SYS')
Prompt
Prompt  === tablespace creation script  ===
Prompt  ========================================
Prompt


set long 900000
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

Prompt
Prompt  === user creation script  ===
Prompt  ========================================
Prompt


set long 9999999
select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users;

Prompt
Prompt  === username -- password script ===
Prompt  ========================================
Prompt


select 'alter user '|| name||' identified by values  '|| ''''|| password||''''||';' from SYS.USER$ WHERE password is not null;

Prompt
Prompt  === username -- its  Default tablespace  ===
Prompt  ========================================
Prompt
set lines 200 pages 9999
SELECT username, default_tablespace,temporary_tablespace,profile FROM dba_users order by 1;

select name,open_mode,log_mode,database_role,dbid from v$database;

col host_name fOR a36
col created fOR a16
col start_time fOR a16
col INSTANCE_NAME fOR a13
col OPEN_MODE fOR a10
set lines 280
select d.name db_name,i.INSTANCE_NAME,i.HOST_NAME,d.open_mode,d.database_role,to_char(d.created,'DD-MON-YY HH24:MI') created,to_char(i.startup_time,'DD-MON-YY HH24:MI') start_time from v$database d, gv$instance i;

spool off


No comments:

Post a Comment