Category Archives: archivelog

set the archive destination And archive to multiple destinations in oracle

By
Method
Initialization Parameter
Host
Example
1
LOG_ARCHIVE_DEST_n where: n is an integer from 1 to 10
Local or remote
LOG_ARCHIVE_DEST_1 = ‘LOCATION=/disk1/arc’ LOG_ARCHIVE_DEST_2 = ‘SERVICE=standby1’
2
LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
Local only
LOG_ARCHIVE_DEST = ‘/disk1/arc’ LOG_ARCHIVE_DUPLEX_DEST = ‘/disk2/arc’




1 Method
Perform the following steps to set the destination for archived redo logs using the LOG_ARCHIVE_DEST_n initialization parameter:

(1) Use SQL*Plus to shut down the database with normal or immediate option but not abort.
SHUTDOWN

(2) Edit the LOG_ARCHIVE_DEST_n parameter to specify from one to ten archiving locations. The LOCATION keyword specifies an operating system specific path name.
For example, enter:
LOG_ARCHIVE_DEST_1 = ‘LOCATION = /disk1/archive’
LOG_ARCHIVE_DEST_2 = ‘LOCATION = /disk2/archive’
LOG_ARCHIVE_DEST_3 = ‘LOCATION = /disk3/archive’

If you are archiving to a standby database, use the SERVICE keyword to specify a valid net service name from the tnsnames.ora file. For example, enter:

LOG_ARCHIVE_DEST_4 = ‘SERVICE = standby1’

(3) Edit the LOG_ARCHIVE_FORMAT initialization parameter, using %s to include the log sequence number as part of the file name and %t to include the thread number. Use capital letters (%S and %T) to pad the file name to the left with zeroes. For example, enter:
LOG_ARCHIVE_FORMAT = arch%s.arc

These settings will generate archived logs as follows for log sequence
numbers 100, 101, and 102:

/disk1/archive/arch100.arc, 
/disk1/archive/arch101.arc,
/disk1/archive/arch102.arc

/disk2/archive/arch100.arc, 
/disk2/archive/arch101.arc,
/disk2/archive/arch102.arc

/disk3/archive/arch100.arc, 
/disk3/archive/arch101.arc,
/disk3/archive/arch102.arc

02 Method
The second method, which allows you to specify a maximum of two locations, is to use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. Whenever Oracle archives a redo log, it archives it to every destination specified by either set of parameters.

Perform the following steps to use method 2:

(1) Use SQL*Plus to shut down the database.
SHUTDOWN

(2) Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST dynamically using the ALTER SYSTEM statement).

For example, enter:

LOG_ARCHIVE_DEST = ‘/disk1/archive’
LOG_ARCHIVE_DUPLEX_DEST = ‘/disk2/archive’

(3) Edit the LOG_ARCHIVE_FORMAT parameter, using %s to include the log sequence number as part of the file name and %t to include the thread number. Use capital letters (%S and %T) to pad the file name to the left with zeroes.

For example, enter:
LOG_ARCHIVE_FORMAT = arch_%t_%s.arc

For example, the above settings generates archived logs as follows for log sequence numbers 100 and 101 in thread 1:

/disk1/archive/arch_1_100.arc, /disk1/archive/arch_1_101.arc
/disk2/archive/arch_1_100.arc, /disk2/archive/arch_1_101.arc

learing abbout postgresql database and oracle database and cricket Entertainment

oracle database into archive/noarchivelog mode in RAC environment:

By

1. Set cluster_database=false for the instance.

alter system set cluster_database=false scope=spfile sid=’PROD1′;

2. Shutdown all the instances accessing the database.

srvctl stop database -d prod

3. Mount the database using the local instance.

startup mount

4. Enabling archiving / noarchiving

alter database archivelog;

OR

alter database noarchivelog;

5. Change the parameter cluster_database=true for the instance prod1.

alter system set cluster_database=true scope=spfile sid=’PROD1′;

6. Shutdown the local database.

shutdown

7. Bring up all the instances.

srvctl start database -d prod

5.A) TO START AND STOP THE DATABASE AND INSTANCE IN RAC ENVIRONMENT:

srvctl status database -d prod

learing abbout postgresql database and oracle database and cricket Entertainment

Nologging and force logging examples

By
Nologging and force logging

NOLOGGING can be used to prevent bulk operations from logging too much information to
Oracle’s Redo log files.On the other hand, FORCE LOGGING can be used on tablespace or
database level to force logging of changes to the redo. This may be required for sites
that are mining log data, using Oracle Streams or using Data Guard (standby databases).
Nologging[edit]

NOLOGGING can be used to minimize the amount of redo generated by Oracle. Only the
following operations can make use of nologging:

SQL*Loader in direct mode
INSERT /*+APPEND*/ …
CTAS

ALTER TABLE statements (move/add/split/merge partitions)
CREATE INDEX

ALTER INDEX statements (move/add/split/merge partitions)
To create a table in NOLOGGING mode:

SQL> CREATE TABLE t1 (c1 NUMBER) NOLOGGING;
Table created.

To enable NOLOGGING for a table:

ALTER TABLE t1 NOLOGGING;
Table altered.
Force logging[edit]

A tablespace or the entire database is either in force logging or no force
logging mode. To see which it is, run:

SQL> SELECT force_logging FROM v$database;
FOR
—
NO
or
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;
TABLESPACE_NAME                FOR
—————————— —
SYSTEM                         NO
…
To enable force logging:
SQL> ALTER DATABASE force logging;
Database altered.
SQL> ALTER TABLESPACE users FORCE LOGGING;
Tablespace altered.
To disable:
SQL> ALTER DATABASE no force logging;
Database altered.
SQL> ALTER TABLESPACE users NO FORCE LOGGING;
Tablespace altered.

learing abbout postgresql database and oracle database and cricket Entertainment

NOARCHIVELOG DATABASE RECOVERY VIA RMAN

By
  • Your database is running in No Archive log mode and you have daily/weekly RMAN backup configured.
  • One fine day, your server crashed and you lose one of the disks or all the disks.
  • You have no other option but to restore the whole database backup to point of last valid backup.
rman target / catalog rman10/rman10@rman10s
Recovery Manager: Release 10.1.0.3.0 – Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.
Connected to target database (not started)
Connected to recovery catalog database
RMAN>
RMAN>
RUN
{
STARTUP NOMOUNT FORCE;
ALLOCATE CHANNEL CH1 DEVICE TYPE ‘SBT_TAPE’
PARMS  ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.orcld.opt)’;
RESTORE SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/freespace/ORACLE/OCM/prod10gr1/dbs/initorcl.ora’
Trying to start the Oracle instance without parameter files…
Oracle instance started
Total System Global Area     142606336 bytes
Fixed Size                      778072 bytes
Variable Size                 70525096 bytes
Database Buffers              67108864 bytes
Redo Buffers                   4194304 bytes
Allocated channel: CH1
Channel CH1: Sid=28 devtype=DISK
Starting restore at 11-FEB-07
Channel CH1: starting datafile backupset restore
Channel CH1: restoring SPFILE
Output filename=/freespace/ORACLE/OCM/prod10gr1/dbs/spfileorcl.ora
Channel CH1: restored backup piece 1
Piece handle=/freespace/ORACLE/OCM/orcl/ORCL.20070211.8.1.1.614285217 tag=TAG20070211T184641
Channel CH1: restore complete
Finished restore at 11-FEB-07
Oracle instance shut down
connected to target database (not started)
Oracle instance started
Total System Global Area    1241513984 bytes
Fixed Size                      778896 bytes
Variable Size                347348336 bytes
Database Buffers             889192448 bytes
Redo Buffers                   4194304 bytes
Starting restore at 11-FEB-07
allocated channel: ORA_DISK_1
Channel ORA_DISK_1: Sid=159 devtype=DISK
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: restoring controlfile
Channel ORA_DISK_1: restored backup piece 1
Piece handle=/freespace/ORACLE/OCM/orcl/ORCL.20070211.8.1.1.614285217 tag=TAG20070211T184641
Channel ORA_DISK_1: restore complete
Output filename=/freespace/ORACLE/OCM/orcl/control01.ctl
Output filename=/freespace/ORACLE/OCM/orcl/control02.ctl
Output filename=/freespace/ORACLE/OCM/orcl/control03.ctl
Finished restore at 11-FEB-07
Database mounted
Released channel: ORA_DISK_1
Starting restore at 11-FEB-07
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: Sid=159 devtype=DISK
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile(s) to restore from backup set
Restoring datafile 00001 to /freespace/ORACLE/OCM/orcl/system01.dbf
Restoring datafile 00002 to /freespace/ORACLE/OCM/orcl/undotbs01.dbf
Restoring datafile 00003 to /freespace/ORACLE/OCM/orcl/sysaux01.dbf
Restoring datafile 00004 to /freespace/ORACLE/OCM/orcl/users01.dbf
Channel ORA_DISK_1: restored backup piece 1
Piece handle=/freespace/ORACLE/OCM/orcl/ORCL.20070211.7.1.1.614285202 tag=TAG20070211T184641
Channel ORA_DISK_1: restore complete
Finished restore at 11-FEB-07
Database opened
New incarnation of database registered in recovery catalog
Starting full resync of recovery catalog
Full resync complete

learing abbout postgresql database and oracle database and cricket Entertainment