FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Command References / Oracle Command References

CREATE SPFILE command Reference

stacyliang83 — Sun, 18 Jan 2009 20:07:46 GMT

[b]CREATE SPFILE[/b]

Export a text initialization parameter file (INIT.ORA) into a binary server parameter file.

Syntax:

CREATE SPFILE [='spfile_name'] FROM PFILE [='Spfile_name'];

A text parameter file can be easily edited in a text editor.

You can execute this statement either before or after instance startup. If you have already started the instance using spfile_name, you cannot specify the same spfile_name in this statement. Use the CREATE SPFILE statement to create a server parameter file from a client-side initialization parameter file. Server parameter files are binary files that exist only on the server and are called from client locations to start up the database.

Server parameter files let you make persistent changes to individual parameters. When you use a server parameter file, you can specify in an ALTER SYSTEM SET parameter statement that the new parameter value should be persistent. This means that the new value applies not only in the current instance, but also to any instances that are started up subsequently. Traditional client-side parameter files do not let you make persistent changes to parameter values.

Server parameter files are located on the server, so they allow for automatic database tuning by Oracle Database and for backup by Recovery Manager (RMAN).

To use a server parameter file when starting up the database, you must create it from a traditional text initialization parameter file using the CREATE SPFILE statement.

Oracle will look for default files, but typically you will need to specify the full pathname.

[b]Example:[/b]

CREATE SPFILE
FROM PFILE = '$ORACLE_HOME/dbs/initMYSID.ora';

CREATE SPFILE='$ORACLE_HOME/dbs/spfileMYSID.spf'
FROM PFILE = '$ORACLE_HOME/dbs/initMYSID.ora';

rman crosscheck - RMAN CROSSCHECK - command reference

Admin — Sat, 17 Jan 2009 12:23:30 GMT

RMAN Crosscheck

Crosschecks update outdated RMAN repository information about backups whose repository records do not match their physical status. For example, if a user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on disk, when in fact they are not.

If the backup is on disk, then the CROSSCHECK command determines whether the header of the file is valid. If the backup is on tape, then the command simply checks that the backup exists. The possible status values for backups are AVAILABLE, UNAVAILABLE, and EXPIRED.

You can view the status of backups using the RMAN LIST command, or by queryingV$BACKUP_FILES or many of the recovery catalog views such as RC_DATAFILE_COPY or RC_ARCHIVED_LOG. Running a crosscheck updaets the RMAN repository so that all of these methods provide accurate information. For each backup in the RMAN repository, if the backup is no longer available, then RMAN marks it as EXPIRED. If it was EXPIRED and is now available, then RMAN marks it AVAILABLE.

Note:
The CROSSCHECK command does not delete operating system files, and it does not remove RMAN repository records of backups that are not available at the time of the crosscheck. It only updates the repository records with the status of the backups. Use the DELETE command to remove records of expired backups from the RMAN repository.

Basic Use of CROSSCHECK with Backup Sets and Image Copies

After connecting to the target database and recovery catalog (if you use one), run CROSSCHECK commands as needed to verify the status and availability of backups known to RMAN.

If you have channnels configured for your tape or other media manager, you can crosscheck all backups on all media with CROSSCHECK BACKUP as shown in this example:

CROSSCHECK BACKUP;

If you do not have configured channels for your tape backups, you can allocate a maintenance channel in a RUN block before running CROSSCHECK as in this example:

RUN {
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
CROSSCHECK BACKUP;
}

This example shows how to crosscheck disk image copies only:

CROSSCHECK COPY; # crosschecks only disk copies of
# database files

This example shows how to crosscheck backup sets only:

CROSSCHECK BACKUPSET; # crosschecks backupsets on disk and SBT

Crosschecking Specific Backup Sets and Copies

You can use the LIST command to report your backups and then use the CROSSCHECK command to check that specific backups described in the LIST output still exist. The DELETE EXPIRED command deletes repository records for backups that fail the crosscheck.

To crosscheck specified backups:

1.

Identify the desired backups that you want to check by issuing a LIST command. For example, issue:

LIST BACKUP; # lists all backup sets, proxy copies, and image copies

2.

Check whether the specified backups still exist. For example, enter:

CROSSCHECK BACKUP; # checks backup sets, proxy copies, and image copies
CROSSCHECK COPY OF DATABASE;
CROSSCHECK BACKUPSET 1338, 1339, 1340;
CROSSCHECK BACKUPPIECE TAG = 'nightly_backup';
CROSSCHECK CONTROLFILECOPY '/tmp/control01.ctl';
CROSSCHECK DATAFILECOPY 113, 114, 115;
CROSSCHECK PROXY 789;

Crosschecking Backups of Specific Database Files

You can use options of the CROSSCHECK command to check only backups of a specific database, tablespace, datafile, control file, or archived redo log. For example:

CROSSCHECK BACKUP OF DATAFILE "?/oradata/trgt/system01.dbf"
COMPLETED AFTER 'SYSDATE-14';
CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;

You can check for backups of archived redo logs and SPFILE using this command:

CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;

Limiting RMAN CROSSCHECK to a Backups Since a Specific Time

You can add a COMPLETED AFTER clause to a CROSSCHECK command to restrict the checking to backups created after a specified point in time. For example, this command checks for backups of a datafile created in the last week:

CROSSCHECK BACKUP OF DATAFILE 4
COMPLETED AFTER 'SYSDATE-14';

Admin

CREATE DATABASE command reference

Admin — Fri, 16 Jan 2009 00:49:50 GMT

CREATE DATABASE

Create a database.

Syntax:

CREATE DATABASE database_name options

Options:

DATAFILE filespec AUTOEXTEND OFF
DATAFILE filespec AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]
MAXDATAFILES int
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tablespace [TEMPFILE filespec] [EXTENT MANAGEMENT LOCAL] [UNIFORM [SIZE int K | M]]
UNDO TABLESPACE tablespace [DATAFILE filespec]
LOGFILE [GROUP int] filespec
MAXLOGFILES int
MAXLOGMEMBERS int
MAXLOGHISTORY int
MAXINSTANCES int
ARCHIVELOG | NOARCHIVELOG
CONTROLFILE REUSE
CHARACTER SET charset
NATIONAL CHARACTER SET charset
SET TIMEZONE = 'time_zone_region'
SET TIMEZONE = '{+|-} hh:mm'
FORCE LOGGING
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password;

[img]http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/img/create_database.gif[/img]

[b][i]Examples:
[/i][/b]
Creating a Database: Example The following statement creates a database and fully specifies each argument:

CREATE DATABASE sample
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K,
GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE
'disk1:df1.dbf' AUTOEXTEND ON,
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp_ts
UNDO TABLESPACE undo_ts
SET TIME_ZONE = '+02:00';

[b][i]Example[/i][/b]

-- Create a database with the SID of MyDemo and char set WE8ISO8859P1
connect SYS/change_on_install as SYSDBA
set echo on
spool CreateDB.log
startup nomount pfile="E:\oradata\MyDemo\scripts\init.ora";

CREATE DATABASE MyDemo
MAXDATAFILES 100
DATAFILE 'E:\OraData\MyDemo\SYS1.dbf' SIZE 250 M
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 'E:\OraData\MyDemo\TEMP.dbf' SIZE 50 M
UNDO TABLESPACE "UndoTbs1" DATAFILE 'E:\OraData\MyDemo\UNDO.dbf' SIZE 200 M
CHARACTER SET WE8ISO8859P1
LOGFILE 'E:\OraData\MyDemo\rbs01.dbf' SIZE 2M,
'E:\OraData\MyDemo\rbs02.dbf' SIZE 2M,
'E:\OraData\MyDemo\rbs03.dbf' SIZE 2M,
'E:\OraData\MyDemo\rbs04.dbf' SIZE 2M,
'E:\OraData\MyDemo\rbs05.dbf' SIZE 2M;