Renaming a redo log member from .log to .rdo

Using dbca in 11gR2 will default the redo log names with the .log extension. This may cause confusion with other log files in the future, so to be safe they should be use the .rdo file extension. In my example I am have 3 redo groups with 2 members each. Some of the members ending up with the default extension of .log. I want to update those members to have the .rdo file extension.

1. Query the current redo locations.
2. Shutdown the database.
3. Rename the redo log members on the server.
4. Startup the database in mount.
5. Rename the redo log members in the database.
6. Open the database.
7. Verify the redo log member names are correct.

SQL> select member from v$logfile;

MEMBER-------------------------------------------------------------------------------
/u02/oradata/prod/redo01a.log
/u03/oradata/prod/redo01b.rdo
/u02/oradata/prod/redo02a.log
/u03/oradata/prod/redo02b.log
/u02/oradata/prod/redo03a.log
/u03/oradata/prod/redo03b.rdo

6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !mv /u02/oradata/prod/redo01a.log /u02/oradata/prod/redo01a.rdo
SQL> !mv /u02/oradata/prod/redo02a.log /u02/oradata/prod/redo02a.rdo
SQL> !mv /u03/oradata/prod/redo02b.log /u03/oradata/prod/redo02b.rdo
SQL> !mv /u02/oradata/prod/redo03a.log /u02/oradata/prod/redo03a.rdo

SQL> startup mount
ORACLE instance started.
Database mounted.

SQL> alter database rename file '/u02/oradata/prod/redo01a.log' to '/u02/oradata/prod/redo01a.rdo';
Database altered.

SQL> alter database rename file '/u02/oradata/prod/redo02a.log' to '/u02/oradata/prod/redo02a.rdo';
Database altered.

SQL> alter database rename file '/u03/oradata/prod/redo02b.log' to '/u03/oradata/prod/redo02b.rdo';
Database altered.

SQL> alter database rename file '/u02/oradata/prod/redo03a.log' to '/u02/oradata/prod/redo03a.rdo';
Database altered.

SQL> alter database open;
Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u02/oradata/prod/redo01a.rdo
/u03/oradata/prod/redo01b.rdo
/u02/oradata/prod/redo02a.rdo
/u03/oradata/prod/redo02b.rdo
/u02/oradata/prod/redo03a.rdo
/u03/oradata/prod/redo03b.rdo

6 rows selected.

Data Pump. Moving tables to new schema, new tablespace.

I want to move a series of tables from one schema to another. At the same time I want to move them to a new tablespace. Data Pump is great for doing this.

The following data pump parameters allow for this. You can remap more then one tablespace at a time.

First export the tables.

Export parfile example (expdp_joe_tables.par):
USERID='/ as sysdba'
TABLES=(JOE.TABLE1, JOE.TABLE2, JOE.TABLE3)
DUMPFILE=joe_tables
LOGFILE=expdp_joe_tables

-- Run the expdp command
#expdp parfile=expdp_joe_tables.par

Import parfile example (impdp_joe_tables.par):
USERID='/ as sysdba'
TABLES=(JOE.TABLE1, JOE.TABLE2, JOE.TABLE3)
REMAP_SCHEMA=JOE:SAM
REMAP_TABLESPACE=USERJOE1:USERSAM1,USERJOE2:USERSAM2
DUMPFILE=joe_tables
LOGFILE=impdp_joe_tables

Import the tables to a new schema and tablespace.

-- Run the impdp command
#impdp parfile=impdp_joe_tables.par

Using DMBS_JOB in a trigger

How to call DBMS_JOB from within a trigger.

I ran into a case where I needed to perform multiple DML’s from within a trigger. This proved to be unacceptable since to do this made the original insert/update unreasonably slow. The solution was to write a procedure to do the related insert/updates and call the procedure from within the trigger using dbms_job.

My extreme case was where I needed to do 131 insert/updates to an aggregate table for every insert/update to the data table. Using a cursor and looping through to do the inserts was taking over a minute and a half. Using dbms_job to perform the same feat took about a second and a half. The insert/updates on the aggregate table is much faster (does not take the original minute and a half). Calling DBMS_JOB.SUBMIT kicks off multiple simultaneous sessions (inserts/updates).

Example of a Trigger using dbms_job to call a procedure.

CREATE OR REPLACE TRIGGER HR.AR$EMPLOYEES
AFTER INSERT OR UPDATE OF EMP_NAME
ON HR.EMPLOYEES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_id number;
l_job number;

BEGIN
v_id := :NEW.EMP_ID;
dbms_job.submit(l_job,'HR.POPULATION('||v_id||');');
END;
/

Example of a Trigger using a cursor to query and results and call dbms_job based on the results.

CREATE OR REPLACE TRIGGER HR.AUR$EMPLOYEES
AFTER UPDATE OF EMP_NAME
ON HR.EMPLOYEES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_id number;
l_job number;
BEGIN
FOR content_rec IN (
select emp_id
from HR.JOBS
where EMP_NAME = :NEW.EMP_NAME)
LOOP
v_id := content_rec.emp_id;
dbms_job.submit(l_job,'HR.POPULATION('||v_id||');');
END LOOP;

END;

/

Oracle archive log directory fills up.

ORA-00257: archiver error. Connect internal only, until freed.
There are a number of reason you might fill up your archive log directory. If this happens your database will hang until space is freed up. You quickly log into the server and delete a number of archive logs and wa-la, problem resolved. Ah ah... not so fast. If you use RMAN to backup your database you will receive errors when your backup runs because the catalog will be out of sync with your current archive logs on disk.

These are the steps I take:

1. Delete the archive logs from one or more directories.
2. Run the crosscheck RMAN command.
3. Backup your database.
   

This the RMAN command to run after manually deleting your archive logs.

RMAN>
RMAN> rman target / catalog rman/rman@rman
RMAN>change archivelog all crosscheck;
.
.
.
Crosschecked 598 objects
RMAN>exit

Run hot backup immediately after this.

Oracle RMAN Backup to disk.

Oracle 10R2 on Solaris.

I am using this method to back up a development database and delete the archivelogs on disk. Normally all backups are to tape, but on an occasion I need to backup up a development database, and backup to disk is the most effective solution for a quick backup.

# rman target / catalog login/password@rman;
.
.
connected to recovery catalog database

I always crosscheck the archive logs to be sure they are in sync with the catalog.

RMAN> change archivelog all crosscheck;
starting full resync of recovery catalog
full resync complete
.
.
RMAN>

To speed things up I like to use 4 channels for the backup. I set the configuration for the Disk Channels at the command line to not override the current RMAN settings.

RMAN> run {
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK
3> FORMAT '/oracle_backups/DEV/rman/backups/DEV_%T_%U';
4> ALLOCATE CHANNEL disk2 DEVICE TYPE DISK
5> FORMAT '/oracle_backups/DEV/rman/backups/DEV_%T_%U';
6> ALLOCATE CHANNEL disk3 DEVICE TYPE DISK
7> FORMAT '/oracle_backups/DEV/rman/backups/DEV_%T_%U';
8> ALLOCATE CHANNEL disk4 DEVICE TYPE DISK
9> FORMAT '/oracle_backups/DEV/rman/backups/DEV_%T_%U';
10> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
11> }

After the backup has competed I want to clean up prior backups still on disk and in the catalog.
Maintainance commands for crosschecks and deleting expired backups

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE NOPROMPT EXPIRED BACKUP;
RMAN> DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

The last 2 commands should return no output if you have deleted the archivelogs during the backup.

Veritas NetBackup - querying your media

To see what tapes you currently have in your storage unit, which pool they are assigned to, which ones are full, and how much is used of the others.

From the Master Server, change directories to goodies and run the available media command:

# cd /usr/openv/netbackup/bin/goodies
#./available_media

media media robot robot robot side/ ret size status ID type type # slot face level KBytes
-----------------------------------------------------------------------------------------------

CatalogBackup pool

xxx151 HCART TLD 0 47 - 3 69689341 ACTIVE
xxx152 HCART TLD 0 46 - 3 177652257 ACTIVE

NetBackup pool

xxx030 HCART TLD 0 28 - 6 477197502 ACTIVE/MPX
xxx139 HCART TLD 0 126 - 4 286852847 ACTIVE/MPX
xxx161 HCART TLD 0 59 - 4 58710487 ACTIVE/MPX
xxx195 HCART NONE - - - - - AVAILABLE
xxx007 HCART TLD 0 29 - 4 1439675239 FULL/MPX
xxx072 HCART TLD 0 128 - 4 1188893711 FULL/MPX
xxx134 HCART TLD 0 40 - 4 1287883499 FULL/MPX
xxx136 HCART TLD 0 100 - 4 1119822897 FULL/MPX
xxx137 HCART TLD 0 139 - 4 1365587550 FULL/MPX
xxx138 HCART TLD 0 140 - 4 1244368634 FULL/MPX
xxx144 HCART TLD 0 57 - 4 1101532193 FULL/MPX

Scratch pool

xxx162 HCART TLD 0 60 - - - AVAILABLE
xxx163 HCART TLD 0 109 - - - AVAILABLE
xxx164 HCART TLD 0 102 - - - AVAILABLE
xxx165 HCART TLD 0 111 - - - AVAILABLE
xxx166 HCART TLD 0 4 - - - AVAILABLE

Veritas NetBackup - Insert media failed: EMM database error (196)

Update failed: could not add new media ID 'XYZ166' into slot 4
Insert media failed:
EMM database error (196)


I am on NetBackup 6.5.4
Attempting to add new media to my tape silo I encounter the above error. Fortunately the problem was easily resolved, but not before spending most of the day researching and on the phone with technical support.
What causes this error? If you have tapes that have been Inventoried by a previous version of NetBackup and have since upgraded or re-installed the master server application. This was a new installation of NetBackup 6.5.4 and I am using unused tapes previously inventoried under the former install of 6.5.2 which had been upgraded from version 5.2. The error only occurs because I am adding new tapes to the inventory and there are existing tapes (used or not) already in the silo that were inventoried once upon a time by a previous version. Why? I believe this is unique to the circumstances, and obviously a bug, but fortunately there is a solution.
On the Master Server type the following (exactly as shown and all on the same line):

# cd /opt/openv/netbackup/bin
# ./nbpushdata -add_flag NBPUSHDATA_G_GLOBDB_HOST dummyhostname:dummyhostname

This command only needs to be executed once. You do not need to restart the Netbackup services. Inventory the robot again and you should have success.