FW - Backup/Restore
main resize tbs DN Crea_DB Backup/Restore
Table of Contents

save.ksh

#!/bin/ksh

mkdir $(date +%Y%m%d)
cd $(date +%Y%m%d)

sqlplus / as sysdba <<EOF

-- creazione script per ricreare le directory:

set lin 300
set pages 5000
set feedback off
set heading off
set serverout on
spool directory.sql
select 'create directory '||DIRECTORY_NAME||' as ''' || DIRECTORY_PATH || ''';'  from dba_directories;
spool off

-- creazione script per ricreare i db_link:

set feedback off
set pagesize 1000
set linesize 132
set long 99999999
set heading off
set serverout on
spool crea_dblink.sql
begin
  dbms_output.enable(NULL);
  for utenti in (select distinct owner from dba_db_links) loop
    if utenti.owner = 'PUBLIC' then
      dbms_output.put_line('conn /as sysdba');
    else
      dbms_output.put_line('conn '||utenti.owner||'/pippo123');
    end if;
    for dblink in (select owner, db_link from dba_db_links where owner=utenti.owner) loop
      dbms_output.put_line(dbms_metadata.get_ddl('DB_LINK', dblink.db_link, dblink.owner)||';');
    end loop;
    dbms_output.put_line('------------------------------------------------------');
  end loop;
end;
/
spool off
set feedback on
set heading on

--script per estrazione password

set long 150
set linesize 150
set pages 200
set longchunksize 150
spool orig_pwd.sql
select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' from dba_users;
spool off

- script per droppare e ricreare tempfile

-- sqlplus / as sysdba <<EOF

-- creazione script per ricreare le directory:

set lin 300
set pages 5000
set feedback off
set heading off
set serverout on
spool directory.sql
select 'create directory '||DIRECTORY_NAME||' as ''' || DIRECTORY_PATH || ''';'  from dba_directories;
spool off

-- creazione script per ricreare i db_link:

set feedback off
set pagesize 1000
set linesize 132
set long 99999999
set heading off
set serverout on
spool crea_dblink.sql
begin
  dbms_output.enable(NULL);
  for utenti in (select distinct owner from dba_db_links) loop
    if utenti.owner = 'PUBLIC' then
      dbms_output.put_line('conn /as sysdba');
    else
      dbms_output.put_line('conn '||utenti.owner||'/pippo123');
    end if;
    for dblink in (select owner, db_link from dba_db_links where owner=utenti.owner) loop
      dbms_output.put_line(dbms_metadata.get_ddl('DB_LINK', dblink.db_link, dblink.owner)||';');
    end loop;
    dbms_output.put_line('------------------------------------------------------');
  end loop;
end;
/
spool off
set feedback on
set heading on

--script per estrazione password

set long 150
set linesize 150
set pages 200
set longchunksize 150
spool orig_pwd.sql
select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' from dba_users;
spool off

-- script per droppare e ricreare tempfile

set feedback off
set heading off
set serverout on
set line 120 pages 2000
col file_filename for a400
spool temp.sql
select 'alter database tempfile '''||file_name||''' drop;' from dba_temp_files;
select 'alter tablespace '||tablespace_name||' add tempfile '''||file_name||''' size '||bytes/1024/1024||'M reuse;' from dba_temp_files;
spool off

--script per droppare i dblink

set feedback off
set pagesize 1000
set linesize 132
set long 99999999
set heading off
set serverout on
spool drop_dblink.sql
begin
  dbms_output.enable(NULL);
  for utenti in (select distinct owner from dba_db_links) loop
        dbms_output.put_line('alter user '||utenti.owner||' identified by pippo123;');
end loop;
  for utenti in (select distinct owner from dba_db_links) loop
    if utenti.owner = 'PUBLIC' then
      dbms_output.put_line('conn /as sysdba');
    else
      dbms_output.put_line('conn '||utenti.owner||'/pippo123');
    end if;
    for dblink in (select owner, db_link from dba_db_links where owner=utenti.owner) loop
      dbms_output.put_line('drop database link '||dblink.db_link||';');
    end loop;
    dbms_output.put_line('------------------------------------------------------');
  end loop;
end;
/
spool off
set feedback on

-- script per droppare le directory

set pages 200
set lines 120
set long 2000
spool dropdir.sql
select 'drop directory '||DIRECTORY_NAME||';' from dba_directories;
spool off

-- script per ricavare l'elenco dei datafiles, control file, tempife, etc.
set lines 300
set pages 1000
col nome for a60
col file_name for a60
spool files_list.log
conn /as sysdba;
select file_name, nome
from
(
select file_name, 'DATAFILE' nome
from   dba_data_files
union all
select file_name, 'TEMPFILE' nome
from   dba_temp_files
union all
select member,   'LOGFILE' nome
from v\$logfile
union all
select name, 'CONTROLFILE' nome
from   v\$controlfile
)
order by file_name;
spool off

EOF
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License