How to change archive mode in rac

How to change archive mode in rac

1. Change the cluster database parameter so the database can be mounted in exclusive mode which is required to enable archive logging

alter system set cluster_database=false scope=spfile;

2. Shutdown the database using srvctl

srvctl stop database -d ORA

3. Startup one of the instances upto the mount state

sqlplus / as sysdba

startup mount

4. Enable archivelog mode

alter database archivelog;

5. Change the cluster_database parameter back to true in the spfile

alter system set cluster_database=true scope=spfile;

6. Shutdown the instance

shutdown immediate

7. Startup the database using srvctl

srvctl start database -d ORA

8. Once the database is back up you can verify the change by connecting to one of the database instances

sqlplus / as sysdba

archive log list

For example:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archlogs/ORA/
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

Simple as that.

How to resolved gap on standby

How to resolved gap on standby

Simple Step--

Firstly in primary db we check archive status -

1.select max(SEQUENCE#) from V$ARCHIVED_LOG;

MAX(SEQUENCE#)

--------------

         30849                

2. Now we check in standby archive detail           

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM v$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM v$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )

IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

           

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                  30850                 30766         84

(As per above we can see gap on standby database )

                         

3. we will immediately check alert log file on standby database. Here we will get some clue.

FAL[client]: Failed to request gap sequence

 GAP - thread 1 sequence 31301-31301

 DBID 2359012130 branch 846904520

FAL[client]: All defined FAL servers have been attempted.

In above noticeable point is that we can see gap in archive file. so we will go to the next step.

4. we will copy missing archive file from primary to standby database

scp * (ip):loction on standby where we want to keep this file

enter

password.xyz

5. Now we register this file in db level.

ALTER DATABASE REGISTER LOGFILE '/archive/MARIGOLD/MARIGOLD_1_29726_846904520.arch';

6.Then we start mrp process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

7. Now automatically archive apply on standby database .We can verify in alert log file.

RFS[10]: Archived Log: '/archive/MARIGOLD/MARIGOLD_1_32222_846904520.arch'

Primary database is in MAXIMUM PERFORMANCE mode

Fri Feb 13 03:19:13 2015

Media Recovery Log /archive/MARIGOLD/MARIGOLD_1_32222_846904520.arch

Fri Feb 13 03:19:39 2015

Media Recovery Waiting for thread 1 sequence 32223 (in transit)

             

                       

                       

             

           

Covert database noarchive mode to archive mode

Covert database noarchive mode to archive mode

1-SQL> archive log list
Database log mode             No Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     2
Next log sequence to archive   3
Current log sequence           3

2.Shutdown the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3. startup mount
SQL> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2089400 bytes
Variable Size             264244808 bytes
Database Buffers          801112064 bytes
Redo Buffers                6295552 bytes
Database mounted.

4- SQL> alter database archivelog;

Database altered.

5- shutdown the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6-startup
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2089400 bytes
Variable Size             264244808 bytes
Database Buffers          801112064 bytes
Redo Buffers                6295552 bytes
Database mounted.
Database opened.

7- Now verify
archive log list
SQL> archive log list;
Database log mode               Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     2
Current log sequence           3

Cloning Steps in same server -

Cloning Steps in same server -
1-Firstly take Full backup
run
{
allocate channel d1 device type disk format '/crd04/oracle/daffodil/backup_6feb/%U.bkp';
allocate channel d2 device type disk format '/crd04/oracle/daffodil/backup_6feb/%U.bkp';
allocate channel d3 device type disk format '/crd04/oracle/daffodil/backup_6feb/%U.bkp';
allocate channel d4 device type disk format '/crd04/oracle/daffodil/backup_6feb/%U.bkp';
backup database plus archivelog;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
2-Backup controlfile
BACKUP CURRENT CONTROLFILE format '/crd04/oracle/daffodil/backup_6feb/%U.ctl';
3.Then set environment file…
[oracle@XXX ~]$ export ORACLE_SID=XYZ
[oracle@XXX ~]$ echo $ORACLE_SID
XYZ
4. Go to in pfile location
cd $ORACLE_HOME/dbs
Then create pfile but make sure in pfile both db name be same.
daffodil__db_cache_size=1610612737
daffodil__java_pool_size=16777216
daffodil__large_pool_size=16777216
daffodil__shared_pool_size=452984832
daffodil__streams_pool_size=33554432
*.background_dump_dest='/crd01/oracle/daffodil/bdump/'
*.control_files='/crd01/oracle/daffodil/control01.ctl'
*.core_dump_dest='/crd01/oracle/daffodil/cdump/'
*.db_block_size=8192
*.db_name='marigold'
db_unique_name=daffodil
*.processes=100
*.sessions=100
*.sga_target=2G
*.undo_management='AUTO'
*.user_dump_dest='/crd01/oracle/daffodil/udump/'
undo_tablespace=undotbs;
5.startup nomount
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size                  2089400 bytes
Variable Size             264244808 bytes
Database Buffers          801112064 bytes
Redo Buffers                6295552 bytes
6.Restore controlfile
RMAN> restore controlfile from '/crd04/oracle/daffodil/backup_6feb/09pui4i1_1_1.ctl';
7.RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
8.Restore the database.
run
{
allocate channel C1 device type disk;
allocate channel C2 device type disk;
allocate channel C3 device type disk;
set newname for datafile 1 to '/archive/oracle/daffodil/data/system.dbf';
set newname for datafile 2 to '/archive/oracle/daffodil/data/undo.dbf';
set newname for datafile 3 to '/archive/oracle/daffodil/data/sysaux.dbf';
set newname for datafile 4 to '/archive/oracle/daffodil/data/users01.dbf';
set newname for datafile 5 to '/archive/oracle/daffodil/data/users03.dbf';
set newname for datafile 6 to '/archive/oracle/daffodil/data/UNDOTBS01.dbf';
set newname for datafile 7 to '/archive/oracle/daffodil/data/users04.dbf';
restore database ;
switch datafile all;
}
9.RMAN> recover database;
Starting recover at 06-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=101 devtype=DISK
starting media recovery
archive log thread 1 sequence 560 is already on disk as file /crd02/oracle/marigold/datafiles/redo2.log
archive log thread 1 sequence 561 is already on disk as file /crd02/oracle/marigold/datafiles/redo1.log
archive log filename=/crd02/oracle/marigold/datafiles/redo2.log thread=1 sequence=560
archive log filename=/crd02/oracle/marigold/datafiles/redo1.log thread=1 sequence=561
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-Feb
10.At last open database with resetlogs
SQL> alter database open resetlogs;
Database altered