RMAN Quick Ref‎ > ‎

SQL returns the min and max SCN numbers contained in the last online backup.

posted Mar 18, 2011, 11:16 AM by Sachchida Ojha   [ updated Mar 18, 2011, 12:21 PM ]
SELECT MIN(VBD.CHECKPOINT_CHANGE#), MAX(VBD.CHECKPOINT_CHANGE#)
FROM V$BACKUP_DATAFILE VBD, V$DATAFILE VDF,
(SELECT
CREATION_CHANGE#,
MAX(COMPLETION_TIME) COMPLETION_TIME
FROM V$BACKUP_DATAFILE
WHERE CREATION_CHANGE# IN (
SELECT CREATION_CHANGE# FROM V$DATAFILE)
GROUP BY CREATION_CHANGE#
) QUERY1
WHERE VBD.CREATION_CHANGE# = VDF.CREATION_CHANGE#
AND VBD.CREATION_CHANGE# = QUERY1.CREATION_CHANGE#
AND VBD.COMPLETION_TIME = QUERY1.COMPLETION_TIME;

Modify TNSNAMES.ORA

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

-- add the following:
REPOS =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.119)(PORT = 1521))
  )
  (CONNECT_DATA =
  (SERVICE_NAME = repos)
  )
)

-- save change
<Esc> zz

$ tnsping repos
Put the Database into Archive Log Mode
-- as oracle
$ sqlplus /nolog

SQL> conn / as sysdba

col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%arch%';

SQL> shutdown immediate;

SQL> startup mount exclusive;

SQL> alter database archivelog;

SQL> alter database open;

SQL> SELECT group#, thread#, sequence#, archived, status, first_time
     FROM v$log;

SQL> alter system switch logfile;

SQL> SELECT group#, thread#, sequence#, archived, status, first_time
     FROM v$log;

SQL> alter system switch logfile;

SQL> SELECT group#, thread#, sequence#, archived, status, first_time
     FROM v$log;

SQL> SELECT dbid, name FROM v$database;
-- be sure you save the dbid for future reference
Create Repository Tablespace and Schema
SQL> conn sys@repos AS SYSDBA
     password: ************

SQL> SELECT file_name
     FROM dba_data_files;

SQL> SELECT name, value
     FROM gv$parameter
     WHERE name like '%block%';

SQL> CREATE TABLESPACE cat_tbs
     DATAFILE '/app/oracle/product/oradata/REPOS/cat_tbs.dbf' SIZE 50M
     AUTOEXTEND ON
     BLOCKSIZE 8192
     FORCE LOGGING
     DEFAULT NOCOMPRESS
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
     SEGMENT SPACE MANAGEMENT AUTO
     ONLINE;

SQL> CREATE USER repoomega#
     IDENTIFIED BY oracle1
     DEFAULT TABLESPACE cat_tbs
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON cat_tbs;

SQL> GRANT create session TO repoomega#;
SQL> GRANT recovery_catalog_owner TO repoomega#;
SQL> GRANT execute ON dbms_stats TO repoomega#;

SQL> CREATE OR REPLACE PUBLIC SYNONYM dbms_stats FOR sys. dbms_stats;

SQL> conn repoomega#/oracle1@repos

SQL> SELECT COUNT(*) FROM user_objects;
Create RMAN Catalog and Register the Database
$ rman target / catalog repoomega#/oracle1@repos

-- if the tablespace already exists:
RMAN> create catalog;

-- if CAT_TBS is not the default tablespace
RMAN> create catalog tablespace cat_tbs;

RMAN> register database;

RMAN> report need backup;
Explore the RMAN Catalog
SQL> SELECT object_type, COUNT(*)
     FROM user_objects
     GROUP BY object_type;

SQL> SELECT object_name, object_type
     FROM user_objects
     WHERE object_type IN ('FUNCTION', 'PACKAGE');

SQL> desc rc_listbackuppipe

SQL> SELECT DISTINCT package_name, object_name
     FROM all_arguments
     WHERE owner = 'repoomega#'
     AND package_name LIKE 'DBMS%';
Configure Backup Parameters

RMAN> show all;

/* Enable the backup optimization feature introduced in 9i to make sure that RMAN won't backup an archivelog or datafile if there already exists a backup of that file. The FORCE option can be used to override optimization on a specific BACKUP command. */
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;                   -- do not back up unchanged data files

RMAN> CONFIGURE MAXSETSIZE TO 2 G;                        -- make filesize <= 2GB
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;               -- encrypt backups
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';            -- use 256 bit encryption
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;        # default

/* Configure the number of server processes (channels) that write backups to DISK. */
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

/* Set the retention policy to a recovery window of 30 days. This ensures that RMAN retains all backups needed to recover the database to any point in time in the last 30 days. You can use the DELETE OBSOLETE command to delete backups that are no longer required by the retention policy. To exclude a backup from consideration by the policy, you can use KEEP option with the BACKUP command. */
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

/* Enable automatic controlfile backup after each database or archivelog backup */
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> show all;
Run Full Backup
Need to configure a backup location. Go to NetApp but for now:
$ mkdir $HOME/backup1
$ mkdir $HOME/backup2

RMAN> report need backup;

RMAN> RUN
      {
        ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/home/oracle/backup1/%U';
        ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/home/oracle/backup2/%U';
        # AS COPY is default when backing up to disk
        BACKUP DATABASE PLUS ARCHIVELOG;
      }

RMAN> report need backup;

RMAN> list backup summary;

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SQL> desc v$rman_status

SQL> SELECT operation, status, mbytes_processed, start_time, end_time
     FROM v$rman_status;
Validate Backup
RMAN> crosscheck backup of database;

-- need to look at data dictionary too
Trash Control File and Recover
$ cd /app/oracle/product/OracleHomes/oradata/orcl

$ ls -la

$ rm control02.ctl

$ cd /app/oracle/product/OracleHomes/admin/orcl/bdump

$ tail alert_orcl.log
Trash Log File and Recover
Error when you try to start db after copying one logfile to another (a common mistake)

ORA-00341: log 1 of thread 1, wrong log # in header
ORA-00312: online log 1 thread 1: '/app/oracle/product/oradata/prod/redo01.log'

-- to recover
alter database clear unarchived logfile group 1;
alter database drop logfile group 1;

-- then recreate the log file
alter database add logfile group 1 ('/app/oracle/product/oradata/prod/redo01.log') SIZE 50M;
Comments