Oracle: To setup RMAN

 1. Connect to rmandb602 as oracle

2. create user wpstag 

    SQL> create user wpstag identified by bkup_123 temporary tablespace temp default tablespace  RMADMIN_DATA01 profile app_account_profile;

User created.



    SQL> grant create session to wpstag ;

    Grant succeeded.


3.RECOVERY_CATALOG_OWNER role must be granted to user wpstag

     grant RECOVERY_CATALOG_OWNER to wpstag ;

     alter user wpstag quota unlimited on RMADMIN_DATA01;

 

     

     grant unlimited tablespace to wpstag ;

 grant select on user_db_links to wpstag;    

grant execute on utl_http to wpstag;

grant execute on dbms_lob to wpstag;

grant execute on dbms_sql to wpstag;

alter package rmadmin.dbms_rcvcat compile body;


oracle-rmandb602:rmprod:/usr/opt/app/oracle/product/19.3.0/bin>rman target / catalog wpstag/bkup_123


Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 27 01:13:10 2020

Version 19.3.0.0.0


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


connected to target database: RMPROD (DBID=1995745915)

connected to recovery catalog database


RMAN> create catalog;


recovery catalog created


RMAN>



oracle-ws7stgdb601:wpstag:/usr/local/oracle/rman_WCSTAG/RCV_DIR>rman target / catalog wpstag/bkup_123@rmprod


Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jan 27 01:19:24 2020


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


connected to target database: WPSTAG (DBID=3259554206)

connected to recovery catalog database

recovery catalog schema release 19.03.00.00. is newer than RMAN release


RMAN> register database;


database registered in recovery catalog

starting full resync of recovery catalog

full resync complete


RMAN>




grant CREATE ANY CONTEXT to wcstag;

grant drop ANY CONTEXT to wcstag;

srdc_rman_backup_script_<date>.txt 

srdc_rman_backup_output_<date>.log

srdc_rman_backup_debug_<date>.trc

srdc_rman_diag.out

Alert.log of target database

Possibly trace files and/or sbtio.log file


TNS entry on source server:

RMPROD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = rmandb602.tmw.com)(PORT = 4091))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = rmprod)

      (SERVER = DEDICATED)

    )

  )





Connect to target database - wvmdvlp on server ws7tstdb601

3. rman target / catalog wcstag/bkup_123@rmprod


RMAN> create catalog;

recovery catalog created


RMAN> register database;


database registered in recovery catalog

starting full resync of recovery catalog

full resync complete





SQL> connect / as sysdba

Connected.

SQL> show parameter recovery_file_dest


NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /usr/opt/app/oracle/admin/wcte

                                                 st/flash01/

db_recovery_file_dest_size           big integer 1G


SQL> show parameter log_archive_dest_1

SQL> alter system set log_archive_dest_1='LOCATION=/usr/opt/app/oracle/admin/bfprod/arch' scope=both;


System altered.


SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /usr/opt/app/oracle/admin/wctest/arch

Oldest online log sequence     65

Current log sequence           68

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.


Total System Global Area 3958439936 bytes

Fixed Size                  2234440 bytes

Variable Size             922748856 bytes

Database Buffers         3019898880 bytes

Redo Buffers               13557760 bytes

Database mounted.

SQL> alter database archivelog;


Database altered.


SQL> alter database open;


Database altered.


SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /usr/opt/app/oracle/admin/wctest/arch

Oldest online log sequence     65

Next log sequence to archive   68

Current log sequence           68

SQL> alter system switch logfile;


System altered.


SQL> !ls -lrt /usr/opt/app/oracle/admin/wctest/arch

lrwxrwxrwx 1 oracle dba 26 Feb 27 12:41 /usr/opt/app/oracle/admin/wctest/arch -> /rd500/oradata/wctest/arch


SQL> !ls -lrt /rd500/oradata/wctest/arch

total 19608

-rw-rw---- 1 oracle dba 20046336 Mar  8 09:05 1_68_776350499.dbf


SQL> alter system switch logfile;


System altered.


SQL> !ls -lrt /rd500/oradata/wctest/arch

total 19616

-rw-rw---- 1 oracle dba 20046336 Mar  8 09:05 1_68_776350499.dbf

-rw-rw---- 1 oracle dba     1024 Mar  8 09:05 1_69_776350499.dbf


SQL> exit





Connect to RMANCAT on server tmwrman01 as oracle


CREATE USER wc7test IDENTIFIED BY bkup_123 

  DEFAULT TABLESPACE RMAN_DATA

  QUOTA UNLIMITED ON RMAN_DATA

  PROFILE APP_ACCOUNT_PROFILE;


GRANT CREATE SESSION, RECOVERY_CATALOG_OWNER TO WC7TEST;


##CONFIGURE RETENTION POLICY TO RECOVERY WINDOW of 7 days; 

CONFIGURE RETENTION POLICY TO REDUNDANCY  7;

CONFIGURE BACKUP OPTIMIZATION ON;  

CONFIGURE DEFAULT DEVICE TYPE TO DISK;  

CONFIGURE CONTROLFILE AUTOBACKUP ON;  

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/opt/app/dbdump/hot_backup/%d/%d_AutoCTL_%F.bak';

CONFIGURE DB_UNIQUE_NAME 'wcprod01' CONNECT IDENTIFIER  'wcprod01';

CONFIGURE DB_UNIQUE_NAME 'wcprod02' CONNECT IDENTIFIER  'wcprod02';

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;



>mkdir /usr/opt/app/hot_backup/WCPROD


oracle-ws7tstdb01:wc7test:/usr/opt/app/oracle/orahome>rman target / catalog wc7test/bkup_123@rmancat                                                                 est/bkup_123@rmancat


Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 25 16:42:19 2012


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


connected to target database: WC7TEST (DBID=1857166688)

connected to recovery catalog database


RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;


CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/opt/app/dbdump/hot_backup/%d/%d_AutoCTL_%F.bak';

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete


RMAN>

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION OFF;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete


RMAN>

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete


RMAN>

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete


RMAN>

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/opt/app/dbdump/hot_backup/%d/%d_AutoCTL_%F.bak';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete


RMAN> show all

2> ;


RMAN configuration parameters for database with db_unique_name HYDVLP are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/opt/app/dbdump/hot_backup/hydvlp/%d_AutoCTL_%F.bak';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default 

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/opt/app/oracle/product/12.1.0/dbs/snapcf_hydvlp.f'; # default





RMAN> crosscheck archivelog all;


allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1717 device type=DISK

specification does not match any archived log in the repository


RMAN> list expired archivelog all;


specification does not match any archived log in the repository



create backup directory: /usr/opt/app/dbdump/hot_backup/WVMDVLP


update files in /usr/local/oracle/rman_WC7TEST


kick off backup: 


usage:

>./rman_backup abc

*ERROR*: Operation 'abc' is not supported with this program!

         Supported Operations are:

         WC7TEST_LEVEL0  WC7TEST_ARCLOG  WC7TEST_LEVEL1  WC7TEST_REPORT


ws7tstdb01:wc7test:/usr/local/oracle/rman_WC7TEST>./rman_backup WC7TEST_LEVEL0


check the log in /usr/local/oracle/log




**possible setup in cron:


30 22 * * * /usr/local/oracle/rman_WCPROD/rman_backup WCPROD_LEVEL0 > /usr/local/oracle/log/WCPROD_LEVEL0.log 2>&1

30 10 * * * /usr/local/oracle/rman_TWPROD/rman_backup TWPROD_ARCLOG > /usr/local/oracle/log/TWPROD_ARCLOG.log 2>&1




FOR ESRPROD

============

RMAN> show all

2> ;


RMAN configuration parameters for database with db_unique_name ESRPROD are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/opt/app/dbdump/rman/%d/%d_AutoCTL_%F.bak';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/opt/app/oracle/product/11.2.0/dbs/snapcf_esrprod.f'; # default


RMAN> crosscheck archivelog all;

Crosschecked 8719 objects



RMAN> list expired archivelog all;

.

.

.

16913   1    8111    X 24-JUL-12

        Name: /usr/opt/app/oracle/admin/esrprod/arch/1_0000008111_789008348.arc

        

        

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 days;


old RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete


        

        

        

        ************** FOR STANDBY / DATAGUARD DATABASES

        

CONFIGURE DB_UNIQUE_NAME 'wcprod01' CONNECT IDENTIFIER  'wcprod01';

CONFIGURE DB_UNIQUE_NAME 'wcprod02' CONNECT IDENTIFIER  'wcprod02';

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;


example:

RMAN> show all;


RMAN configuration parameters for database with db_unique_name WPSTAG01 are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/opt/app/hot_backup/%d/%d_AutoCTL_%F.bak';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE DB_UNIQUE_NAME 'WPSTAG01' CONNECT IDENTIFIER  'WPSTAG01';

CONFIGURE DB_UNIQUE_NAME 'WPSTAG02' CONNECT IDENTIFIER  'WPSTAG02';

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/opt/app/oracle/product/11.2.0/dbs/snapcf_wpstag.f'; # default


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