Tag: list

Changing Archive Log Destination

Posted on by Osama Mustafa in Database
Today I am Gonna Show you How to change Archive log Destination in Two Ways :

1.Temporarily Changing the Destination Using SQL*Plus

sqlplus / as sysdba

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287

 To change the location

sql>ARCHIVE LOG START ‘/u01/arch’;

To Verify your changes:

sql> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287

2-Permanently Changing the Destination Using SQL*Plus

sqlplus / as sysdba

ALTER SYSTEM SET log_archive_dest =’/oradata/arch’ scope=both;

To Verify your changes:

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287   

To change the size of archive log

SQL> alter system SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID=’orcl’;

System altered. 

Thank You
Osama Mustafa

  

ORA-16018 ORA-16019

Posted on by Osama Mustafa in Database
Cause : 
These two errors come whenever LOG_ARCHIVE_DEST is set as archival location and you want to set DB_RECOVERY_FILE_DEST

Solution : 

1- You need to see Archive log destination First .

SQL > archive log list ; 

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217

2-You Can Check Archive Location  By Check Database Parameter

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /oradata
 db_recovery_file_dest_size big integer 10G

 3-Set New Archive Log Location

SQL> alter system set log_archive_dest=’/u01′;

alter system set log_archive_dest=’/u01′
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

4- If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .

SQL> alter system set DB_RECOVERY_FILE_DEST=”;

System altered.

SQL> alter system set log_archive_dest=’/u01′;

System altered.

 SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01

Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217

5-Also if you now want to set DB_RECOVERY_FILE_DEST ORA-16019: will occur.

SQL> alter system set DB_RECOVERY_FILE_DEST=’/u02′;

alter system set DB_RECOVERY_FILE_DEST=’/u02′
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST

 6-To set DB_RECOVERY_FILE_DEST first reset LOG_ARCHIVE_DEST.

SQL> alter system set log_archive_dest=”;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST=’/u02′;

System altered.

 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217

To set multiple location of archival destination set another log_archive_dest_n parameter like,
SQL> alter system set log_archive_dest_3=’LOCATION=/u02′;
System altered.

Thank you
Osama mustafa
 

Useful Command in RMAN

Posted on by Osama Mustafa in Scripts

List Command :

1)RMAN> list backup;
List all your backup sets.
***************
2)RMAN>LIST BACKUPSET;
Lists only backup sets and proxy copies.
***************
3)RMAN>LIST COPY;
Lists of Image copies and Archive Logs.
***************
4)RMAN>LIST EXPIRED BACKUP;
Backups did not found after crosscheck. That is backup is manually moved or deleted from OS.
***************
5)RMAN>LIST BACKUP BY FILE;
List backup by Datafile, controlfile, spfile.
***************
6)RMAN>LIST BACKUP SUMMARY;
Lists backup sets, proxy copies, and disk copies.
***************
7)RMAN>LIST BACKUP OF DATABASE;
LIST BACKUP LIKE ‘/tmp/%’; list backup of datafile 1; LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;LIST COPY OF DATAFILE 2 COMPLETED BETWEEN ’17-MAR-2008′ AND ’22-MAR-2008′; are also available.
***************
8) RMAN>LIST INCARNATION;
LIST INCARNATION OF DATABASE; to see the incarnations of your database.

Report Command:

RMAN REPORT command analyzes the available backups and return results about while files need backup which files are obsolete etc.

1)REPORT NEED BACKUP;
Determine which database files need backup under a specific retention policy.
***************
2)REPORT UNRECOVERABLE;
Report which database files require backup because they have been affected by some NOLOGGING operation.
***************
3)REPORT SCHEMA;
Lists and displays information about the database files.
***************
4)REPORT OBSOLETE;
REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy.
***************
5)REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01;
If you use Recovery Catalog then past data can be shown.Like, REPORT SCHEMA AT TIME ‘SYSDATE-10’;
***************

Delete Command:

Delete commands is used to delete any backup or backupsets.

1)RMAN> delete backupset all;
Delete all your backup sets
***************
2) RMAN> delete copy all;
Delete all image copies.
***************
3) RMAN>DELETE OBSOLETE;
Delete obsolete Backups.
***************

thank you all .