REAL TIME AI ORACLE DATABASE PROBLEM AND SOLUTIONS

Tuesday, July 16, 2013

STEP BY STEP RMAN RECOVERY CATALOG CREATION AND CONFIGURATION

create databse rman using dbca

SQL> create tablespace catalog datafile '/oracle/product/10.2.0/oradata/rman/catalog01.dbf' size 30m;

Tablespace created.

SQL>
SQL>
SQL> create user rman identified by rman;

User created.

SQL>
SQL>
SQL> alter user rman quota unlimited on catalog;

User altered.

SQL>
SQL>
SQL> grant recovery_catalog_owner to rman;
Grant succeeded.

SQL>
SQL>
SQL>
SQL> alter user rman default tablespace catalog;

User altered.

SQL> conn rman/rman
Connected.
SQL>

connect with rman

[oracle@localhost ~]$ rman catalog rman/rman@rman

create catalog;

[oracle@localhost ~]$ rman target sys/orcl@orcl catalog rman/rman@rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 16 14:58:14 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1348274388)
connected to recovery catalog database

RMAN>

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM YES /oracle/product/10.2.0/oradata/orcl/system01.dbf
2 30 UNDOTBS1 YES /oracle/product/10.2.0/oradata/orcl/undotbs01.dbf
3 240 SYSAUX NO /oracle/product/10.2.0/oradata/orcl/sysaux01.dbf
4 5 USERS NO /oracle/product/10.2.0/oradata/orcl/users01.dbf
5 100 EXAMPLE NO /oracle/product/10.2.0/oradata/orcl/example01.dbf
6 20 RMAN_TEST NO /oracle/product/10.2.0/oradata/orcl/rman01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /oracle/product/10.2.0/oradata/orcl/temp01.dbf

RMAN>

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

thease all tables are creating by create catalog statement in rman prompt

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DB TABLE
NODE TABLE
CONF TABLE
DBINC TABLE
CKP TABLE
TS TABLE
TSATT TABLE
DF TABLE
DFATT TABLE
TF TABLE
TFATT TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OFFR TABLE
RR TABLE
RT TABLE
ORL TABLE
RLH TABLE
AL TABLE
BS TABLE
BP TABLE
BCF TABLE
CCF TABLE
XCF TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BSF TABLE
BDF TABLE
CDF TABLE
XDF TABLE
BRL TABLE
BCB TABLE
CCB TABLE
SCR TABLE
SCRL TABLE
CONFIG TABLE
XAL TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RSR TABLE
FB TABLE
RC_DATABASE VIEW
RC_DATABASE_INCARNATION VIEW
RC_RESYNC VIEW
RC_CHECKPOINT VIEW
RC_TABLESPACE VIEW
RC_DATAFILE VIEW
RC_TEMPFILE VIEW
RC_REDO_THREAD VIEW
RC_REDO_LOG VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_LOG_HISTORY VIEW
RC_ARCHIVED_LOG VIEW
RC_BACKUP_SET VIEW
RC_BACKUP_PIECE VIEW
RC_BACKUP_DATAFILE VIEW
RC_BACKUP_CONTROLFILE VIEW
RC_BACKUP_SPFILE VIEW
RC_DATAFILE_COPY VIEW
RC_CONTROLFILE_COPY VIEW
RC_BACKUP_REDOLOG VIEW
RC_BACKUP_CORRUPTION VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_COPY_CORRUPTION VIEW
RC_OFFLINE_RANGE VIEW
RC_STORED_SCRIPT VIEW
RC_STORED_SCRIPT_LINE VIEW
RC_PROXY_DATAFILE VIEW
RC_PROXY_CONTROLFILE VIEW
RC_RMAN_CONFIGURATION VIEW
RC_DATABASE_BLOCK_CORRUPTION VIEW
RC_PROXY_ARCHIVEDLOG VIEW
RC_RMAN_STATUS VIEW
ROUT TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_RMAN_OUTPUT VIEW
RCVER TABLE
RC_BACKUP_FILES VIEW
RC_RMAN_BACKUP_SUBJOB_DETAILS VIEW
RC_RMAN_BACKUP_JOB_DETAILS VIEW
RC_BACKUP_SET_DETAILS VIEW
RC_BACKUP_PIECE_DETAILS VIEW
RC_BACKUP_COPY_DETAILS VIEW
RC_PROXY_COPY_DETAILS VIEW
RC_PROXY_ARCHIVELOG_DETAILS VIEW
RC_BACKUP_DATAFILE_DETAILS VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_BACKUP_CONTROLFILE_DETAILS VIEW
RC_BACKUP_ARCHIVELOG_DETAILS VIEW
RC_BACKUP_SPFILE_DETAILS VIEW
RC_BACKUP_SET_SUMMARY VIEW
RC_BACKUP_DATAFILE_SUMMARY VIEW
RC_BACKUP_CONTROLFILE_SUMMARY VIEW
RC_BACKUP_ARCHIVELOG_SUMMARY VIEW
RC_BACKUP_SPFILE_SUMMARY VIEW
RC_BACKUP_COPY_SUMMARY VIEW
RC_PROXY_COPY_SUMMARY VIEW
RC_PROXY_ARCHIVELOG_SUMMARY VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_UNUSABLE_BACKUPFILE_DETAILS VIEW
RC_RMAN_BACKUP_TYPE VIEW

90 rows selected.

SQL>

RESYNC CATALOG

Updates the recovery catalog with RMAN metadata in the current control file of the target database (default).

RMAN creates a snapshot control file in order to obtain a read-consistent view of the control file, then updates the recovery catalog with any new information from the snapshot. The RESYNC CATALOG command updates the following classes or records:

Log history records, which are created when a log switch occurs. Note that log history records describe an online log switch, not a log archival.
Archived redo log records, which are associated with archived logs created by archiving an online redo log, copying an existing archived log, or restoring backups of archived logs.
Backup records, which are records of backup sets, backup pieces, proxy copies, and image copies.
Physical schema records, which are associated with datafiles and tablespaces. If the target database is open, then rollback segment information is also updated.

Tuesday, June 25, 2013

ORACLE 10g INSTALLATION ON LINUX

RECOVERY USING RMAN IF ALL DATAFILE,CONTROLFILE,REDOLOGFILES

FIRST HAVE FULL BACKUP OF DATABASE

IF DATABASE OPEN THEN FIRST

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 25 15:58:42 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1334973392, not open)

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> STARTUP MOUNT;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     608174080 bytes

Fixed Size                     1220844 bytes
Variable Size                192941844 bytes
Database Buffers             411041792 bytes
Redo Buffers                   2969600 bytes

RMAN> SHOW ALL;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN> BACKUP DATABASE;

Starting backup at 25-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/product/10.2.0/oradata/orcl/system01.dbf
input datafile fno=00003 name=/oracle/product/10.2.0/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/oracle/product/10.2.0/oradata/orcl/example01.dbf
input datafile fno=00002 name=/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/oracle/product/10.2.0/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-JUN-13
channel ORA_DISK_1: finished piece 1 at 25-JUN-13
piece handle=/oracle/product/10.2.0/flash_recovery_area/ORCL/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T161443_8wlx4vy9_.bkp tag=TAG20130625T161443 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06
Finished backup at 25-JUN-13

Starting Control File and SPFILE Autobackup at 25-JUN-13
piece handle=/oracle/product/10.2.0/flash_recovery_area/ORCL/autobackup/2013_06_25/o1_mf_s_819044006_8wlx8t3h_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JUN-13

DELETE ALL DATAFILES,CONTROLFILE AND REDO LOGFILES

RMAN> SHUTDOWN IMMEDIATE;

database dismounted
Oracle instance shut down

RMAN>

RMAN>

RMAN> STARTUP NOMOUNT;

connected to target database (not started)
Oracle instance started

Total System Global Area     608174080 bytes

Fixed Size                     1220844 bytes
Variable Size                192941844 bytes
Database Buffers             411041792 bytes
Redo Buffers                   2969600 bytes

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 25-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /oracle/product/10.2.0/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/product/10.2.0/flash_recovery_area/ORCL/autobackup/2013_06_25/o1_mf_s_819044006_8wlx8t3h_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/product/10.2.0/oradata/orcl/control01.ctl
output filename=/oracle/product/10.2.0/oradata/orcl/control02.ctl
output filename=/oracle/product/10.2.0/oradata/orcl/control03.ctl
Finished restore at 25-JUN-13

RMAN> ALTER DATABASE MOUNT;

database mounted
released channel: ORA_DISK_1

RMAN>

RMAN>

RMAN> RESTORE DATABASE;

Starting restore at 25-JUN-13
Starting implicit crosscheck backup at 25-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 25-JUN-13

Starting implicit crosscheck copy at 25-JUN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUN-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/product/10.2.0/flash_recovery_area/ORCL/autobackup/2013_06_25/o1_mf_s_819044006_8wlx8t3h_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/product/10.2.0/oradata/orcl/system01.dbf
restoring datafile 00002 to /oracle/product/10.2.0/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /oracle/product/10.2.0/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /oracle/product/10.2.0/oradata/orcl/users01.dbf
restoring datafile 00005 to /oracle/product/10.2.0/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/product/10.2.0/flash_recovery_area/ORCL/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T161443_8wlx4vy9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/product/10.2.0/flash_recovery_area/ORCL/backupset/2013_06_25/o1_mf_nnndf_TAG20130625T161443_8wlx4vy9_.bkp tag=TAG20130625T161443
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 25-JUN-13

RMAN>

RMAN>

RMAN> RECOVER DATABASE;

Starting recover at 25-JUN-13
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/25/2013 16:23:28
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 550400

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN>

THE DATABASE OPEN WITH RESETLOGS

SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL>

Tuesday, July 16, 2013

STEP BY STEP RMAN RECOVERY CATALOG CREATION AND CONFIGURATION

Tuesday, June 25, 2013

ORACLE 10g INSTALLATION ON LINUX

RECOVERY USING RMAN IF ALL DATAFILE,CONTROLFILE,REDOLOGFILES

Complex Oracle 19c to PostgreSQL 15 Database Migration