Restore archivelogs to disk from ASM

During recovering a database, usually Oracle tells which archivelog is needed by specifying the sequence number, such as:
ORA-00279: change 39138275307 generated at 01/02/2009 00:12:07 needed for thread 1
ORA-00280: change 39138275307 for thread 1 is in sequence #78240

Here are the steps to get the necessary logs from the tape or from the ASM:

1. Make sure the status of archivelog is "AVAILABLE" on the tape, and the tape is also available on the tape drive.

RMAN> list backup of archivelog sequence 78240 thread 1;

2. Restore the archivelogs from the tape to the disk. But it may say the logs are already on the disk.

RMAN> run {
2> set archivelog destination to '/images';
3> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
5> restore archivelog from logseq=78240 until logseq=78241;
6> RELEASE CHANNEL ch00;
7> RELEASE CHANNEL ch01;
8> }

executing command: SET ARCHIVELOG DESTINATION

allocated channel: ch00
channel ch00: sid=1017 instance=pdw1 devtype=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

allocated channel: ch01
channel ch01: sid=1003 instance=pdw1 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

Starting restore at 12-JAN-09:06:50:05

archive log thread 1 sequence 78240 is already on disk as file +PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78240.11985.675049215
archive log thread 1 sequence 78241 is already on disk as file +PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78241.14012.675049273
restore not done; all files readonly, offline, or already restored
Finished restore at 12-JAN-09:06:50:06

released channel: ch00
released channel: ch01

3. Use RMAN "COPY" command to copy the logs from ASM to disk. Make sure the disk folder is writable.

RMAN> connect target
connected to target database: PDB (DBID=1190101294)

RMAN> copy archivelog '+PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78240.11985.675049215' to '/images/archive_78240.arc';

Starting backup at 12-JAN-09:06:57:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=973 instance=pdb1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=78240 recid=3055 stamp=675049214
output filename=/images/archive_78240.arc recid=3088 stamp=675932248
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-JAN-09:06:57:29

Starting Control File and SPFILE Autobackup at 12-JAN-09:06:57:29
piece handle=+PFRA/pdb/autobackup/2009_01_12/s_675932297.12505.675932301 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JAN-09:06:58:25

RMAN> copy archivelog '+PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78241.14012.675049273' to '/images/archive_78241.arc';

Starting backup at 12-JAN-09:06:59:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=78241 recid=3056 stamp=675049272
output filename=/images/archive_78241.arc recid=3089 stamp=675932369
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-JAN-09:06:59:30

Starting Control File and SPFILE Autobackup at 12-JAN-09:06:59:30
piece handle=+PFRA/pdb/autobackup/2009_01_12/s_675932372.12504.675932373 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JAN-09:06:59:34

RMAN> exit

That also shows a good way to copy files out from ASM.

Monitor RMAN jobs

After you start a RMAN restore or backup session, RMAN needs to reach the tape first and you may have to wait for a while before you see the progress. In Veritas Netbackup client, there is a folder (on database server) holding Veritas logs. You can check them for any access errors or tape availability. The location could be

/usr/openv/netbackup/logs/user_ops/dbext/logs/

To find the Netbackup client location, try "ps -ef | grep netbackup" or "ps -ef |grep bpcd" when the RMAN job is running. Note the "dpcd" process may not be running all the time, and just be in and out when RMAN runs.

Sometimes, no news is good news. Once the session passes the tape level, Note 1444640.1 gives ways to monitor RMAN jobs from the database.

Manage DBSNMP account

To change the password for DBSNMP accout, you have to stop the Agent first. Here is a way you do not need to edit the targets.xml file under $AGENT_HOME/sysman/emd (as described in note 748668.1) on the EM client and Grid Control automatically reloads the new information to the agent on the client:
1. Stop EM agent on the server $AGENT_HOME/bin/emctl stop agent.
2. Use Sql*plus to change dbsnmp paaword.
3. Start the agent on the DB server.
4. In Grid Contrl, go the database and then click on "Monitoring configuration"
5. You may see "ORA-01017: invalid username/password; logon denied".
6a. You can enter the new password, and proceed to next. It seems to me that Grid Control saves the new encrypted password to the targets.xml file on the client. If this does not work, you can go to 6b.
6b. Click on "Change dbsnmp Password" to change its password again. After that, you should see "The dbsnmp passsword has been successfully changed for both the datbase and the agent. This new password has also been set below as the Monitor Password. The dbsnmp user is unlocked in the database. You may now proceed with any additional configuration. Otherwise, to exit the wizard, click Cancel."
7. Clieck on "Next" and then "OK" to re-connect.

Sometimes, you need to re-create DBSNMP account. You have to stop the agent first to terminate all DBSNMP connections to the database. Otherwise, you will get "ORA-01940: cannot drop a user that is currently connected" error when try to drop it. The steps are
1. Stop the EM agent.
2. SQL> @?/rdbms/admin/catnsnmp.sql -- drop user
3. SQL> @?/rdbms/admin/catsnmp.sql -- create user
It also creates 46 objects in DBSNMP schema, but keeps the account in LOCKED status.
4. Start the EM agent.

Use srvctl to remove service or disable service

Before using srvctl to reomve service or database from the Oracle clusterware (OCR), you need to use 'crs_stat -t' to check if they are OFFLINE or not in Target and State columns. If they are not, you should stop them first to make them OFFLINE.

. Stop and remove the service using the below srvctl command (Guide B14197-08):
$ srvctl stop service -d db_name -s service_name_list
$ srvctl remove service -d db_name -s service_name

. Stop and remove the database using the below srvctl command:
$ srvctl stop instance -d db_name -i inst_name_list
$ srvctl stop database -d db_name
$ srvctl remove instance -d db_name -i inst_name
$ srvctl remove database -d db_name

. To disable the database from automatic startup by clusterware, use the below srvctl command when it is OFFLINE status:
$ srvctl disable database –d db_name

Move Tempfile and Online logs to a new location

One partition on the disk was almost full and I had to move database files around. This is what I did on Tempfile and Redo logs without re-creating control file. It also shows a way to re-size the tempfile (sometimes, you have to do that because it becomes too big).

SQL> conn / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 274995432 bytes
Database Buffers 260046848 bytes
Redo Buffers 1048576 bytes
Database mounted.

-- Drop the tempfile when the database is mounted
SQL> alter database tempfile '/var/oracle/oradata/emrep/temp01.dbf' drop including datafiles;

Database altered.

-- Rename the online redo logs. Make sure you copied all them to the new loaction before issuing below lines to avoid ORA-27037 error.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo01.log'
2 to '/var/oracle/oradata/emrep/redo01.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo02.log'
2 to '/var/oracle/oradata/emrep/redo02.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo03.log'
2 to '/var/oracle/oradata/emrep/redo03.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo04.log'
2 to '/var/oracle/oradata/emrep/redo04.log';
Database altered.

-- Open the database
SQL> alter database open;
Database altered.

-- Run "SQL> select * from v$logfile;" to verify all redo logs are in "ONLINE" status.

-- Verify the tempfile was dropped
SQL> select * from dba_temp_files;
no rows selected

-- Add tempfile to the new location. Make sure there is no same file in the location to avoid over-writing an existin one!
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/emrep/temp01.dbf'
2 SIZE 8012M REUSE AUTOEXTEND ON NEXT 102400000 MAXSIZE 12000M;
Tablespace altered.

-- Verify the result
SQL> select tablespace_name, file_name, bytes/(1024*1024) from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024)
--------------- --------- -----------------
TEMP /app/oracle/oradata/emrep/temp01.dbf 8012

-- Backup the new controlfile
SQL> alter database backup controlfile to trace;
Database altered.

Metalink note 99275.1 gives more details on "alter database rename file '/old path/filename.dbf' to '/new path/filename.dbf';"