Change standby database Protection mode

         Convert MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY

On Primary DB

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         KANDY            PRIMARY          MAXIMUM PERFORMANCE


* Number of standby and online redo logs and it’s sizes should be same

SQL> select group#,bytes/1024/1024 from v$log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1              50
         2              50
         3              50

SQL> select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
         4              50
         5              50
         6              50


SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             49

ON STANDBY DB

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             49

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      COLOMBO          PHYSICAL STANDBY MAXIMUM PERFORMANCE


ON PRIME DB

Configure to use SYNCHRONOUS mode

SQL> select value from v$parameter where name='log_archive_dest_2';

VALUE
--------------------------------------------------------------------------------
service=TNS_COL LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180
valid_for=(online_logfiles,primary_role) db_unique_name=colombo

alter system set log_archive_dest_2='service="TNS_COL" LGWR AFFIRM SYNC db_unique_name="colombo" valid_for=(all_logfiles,primary_role)';
alter database set standby database to maximize availability;
select status,instance_name,database_role,protection_mode from v$database,v$instance;

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         KANDY            PRIMARY          MAXIMUM AVAILABILITY

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

If you have configured broker , change the below parameter

edit database 'colombo' SET PROPERTY LogXptMode='SYNC';

ADD IP to Solaris 11

Step 1 : Set the NCP

To check the current NCP setting:

# netadm list
TYPE        PROFILE        STATE
ncp         Automatic      disabled
ncp         DefaultFixed   online
loc         Automatic      offline
loc         NoNet          offline
loc         DefaultFixed   online

As seen in the output above, the NCP is set to DefaultFixed. In case it is not set, use netadm command to set it to DefaultFixed :

# netadm enable -p ncp DefaultFixed

Step 2 : Check the link status
The command to check the link status in Solaris 11 is :

# dladm show-phys
LINK              MEDIA                STATE      SPEED  DUPLEX    DEVICE
net0              Ethernet             up         1000   full      e1000g0

 Step 3 : Create a new interface

The ipadm command creates the new interface to be configured :

# ipadm create-ip net0

Check the newly created interface :

# ipadm show-if
IFNAME     CLASS      STATE      ACTIVE      OVER
lo0        loopback   ok         yes         ---
net0       ip         down       no          ---

Step 4 : Creating IP address

-T specifies either static, dhcp or addrconf (for IPv6) types of addresses.

# ipadm create-addr –T static –a local=192.168.1.10/24 net0/techh

To check the interface status of the interface :

# ipadm show-if
IFNAME     CLASS    STATE    ACTIVE OVER
lo0        loopback ok       yes    --
net0       ip       ok       yes    --

To check the configured IP address :

# ipadm show-addr
ADDROBJ                TYPE     STATE        ADDR
lo0/v4                 static   ok           127.0.0.1/8
net0/geeklab           static   ok           192.168.1.20/24
lo0/v6                 static   ok           ::1/128

delete the interface

 

# ipadm delete-ip net0

RMAN Configuration

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

 

To Change the archivelog destination

 

alter system set log_archive_dest_1='LOCATION=/vol2/oracle/Archives’;

 

To change the rman backup location..

 

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/vol0/RMAN_BK/%d-%T-%U';

 

To change the controlfile  location..

 

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 
'/u01/app/oracle/admin/flash/%F';

 

 

LOG_ARCHIVE_FORMAT = “LOG%s_%t_%r.arc”
Various parameters that can be used with the LOG_ARCHIVE_FORMAT parameter are given below:
%s – log sequence number
%S – log sequence number, padded with zero
%t – thread number
%T – thread number, padded with zero
%a – activation id
%d – database id
%r – reset logs id

 

SQL> alter system set log_archive_format='hasaral_%s_%t_%r.arc' scope=spfile;
System altered.

SQL> shut immediate
SQL>startup

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

 

Steps to enable archivelog—

 

 

SQL> archive log list

SQL> show parameter recovery_file_dest

 

alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both;

 

shutdown immediate;

 

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list

SQL> alter system switch logfile;

 

SQL> host

[oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/arch

1_27_711369564.dbf

[oracle@ora1 ~]$ exit

exit

 

SQL>

 

 

 

 

 

 

 

1. show parameter db_recovery_file_dest;

2. Alter system set  db_recovery_file_dest=’/vol1/oracle/backup’;

3. Alter system set db_recovery_file_dest_size=2G;

 

 

4. rman target /

 

Offline Backup--------

 

5. RMAN> configure retention policy to redundancy 2; (This means RMAN will try to keep                         

      2 copies of the database backups)

6. RMAN>configure controlfile autobackup on;

 

 

 CONFIGURE DEFAULT DEVICE TYPE DISK FORMAT ‘/vol0/rman_backups/cdb/%U’;

 

 

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 
'/u01/app/oracle/admin/flash/%F';

 

7. RMAN>shutdown immediate

8. RMAN>startup mount

9. RMAN>backup database;

1. RMAN>sql ’alter database open’;

 

 

 

Online Backup---------

 

1. RMAN>backup database plus archivelog delete input;

2. RMAN>backup archivelog all delete input;

 

 

-------- To Find the archive Destination---------

 

SQL> archive log list

SQL> show parameter db_recovery_file_dest

SQL> select dest_name,status,destination from v$archive_dest;

 

 

ENABLE BLOCK CHANGE TRACKING

 

SQL> alter database enable block change tracking
using file /u01/app/oracvle/mysid/data/block_change_tracking.dbf';

 

SQL> alter database enable block change tracking;

 

 

 

 

select filename, status, bytes from   v$block_change_tracking
SELECT * FROM v$sgastat WHERE name like 'CTWR%';

 

 

 

 

 

Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initbooktst.ora

restore spfile from autobackup;

 

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

 

 

Archivelog Delete Commands

 

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

 

delete noprompt force archivelog all;

delete noprompt force archivelog all completed before 'SYSDATE-2';

 

 

crosscheck backup;

Crosscheck archivelog all;

delete backup tag=TAG20131112T041335;

delete backup;

List backup summary;

 

 

select dbid from v$database;

 

 

LIST COPY;

LIST INCARNATION OF DATABASE;

DELETE EXPIRED BACKUP

DELETE EXPIRED ARCHIVELOG ALL

 

 

 

 

 

--------- Database must be in mount mode to change the archivelog mode--------

 

Disable flashback mode first if it enabled..

 

 

SQL> alter database flashback off;

alter database noarchivelog;

DELETE FORCE NOPROMPT BACKUPSET TAG 'weekly_bkup';

 

backup current controlfile;

 

backup datafile 4 include current controlfile;

backup as copy current controlfile format c:\bkp_controlfile.ctl

alter system switch logfile;

alter system archive log current

 

 

 

RESTORE DATABASE UNTIL TIME "to_date('07/23/2010 00:00:00' , 'mm/dd/yy hh24:mi:ss')";

 

 

 

restore archivelog from logseq 665 until logseq 671;

 

 

 

 

EXPDP Script

#!/bin/bash
export ORACLE_HOME=/vol0/app/oracle/product/11.2.0/db_1
export ORACLE_SID=XLR
export EXPORT_FOLDER=/vol0/pump
DATE=$(date +"%Y%m%d")
$ORACLE_HOME/bin/expdp \"/ as sysdba\" full=y directory=PUMP \
dumpfile=$DATE-${ORACLE_SID}_full_export.dmp logfile=$DATE-${ORACLE_SID}_full_export.log \
flashback_time=SYSTIMESTAMP
tar cjf $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.tar.bz2 \
$EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.dmp \
$EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.log
rm $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.dmp $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.log
find $EXPORT_FOLDER/*${ORACLE_SID}_full_export.tar.bz2 -mtime +15 -delete

Re-Create Controlfile

 

 

The restored RAMN open the database with resetlogs encountered ORA-00392 error:

ORA-00392: log 7 of thread 1 is being cleared, operation not allowed

RMAN> alter database open resetlogs; 
RMAN-00571: ============================================== ============= 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: ============================================== ============= 
RMAN-03002: failure of alter db command at 05/25/2012 09:41:46 
ORA-00392: log 7 of thread 1 is being cleared, operation not allowed 
ORA-00312: online log 7 thread 1: '/ opt/ora11g/oradata/acscnprd/redo/redo07.rdo'

Get the script to create the control file 
SQL>

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.sql' resetlogs;

(2) modify / tmp / control.sql the location of the control file is created on the RESETLOGS option. 
CREATE CONTROLFILE REUSE DATABASE "ACSCNPRD" FORCE LOGGING ARCHIVELOG 
.... 
LOGFILE 
...... 
RESETLOGS 
DATAFILE

3 Close the database 
SQL> SHUTDOWN IMMEDIATE

Database state started to mount 
SQL> STARTUP FORCE NOMOUNT

Execute the generated control.sql 
SQL> @/tmp/control.sql 
ORA-01081: cannot start already-running ORACLE - shut it down first 
Control file created. 
PL / SQL procedure successfully completed. 
PL / SQL procedure successfully completed. 
PL / SQL procedure successfully completed. 
PL / SQL procedure successfully completed. 
ORA-00279: change 733410844 generated at 05/24/2012 05:15:53 ??needed for thread1 
ORA-00289: suggestion: 
/ Opt/ora11g/fast_recovery_area/ACSCNPRD/archivelog/2012_05_25/o1_mf_1_7293_% u_.arc 
ORA-00280: change 733410844 for thread 1 is in sequence # 7293

ORA-00308: cannot open archived log 
'/ Opt/ora11g/fast_recovery_area/ACSCNPRD/archivelog/2012_05_25/o1_mf_1_7293_% u_.arc' 
ORA-27037: unable to obtain file status 
Linux-x86_64 Error: 2: No such file or directory 
Additional information: 3 
ALTER DATABASE OPEN RESETLOGS 
* 
ERROR at line 1: 
ORA-01113: file 1 needs media recovery 
ORA-01110: data file 1: '/ opt/ora11g/oradata/acscnprd/system01.dbf'

ALTER TABLESPACE TEMP ADD TEMPFILE '/ opt/ora11g/oradata/acscnprd/temp01.dbf' REUSE 
* 
ERROR at line 1: 
ORA-01109: database not open

Database altered.

6 Recovery 
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; 
ORA-00279: change 733410844 generated at 05/24/2012 05:15:53 ??needed for thread1 
ORA-00289: suggestion: 
/ Opt/ora11g/fast_recovery_area/ACSCNPRD/archivelog/2012_05_25/o1_mf_1_7293_% u_.arc 
ORA-00280: change 733410844 for thread 1 is in sequence # 7293

Specify log: {<RET> = suggested | filename | AUTO | CANCEL} 
CANCEL 
Media recovery canceled.

7 RESETLOGS open the database. 
SQL> ALTER DATABASE OPEN RESETLOGS; 
Database altered.

View the database state 
SQL> select open_mode from v $ database;

OPEN_MODE 
-------------------- 
READ WRITE