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