Pre- Clone script for Database .
Connect the database and create below directory path.
Ex: cd /home/oradev1/
midir Clone03AUG2021
cd Clone03AUG2021
mkdir clone and vi pre_clone.sql then copy the blow script in pre_clone.sql and run.
Prompt
Prompt === copying init.ora and spfiles ===
Prompt ========================================
Prompt
host cp $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora /home/oraprod/Clone03AUG2021/clone
host cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora /home/oradev1/Clone03AUG2021/clone
host cp $ORACLE_HOME/oraInst.loc /home/oradev1/Clone03AUG2021/clone
host cp $ORACLE_HOME/root.sh /home/oradev1/Clone03AUG2021/clone
host cp $ORACLE_HOME/*.env //home/oradev1/Clone03AUG2021/clone
host cp -R $ORACLE_HOME/network/admin/* /home/oradev1/Clone03AUG2021/clone
spool /home/oradev1/Clone03AUG2021/clone/Precloneinfo.log
alter database backup controlfile to trace as '/home/oradev1/Clone03AUG2021/clone/ctrl_$ORACLE_SID.sql';
create pfile='/home/oradev1/Clone03AUG2021/clone/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