Oracle 11g asmcmd – new commands

New asmcmd Commands in Oracle 11g

Oracle introduced three new features into the ASMCMD utility to improve node recovery after a crash, to help repair bad blocks on a disk, copy files and to simplify the listing of the ASM disks in a diskgroup.

ASMCMD cp Command

The ASMCMD cp option lets you copy files between ASM disk groups and the OS file systems and between two ASM servers.

The following file copy is now available:

1. ASM Diskgroup to OS filesystem
2. OS filesystem to ASM Diskgroup
3. ASM Diskgroup to another diskgroup

ASM Diskgroup to OS Filesystem

ASMCMD> cp +psdisk1/ORCLSID/datafile/users.212.43245666 /opt/app/oracle/oradata/ORCLSID/users01.dbf

OS Filesystem to ASM Diskgroup

ASMCMD> cp /opt/app/oracle/oradata/ORCLSID/users01.dbf +psdisk1/ORCLSID/datafile/users01.dbf

ASM Diskgroup to Another Diskgroup

ASMCMD> create diskgroup psdisk2 external redundancy disk ‘/dev/oracleasm/ct3dl4′,’/dev/oracleasm/ct3dl5′;

ASMCMD> cd psdisk2
ASMCMD> mkdir prod
ASMCMD> cd prod
AMSMCD> mkdir datafile
AMSMCD> cd datafile
ASMCMD> pwd

SQL> create tablespace psdefault size 10M;

SQL> select name from v$datafile;

cp +psdisk1/ORCLSID/datafile/psdefault.212.34343434 +psdisk2/PROD/datafile/psdefault

ASMCMD> cp +psdisk/ORCLSID/datafile/psdefault.212.34343434 +psdisk2/prod/datafile/psdefault

The alias for psdefault is created in the folder +PSDISK2/PROD/datafile and the actual file is created in the +PSDISK2/PROD/datafile with a fully qualified ASM filename.

ASMCMD> cd +psdisk1/ORCLSID/DATAFILE
ASMCMD> ls -ltr
ASMCMD> cd +psdisk2/PROD/DATAFILE
ASMCMD> ls -ltr

ASMCMD md Backup and md Restore Commands

ASMCMD has been extended to include the ASM disk group metadata backup and restore functionality. This now provides us with the ability to recreate a pre-existing ASM disk group with the same disk paths, disk names, failure groups, attributes, templates, and alias directory structures.

In 10g if an ASM disk group was lost, the only possible way to restore the lost files was to use RMAN but you would have to manually recreate the ASM disk groups and all of the required user directories and templates.

In 11g you can take a backup of the ASM diskgroup metadata using the md_backup command which creates a backupfile containing the metadata for one or more of your diskgroups. The default is all mounted disk groups are included in the backup file which is saved in your current working directory. If the name of the backup file is not supplied then ASM names the file ASMBR_BACKUP_INTERMEDIATE_FILE.

ASMCMD> md_backup -b dgbackup -g psdisk1
Disk group to be backed up: PSDISK1

In restore mode, the previously generated file is read to reconstruct the diskgroup along with its metadata. You have the possibility to control the behaviour in restore mode by specifying a full, nodg, or newdg restore.

The full mode restores the diskgroup exactly as it was at the time of the last backup.

ASMCMD> md_restore -b dgbackup -t full -g psdisk1
Current Diskgroup being restored: PSDISK1
ASMCMD-09353: CREATE DISKGROUP failed
ORA-15018: diskgroup cannot be created
ORA-15030: diskgroup name “PSDISK1″ is in use by another diskgroup(DBD ERROR: OCIStmtExecute)

I didn’t wipe out or blow away my existing diskgroup but the above illustrates the ASM won’t restore and write over a valid diskgroup.

SQL> drop diskgroup psdisk1;
Diskgroup dropped.

ASMCMD> md_restore -b dgbackup -t full -g psdisk1
Current Diskgroup being restored: PSDISK1
….

SQL> select group_number, name, type from v$asm_diskgroup;

The nodg mode restores the attributes, templates and directory structure specified in the backup file to an existing disk group.

ASMCMD> md_restore -b dbbackup -t nodg -g psdisk1
Current Diskgroup being restored: PSDISK1
….
_

RMAN Info

RMAN General Info Oracle

RMAN’s %s format represents the backup set number and is essentially a counter in the controlfile that is incremented for each backupset. The counter starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then it is possible duplicate values could result. You can initialize the counter back to 1 by issuing a CREATE CONTROLFILE command.

The %s doesn’t represent the same number that is in the BS_KEY column in the RC_BACKUP_SET RMAN Catalog view.

There are two different numbers tracked for the backup sets, BS_KEY which is a unique value within the catalog and the RECID which is unique to the controlfile.

example:

connect / as sysdba
select max(recid) from v$backup_set;

execute an RMAN backup:

configure channel type disk format ‘….’;

select max(set_count) from v$backup_set;

in the catalog: connect rman/rman@rman.db.name.com

select max(bs_key) from rc_backup_set;

The RESTORE command supports a PREVIEW option, which identifies the backups (backup sets or image copies, on disk or sequential media like tapes) required to carry out a given restore operation.

You can use RESTORE.. PREVIEW to ensure that all of the required backups are available or to identify situations where you may want to instruct RMAN to use or avoid a specific backup.

RESTORE… PREVIEW can show you that RMAN will request a tape during the restore process which you know is not located onsite. You can then execute the command CHANGE… UNAVAILABLE to set the backup status to UNAVAILABLE. If you then execute the RESTORE… PREVIEW command again, RMAN will not include that media you just marked as UNAVAILABLE and will show only the media it will use to perform the restore operation.

RESTORE… PREVIEW can be applied to any RESTORE operation to create a detailed report of every backup that will be requested during the RESTORE operation:

RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE n PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ xxxx PREVIEW;
RESTORE ARCHIVELOG FROM TIME ‘SYSDATE-6′ PREVIEW;
RESTORE ARCHIVELOG FROM SCN xxxxxx PREVIEW;
RESTORE… PREVIEW output is in the same exact format as the output of the LIST command.

If the detailed report produced by RESTORE… PREVIEW provides more information than you need, you can add the SUMMARY option to the command which will suppress much of the detail about specific files that will be used and affected by the restore process.

RESTORE DATABASE PREVIEW SUMMARY;
RESTORE TABLESPACE users PREVIEW SUMMARY;
RESTORE DATAFILE x PREVIEW SUMMARY;
RESTORE ARCHIVELOG FROM LOGSEQ xxx PREVIEW SUMMARY;
RESTORE ARCHIVELOG FROM TIME ‘SYSDATE-6′ PREVIEW SUMMARY;
RESTORE ARCHIVELOG FROM SCN xxxxxx PREVIEW SUMMARY;
RESTORE… PREVIEW SUMMARY reports are in the same format as output from the LIST SUMMARY command.

To backup the controlfile using RMAN:
run {
allocate channel d1 type disk format ‘c:\backup\%U’; (windows format)
allocate channel d1 type disk format ‘/opt/app/oracle/admin/backup/snapcf_ev.f’; (unix format)_
backup current controlfile;
}

To check the backup of controlfile using RMAN:
list backup of controlfile;

To recover using backup controlfile: (startup nomount)
run {
allocate channel d1 type disk;
restore controlfile;
alter database mount;
restore database;
recover database;
sql ‘alter database open resetlogs’;
}

To backup all datafiles and controlfile using RMAN:
run {
allocate channel d1 type disk;
backup full tag=’full_bkup” database include current controlfile format=’/opt/app/oracle/admin/backup/df_t%t_s%s_p%p’;
release channel d1;
}

To check all backups of datafiles using RMAN:
list backupset;

To restore because of missing / corrupt file(s) (first mount the database and then execute RMAN):
run {
allocate channel d1 type disk;
restore database;
recover database;
}

Restore until time: The ‘SET UNTIL TIME’ must match with the variable NLS_DATE_FORMAT, prior to logging onto RMAN set the NLS_DATE_FORMAT with the desired format.

ex: Unix – export NLS_DATE_FORMAT=’YYYY-MM-DD:HH24:MI:SS’;
Windows – set NLS_DATE_FORMAT in the registry or Control Panel->System->Environment Variables:
run {
set until time ‘October 1 2008 12:00:00′;
allocate channel d1 type disk;
shutdown abort;
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
sql ‘alter database open resetlogs’;
}

Purge Obsolete Backups:
rman> report obsolete redundancy 3 device type disk;

Use the report id returned to fill in the xxx below;
rman> report obsolete orphan;

rman> allocate channel for maintenance type disk;
allocate channel for delete type disk;
change backuppiece ‘/opt/app/oracle/admin/backup/xxxx’ delete;
release channel;

To Backup All Archive Logs:
run {
allocate channel d1 type disk format ‘/opt/app/oracle/admin/backup/al_t%t_s%s_p%p’;
backup archivelog all delete input;_
}

To Remove All Archive Log Files After Backup Up
backup archivelog all delete input;

Skip an Archive Log File that can’t be read or manually deleted
backup archivelog skip inaccessible;

To Remove One Archive Log That you Manually Deleted and Now Get an RMAN-6089 (prior to 8.0):
allocate channel for delete type disk; or ‘SBT_TAPE’;
change archivelog ‘/opt/app/oracle/admin/backup/filename’ delete;
and/or
resync catalog;

To Remove One Archive Log That you Manually Deleted and Now Get an RMAN-6089 (prior to 8.1):
allocate channel for maintenance type …
change archivelog uncatalog

Oracle 11G RMAN Worksheet Sizing

Flash Recovery Area Sizing / RMAN Image Backups

===================================================================================

Type	Size Per File or Set	Total Per Cycle (Week)	Total	Description
Full Backup Set	0	0	0	Use Image Copies, so size is 0
Image Copied	3,000	2	6,000	Need 2 Level 0 Backups per Cycle, 1 week cycle
Archived Redo Logs	100	2	200	Two days worth of archived redo logs available
Incremental copy of datafiles	200	6	600
Control file autobackup	0.2	7	1.4
Total Space Required			6,801

Flash Recovery Area Sizing / RMAN Regular Backups

=================================================================================

Type	Size Per File or Set	Total Per Cycle (Week)	Total	Description
Full Backup Set	0	0	0	Use Image Copies, so size is 0
Image Copied	3,000	2	6,000	Need 2 Level 0 Backups per Cycle, 1 week cycle
Archived Redo Logs	100	2	200	Two days worth of archived redo logs available
Incremental copy of datafiles	200	6	600
Control file autobackup	0.2	7	1.4
Total Space Required			6,801

Flash Area Recovery Sizing for RMAN Image Backups w/Merge

===================================================================================

Type	Size Per File or Set	Total Per Cycle (Week)	Total	Description
Full Backup Set	0	0	0	Use Image Copies, so size is 0
Image Copied	3,000	2	6,000	Need 2 Level 0 Backups per Cycle, 1 week cycle
Archived Redo Logs	100	2	200	Two days worth of archived redo logs available
Incremental copy of datafiles	200	6	600
Control file autobackup	0.2	7	1.4
Total Space Required			6,801

Oracle 9i / 10g RMAN Duplicate Database Errors

Recovering from RMAN Errors

The options for Manual Completion of an RMAN Duplication Task vary depending on which phase of the duplication failed. The following examples cover Oracle 9i and 10g. Manual completion steps out outlined per phase and most likely you’ll only two to execute a couple of the steps depending on where the failure occurred.

Restore Failure

If you receive an error during the RMAN restore of the database you need to determine what caused the problem and fix it. If very few files have been restored it may be easier to just start over and rerun the task from the beginning. If the duplication process failed after running for a long period of time and you rather not to start from the beginning (especially if it takes hours+) then you can try to recover manually and attempt to complete the process.

A likely cause if you are going from one server to another server is missing files or the rare outside possibility of a bad block in a file required for restore. You need to address whatever problem caused the file(s) to be missing from the restore location.

Remember that in order to successfully duplicate a database using RMAN’s DUPLICATE feature is that ALL files required to restore the database must be present on the remote server and in the same exact location unless you catalog the files if they are intentionally located in another directory. RMAN will not even start the restore process if it can’t find the backupsets in the expected location. A reason RMAN may be interrupted in this phase is because during the copy operating you ran out of disk space in the filesystem where you were depositing the backupsets, backup current controlfile and backup spfile and RMAN can’t locate one of the backupsets or arcivelog files.

$ export ORACLE_SID= (name of database cloning to)
$ rman target /
RMAN> run {
set until scn xxxxxxxx;
restore current controlfile from ‘restore directory’;
alter database mount;
set newname for datafile 1 to ”;
set newname for datafile 2 to ”;
….
restore datafile i,i2,….;
}

You need to identify the SCN from the output from the failed RMAN duplicate log and you must use the ‘SET NEWNAME’ for each datafile that remains to be restored as DB_FILE_NAME_CONVERT will not work with a normal restore.

For Oracle 10g+ it really is best to start over if RMAN failed during the first phase or restore step. Any files that have already been restored will be skipped and the duplicate process can be restarted without manual intervention.

Phase 2 Failure

This is the controlfile creation or switch of the datafile names after the datafiles have been restored. You need to review the log files and identify what the problem is and make sure you make a list to use for all the datafiles that have not been switched over. You can then attempt to complete this step manually by rename each datafile if the auxiliary instance uses a different file structure or the ASM Disk Group is different than that of the target’s directory structure or ASM Disk Group.

After the rename (switch) of all the datafiles that need to be renamed:

CREATE CONTROLFILE REUSE SET DATABASE RESETLOGS ARCHIVELOG
…

SQL> alter database backup controlfile to trace;

Phase 3 Failure

Failure during the recovery of the restored datafiles. This is the next phase where each datafile is recovered to either a point in time or SCN. Determine the cause from the log file and then to continue after fixing the problem:

$ rman target / auxiliary sys/@
RMAN> run {
set until scn xxxxxxx;
recover clone database;
alter clone database open resetlogs;
}

Get the ‘UNTIL SCN’ value from the duplicate logfile, connect to the target. Archivelogs will be automatically restored at 10g, restored into the Flash Recovery Area if this is defined. After completing recovery, change the Database Identifier (DBID) using the NID utility on Windows:

$ nid target=sys/oracle

DBNEWID …….
…..
Change database ID of database AUX? (Y/N)=>Y

The manual duplication process should be complete and you can jump down to Step 6 – Final Actions.

Phase 4 Failure

This phase is the controlfile recreation phase. Check the rman duplicate log and identify the reason the recovery didn’t complete – look for:

media recovery complete
Finished recover at

Figure out what the problem is and fix the cause then execute the following after fixing the cause:

CREATE CONTROLFILE REUSE SET DATABASE ‘AUX’ RESETLOGS ARCHIVELOG
…

Make sure ALL the files have been restored in the DATAFILE section of the RMAN duplicate log.

Phase 5 Failure

This failure would be in the phase that opens the database with resetlogs. Check the log file again and fix the problem. Look for Thread x closed at log sequence y

If the resetlogs was completed, determine what cause the error and fix the problem and restart the auxiliary instance. If resetlogs wasn’t completed successfully, determine what cause that problem and then open the clone database with resetlogs using RMAN (you can’t use SQL*Plus for this step) and connect to the target database first:

$ rman target / auxiliary sys/oracle@
RMAN> alter clone database open resetlogs;

If the duplication process failed only in steps 5 then you are done, no further action is required. The DBID will have already been changed. Otherwise, execute the NID command to change the DBID (Windows).

Final Steps

$ rman target / auxiliary sys/oracle@
RMAN> alter clone database open resetlogs;

Add any temp files missing to the new cloned auxiliary database. Files that were manuall restored to the auxiliary instance will be cataloged as datafile copies. Connect to the original target and execute:

RMAN> list copy of database;
RMAN> crosscheck copy of datafile ;
RMAN> delete expired copy of datafile ;

Duplicating a Database using Oracle 11g RMAN

RMAN Duplicate Database Feature in 11G

You can create a duplicate database using the RMAN duplicate command. The duplicate database will have a different DBID from the source database and it functions entirely independently because it is completely independent once the duplication has taken place and the duped database is open for use.

Starting with Oracle 11g there are now two ways to duplicate a database:

1. Active database duplication
2. Backup-based duplication

Active database duplication involves copying the live running database over the network to the auxiliary destination and creating the duplicate database. The backup-based duplication requires copying over using NFS to make available the backupset(s) to the destination database. The only difference between the two is you do not need pre-existing RMAN backups and copies (archivelogs). The duplication work is performed by the auxiliary channel and this channel corresponds to a server session on the auxiliary instance on the auxiliary (destination) host.

The active database duplication is slower because it is using the network to transport the data blocks instead of accessing existing RMAN backupsets. RMAN carries out the following steps as part of the duplication process:

1. Creates a control file for the duplicate database
2. Restarts the auxiliary instance and mounts the duplicate control file
3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs
4. Opens the duplicate database with the RESETLOGS option

In the case of active database duplication, RMAN copies the target datafiles over the network to the auxiliary (destination) instance.

A RAC Target database can be duplicated as well, this feature is not restricted to non-RAC databases. The procedure is the same as what is outlined below. If the auxiliary database needs to be a RAC-database then you start the process to duplicate a single instance and convert the auxiliary to RAC after the duplicate process has succeeded.

The next section is devoted to describing the process for Active Database Duplication.

Active Database Duplication

1. Prepare the auxiliary database instance
2. Create the initialization parameter file for the Auxiliary instance

If you are using SPFILE then according to Oracle the only parameter required for the duplicate database is the DB_NAME parameter. The rest of the parameters can be set in the DUPLICATE command. If you are not using the SPFILE technique for your Oracle initialization parameter file then you will need to create an init.ora file and set the initialization parameters. Again according to Oracle the only required parameters are:

DB_NAME
CONTROL_FILES
DB_BLOCK_SIZE
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
DB_RECOVERY_FILE_DEST

However, I have found if I use a full meaning a copy of one of my running Database’s parameter file it is easier than specifying the parameters in the DUPLICATE command itself. That is a personal preference and you should go with whatever works best for you. I use SPFILE files for all my Oracle Databases and whenever I have a need to duplicate one of them I will generate (create) an init.ora initialization parameter file for use with RMAN. I will then use my favorite editor to search and replace the database specific parameters with my new Oracle Database parameters like DB_NAME, DB_RECOVERY_FILE_DEST, etc.

3. Create the Oracle Password file for the Auxiliary Instance – this is a requirement for RMAN in order to duplicate a database you need to connect directly to the auxiliary instance using the password file with the same SYSDBA password as the target database’s password. The passwords have to match exactly in order for this to work. You can specify the PASSWORD FILE option on the DUPLICATE command in which case if you do RMAN will copy the source database password file to the destination host and overwrite any existing password file with the same name as the auxiliary instance’s name.

4. Make the necessary changes to the listener.ora and tnsnames.ora file in order to establish SQL*Net connectivity before starting the RMAN duplicate session. You have to be able to connect through Oracle Net to the target and the auxiliary instance in order to use Active Database duplication.
5. Start the Auxiliary instance from SQL*Plus – start the database and put it in nomount mode but first take care of the following steps:

I always create a link in $ORACLE_HOME/dbs to the initialization file and I just rename this link when I’m finished with RMAN to switch over to using a spfile instead of the pfile method. This way you do not have to type in a lot of characters whenever you stop, start the auxiliary instance because more than likely you will have to execute the process more than once unless you get it right the very first time! I set up the ADR – in Oracle 11G the new Automatic Diagnostic Recovery filesystem (if you aren’t using ADR set up the $ORACLE_BASE/admin/$ORACLE_SID/bdump/…._) directories prior to bringing up the auxiliary instance in nomount mode. You will need to create the supporting directories prior to starting the auxiliary instance.

Windows:

Create the parameter file (using Oracle’s example):

initNEW.ora
DB_NAME=NEW
diagnostic_dest=’E:\opt\oracle’
DB_FILE_name_CONVERT=(’I:\oradata\OLD’,'E:\oradata\NEW’)
LOG_FILE_NAME_CONVERT=(’I:\oradata\onlinelog\OLD’,'E:\oradata\onlinelog\NEW’)
SGA_TARGET=26214400
CONTROL_FILES=’E:\oradata\controlfile\NEW\control01.dbf’
COMPATIBLE=11.1.0.0.0

Create the Database Service (Windows Only) and password file:

% set ORACLE_SID=NEW
% set ORACLE_HOME=E:\opt\app\oracle\product\11.1.0\db_1
% oradim -NEW -SID NEW
% orapwd FILE=E:\opt\app\oracle\product\11.1.0\db_1\database\PWPDNEW.ora PASSWORD=sys

% sqlplus “/ as sysdba”
sql> startup nomount;

6. Create the necessary Oracle Net connectivity in the listener.ora and tnsnames.ora files – you need to make sure you specify SERVER = DEDICATED in the tnsnames.ora entry for your auxiliary instance and if you are using Oracle on HP-UX 11i v3 Itanium there is a bug that requires you to use the string ‘(UR=A)’ at the end of the tnsnames.ora entry (put it after the SERVICE_NAME entry). Confirm the connectivity to the target, auxiliary and you may want to confirm connectivity to your RMAN catalog even though you will not be using the catalog for this type of database duplication.

7. Start RMAN and connect to the source database by specifying the source as the target database. The duplicate database instance will be specified in the AUXILIARY connection. You can invoke the RMAN client on any host so long as that host has connectivity and you can connect to all of the required database instances. If the auxiliary instance requires a text-based initialization parameter file (pfile) then this file must exist and it must reside on the same host that runs the RMAN client application.

% rman
rman> connect target sys/sys@old;
rman> connect auxiliary sys/sys

8. Next you will be issuing the DUPLICATE database command in order to start the duplicate process and the simplest case is when you duplicate the target database to a different host and use a different directory structure. This example will assume you are using a recovery catalog, the target database is on hosta and it contains four datafiles. You duplicate the target to database AUX on a different host (hostb) and hostb has a different directory structure. The tablespace USERS is a read-only tablespace for the purpose of this example. Execute the duplciate database command from the Auxiliary site:

rman duplicate target database to ‘NEW’ from active database db_file_name_convert ‘i:\oradata\OLD’,'e:\oradata\NEW’;

A dedicated listener configuration for RMAN is required. Using instance registration requires that the database be mounted in order to register with the listener. RMAN also requires SYSDBA access to the nomount instance (Auxiliary). The control files will be create using the location and names specified in the Oracle initialization file. The use of log_file_name_convert and db_file_name_convert instructs RMAN to generate the “set newname” commands for you. You can create your own set instead of using these two commands and you can use the ‘logfile’ command to specify where the log files will be created and what size they will be created with.

RMAN 11g Snapshot Standby Database

Snapshot Standby Database

Oracle 11g introduces a new type of database – the Snapshot Standby Database which allows the use of a physical standby databasee in read-write mode for a short period of time. A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

This database is open in the read-write mode and as thus it is possible to process transactions apart from the primary database. It maintains protection by continual feed from the production database by archiving that data for later use.

Using a single command change made while the database is in read-write mode can throw away the changes made to the standby database only and re-synchronize the standby database with the production database.

Snapshot Database has these Characteristics

1. Snapshot standby database receives and archive but does not apply the redo data.
2. Redo data received from the primary database is applied automatically once the standby database is converted back into a physical standby database.
3. Data from the primary database is always protected as the archivelogs are being received and stored in their designated location.
4. All local updates will be discarded when the snapshot database is converted back to physical standby database.
5. If the primary database moves to a new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from the new database branch.
6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.
8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Once the snapshot standby is activated this database diverges from its primary database over time because redo data from the primary database is not applied. Local updates to the snapshot standby database will cause additional divergence.

Steps to Convert Physical Standby Database to the Snapshot Standby Database

The conversion from physical standby to snapshot standby database can be done through the command:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

1. If not already configured, configure flash recovery area as show below:

first set the size of the recovery area

alter system set db_recovery_file_dest_size=;

Next, set the location

alter system set db_recovery_file_dest=;

Bring the physical standby database to mount stage:

Stop managed recovery if it is active and convert the physical standby database to snapshot standby database

alter database convert to snapshot standby;

The database is dismounted during conversion and must be restarted after the conversion completes.

SQL> select open_mode, database_role from v$database;

The database is now ready for transactions.
An implicit restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby database to its original state when it is converted back into a physical standby database from a snapshot standby database.

Steps to Convert the Snapshot Standby Database Back to the Physical Standby Database:

1. shutdown the snapshot standby database
2. bring the database to the mount state
3. issue the following command

alter database convert to physical standby;

4. shutdown the database and mount it again

SQL> select open_mode, database_role from v$database;

You are now ready to start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted – start the process of applying the redo logs and all of the redo received by the snapshot standby database will be applied to the physical standby database to roll it forward.

Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using the Flashback Database technology will prevent a snapshot database being converted back to a physical standby.

RMAN Cataloging Backups / Archivelogs in RMAN 10g / 11g

Oracle Server Version 9.2.0.8.0 to 11.1

How to catalog backups / archivelogs / datafile copies / control file copies, etc. in various versions of Oracle.

The RMAN Catalog is used to accomplish the following:

- Add backup pieces and image copies on disk to the RMAN repository
- Record a datafile copy as a level 0 incremental backup in the RMAN repository, which enables you to use it as part of an incremental backup strategy.
- Record the existence of the last user-managed datafile copies made after the final shutdown

Restrictions and Usage Notes:

1 You must be connected to the target database which must be mounted or open.
2. If RMAN is connected to a recovery catalog, then the catalog database must be opened
3. For a user-managed copy to be cataloged, it must be a datafile copy, control file copy, archived log or backup piece.
4. Accessible on disk
5. You cannot use CATALOG to catalog a file that belongs to a different database.

RMAN treats all user-managed backups as image copies. Note that during cataloging, RMAN does not check whether the file was correctly copied by the operating system utility; it just reads and checks the header.

Examples:
1. Cataloging an Archived Redo Log: This example assumes that you made an operating system copies of the archived logs or transferred them from another location, and then added them to the RMAN repository:

CATALOG ARCHIVELOG ‘?/oradata/archive1_30.dbf’,'?/oradata/archive1_31.dbf’,'?/oradata/archive1_32.dbf’;

2. Cataloging a File Copy as an Incremental Backup: The following example catalogs datafile copy users01.bak as an incremental level 0 backup:

CATALOG DATAFILECOPY ‘?/oradata/users01.bak’ LEVEL 0;

Note: That you can create datafile copies either using the RMAN BACKUP AS COPY command, or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.

3. The following example catalogs a user managed controlfile copy in the RMAN Repository:

The command to take a controfile copy is below:

SQL> alter database backup controlfile to ‘disk1/controlfile01.dbf’;

The command to catalog a controlfile copy is blow:

RMAN> catalog controlfilecopy ‘rdisk1/control01.ctl’;

4. Cataloging Multiple Copies in a Directory:
The following example catalogs a directory full of archived logs that were copied into the /tmp/arch_logs directory with an operating system utility:

CATALOG START WITH ‘/tmp/arch_logs’;

5. Cataloging Files in the Flash Directory Area:
The following example catalogs a directory enabled flash recovery area without prompting the user for each one:

CATALOG RECOVERY AREA NOPROMPT;

6. Cataloging Backup Pieces:
The following example catalogs a RMAN backup piece that was copied with an operating system utility:

CATALOG BACKUPPIECE ‘?/oradaata/01dmsbj4_1_1.log’;

7. The Command Below will catalog the Backup Pieces Help in the Location set for DB_RECOVERY_FILE_DEST Parameter:

RMAN> catalog db_recovery_file_dest;

Note: All of the above examples are valid for 10g and 11g but the examples 4,5,6 and 7 are not possible for 9i as these features are not available in 9i.

catalog backuppiece, catalog recovery area, catalog start with, catalog db_recovery_file_dest commands are not available in 9i.

HP-UX Kernel Parameters for PeopleSoft

Kernel Parameters Summary

This post is simply to provide some guidelines for choosing values for HP’s HP-UX 11i v1, v2 and v3 Operating System for PeopleSoft Installations. It is not a definitive guide, set in stone, etc. It is simply a list of kernel parameters that are relevant to PeopleSoft Applications. I derived the list from HP’s documentation and based on several production/development HP PA-RISC and Itanium installations.

The values are based on PeopleSoft deployments structured around PeopleSoft’s PIA (Pure Internet Architecture), that divides the PeopleSoft architecture into the following components:

Web Server, Application Server, Process Scheduler (Batch) and Database tiers.

Unless noted the values are applicable to all tiers are intended to provide a starting point for tuning kernel parameters and you have to keep in mind all installations have their own unique characteristics that will drive the ultimate values.

HP-UX 11i (v1, v2, v3) provides some dynamic kernel parameter support and error checking by SAM (System Administration Manager for HP-UX) may prevent changing some of these parameters until After a reboot has been performed in order to avoid invalidating and/or causing problems with non-dynamic (static) kernel parameters in use for the current boot. A second boot will not be required just a second invocation of SAM in the event that you need to change some values, reboot and then invoke SAM to make the final change(s).

HP-UX 11i v1 is 11.11, v2 is 11.23 and v3 is 11.31. An “nc” in the below table indicates that no change is required or necessary.

Parameter	Dynamic	Default Value	Recommended Value
maxuprc		11i v1 : 75	(NPROC/8->514
maxusers (11iv1 only)		32	128
nproc		11iv1 : (20+8*MAXUSERS)	(20+32*MAXUSERS)->4116
nproc (nc for v2/v3)		v2/v3 : 4200
maxfiles_lim	yes	11iv1 : 1024	2048
maxfiles_lim (nc for v2/v3)		v2/v3 : 4096
maxfiles		11iv1 : 60	2048
maxfiles (nc for v2/v3)		v2/v3 : 2048
max_thread_proc		11iv1 : 64	1024
		v2/v3 : 256
maxdsize		11iv1 : 0×10000000->256MB	0xC0000000->3GB
		v2/v3 : 0×40000000->4GB	0xC0000000->3GB
maxdsiz_64bit		11iv1 : 0×40000000->1GB	0×000003F000000000
		v2/v3 : 0×10000000->4GB	->4TB
maxssiz		0×00800000->8MB	0×04000000->64MB
maxsize_64bit		11iv1 : 0×00800000->8MB	0×08000000->128MB
maxsize_64bit (nc for v2/v3)		v2/v3 : 0×10000000->256MB
vx_ninode		0	(8*NPROC)
dbc_min_pct	yes	50	10 (or 5)
dbc_max_pct		5	2
swapmem_on		1 (but may be 0 for some)	1 (set to 0 if configured device swap>=server memory)
swchunk		2048	16384
vps_ceiling		16	256

NOTE: dbc_[min|max]_pct have been replaced with filecache_[min|max]
for HP-UX 11.31. Buffer cache settings are dynamic kernel parameters for HP-UX v2 and later.

Use of Formulas in HP-UX 11i V1

In the older HP-UX 11i v1 version of the Operating System, formulas were used for many kernel parameters and as a result changes to MAXUSERS and NPROC values would result in indirect changes to the following parameters:

Parameter	Formula	Original Value	New Value
ncallout	(16+NKTHREAD)	292	4132
nclist		(100+16*MAXUSERS)	2148
nkthread	((NPROC*7)/4)+16)	499	7219
nfile	~(16*(NPROC+MAXUSERS)/10)	910	7208
ninode	~(NPROC+MAXUSERS)	476	4412

Kernel Parameters Fixed Values and Obsoleted Parameters

HP-UX 11i v2 and v3 use fixed values for these parameters. Additionally, the NCALLOUT and MAXUSERS kernel parameters have been deprecated in those operating system versions. It is not necessary to modify the 11i v2 or v3 kernel to use the formulas because the fixed values are sufficient for most deployments and unless there is a demonstrated need it is best to not implement formulas for v2 and v3.

Summary of Changes

The changes to the max parameters show above are necessary to allow sufficient kernel resources for a typical production deployment. Servers that support multiple environments (development and test environments) may need higher values to accommodate the number of PeopleSoft environments. Error messages in the PeopleSoft log files should help provide insight into which configured parameters need to be adjusted. For example, error messages noting “process table full” or “fork of child process failed” are usually indicators that NPROC and/or MAXUPRC need to be increased.

The dbc_min_pct and dbc_max_pct values (filecahe_min and filecache_max for HP-UX 11i v3) should be set so as to restrict the kernel’s buffer cache to several hundred megabytes at most (10% of a 4 GB sever, 5% of an 8 GB server, and 1% of a 32 GB server). Database servers may require large file system buffer caches if the database instance is maintained on standard file systems and those file systems are configured to use buffered I/O in the first place (not a good practice and not recommended by Oracle or HP).

Setting a non-zero value for vx_ninode also has a positive impact on overall file system performance. An explicit value ensures that the kernel’s Veritas file system code does not inadvertently consume more resources than necessary for file-system intensive operations and applications. Changes in HP-UX 11i v2 require that the formula for vx_ninode be tied to a parameter with a fixed value (e.g. NPROC), not one that itself is another formula (e.g. NFILE).

Increasing swchunk faciliates adding swap space should it become necessary. Configuring additional storage as device swap does not require a reboot, modifying swchunk, however, does.

A higher setting for vps_ceiling allows the HP-UX kernel to minimize in-memory fragmentation of applications with large data footprints. This is particulary useful for PeopleSoft server processes and Oracle RDBMS processes, both of which allocate, contiguous memory at runtime.

PeopleSoft Enterprise / BEA Tuxedo Kernel Parameters

PeopleSoft Enterprise uses BEA Tuxedo’s Infrastructure, which requires additional kernel configuration changes. These settings are required on the application and batch server tiers. Below is a guideline for assigning values for the kernel parameters related to BEA Tuxedo and are based on HP’s recommendation documentation:

Parameter	Dynamic	Default Value	Recommended Value
msgssz		11iv1 : 8	512
		v2/v3 : 96
msgmni		11iv1 : 50	256
		v2/v3 : 512	256
msgseg		11iv1 : 2048	32767
		v2/v3 : 8192
msgtql		11iv1 : 40	2046
		v2/v3 : 1024
msgmnb	yes	16384	1048576
msgmax	yes	8192	256000
msgmap		11iv1 : (2+msgtql)	(2+msgtql)
		v2/v3 : 1026
semmns		11iv1 : 128	512
(nc for v2/v3)		v2/v3 : 4096
semmni		11iv1 : 64	(SEMMNS/2)
(nc for v2/v3)		v2/v3 : 2048
semmnu		11iv1 : 30	(SEMMNS/4)
(nc for v2/v3)		v2/v3 : 256
shmmax	yes	11iv1 : 0×04000000	0×40000000 (1 GB)
(nc for v2/v3)		v2/v3 : 0×40000000

Note: Systems that have multiple deployments (more than one PeopleSoft Environment) should use BEA’s utility tmloadcf to verify that there are sufficient allocation of IPC resources. In particular, semmnu will need to be increased for systems that will run multiple PeopleSoft instances simultaneously (e.g., we have 7 or more environments all running at the same time). I will post a follow-up note with information and samples of output for the BEA tmlaodcf utility to illustrate the kernel parameters we have deployed for this server.

Installing Perl’s CPANPLUS Module for OS X 10.5+

Install CPANPLUS for Apple’s OS X 10.5+ Operating System

The source for CPANPLUS is located at:

http://search.cpan.org/search?query=cpanplus&mode=all

To begin you just need to download the API and CLI source which should be the first item in the list, the other entries are modules for CPANPLUS that you can elect to install after install CPANPLUS. I’m keeping this post very simple so I won’t delve into the inner workings of CPAN/CPANPLUS and the many features available to you for configuration, etc.

If you haven’t used Perl on your Apple yet, you have a fresh install of OS X, … you will need to install several prerequisite packages before you can install CPANPLUS.

Without going into details I’ll try to make an attempt to outline the steps to configure perl for the first time on your Apple machine with just enough details to get you started and to the point where you have CPANPLUS installed.

Beginning with Perl Version 5.004 and later CPAN comes with the Perl package and this is the module you use to install other mdoules. To Invoke CPAN and install the module IPC::Cmd type the following:

su – root – Log in as root or you can use sudo to execute the commands, I put my .cpan and .cpanplus directories in /Users/shared. If you intend on placing the new Perl Programs in the System Directory Structure you have to use sudo to make install, otherwise your account (even though it may be the administrator account) does not have access to overwrite files located in the System Directory Structure. You can compile and test Perl modules but you will not be able to install them.

su – root
cd /Users/Shared

perl -MCPAN -e shell

/System/Library/Perl/5.8.8/CPAN/Config.pm initialized.

CPAN is the world-wide archive of perl resources. It consists of about
100 sites that all replicate the same contents all around the globe.
Many countries have at least one CPAN site already. The resources
found on CPAN are easily accessible with the CPAN.pm module. If you
want to use CPAN.pm, you have to configure it properly.

If you do not want to enter a dialog now, you can answer ‘no’ to this
question and I’ll try to autoconfigure. (Note: you can revisit this
dialog anytime later by typing ‘o conf init’ at the cpan prompt.)

Are you ready for manual configuration? [yes]
The following questions are intended to help you with the
configuration. The CPAN module needs a directory of its own to cache
important index files and maybe keep a temporary mirror of CPAN files.
This may be a site-wide directory or a personal directory.

First of all, I’d like to create this directory. Where?

CPAN build and cache directory? [/var/root/.cpan]

If you want, I can keep the source files after a build in the cpan
home directory. If you choose so then future builds will take the
files from there. If you don’t want to keep them, answer 0 to the
next question.

How big should the disk cache be for keeping the build directories
with all the intermediate files?

Cache size for build directory (in MB)? [10] 30

By default, each time the CPAN module is started, cache scanning
is performed to keep the cache size in sync. To prevent from this,
disable the cache scanning with ‘never’.

Perform cache scanning (atstart or never)? [atstart]
To considerably speed up the initial CPAN shell startup, it is
possible to use Storable to create a cache of metadata. If Storable
is not available, the normal index mechanism will be used.

Cache metadata (yes/no)? [yes]
The next option deals with the charset your terminal supports. In
general CPAN is English speaking territory, thus the charset does not
matter much, but some of the aliens out there who upload their
software to CPAN bear names that are outside the ASCII range. If your
terminal supports UTF-8, you say no to the next question, if it
supports ISO-8859-1 (also known as LATIN1) then you say yes, and if it
supports neither nor, your answer does not matter, you will not be
able to read the names of some authors anyway. If you answer no, names
will be output in UTF-8.

Your terminal expects ISO-8859-1 (yes/no)? [yes]
If you have one of the readline packages (Term::ReadLine::Perl,
Term::ReadLine::Gnu, possibly others) installed, the interactive CPAN
shell will have history support. The next two questions deal with the
filename of the history file and with its size. If you do not want to
set this variable, please hit SPACE RETURN to the following question.

File to save your history? [/var/root/.cpan/histfile]
Number of lines to save? [100] 500
The CPAN module can detect when a module that which you are trying to
build depends on prerequisites. If this happens, it can build the
prerequisites for you automatically (’follow’), ask you for
confirmation (’ask’), or just ignore them (’ignore’). Please set your
policy to one of the three values.

Policy on building prerequisites (follow, ask or ignore)? [ask] follow
The CPAN module will need a few external programs to work properly.
Please correct me, if I guess the wrong path for a program. Don’t
panic if you do not have some of them, just press ENTER for those. To
disable the use of a download program, you can type a space followed
by ENTER.

Where is your gzip program? [/usr/bin/gzip]
Where is your tar program? [/usr/bin/tar]
Where is your unzip program? [/usr/bin/unzip]
Where is your make program? [/usr/bin/make]
Warning: lynx not found in PATH
Where is your lynx program? []
Warning: wget not found in PATH
Where is your wget program? []
Warning: ncftpget not found in PATH
Where is your ncftpget program? []
Warning: ncftp not found in PATH
Where is your ncftp program? []
Where is your ftp program? [/usr/bin/ftp]
Warning: gpg not found in PATH
Where is your gpg program? []
What is your favorite pager program? [col -b | bbedit --clean --view-top]
What is your favorite shell? [/bin/sh]

Every Makefile.PL is run by perl in a separate process. Likewise we
run ‘make’ and ‘make install’ in processes. If you have any
parameters (e.g. PREFIX, LIB, UNINST or the like) you want to pass
to the calls, please specify them here.

If you don’t understand this question, just press ENTER.

Parameters for the ‘perl Makefile.PL’ command?
Typical frequently used settings:

PREFIX=~/perl non-root users (please see manual for more hints)

Your choice: []
Parameters for the ‘make’ command?
Typical frequently used setting:

-j3 dual processor system

Your choice: []
Parameters for the ‘make install’ command?
Typical frequently used setting:

UNINST=1 to always uninstall potentially conflicting files

Your choice: []

Sometimes you may wish to leave the processes run by CPAN alone
without caring about them. As sometimes the Makefile.PL contains
question you’re expected to answer, you can set a timer that will
kill a ‘perl Makefile.PL’ process after the specified time in seconds.

If you set this value to 0, these processes will wait forever. This is
the default and recommended setting.

Timeout for inactivity during Makefile.PL? [0]
If you’re accessing the net via proxies, you can specify them in the
CPAN configuration or via environment variables. The variable in
the $CPAN::Config takes precedence.

Your ftp_proxy?
Your http_proxy?
Your no_proxy?
You have no /var/root/.cpan/sources/MIRRORED.BY
I’m trying to fetch one
CPAN: LWP::UserAgent loaded ok
Fetching with LWP:
ftp://ftp.perl.org/pub/CPAN/MIRRORED.BY

Now we need to know where your favorite CPAN sites are located. Push
a few sites onto the array (just in case the first on the array won’t
work). If you are mirroring CPAN to your local workstation, specify a
file: URL.

First, pick a nearby continent and country (you can pick several of
each, separated by spaces, or none if you just want to keep your
existing selections). Then, you will be presented with a list of URLs
of CPAN mirrors in the countries you selected, along with previously
selected URLs. Select some of those URLs, or just keep the old list.
Finally, you will be prompted for any extra URLs — file:, ftp:, or
http: — that host a CPAN mirror.

(1) Africa
(2) Asia
(3) Central America
(4) Europe
(5) North America
(6) Oceania
(7) South America
Select your continent (or several nearby continents) [] 5
Sorry! since you don’t have any existing picks, you must make a
geographic selection.

(1) Bahamas
(2) Canada
(3) Mexico
(4) United States
Select your country (or several nearby countries) [] 4
Sorry! since you don’t have any existing picks, you must make a
geographic selection.

(1)
(2) ftp://carroll.cac.psu.edu/pub/CPAN/
(3) ftp://cpan-du.viaverio.com/pub/CPAN/
(4) ftp://cpan-sj.viaverio.com/pub/CPAN/
(5) ftp://cpan.calvin.edu/pub/CPAN
(6) ftp://cpan.cs.utah.edu/pub/CPAN/
(7) ftp://cpan.erlbaum.net/CPAN/
(8) ftp://cpan.hexten.net/
(9) ftp://cpan.hostrack.net/pub/CPAN
(10) ftp://cpan.llarian.net/pub/CPAN/
(11) ftp://cpan.mirrors.redwire.net/pub/CPAN/
(12) ftp://cpan.mirrors.tds.net/pub/CPAN
(13) ftp://cpan.netnitco.net/pub/mirrors/CPAN/
(14) ftp://cpan.pair.com/pub/CPAN/
(15) ftp://csociety-ftp.ecn.purdue.edu/pub/CPAN
(16) ftp://ftp-mirror.internap.com/pub/CPAN/
38 more items, hit SPACE RETURN to show them
Select as many URLs as you like (by number),
put them on one line, separated by blanks, e.g. ‘1 4 5′ [] 16 6 13 8 2

Enter another URL or RETURN to quit: []
New set of picks:
ftp://ftp-mirror.internap.com/pub/CPAN/
ftp://cpan.cs.utah.edu/pub/CPAN/
ftp://cpan.netnitco.net/pub/mirrors/CPAN/
ftp://cpan.hexten.net/
ftp://carroll.cac.psu.edu/pub/CPAN/

commit: wrote /System/Library/Perl/5.8.8/CPAN/Config.pm

cpan shell — CPAN exploration and modules installation (v1.7602)
ReadLine support enabled

cpan> exit

My list of missing modules when I attempted to install CPANPLUS were;

Checking if your kit is complete…
Looks good
Warning: prerequisite Archive::Extract 0.16 not found.
Warning: prerequisite File::Fetch 0.13_04 not found.
Warning: prerequisite IPC::Cmd 0.36 not found.
Warning: prerequisite Log::Message 0.01 not found.
Warning: prerequisite Module::CoreList 2.09 not found.
Warning: prerequisite Module::Load 0.10 not found.
Warning: prerequisite Module::Load::Conditional 0.18 not found.
Warning: prerequisite Module::Loaded 0.01 not found.
Warning: prerequisite Object::Accessor 0.32 not found.
Warning: prerequisite Package::Constants 0.01 not found.
Warning: prerequisite Params::Check 0.22 not found.
Warning: prerequisite Term::UI 0.18 not found.
Warning: prerequisite Test::Harness 2.62 not found. We have 2.56.
Warning: prerequisite version 0.73 not found. We have 0.700.
Writing Makefile for CPANPLUS

perl -MCPAN -e shell (from /Users/Shared)

install Archive::Extract
…
install version
exit

cd $HOME/Downloads; cd cpanplus-0.84 (location of the CPANPLUS source from earlier download):

perl Makefile.PL

imac:cpanplus-0.84 repettas$ perl Makefile.PL

### IMPORTANT! ######################################################

As of CPANPLUS 0.070, configuration during ‘perl Makefile.PL’
is no longer required. A default config is now shipped that
should work out of the box on most machines, for priviliged
and unprivileged users.

If you wish to configure CPANPLUS to your environment, you can
either do that from the interactive shell after installation:

$ cpanp
CPAN Terminal> s reconfigure

Or you can invoke this program as follows, to do it now:

$ perl Makefile.PL –setup

This also means that any config created by any CPANPLUS older
than 0.070 will no longer work, and you are required to
reconfigure. See the ChangeLog file for details.

We appologize for the inconvenience.

### PLEASE NOTE ###################################################

Since CPANPLUS has a few prerequisites that are not included
in versions of perl prior to 5.9.5, they are bundled with the
distribution for boot-strapping purposes.

You should install these prerequisites before continuing to
install CPANPLUS. You can do this from the build directory
with the following commands:

$ perl bin/cpanp-boxed -s selfupdate dependencies
$ perl bin/cpanp-boxed -s selfupdate enabled_features

Or you may install them using your system’s package manager.

###################################################################

Writing Makefile for CPANPLUS

make
make test

If everything builds and tests without errors you are ready to install CPANPLUS by issuing the following line;

make install

To update CPANPLUS do the following;

su – root
cd /Users/Shared

cpanp
s selfupdate all

You can get help by entering in help from the command prompt inside of cpanp. There are several options for the selfupdate which you can see by typing in: s selfupdate (return). You can execute the selfupdate and view what it will do without actually executing the command by using the flag –dryrun at the end of the command line.