Monday, April 4, 2011

Get ASM Files Using LFTP

If a database has XML DB set and ftp port configured then lftp could be used to get files out of the ASM.
FTP port could be configured with
SQL> @$ORACLE_HOME/rdbms/admin/catxdbdbca.sql 7001 8001
7001 is the ftp port 
8001 is the http port
Listener status would display these ports
lsnrctl status
...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=7001))(Presentation=FTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=8001))(Presentation=HTTP)(Session=RAW))
...
To get a file for example an archive log file use
lftp rac4 -usystem,passwd -p7001 -e 'get /sys/asm/FLASH/RAC11G2/ARCHIVELOG/2011_04_02/thread_2_seq_472.390.747360023;exit;'
41125376 bytes transferred in 4 seconds (11.02M/s)
Here rac4 is the host on which the ASM instance is running, command could be executed from a remote node as well. Following -u is the username used to login into the system, in this case database's system user and comma separated by the password for system user. FTP port is specified with -p.

Without the exit at the end, after the file is transferred shell prompt will end up in the ftp prompt.

To remove the http and ftp ports get rid of the dispatcher parameter (as per 274508.1 Listener Issue: Removing XDB Handlers for HTTP and FTP Ports) or
conn / as sysdba
exec dbms_xdb.sethttpport(0);
exec dbms_xdb.setftpport(0);
alter system register;
More on Master Note for Oracle XML DB Protocols: FTP HTTP HTTPS WebDAV, APEX and Native Database Web Services [ID 1083991.1]

Apart from lftp ftp client such filezilla could be used to connect to the ftp port specified and get the files.

Friday, April 1, 2011

Enable Flashback On Standby

It is possible to enable flashback on a standby independent of the primary. (It's good to have flashback enabled both on primary and standby, if flashback IO is no concern).
Data guard environment created earlier is used here. Steps are similar to How to Enable Flashback for a RAC Database on ASM ( 819905.1)

To enable flashback on standby bring the stop log apply on standby (transport could also be stopped for the duration).
DGMGRL>  edit database rac11g2 set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.
Trying to enable flashback while log apply is on will result in
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
If the standby is open in read only mode then shutdown and bring it up only one instance in mount mode as this is required to enable flashback.
srvctl stop database -d rac11g2s
srvctl start instance -d rac11g2s -i rac11g2s1 -o mount
Once in mount mode enable flashback on the standby
SQL> alter database flashback on;

Database altered.
Enable log apply and transport on
DGMGRL> edit database rac11g2 set state='TRANSPORT-ON';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-ON';
Succeeded.
Open the standby mode in read only if required. Check the flashback on status of the database
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------
YES
Having flashback on standby is important especially to recover from open resetlogs situations in primary. If the standby has applied changes past the new resetlog scn and there's no flashback enabled on standby, only way to recover is to recreate the standby. If flashback was enabled on standby then it could be used to flashback to a scn prior to resetlogs and continue to use the standby from then onwards.