Oracle: Refresh Database From HotBackup

 1. bring down the target environment (fsdvlp) - appservers, process schedulers, database server

2. mkdir /usr/opt/app/dbdump/hot_backup/FSPROD on the target server (psfststdb01)

3. mkdir /usr/opt/app/dbdump/hot_backup/fsprod on the target server (psfststdb01)

3. copy all the necessary files to ..../fsprod and .../FSPROD   on the source, prod server (psfsdb01)                              --20mins  - 98GB

4. tar up the files in ../temp

   tar cf backupfiles.tar *                                                                    --15mins  -50GB   

5. ensure /usr/opt/app/dbdump/hot_backup/FSPROD is cleanedup on target server

6. 'scp' the tar file from ../temp to oracle@psfststdb01:/usr/opt/app/dbdump/hot_backup/FSPROD on target server   --40mins

7. unTAR tar file on /usr/opt/app/dbdump/hot_backup/FSPROD on target server                    --20mins

8. backup init.ora , spfile, passwd file from on target database

9. backup controlfile to trace for target database - fsdvlp

9A. Get a snapshot of the filesystem layout on the target - fsdvlp

9b. Get configuration from fsdvlp: PT_CONFIG_TABLE_EXP.DMS / ExportHRDVLPbeforeRefresh.dms on tmwpumpsfsdv401 c:\temp

10. drop database:

       SQL> shutdown immediate

       SQL> startup mount

       SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

       SQL> exit;

       >rman target / catalog fsdvlp/bkup_123@rmprod

oracle-psfststdb601.tmw.com:fsdbug:/usr/opt/app/oracle/admin/fsdbug>rman target / catalog fsdbug/bkup_123@rmprod


Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 2 10:54:08 2020


Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


connected to target database: FSDBUG (DBID=2616725699, not open)

connected to recovery catalog database

recovery catalog schema release 19.03.00.00. is newer than RMAN release


RMAN> drop database noprompt;


database name is "FSDBUG" and DBID is 2616725699

database dropped


RMAN> exit



Recovery Manager complete.


database name is "FSDVLP" and DBID is 285801990

                                                          --20mins

11. ensure tnsnames.ora on prod server has fsdvlp

12. Get the logseq:

    On the source server , production: 

     >rman target / catalog fsprod/bkup_123@rmancat

     RMAN> list backupset;   OR    # lists only backup sets and proxy copies

     RMAN> list backup;      OR    # lists backup sets, image copies, and proxy copies

     RMAN> list copy;              # lists only disk copies

     

     RMAN> spool log to /usr/local/oracle/listback.txt;

     RMAN> list backupset;

     RMAN> spool log off;

     RMAN> quit


13. Ensure password file is in $ORACLE_HOME/dbs: orapwfsdvlp  AND

    relink spfile from ../pfile to $ORACLE_HOME/dbs


    

14. on Source server (production) rman to target server

    >rman target / catalog fsprod12/bkup_123@rmprod auxiliary sys/Ysw8PTgJ@fsdvlp  @11.35am to 3pm 

                                                                                 



15. for HR rstores only, copy     /usr/opt/app/oracle/admin/hrprod/* to /usr/opt/app/oracle/admin/HRPROD/*

   

oracle-psfsdb01:fsprod:/usr/opt/app/dbdump/hot_backup/FSPROD/temp>rman target / catalog fsprod/bkup_123@rmprod auxiliary sys/Ysw8PTgJ@fsdvlp


Recovery Manager: Release 11.2.0.2.0 - Production on Mon Nov 11 11:11:59 2013


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: FSPROD (DBID=2458593881)

connected to recovery catalog database

connected to auxiliary database (not started)


RMAN> run {

2> startup clone nomount;

3> DUPLICATE target database to fsdvlp until logseq 366622;

4> }


At the end of the restore:

.

.

.


input datafile copy RECID=205 STAMP=840117125 file name=/usr/opt/app/oracle/admin/hrdvlp/data01/tmw_goldenbrand_01.dbf

datafile 207 switched to datafile copy

input datafile copy RECID=206 STAMP=840117125 file name=/usr/opt/app/oracle/admin/hrdvlp/ndx01/tmw_goldenbrand_ndx_01.dbf


contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script


database opened

Finished Duplicate Db at 21-FEB-14

released channel: aux1


RMAN>





15. Shutdown database

16. Run controlfile script from step 9.

17. Run post - refresh scripts

18. register database in RMAN   (register database;)

19. update dbsnmp password for OEM12

20. Turn off archivelog;

21. reduce TEMP tablespace size to one 32GB datafile. 


On non-prod FSDBUG, move temp01 to accomodate the 127GB of TEMP

SQL> shutdown immediate

>ls -lrt /rd340/oradata/fsdbug/temp01

total 2221024

-rw-r-----. 1 oracle dba  524296192 May 26  2023 psgtt01.dbf

-rw-r-----. 1 oracle dba 2285903872 Nov 26 22:10 temp01.dbf

>mv /rd340/oradata/fsdbug/temp01/* /rd900/oradata/fsdbug/temp01

>ls -lrt /rd900/oradata/fsdbug/temp01

total 2234312

-rw-r-----. 1 oracle dba  524296192 May 26  2023 psgtt01.dbf

-rw-r-----. 1 oracle dba 2285903872 Nov 26 22:10 temp01.dbf

SQL> startup

ORACLE instance started.


Total System Global Area 2.1072E+10 bytes

Fixed Size                 18373392 bytes

Variable Size            1.1610E+10 bytes

Database Buffers         9395240960 bytes

Redo Buffers               48734208 bytes

Database mounted.

Database opened.

SQL>



 

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/usr/opt/app/oracle/admin/fsdbug/temp01/temp_01.dbf' SIZE 10m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

DROP TABLESPACE TEMP including contents and datafiles;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/usr/opt/app/oracle/admin/fsdbug/temp01/temp01.dbf' SIZE 10m;

alter database tempfile '/usr/opt/app/oracle/admin/fsdbug/temp01/temp01.dbf' autoextend on maxsize 32024M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE TEMP_NEW including contents and datafiles;

shutdown the database

put back the old symbolic link for the temp01


show parameter undo_tablespace;

create undo tablespace UNDOTBS1 datafile '/usr/opt/app/oracle/admin/fsdbug/undo01/undotbs01.dbf' size 500M;

alter system set undo_tablespace = UNDOTBS1 scope=both;

select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';


Drop tablespace UNDOTBS2 including contents and datafiles;

 alter database datafile '/usr/opt/app/oracle/admin/fsdbug/undo01/undotbs01.dbf' autoextend on maxsize 32024M;




REDO:

set linesize 200

col MEMBER format a80

select group#, status, member from v$logfile order by group#;




ALTER DATABASE DROP LOGFILE GROUP 22;

alter database drop logfile  '/usr/opt/app/oracle/admin/hrprod/redo01/redo01g22.log';

alter database drop logfile '/usr/opt/app/oracle/admin/hrprod/redo02/redo02g22.log';

OR

ALTER DATABASE DROP LOGFILE GROUP 15;

alter database add logfile group 15 ('/usr/opt/app/oracle/admin/hrprod/redo01/redo01g15.log', '/usr/opt/app/oracle/admin/hrprod/redo02/redo02g15.log') size 1032M reuse;



 col REDOLOG_FILE_NAME format a80

SELECT  a.GROUP#,  a.THREAD#,  a.SEQUENCE#,  a.ARCHIVED,  a.STATUS,  b.MEMBER AS REDOLOG_FILE_NAME,  (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP#;



SQL> alter system switch logfile;


System altered.


SQL> select group#, sequence#, archived, status from v$log;


    GROUP#  SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

         1          3 YES ACTIVE

         2          2 YES ACTIVE

         3          4 NO  CURRENT

         4          0 YES UNUSED

         5          0 YES UNUSED

         6          0 YES UNUSED

         7          0 YES UNUSED

         8          0 YES UNUSED

         9          0 YES UNUSED

        10          0 YES UNUSED


10 rows selected.


SQL>  ALTER DATABASE DROP LOGFILE GROUP 2;

 ALTER DATABASE DROP LOGFILE GROUP 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance fstest (thread 1)

ORA-00312: online log 2 thread 1: '/usr/opt/app/oracle/admin/fstest/flash01/FSTEST/onlinelog/o1_mf_2_mzgdwk0t_.log'



SQL> select group#, sequence#, archived, status from v$log;


    GROUP#  SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

         1          3 YES ACTIVE

         2          2 YES ACTIVE

         3          4 NO  CURRENT

         4          0 YES UNUSED

         5          0 YES UNUSED

         6          0 YES UNUSED

         7          0 YES UNUSED

         8          0 YES UNUSED

         9          0 YES UNUSED

        10          0 YES UNUSED


10 rows selected.


SQL>  ALTER DATABASE clear unarchived logfile group 2;


Database altered.


SQL> select group#, sequence#, archived, status from v$log;


    GROUP#  SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

         1          3 YES INACTIVE

         2          0 YES UNUSED

         3          4 NO  CURRENT

         4          0 YES UNUSED

         5          0 YES UNUSED

         6          0 YES UNUSED

         7          0 YES UNUSED

         8          0 YES UNUSED

         9          0 YES UNUSED

        10          0 YES UNUSED


10 rows selected.


SQL> ALTER DATABASE DROP LOGFILE GROUP 2;


Database altered.


Comments

Post a Comment

Popular posts from this blog

Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...
Read more