Backup Archive Log with RMAN NOCATALOG

Hi All,

Below is the script that will help you to take a backup of Archive log files when you had not created a catalog.

RMAN> RUN {
2> SET COMMAND ID TO 'RMAN-LOG';
3> ALLOCATE CHANNEL T1 TYPE DISK;
4> allocate channel T2 TYPE DISK;
5> BACKUP ARCHIVELOG ALL
6> TAG = 'OFTT_ARCHIVE'
7> FORMAT = '/db/u11/arch_bkp/%d_log%U_%t' filesperset = 8 delete input;
8> release channel T1;
9> RELEASE CHANNEL T2;
10> }

executing command: SET COMMAND ID
allocated channel: T1
channel T1: sid=253 devtype=DISK
allocated channel: T2
channel T2: sid=18 devtype=DISK
Starting backup at 29-AUG-08
current log archived
channel T1: starting archive log backupset
channel T1: specifying archive log(s) in backup set
input archive log thread=1 sequence=295798 recid=139 stamp=663952391
....................
....................
...................
...................
Finished backup at 29-AUG-08
released channel: T1
released channel: T2
RMAN>

ORA-12154: TNS:could not resolve service name

Symptom:
When trying to connect to Oracle the following error is generated:
ORA-12154: TNS:could not resolve service name

Cause:
This error indicates that the service (or TNS alias) specified when trying to connect does not exist.

‘TNS aliases’ or ‘service names’ are defined locally to each workstation. Thus different workstations could have a completely different alias to refer to the same database.

Remedy:
* Check what aliases are defined on the workstation, either use one that is defined or add an appropriate one.

The following methods can be used to view the TNS names:
* Look in the file ‘ORANT\ Network\ Admin\ Tnsnames.ora’ (for versions of Oracle prior to 8.1) or ‘Oracle\ Ora81\ Network\ Admin\ Tnsnames.ora’ (for versions of Oracle from 8.1 onwards). In this file each defined name will normally have ‘.world’ appended to it.

* For Oracle 8, run ‘Oracle Net8 Easy Config’. This will list the available service names.

* For Oracle 7, run ‘SQL Net Easy Configuration’. This provides the option to list the available service names.

* For Oracle 8.1 onwards: Try specifying the full service name, i.e. as it appears in the tnsnames file.
If a connection alias is not being explicitly provided then try to identify what alias Oracle thinks you are using. This will typically be contained in the ‘sqlnet.log’ file that the Oracle client will produce. Possible places where Oracle could be getting this (default) alias from:

* The environment variable ‘LOCAL’.

ORA-01113: File XXXX needs media recovery

Symptom:
When trying to start Oracle, the error:
ORA-01113: file n needs media recoveryORA-01110: data file n: ''

Cause:
Oracle has detected a corruption in the file specified.

Possible Remedies:
1. Try: (this requires archive logs)
Recover datafile '';
2. Restore the database from backup and reapply any archive log files.
3. If the database is a standby database then check the procedure for copying the file in the first place. A common mistake is to perform a hot backup of a database but without issuing a ‘alter tablespace ... begin backup ... end backup’ for the tablespace in question.

Once the media error has been recovered from the database can be opened normally (i.e. ‘startup’ or ‘alter database open’).

ORA-01034: ORACLE not available

Symptom:
When attempting to start (or alter) the database the following error is generated:
ORA-01034: ORACLE not available

Possible Cause and Remedy
Oracle indicates the following possible causes:
1. The SGA requires more space than was allocated for it.
2. The operating system variable pointing to the instance is improperly defined.

And ORACLE recommends the following remedial action: Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages. Retry after ORACLE has been initialised. If ORACLE has been initialised, verify that ORACLE was linked correctly. Also, refer to the installation or user's guide for additional information about this error.

1. After exhausting all other options, a simple reboot has been observed to resolve this error.

ORA-01000: Maximum open cursors exceeded

Symptom:
The Oracle error:
ORA-01000: Maximum open cursors exceeded

Cause
Oracle indicates that this error is usually encountered in application programs, but could occur at the database level. I have only encountered it in application programs. The error indicates that Oracle has reached the set limit for open cursors allowed for the session.

Possible Remedies:
Identify if there is a bug in the application program meaning that some cursors do not get closed properly.
Increase the OPEN_CURSORS parameter in the Oracle initialisation file.

ORA-00001: unique constraint violated

Symptom:
When inserting or updating data, the following error is returned:
ORA-00001: unique constraint violated (.)

Cause:
This error means that an attempt has been made to insert a record with a duplicate (unique) key. This error will also be generated if an existing record is updated to generate a duplicate (unique) key. Typically this is a duplicate primary key, but it need not be the primary key.

Remedy:
Only one of the following will be appropriate:

1. Remove the unique restriction.
2. Change the restriction to allow duplicate keys. An index could be changed to be a non-unique index, but remember that the primary key must always be unique.

3. Do not insert the duplicate key.

Usually this error indicates an application error or error on the part of the user.

The error gives the name of the constraint that has been violated, but not the name of the table. To identify the table and find out all the relevant information about the index it is normally easiest to use Oracle Schema Manager - the name of the constraint given in the error will match with the name of the corresponding index. Alternately, to identify the name of the table use:

select table_name from all_indexes where index_name='';

this view ('all_indexes') also contains some other information about the index, such as its uniqueness.
and to identify the files that together constitute the index:

select * from all_ind_columns where index_name='';

Difference Between SCN and checkpoint:

System change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This way you can have a maximum SCN at 1.8E+19.SCN = (SCN_WRAP * 4294967290) + SCN_BASE

Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.

Checkpoint number is never updated for the datafiles of readonly tablespaces.Oracle doesn't use any sequence to generate SCN_BASE numbers, it make calls to "kcmgas" function (per Steve Adams), which is a permanent memory structure for the instance. It is reported in v$sysstat under "calls to kcmgas" name.

Does Oracle do either crash recovery or transaction recovery after shutdown abort if the checkpoint was taken right before the instance crash?

Yes, Oracle perform rollforward first if there are any changes beyond last checkpoint and rollback any uncommitted transactions.SCN numbers are being reported at frequent intervals by SMON in "SMON_SCN_TIME" table.

You can also query v$transaction to arrive at the SCN for that transaction.

Controlfile records information about last checkpoint and archived sequence along with other information.

ORACLE- Who is using TEMP and UNDO

Execute the following query to determine who is using a UNDO Segment:

SELECT TO_CHAR(s.sid)', 'TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'
/

Execute the following query to determine who is using a TEMP Segment:

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2) 'M' "SIZE", a.sid', 'a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks
/