FW - Backup/Restore
main | resize tbs | DN | Crea_DB | Backup/Restore |
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
page revision: 2, last edited: 21 Nov 2017 10:25