archivelog

See whether the archive and modify oracle database archiving mode

kods.net — Sat, 12 Jun 2010 16:11:18+0000

See whether the archive and modify oracle database archiving mode

Oracle is divided into non-archive mode (NOARCHIVELOG) and archive mode (ARCHIVELOG). Non-archive mode archive log not generated, although the save hard disk space, but Select the backup options are limited, usually only select the cold backup. Restore can only restore to the backup data that moment, usually only used in the development of (allegedly also used in the data warehouse), Oracle installing the default is non-archive mode. We are in a production environment because of the use of archive mode, it will generate archive logs, you can use a variety of backup and restore program with Oracle administrators should change the mode is an inevitable choice.

First View Database of existing models can use the following statement

select name, log_mode from v $ database;

Can also use the following statement
archive log list; (the method requires as sysdba)

For non-archive mode to archive mode of the database (Oracle 10g as a reference mainly) using the following steps:

1. SQL> alter system set log_archive_dest_1 = 'location = / oracle/oracle10g/log/archive_log';

The statement means that the archive logs to determine the path, in fact, Oracle 10g can generate as many copies of the log, save multiple locations, in case of emergency such as a log location and then add the following statement can be used

SQL> alter system set log_archive_dest_2 = 'location = / oracle/oracle10g/log2/archive_log';

2. Close the database
SQL> shutdown immediate

3. Start data mount status:
SQL> startup mount;

4, modify the database to archive mode:
SQL> alter database archivelog;

5, open the database, the query:
SQL> alter database open;

Modify the log file name format:
SQL> alter system set log_archive_max_processes = 5;
SQL> alter system set log_archive_format = "archive_% t_% s_% r.log" scope = spfile;

After editing you can view the log mode is changed successfully!
Particularly noted that in Oracle 9i, you should also modify the parameters alter system set log_archive_start = true take effect, oracle 10g this parameter has been abolished, so do not need to set the parameters.

Automatic archiving is disabled by default, need to modify the parameter log_archive_start = TRUE to enable automatic archiving.
SQL> show parameter log_archive_start

NAME TYPE value

------------------------------------ ----------- --- ---------------------------

log_archive_start boolean FALSE

SQL> archive log list

Database log mode Archive Mode

Disable Auto Archive

Archive destination / oracle/oracle9i/u01/product/9201/dbs/arch

Summary of the earliest log sequence 18

The next archive log sequence 19

Current log sequence 20

SQL> alter system set log_archive_start = TRUE scope = spfile;

System has been changed.

SQL> startup force

ORACLE instance started.

Total System Global Area 320308312 bytes

Fixed Size 730200 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 811008 bytes

Completion of the loading database.

Database has been opened.

SQL> archive log list

Database log mode Archive Mode

Enable automatic archiving

Archive destination / oracle/oracle9i/u01/product/9201/dbs/arch

Summary of the earliest log sequence 19

The next archive log sequence 21

Current log sequence 21

SQL> show parameter log_archive_start

NAME TYPE value

------------------------------------ ----------- --- ---------------------------

log_archive_start boolean TRUE

SQL>

Mandatory automatic archive mode archive

SQL> alter system switch logfile;

SQL> select * from v $ log;

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle archive log deletion

kods.net — Mon, 31 May 2010 06:47:12+0000

We all know the record in the controlfile archivelog with relevant information for each course are in the OS, delete these files off, after the physical, in our

controlfile archivelog still record such information, in the oracle's OEM manager has a visual log show that when we hand-Qing Chu archive directory file, these records have not been removed we are from the controlfile in, but also is the oracle does not know these files does not exist! this time we have to do manual removal, then I passed the following test, you can try this method:

1. Into rman

2. Connect target /

3. Crosscheck archivelog all;

4. Delete expired archivelog all;

Seen in this time we will definitely go OEM can not see, if you have never done this action, we can compare this action after the action before the controlfile after controlfile size!

ORACLE right to delete archive and recovery space approach

ORACLE right to delete archive and recovery space approach

ORACLE archiving log a regular full, showing / oraarchive this file space occupied by 100% you must complain about the reasons for not filing ORACLE maintenance tools, many people simply delete wrong, wrong, ORACLE there, and very smart, you can delete the file and correct FLASHBACK, but remember, ORACLE ORACLE archive logs for data recovery and backup is important, not a last resort and do not delete the archive log.

Delete archive log process

ORACLE user to log on to the database server host or through a network connection

Into the ORACLE data backup tools

rman target /

Or rman target / @ orcl

In the command window inside the implementation of the

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

Explain

SYSDATA-7, show that the current system for 7 days, before keyword indicated in the 7 days prior to the log file, if you use the flashback feature, will also delete flashback data.

Similarly, you can delete from 7 days ago to the present all the logs, but this command should think carefully done this remove, the best full-back up the database immediately

DELETE ARCHIVELOG FROM TIME 'SYSDATE-7';

UNIX / LINUX FIND can also be found under the 7 days before the filing of data, use the delete EXEC sub-operation

find / oraarchive-xdev-mtime +7-name "*. dbf"-exec rm-f ();

This still left a not in the RMAN archive management

Still needed in the RMAN command in the implementation of the following two

crosscheck archivelog all;

delete expired archivelog all;

Therefore, the above method is not as easy to use, but with the benefits of FIND is, can conditions, and EXEC children do a lot of action, the more complex functions

3. A brief overview report obsolete command

Report using the report obsolete command expired backup

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of obsolete backups and copies

Type Key Completion Time Filename / Handle

-------------------- ------ ------------------ ------ --------------

Backup Set 125 01-NOV-04

Backup Piece 125 01-NOV-04 / data1/oracle/orabak/full_1_541045804

Backup Set 131 04-NOV-04

Backup Piece 131 04-NOV-04 / data1/oracle/orabak/full_AVATAR2_20041104_131

....

Backup Set 173 06-DEC-04

Backup Piece 173 06-DEC-04 / data1/oracle/orabak/full_AVATAR2_20041206_173

Backup Set 179 11-DEC-04

Backup Piece 179 11-DEC-04 / data1/oracle/orabak/arch544588206.arc

.....

Backup Piece 189 17-DEC-04 / data1/oracle/orabak/arch545106606.arc

Backup Set 190 17-DEC-04

Backup Piece 190 17-DEC-04 / data1/oracle/orabak/arch545106665.arc

Backup Set 191 20-DEC-04

Backup Piece 191 20-DEC-04 / data1/oracle/orabak/arch_AVATAR2_20041220_194

Archive Log 2973 20-DEC-04 / opt/oracle/oradata/avatar2/archive/1_2985.dbf

Archive Log 2971 20-DEC-04 / opt/oracle/oradata/avatar2/archive/1_2984.dbf

.....

Archive Log 2705 17-DEC-04 / opt/oracle/oradata/avatar2/archive/1_2717.dbf

Archive Log 2704 17-DEC-04 / opt/oracle/oradata/avatar2/archive/1_2716.dbf

Archive Log 2703 17-DEC-04 / opt/oracle/oradata/avatar2/archive/1_2715.dbf

Archive Log 2702 17-DEC-04 / opt/oracle/oradata/avatar2/archive/1_2714.dbf

This all-inclusive network transferred from ☆ ★ ★ ☆ - http://www.baoluowanxiang.com reproduced, please indicate the source, tort reserved!
Original link: http://www.baoluowanxiang.com/a/website/database/2010/0522/877.html

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

oracle data dictionary to learn ------ Application (1)

kods.net — Wed, 19 May 2010 23:12:37+0000

1: First of all, switch to the system administrator sqlplus

SQL> connect system/manager as sysdba

 Connected  .

2: query the database name, creation date and other information

SQL> select name,created,log_mode,open_mode from v$database;



NAME   CREATED    LOG_MODE       OPEN_MODE

--------- ---------- ------------ ----------

ISO  08-4 Month   -10  NOARCHIVELOG    READ WRITE

NOARCHIVELOG that the database is running in non archive mode, the status is read-write (normal state).

ARCHIVELOG and non-archive file on the difference between NOARCHIVELOG, and mode transitions, the unity of order in the next one log.

3: query host name, database instance name, database management system version

SQL> col host_name for a15;

SQL> select host_name,instance_name,version from v$instance;

HOST_NAME     INSTANCE_NAME     VERSION

--------------- ---------------- -----------------

XXXXXXXXXX      iso            9.2.0.1.0

The host running oracle database name: XXXXXXXXXX, database instance name for iso.

4: v $ version on view, query the database management system version of the detailed information.

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

Which, TNS stands for the Transparent Network Substrate

NLSRTL for the Oracle NLS Runtime Library of abbreviations

5: Get Control of File Name

SQL> col name for a45;

SQL> select * from v$controlfile;

STATUS NAME

------- ---------------------------------------------

        D:\ORACLE\ORADATA\ISO\CONTROL01.CTL

        D:\ORACLE\ORADATA\ISO\CONTROL02.CTL

        D:\ORACLE\ORADATA\ISO\CONTROL03.CTL

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Flashback log management reference documents

kods.net — Thu, 01 Apr 2010 11:27:51+0000

Solution
Flashback logs are handled internally and we have no control over them. This means
that we cannot delete flashback logs manually, the ONLY way to delete then
is disabling FLASHBACK.

The FlashBack Logs can ONLY be deleted by DISABLING FLASHBACK
on database level

SQL> startup mount
alter database flashback off;
alter database open;

During space pressure, the Flashback logs are deleted under two
conditions:

1) When an archivelog is deleted, it would also delete the flashback logs
that are dependent on the archived log.
2) When quota is shrunk, then flashback logs is deleted to reclaim disk
space. This is decreasing the DB_RECOVERY_FILE_DEST_SIZE to such a value
that the FLB itself will run into a space pressure and than the old
FLB \ 's will get deleted.

We can hit bellow bug and then the flashback logs are not deleted
even there is space pressure in the Flash backup disk space.

Oracle RMAN backup of the physical meaning of RMAN configuration 2009-04-09 16:20:02
Cloning Oracle Home (10g2) 2009-04-06 21:46:55
Block Oracle paragraph (reprint) 2009-03-26 17:41:14
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17
High Availability Oracle Flashback 2009-02-26 17:23:02

RMAN backup and recovery

kods.net — Thu, 25 Mar 2010 12:59:13+0000

Recovery catalog management

First, establish the steps to restore directory
1, the establishment of recovery catalog table space
SQL> CREATE TABLESPACE RMAN_TS
2> DATAFILE 'D: \ ORACLE \ TEST \ RMAN.DBF' SIZE 100M

2, create a restore directory owner
SQL> CREATE USER RMAN IDENTIFIED BY RMAN
2> DEFAULT TABLESPACE RMAN_TS

3, given the role of recovery catalog owner. Recovery catalog owner must have RECOVERY_CATALOG_OWNER role. In addition, when creating recovery catalog, the need to connect to the database, and in restoration of Mulu tablespace to create tables, so the owner should also be for the restoration of Mulu CONNECT and RESOURCE Jiaose Shouyu
SQL> GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO RMAN

4, create a restore directory.
C: \> RMAN CATALOG RMAN / RMAN @ CATADB.
RMAN> CREATE CATALOG;

5, register the target database. After the establishment of a recovery catalog, RMAN to the target database to recovery catalog metadata store, you must connect to the target database, and use the REGISTER DATABASE command register the target database,
Can use the REPORT SCHEMA command to check whether the registration was successful.

C: \> RMAN CATALOG RMAN / RMAN @ CATADB

RMAN> CONNECT TARGET SYS / ORACLE @ MYDB

RMAN> REGISTER DATABASE

RMAN> REPORT SCHEMA;

Second, use stored scripts
1, the establishment of storage script
RMAN> CREATE SCRIPT BACKUP_DATABASE (
2> BACKUP FORMAT = 'D: \ BACKUP \% d_% s.bak' database
3> include current controlfile
4> plus archivelog;
5> sql 'alter system archive log current';
6>)

2, run the stored script
(1) in the RMAN prompt, run the stored script
RMAN> RUN (EXECUTE SCRIPT BACKUP_DATABASE;)

(2) stored in the command line to run the script.
C: \> RMAN CATALOG RMAN / RMAN @ MYORCL TARGET SYS / WENZI @ MYDB SCRIPT BACKUP_DATABASE

3, showing script content
RMAN> PRINT SCRIPT BACKUP_DATABASE

4, replace the stored script
RMAN> REPLACE SCRIPT BACKUP_DATABASE (
2> BACKUP DATABASE;
3)

5, delete the stored script
RMAN> DELETE SCRIPT BACKUP_DATABASE;

7, list all stored scripts
RMAN> LIST SCRIPT NAMES;

Third, to maintain recovery catalog

1, re-synchronization recovery catalog resynchronization recovery catalog is intended to enable recovery catalog and target database control file sync, when the target changes the physical structure of the database (add and remove table space, more data files), you should re-sync recovery catalog .
RMAN> RESYNC CATALOG;

2, off the target database
RMAN> UNREGISTER DATABASE;

3, the upgrade to restore catalog if the RMAN recovery catalog client version than the version number, you should upgrade to restore directory. Note that when upgrading to restore directory, need to perform two UPGRADE CATALOG command.
RMAN> UPGRADE CATALOG;
...
...
RMAN> UPGRADE CATALOG;

4, delete the recovery catalog to delete recovery catalog, it will delete the directory of all information. Note that when removed to restore directory, the need to perform two DROP CATALOG command.

5, restore the database to an earlier copy of the database is not fully recovered when implemented, the need to open the database using the RESETLOGS option, when using this option to open the database, creates a copy of the database, if you want to copy the database to restore to an earlier state
You must use the RESET DATABASE TO INCARNATION command to set the early copy of a copy of the current database, and then perform the appropriate action. Steps are as follows:
(1) copy of a copy of the database to determine key
RMAN> LIST INCARNATION OF DATABASE;

(2) set the database to an earlier version
RMAN> STARTUP FORCE MOUNT
RMAN> RESET DATABASE TO INCARNATION 2;

(3) dump an earlier version of the control file. When the dump control file, the required target database must be in NOMOUNT state. Note that when an earlier version of the control of the dump file, to ensure control of copies exist.
RMAN> RUN (
2> STARTUP FORCE NOMOUNT;
3> SET UNTIL SCN = 656740;
4> RESTORE CONTROLFILE;
5>)

(4) to restore the database to an earlier copy.
RMAN> RUN (
2> ALTER DATABASE MOUNT;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
6>)

4, the query restore directory

1, RC_DATABASE
The data dictionary view used to display the directory has been registered in the recovery of all target database information.
SQL> SELECT DBID, NAME, RESETLOGS_CHANGE # FROM RC_DATABASE

2, RC_TABLESPACE
The data dictionary view used to display records in the recovery catalog in the target database table space information.
SQL> SELECT TS #, NAME, CREATION_CHANGE # FROM RC_TABLESPACE WHERE DB_NAME = 'MYDB'

3, RC_DATAFILE
The data dictionary view used to display records in the recovery catalog in the target database data file information.
SQL> SELECT FILE #, NAME, BYTES FROM RC_DATAFILE WHERE DB_NAME = 'MYDB'

4, RC_STORED_SCRIPT
The data dictionary view used to display the directory in the restoration of all the recorded information stored in the script.
SQL> SELECT SCRIPT_NAME FROM RC_STORED_SCRIPT WHERE DB_NAME = 'MYDB'

5, RC_STORED_SCRIPT_LINE
The data dictionary view used to store the contents of the script.
SQL> SELECT LINE, TEXT FROM RC_STORED_SCRIPT_LINE WHERE SCRIPT_NAME = 'BACKUP_DATABASE'

RMAN configuration and maintenance of command

1, REPORT
1, report the physical structure of the target database
RMAN> REPORT SCHEMA

2, the report need to back up data files

Before the implementation of backup and often need to obtain the information needed to back up data files, through the implementation of the REPORT NEED BACKUP command, you can list needs to back up data files. When using the REPORT NEED BACKUP command, you can with the INCREMENTAL, DAYS, REDUNDANCY, RECOVERY WINDOW four options.

(1) The need to dump a specific number of incremental backups of data files beyond recovery data files when the need to dump the data file backup, and then apply the implementation of recovery archive log. If the backup data files and cumulative incremental backup using backup strategy, then dump the data files in the order of the dump
0,1,2 .. level incremental backup, file backup dump of the data the more the longer the recovery time, to report the following data files need to restore the incremental backup dump more than 3 times the number of data files as an example :
RMAN> REPORT NEED BACKUP INCREMENTAL 3

One file is used to identify the data file number, Incrementals used to identify the need to dump the backup file number, Name used to identify the data file name, if a file appears more than three times the number of dump,
Should be performed on the data file level 0 full backup or incremental backup.

(2) reported a specific number of days did not restore the backup of data files when the data file, the required data in the dump file backup, the backup point from the start will be applied to all archived logs. Need to apply more archive logs, the longer the recovery time, in order to reduce the recovery time, backup data files should be established period of time.

3 days on USERS tablespace data files are not backed up
RMAN> REPORT NEED BACKUP DAYS 3 TABLESPACE USERS;

More than 10 days within the database data files are not backed up
REPORT NEED BACKUP DAYS 10

(3) report less than a specific number of data file backup to restore data files, you need to dump the data file backup. Media failure occurs when the data file and data file backup is also a problem, it will lead to the data file can not be recovered. When making backup copies off,
Data file backup to prevent problems, should keep multiple backup data files, but some file backup times too low, the need to increase the number of backups.
RMAN> REPORT NEED BACKUP REDUNDANCY 2 DATABASE;

(4) report the recovery time window when data files are not backed up to recover data file, the required data in the dump file backup, the backup point from the start will be applied to all archived logs. Need to apply more archive logs, and recovery time is longer. Window by using the recovery time, recovery operations can be applied archive logs need to control within a specific time. Below to report more than 6 days did not restore the window back up data files to illustrate the method using DAYS options:
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 6 DAYS

DAYS used to identify a few days to restore data files need to apply archive logs.

3, report the implementation of the old backups when using RMAN restore operation, RMAN will automatically dump the most recent backup file, and then apply the archive logs after the backup file. If the specific data files on a number of backups, you can delete the earlier backups, by using REPORT OBSOLETE command, you can report the old backup. The following example report back more than 2 times the number of old backups:
RMAN> REPORT OBSOLETE REDUNDANCY 2;

4, the report can not restore the data file when loading the database using the NOLOGGING option after the change has not been recorded because the data to the archive log, it will lead to past data file backup can not restore the current data file. To prevent data files can not be restored, the data files should be backed up by the following statement, you can view the database can not restore all data files:
RMAN> REPORT UNRECOVERABLE;

2, LIST

A, listed in the data file backup set
RMAN> LIST BACKUP OF TABLESPACE USER01;

2, lists the control file backup set
RMAN> LIST BACKUP OF CONTROLFILE;

3, lists the archive log backup set
RMAN> LIST BACKUP OF ARCHIVELOG ALL;

4, lists the SPFILE backup set
RMAN> LIST BACKUP OF SPFILE;

5, lists the data file image copies
LIST COPY OF DATABASE can list all of the data file image copies; with LIST COPY OF TABLESPACE tablespace data files are listed in a specific image copy; listed with LIST COPY OF DATAFILE specific image copy data files.

RMAN> LIST COPY OF DATAFILE 5

6, lists the control file image copies
RMAN> LIST COPY OF CONTROLFILE

7, archive log image copy of the list.
RMAN> LIST COPY OF ARCHIVELOG ALL;

8, lists each copy of the database using the RESETLOGS option to open the database after the database will generate a new copy.
RMAN> LIST INCARNATION OF DATABASE;

3, CROSSCHECK
This command is used to check the disk and tape backup files to ensure that the RMAN database backup files in sync. RMAN will check the order recorded in the database backup file. When the implementation of the CROSSCHECK command, if the database record does not match the physical state of the backup file, then the command will update the database record of status information. When using CROSSCHECK check the backup file after the command, the status of the backup file will include AVALIABLE, UNAVALIABLE and EXPIRED three,
If the backup file is in EXPIRED status, it indicates the physical file has been manually deleted or damaged. Note that if the backup file is in EXPIRED status, should use the DELETE command to delete the backup files.

1, check all backup sets
RMAN> CROSSCHECK BACKUP;

2, check all the data files of the backup set
RMAN> CROSSCHECK BACKUP OF DATABASE;

3, check a specific table space backup set
RMAN> CROSSCHECK BACKUP OF TABLESPACE SYSTEM;

4, check a specific backup set data file
RMAN> CROSSCHECK BACKUP OF DATAFILE 4;

5, check the control file backup set
RMAN> CROSSCHECK BACKUP OF CONTROLFILE;

6, check the SPFILE in backup set
RMAN> CROSSCHECK BACKUP OF SPFILE;

7, check the archive log backup set
RMAN> CROSSCHECK BACKUP OF ARCHIVELOG SEQUENCE 3;

8, check a copy of all images
RMAN> CROSSCHECK COPY;

9, check all of the data file image copies
RMAN> CROSSCHECK COPY OF DATABASE;

10, check a specific image copy of table space
RMAN> CROSSCHECK COPY OF TABLESPACE USERS;

11, matching a particular data file image copies
RMAN> CROSSCHECK COPY OF DATAFILE 4;

12, check the control file copy of the image
RMAN> CROSSCHECK COPY OF CONTROLFILE;

13, check the archived log image copy
RMAN> CROSSCHECK COPY OF ARCHIVELOG SEQUENCE 4;

4, DELETE

This command is used to delete RMAN backup records and the corresponding physical file

1, delete the old backup
RMAN> DELETE OBSOLETE;

2, delete EXPIRED backup set when using the OS command to delete the backup set that corresponds to the backup piece file, check if command execution CROSSCHECK backup set, then the backup set will be marked as EXPIRED state.
RMAN> DELETE NOPROMPT EXPIRED BACKUP;

3, delete the copy EXPIRED
RMAN> DELETE NOPROMPT EXPIRED COPY;

4, delete a specific backup set
RMAN> DELETE NOPROMPT BACKUPSET 19;

5, to delete a specific backup pieces
RMAN> DELETE NOPROMPT BACKUPPIECE 'D: \ BACKUP \ DEMO_18.BAK';

6, delete all backup sets
RMAN> DELETE NOPROMPT BACKUP;

7, to delete specific image copy
RMAN> DELETE NOPROMPT DATAFILECOPY 'D: \ BACKUP \ DEM0_19.BAK';

8, delete all image copies
RMAN> DELETE NOPROMPT COPY;

9, after the backup to delete the object when using the BACKUP command input archive log backup or backup set, the backup is complete you can delete the original input object, an example is as follows:
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
RMAN> BACKUP BACKUPSET 22 FORMAT = 'D: \ BACKUP \% u.BAK' DELETE INPUT;

5, CHANGE

1, to change the status of the backup set available CHANGE BACKUPSET .. UNAVAILABLE / AVAILABLE to change the status of a backup set.

RMAN> CHANGE BACKUPSET 22 UNAVAILABL E

2, change the image copy of the state data files: CHANGE DATAFILECOPY .. AVAILABLE / UNAVAILABLE
Control file: CHANGE CONTROLFILECOPY .. AVAILABLE / UNAVAILABLE
Archive Log: CHANGE ARCHIVELOG .. AVAILABLE / UNAVAILABLE

For example:
RMAN> CHANGE DATAFILECOPY 'D: \ BACKUP \ DEMO_37.BAK' UNAVAILABLE;

3, long-term backup long-term backup is to be a long-term retention of backup files, using the long-term objective is to backup the database can be restored to a specific point in time past. When the agency after the top of a long-term backup, RMAN will remove from the device redundancy in the backup file, that is, long-term backup file backup will not be marked as obsolete.
Command: CHANGE .. KEEP / NOKEEP

(1) to permanently keep the backup set 156
RMAN> CHANGE BACKUPSET 156 KEEP FOREVER LOGS;
(2) to make reservations 60 days in the backup set 198
RMAN> CHANGE BACKUPSET 198 KEEP UNTIL TIME 'SYSDATE +60' LOGS;
(3) to remove the backup set retention period 156
RMAN> CHANGE BACKUPSET 156 NOKEEP

RMAN backup and recovery management

First, configure the RMAN environment
1. Configure Automatic channel before 9i, when you use RMAN backup, dump and restore operation, you must manually allocate channels, and specify the corresponding parameter configuration, starting from 9i, DBA can be stored RMAN RMAN channel configuration database, which make
RMAN can automatically allocated channel. However, when the implementation of special operations when RMAN, DBA can still use the ALLOCATE CHANNEL override the default channel configuration.
(1) configure channel parallelism.
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 3

(2) configuration backup file format for all channels. By default, RMAN will automatically allocate a DISK channel, and there is no option. By configuring automatic channels, you can specify backup file format for all channels. Examples are as follows:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D: \ BACKUP \% d_% s_% p.bak';

(3) a specific channel configuration backup file format. When the auto-channel configuration, if you do not specify channel number, will be configured for all channels. If you want to cultivate a particular channel, you can specify the channel number. Examples are as follows:
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'D: \ BACKUP \% d_% s_% p.bak';

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'D: \ BACKUP \% u.bak';

(4) configure the default storage device. By default, RMAN storage devices for the DISK. By configuring the default storage devices, storage devices can be configured as the default disk or tape. Examples are as follows:
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT;

2. Configure backup sets and backup pieces maximum size maximum size by configuring the backup set, you can back up the distribution of Togo files to multiple backup sets. Note that when configure backup sets the maximum size, must ensure that its size can accommodate maximum data file backup group. Examples are as follows:
RMAN> CONFIGURE MAXSETSIZE TO 1G;

By default, each backup set contains only one backup piece file, by cultivating the maximum size of the backup piece can be a large backup set into a relatively small number of backup piece file, and the distribution of backup files to multiple-chip a device. Examples are as follows:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 500M;

3. Configure backup optimization backup optimization skips in some cases for a specific file backup. If you feel exactly the same backup files exist, then when activated when backup optimization skips the file. Note that backup optimization applies only to BACKUP DATABASE,
BACKUP ARCHIVELOG ALL / LIKE and BACKUP BACKUPSET ALL command. By default, RMAN backup optimization prohibited. Activate the backup optimization example is as follows:
RMAN> CONFIGURE BACKUP OPTIMIZATION ON
4. Configure multiple backup default, when you use RMAN to perform backup operations, will generate a backup copy of the film, in order to generate multiple copies of backup pieces, you can configure multiple backup. Examples are as follows:
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE TO 3;

5. Configuration to remove the table space by default, when the implementation of the BACKUP DATABASE command, will be back up in addition to all the temporary table space, table space. If some table space is used for storage of test data, or table space change very little, you can remove the backup database, the table space.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE USER03;

6. Configuration backup redundancy
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

7. Configuration supporting routine data file name.
When using RMAN tablespace point in time the implementation of recovery (TSPITR) or copy the database, you may need to set the auxiliary routine data file name.
RMAN> CONFIGURE AUXNAME FOR DATAFILE 2 TO 'D: \ AUXI \ DF_2.DBF'

8. Configuration snapshot control file name
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D: \ ORACLE \ ORA10 \ snfcDEMO.ORA';

9. Showing RMAN Configuration

(1) show all RMAN configuration
RMAN> SHOW ALL;

(2) shows the default device type
RMAN> SHOW DEFAULT DEVICE TYPE;

(3) shows the auto-channel configuration
RMAN> SHOW CHANNEL FOR DEVICE TYPE DISK / sbt;

(4) shows the backup redundancy
RMAN> SHOW RETENTION POLICY;

(5) shows that backup optimization
RMAN> SHOW BACKUP OPTIMIZATION

(6) shows a snapshot control file
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

(7) shows the maximum size of backup sets
RMAN> SHOW MAXSETSIZE;

(8) showed that multiple backup
RMAN> SHOW DATAFILE BACKUP COPIES

RMAN> SHOW ARCHIVELOG BACKUP COPIES;

(9) Clear RMAN configuration (restore default). Examples are as follows:
RMAN> CONFIGURE RETENTION POLICY CLEAR

Second, RMAN backup

1, name format, match operator

% C: when generating multiple backup, is used to specify a backup copy of film
% D: is used to specify the database name
% E: log sequence number for the specified archive
% P: is used to specify the backup piece within the backup set number
% S: used to specify the backup set number
% N: is used to specify the name of the table space
% F: language specifies the absolute file number

2, full database backup set
(1) consistency of the backup. Consistency is the backup after the database is closed back up all data files and control of documents, both for the archive mode and non-archive mode.
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> BACKUP DATABASE FORMAT = 'D: \ BACKUP \% d_% s.dbf';
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'

(2) non-conformance backup
RMAN> BACKUP DATABASE FORMAT = 'D: \ BACKUP \% d_% s.dbf';
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'

(3) to remove the table space
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE USER03;
RMAN> BACKUP DATABASE FORMAT = 'D: \ BACKUP \% d_% s.dbf';
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'

3, table space backup set backup table space is only applicable to archive mode, and requires the database must be in OPEN state.
RMAN> BACKUP TABLESPACE USER03 FORMAT = 'D: \ BACKUP \% N_% f_% s.dbf';

4, data file backup set of backup data files both in OPEN state, can also MOUNT state.
RMAN> BACKUP DATAFILE 5 FORMAT = 'D: \ BACKUP \% N_% f_% s.dbf';

5, control file backup set
RMAN> BACKUP CURRENT CONTROLFILE FORMAT = 'D: \ BACKUP \% d_% s.dbf'
Note: When the backup data file 1, will automatically back up the control file. When the back up other data files, by specifying the INCLUDE CURRENT CONTROLFILE option to also back up the control file, such as:
RMAN> BACKUP DATAFILE 5 FORMAT = 'D: \ BACKUP \% d_% s.dbf' include current controlfile;

6, SPFILE backup set
RMAN> BACKUP SPFILE FORMAT = 'D: \ BACKUP \% d_% s.par';

7, archive log backup set
RMAN> BACKUP FORMAT = 'D: \ BACKUP \% d_% s.arc' ARCHIVELOG FROM TIME = 'SYSDATE-1' UNTIL TIME = 'SYSDATE';

8, parallel backup set
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
RMAN> BACKUP DATABASE FORMAT = 'D: \ BACKUP \% d_% s.dbf';
RMAN> CONFIGURE DEVICE TYPE DISK CLEAR;

9, the establishment of multiple backup
RMAN> BACKUP COPIES 3 TABLESPACE USERS FORMAT = 'D: \ BACKUP \% N_% s.dbf', 'E: \ BACKUP \% N_% s.DBF', 'F: \ BACKUP \% N_% s.DBF'

10, backup backup set
RMAN> BACKUP BACKUPSET 32 FORMAT = 'D: \ BACKUP \% d_% s.bak';

11, create multiple backup discs when creating a backup set, by default, each backup set contains only one backup piece. If the tape storage space is not set to hold a single backup piece backup set files, you need to backup sets divided into several smaller backup piece file, and the maximum size of the backup piece size should be smaller than the tape. Note that when creating multiple backup piece, specify the% p must match the character.
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt MAXPIECESIZE 4G;
RMAN> BACKUP DEVICE TYPE sbt FORMAT '% d_% s_% p.dbf' database;

12, the establishment of compressed backup sets
RMAN> BACKUP AS COMPRESSED BACKUPSET TABLESPACE USERS FORMAT = 'D: \ BACKUP \% d_% s.dbf';

13, backup data files and archive logs
RMAN> BACKUP FORMAT 'D: \ BACKUP \% d_% s.bak' tablespace user01 plus archivelog;

14, the designated backup sets mark
RMAN> BACKUP FORMAT 'D: \ BACKUP \% d_% s.bak' tablespace users tag = 'users';

15, the establishment of image copies
(1) establishing a data file image copies
RMAN> BACKUP AS COPY FORMAT = 'D: \ BACKUP \ DF_5.DBF' DATAFILE 5;
RMAN> COPY DATAFILE 5 TO 'D: \ BACKUP \ DF_5.DBF'

(2) to establish control file image copies
RMAN> BACKUP AS COPY FORMAT = 'D: \ BACKUP \ DEMO.CTL' CURRENT CONTROLFILE;
RMAN> COPY CURRENT CONTROLFILE TO 'D: \ BACKUP \ DEMO.CTL';

(3) the establishment of archived log image copies
RMAN> BACKUP AS COPY FORMAT = 'D: \ BACKUP \ ARCHIVE \ ARC20.LOG' ARCHIVELOG SEQUENCE 20;
RMAN> COPY ARCHIVELOG 'D: \ DEMO \ ARCHIVE \ ARC00020_0538067512.001' TO 'D: \ BACKUP \ ARCHIVE \ ARC20.LOG'

(4) the establishment of parallel image copy
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
RMAN> BACKUP AS COPY FORMAT = 'D: \ BACKUP \ DF_% f.DBF' DATAFILE 5,5,7;
RMAN> CONFIGURE DEVICE TYPE DISK CLEAR;

(5) a copy of the specified image tag
RMAN> BACKUP AS COPY FORMAT = 'D: \ BACKUP \ DF_% f.DBF' DATAFILE 5 TAG = DF_5;
RMAN> COPY DATAFILE 5 TO 'D: \ BACKUP \ DF_% f.DBF' TAG = DF_5;

16, to establish the cumulative incremental backups and backup
(1) the establishment of an incremental backup
RMAN> BACKUP INCREMENTAL LEVEL 0 FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

RMAN> BACKUP INCREMENTAL LEVEL 1 FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

RMAN> BACKUP INCREMENTAL LEVEL 2 FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

(2) to establish the cumulative backup
RMAN> BACKUP INCREMENTAL LEVEL 0 FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

RMAN> BACKUP INCREMENTAL LEVEL 2 FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

RMAN> BACKUP INCREMENTAL LEVEL 2 cumulative FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

RMAN> BACKUP INCREMENTAL LEVEL 1 FORMAT = 'D: \ BACKUP \% d_% s.dbf' tablespace users;

17, shows backup information
(1) V $ ARCHIVED_LOG. The dynamic performance view to display all archived log image copy of the information.
SQL> select sequence #, name, first_change # from v $ archived_log where status = 'A'

(2) V $ BACKUP_DATAFILE dynamic view of the display control file for backup information and data files. Through the dynamic performance view V $ BACKUP_DATAFILE, V $ BACKUP_PIECE connection between the query
Can display a specific data file backup piece file information.
SQL> SELECT A. FILE #, B. HANDLE, A. BLOCKS * A. BLOCK_SIZE BYTE FROM V $ BACKUP_DATAFILE A, V $ BACKUP_PIECE B WHERE A. SET_STAMP = B. SET_STAMP AND B. STATUS = 'A';

(3) V $ BACKUP_REDOLOG dynamic performance view of the archive log backup set is used to display information, for each archive log backup set can contain one or more archived logs. Through the V $ BACKUP_PIECE and V $ BACKUP_REDOLOG
The connection between the query, you can display the backup piece file contains archive log backup information.
SQL> SELECT DISTINCT A. HANDLE, B. SEQUENCE #, B. FIRST_CHANGE #, B. BLOCKS FROM V $ BACKUP_PIECE A, V $ BACKUP_REDOLOG B WHERE A. SET_STAMP = B. SET_STAMP AND A. STATUS = 'A'

(4) V $ BACKUP_SET, V $ BACKUP_PIECE, the former is used to display the details of the backup set, which is used to display the details of the backup piece, by the connection between the two queries, you can display the corresponding backup set backup piece of information.
SQL> SELECT B. HANDLE, A. BACKUP_TYPE, A. PIECES, A. ELAPSED_SECONDS FROM V $ BACKUP_SET A, V $ BACKUP_PIECE B WHERE A. SET_STAMP = B. SET_STAMP AND B. STATUS = 'A'

Note: handle for that backup piece file name, backup_type backup for that film type (D: data file backup completed, I: data file incremental backup, L: archive log backup), pieces for that backup piece number,
elapsed_seconds used to indicate the total cost of backup time.

(5) V $ BACKUP_CORRUPTION BACKUP command is used to display in the implementation of the damage was detected when the data block information.
SQL> SELECT FILE #, BLOCK #, BLOCKS, MARKED_CORRUPT FROM V $ BACKUP_CORRUPTION;

(6) V $ COPY_CORRUPTION used to display the COPY command in the implementation of the damage was detected when the data block information.
SQL> SELECT FILE #, BLOCK #, BLOCKS, MARKED_CORRUPT FROM V $ COPY_CORRUPTION;

3, RMAN restore
1, RMAN dump command

(1) RESTORE DATABASE database used to dump all the files, all data files when the database is all the media failure occurred, or perform incomplete recovery, you should use this command.
Note that this command can only be used in the MOUNT state.

(2) RESTORE TABLESPACE used to dump a specific table space data file. If a table space, all the data files all the media failure occurred, and the database is in OPEN state, you should use this command to dump all their data files. Note that this command can only be used in OPEN state.

(3) RESTORE DATAFILE. Specific data for the dump file, the command can use the MOUNT and OPEN state

(4) RESTORE CONTROLFILE control file for the dump, when implementation of the control file based on the incomplete recovery or restore the database to a previous copy of the state, must dump the control file.
Note that this command can only be used in NOMOUNT state.

(5) RESTORE ARCHIVELOG to archive the log dump. When the implementation of full recovery and incomplete recovery, will need to apply archive log. If the archive log media failure occurs, or archived log image copy does not exist,
You need to use the command to dump the archive log, the command can use the MOUNT and OPEN state.

(6) RESTORE SPFILE server parameter file dump term can only be used in NOMOUNT state.

2, RMAN restore command

(1) RECOVER DATABASE to restore a database for all the files can only be used in the MOUNT state.

(2) RECOVER TABLESPACE to restore a specific table space for all the data files can only be used in OPEN state.

(3) RECOVER DATAFILE for the restoration of specific data files, can be used OPEN AND MOUNT state.

3, RMAN full recovery
(1) restore database
a) loss of all data files

RMAN> STARTUP FORCE MOUNT
RMAN> RUN (
2> RESTORE DATABASE;
3> RECOVER DATABASE;
4> SQL 'ALTER DATABASE OPEN';
5>)

b) where the disk data file hardware failure
If the data file where the disk hardware failure, data files can not dump to the original location, in order to restore the database, to dump the data files to another location. Before the implementation of the RESTORE DATABASE, by
Implementation of the SET NEWNAME command to specify the data file for the new location; the implementation of the RESTORE DATABASE command, through the implementation of the SWITCH DATAFILE command to change the control file data files recorded in
Location and name, through the implementation of the RECOVER DATABASE command can be applied to archived logs.
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SET NEWNAME FOR DATAFILE 1 TO 'E: \ DEMO \ SYSTEM01.DBF';
4> SET NEWNAME FOR DATAFILE 2 TO 'E: \ DEMO \ UNDOTBS01.DBF';
5> SET NEWNAME FOR DATAFILE 3 TO 'E: \ DEMO \ SYSAUX01.DBF';
6> SET NEWNAME FOR DATAFILE 4 TO 'E: \ DEMO \ USERS01.DBF';
7> RESTORE DATABASE;
8> SWITCH DATAFILE ALL;
9> RECOVER DATABASE;
10> SQL 'ALTER DATABASE OPEN';
11>)

In the restoration and open the database, you can view the database through the REPORT SCHEMA new physics program
RMAN> REPORT SCHEMA

(2) restoration of SYSTEM tablespace data file

a) SYSTEM tablespace data files are accidentally deleted
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> RESTORE DATAFILE 1;
4> RECOVER DATAFILE 1;
5> SQL 'ALTER DATABASE OPEN';
6>)

b) SYSTEM tablespace data file where the disk fails
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SET NEWNAME FOR DATAFILE 1 TO 'D: \ DEMO \ SYSTEM01.DBF';
4> RESTORE DATAFILE 1;
5> SWITCH DATAFILE 1;
6> RECOVER DATAFILE 1;
7> SQL 'ALTER DATABASE OPEN';
8>)

(3) in the OPEN state after the closure of accident to restore lost data files

a) data files were accidentally deleted
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
4> SQL 'ALTER DATABASE OPEN';
5> RESTORE DATAFILE 4;
6> RECOVER DATAFILE 4;
7> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
8>)

b) where the data files damaged disk
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
4> SQL 'ALTER DATABASE OPEN';
5> SET NEWNAME FOR DATAFILE 4 TO 'D: \ DEMO \ USERS01.DBF';
6> RESTORE DATAFILE 4;
7> SWITCH DATAFILE 4;
8> RECOVER DATAFILE 4;
9> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
10>)

(4) OPEN state to restore accidentally lost when opening the data files
When the database is in OPEN state, if the SYSTEM tablespace data file media failure occurs, it will immediately terminate the routine, this time in the MOUNT state needs to restore its data file, if the OPEN state
Other data files under the media failure occurs, only the media failure data file can not be related to access, without affecting the other data files.

a) data files were accidentally deleted
RMAN> RUN (
2> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
3> RESTORE DATAFILE 4;
4> RECOVER DATAFILE 4;
5> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
6>)

b) where the disk fails, data files
RMAN> RUN (
2> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
3> set newname for datafile 4 to 'd: \ demo \ users01.dbf';
4> RESTORE DATAFILE 4;
5> SWITCH DATAFILE 4
6> RECOVER DATAFILE 4;
7> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
8>)

(5) in the OPEN state to restore the backup data files are not
Premise: the establishment of data from all archived log file date must be present.

a) data files were accidentally deleted
Starting from the 10G, if the data file backup does not exist, dump data file will automatically create a data file.
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
4> SQL 'ALTER DATABASE OPEN';
5> RESTORE DATAFILE 7;
6> RECOVER DATAFILE 7;
7> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';
8>)

b) where the disk fails, data files
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
4> SQL 'ALTER DATABASE OPEN';
5> SET NEWNAME FOR DATAFILE 7 TO 'D: \ DEMO \ USERS04.DBF';
6> RESTORE DATAFILE 7;
7> SWITCH DATAFILE 7;
8> RECOVER DATAFILE 7;
9> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';
10>)

(6) to restore the table space

a) Table space is accidentally deleted all data files
RMAN> RUN (
2> SQL 'ALTER TABLESPACE USERS OFFLINE FOR RECOVER';
3> RESTORE TABLESPACE USERS;
4> RECOVER TABLESPACE USERS;
5> SQL 'ALTER TABLESPACE USERS ONLINE';
6>)

b) table space where the disk fails, data files
RMAN> RUN (
2> SQL 'ALTER TABLESPACE USERS OFFLINE FOR RECOVER';
3> SET NEWNAME FOR DATAFILE 4 TO 'D: \ DEMO \ USERS01.DBF';
4> RESTORE TABLESPACE USERS;
5> SWITCH DATAFILE 4;
6> RECOVER TABLESPACE USERS;
7> SQL 'ALTER TABLESPACE USERS ONLINE';
8>)

(7) Data block media recovery
Starting from 9i, use BLOCKRECOVER command to restore the damaged data block, the sample is as follows:
RMAN> BLOCKRECOVER DEVICE TYPE DISK
2> DATAFILE 5 BLOCK 21,48,128;

4, RMAN incomplete recovery
RMAN incomplete recovery mode applies only to archive, and can only be completed in the MOUNT state.

(1) time-based recovery
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SET UNTIL TIME = '2007-08-24 17:23:22 ';
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> SQL 'ALTER DATABASE OPEN RESETLOGS';
7>)

Open the database with the RESETLOGS option, it will reset the log sequence number, proposed to delete the earlier backup, and re-backup the database, the sample is as follows:
RMAN> RUN (
2> DELETE NOPROMPT BACKUP;
3> DELETE NOPROMPT COPY;
4> BACKUP DATABASE FORMAT = 'D: \ BACKUP \% d_% s.bak';
5> sql 'alter system archive log current';
6>)

(2) recovery based on SCN
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SET UNTIL SCN = 511402;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> SQL 'ALTER DATABASE OPEN RESETLOGS';
7>)

(3) recovery based on the log sequence number
RMAN> RUN (
2> STARTUP FORCE MOUNT;
3> SET UNTIL SEQUENCE = 21;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> SQL 'ALTER DATABASE OPEN RESETLOGS';
7>)

(4) Based on the backup control file recovery
c: \> set nls_date_format = yyyy-mm-dd hh24: mi: ss
c: \> rman target sys / oracle @ demo nocatalog
RMAN> STARTUP FORCE NOMOUNT
RMAN> SET DBID = 3282656886;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP MAXSEQ 6;
RMAN> ALTER DATABASE MOUNT;
RMAN> RUN (
2> SET UNTIL TIME = '2007-08-25 14:25:02 ';
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> SQL 'ALTER DATABASE OPEN RESETLOGS';
6>)

User Management Backup and Recovery

The first part of the backup
1.1 The consistency of the backup
1.1.1 check to back up data files and control files
select name from v $ datafile
union
select name from v $ contrilfile

1.2.2, close the database
1.2.3 copy the files to backup to the backup directory

1.2 Non-conformance backup (for archive mode)
2.2.1 check data files to back up
select name from v $ datafile;

2.2.2 Set the database backup status
alter database begin backup
2.2.3 copy the data files to the backup directory

2.2.4 Backup control file
alter database backup controlfile to 'd: \ controlfile.ctl';
2.2.5 the end of database backup, file backup to ensure the synchronization of the data should also archive the current log group
alter database end backup
alter system archive log current

1.3 backup tablespace
1.3.1 Offline backup
a) determine the table space contains data files
select file_name from dba_data_files where tablespace_name = 'USERS';

b) set the table space is offline
alter tablespace users offline;

c) copy the data files to the backup directory

d) set the table space online
alter tablespace users online;

1.3.2 Online Backup

a) determine the table space contains data files
select file_name from dba_data_files where tablespace_name = 'USERS';

b) set the table space backup mode
alter tablespace users begin backup;

c) Copy the corresponding data files to a backup directory

d) set the table space for normal mode
alter tablespace users end backup

1.4 deal with failure when the implementation of the online backup online backup, if routine fails, then the implementation of the STARTUP starts the database, will show
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'd: \ demo \ users01.dbf'

As shown above, if the data file is still in state of online backup, then open the database will display an error message, in order to open the database, the data must come to an end
Online file backup, concrete steps are as follows:

a) loading the database data files to the end of the online backup status, first need to load the database.
startup force mount

b) determine the status is online backup all data files
select file # from v $ backup where status = 'ACTIVE';

c) end of the online backup status
If there are multiple data files in online backup status, use the following command-line backup of the end of the state
ALTER DATABASE END BACKUP

If only a data file backup status is online, you can use the following command-line backup of the end
ALTER DATABASE DATAFILE 4 END BACKUP

Also use the RECOVER command can also be the end of data file backup status online
RECOVER DATAFILE 4

1.5 read-only table space backup
a) determine the state in a READ ONLY tablespace
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE STATUS = 'READ ONLY';

b) determine the read-only table space that contains the data files
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'QUERY'

c) copy the data files read-only tablespace to the backup directory. Because the data files read-only table space will not be any change, so can the backup.

1.6 backup control file
a) a copy of the establishment of control
alter database backup controlfile to 'd: \ backup \ ctlfile.ctl'

alter database backup controlfile to 'd: \ backup \ ctlfile.ctl' REUSE

b) back up to trace file
alter database backup controlfile to trace;

Determine the trace file name: name of trace file format: _ora_ . Trc
Determine the location of trace file

show parameter user_dump_dest

1.7 backup other files
a) backup archive logs in the archive mode to archive logs to use physical restoration, to ensure the smooth progress of recovery can also be backed up archive log archive log backup, first determine the need for backup
Archive logs, and then copied to the backup directory, example as follows:

select name from v $ archived_log where dest_id = 1 and first_time> = sysdate-1 - one day before the backup log

The corresponding files can be copied to the backup directory.

b) the backup parameter file
create pfile = 'd: \ backup \ pfilemydb.ora' from spfile = '% ORACLE_HOME% \ DATABASE \ SPFILEMYDB.ORA'

c) the backup password file
Password files can be directly to the backup directory on the line COPY
SQL> host copy% ORACLE_HOME% \ DATABASE \ PWDMYDB.ORA D: \ BACKUP

The second part of the user management of the fully restored

2.1 dump data files to other disk media failure occurs when the data file after the restore command in the implementation of SQL, you must use the OS command to copy the data file. If the data file is accidentally deleted, you can copy the backup file to the original directory; but if the disk where data files are damaged, then the backup data files must be copied to other disks.

a) in the MOUNT state change data file locations
ALTER DATABASE RENAME FILE 'D: \ DEMO \ USERS01.DBF' TO 'E: \ DEMO \ USERS01.DBF'
In this command before, you must ensure that the target file already exists

b) changes in the OPEN state the location of data files in the state, both can use the ALTER TABLESPACE RENAME DATAFILE command can also use the ALTER DATABASE RENAME FILE command to change the data file location.
* The state can not SYSTEM tablespace of the table data file location
* Modify the data file location, you must ensure that the target file exists.
* Modify the data file location, you must first make the table space or data files offline

SQL> ALTER DATABASE DATAFILE 'D: \ DEMO \ USERS01.DBF' OFFLINE
SQL> HOST COPY d: \ backup \ users01.dbf e: \ demo
SQL> ALTER TABLESPACE USERS rename DATAFILE 'D: \ DEMO \ USERS01.DBF' TO 'E: \ DEMO \ USERS01.DBF'

2.2 fully restored order when using the user management completely restored, in the data file to the target path and then still need to use the RECOVER DATABASE, RECOVER TABLESPACE or RECOVER DATAFILE
Application of three command archived logs and redo logs.
a) RECOVER DATABASE
The MOUNT command can only state run MOUNT state if you want to recover data files and multiple data file is corrupted, you can use this command to recover. Examples are as follows:
SQL> RECOVER DATABASE

b) RECOVER TABLESPACE
This command can only run in the OPEN state, the command used to restore one or more table space, all the data files, such as:
SQL> RECOVER TABLESPACE USERS, USERS2

c) RECOVER DATAFILE
This command can be run MOUNT and OPEN state, when the execution of the command, can specify the data file name, also specify the data file number, for example:
SQL> RECOVER DATAFILE 'D: \ DEMO \ USERS01.DBF'
OR
SQL> RECOVER DATAFILE 4,5

2.3 Applications archive log
a) the proposed use of ORACLE archive log location when the implementation of the full restoration of order, if you do not specify the archive location, ORACLE applications will provide advice archive logs, and displays the message. As follows:
SQL> RECOVER DATAFILE 5

b) If the specified archive log location does not exist in the default location under the archive logs, then when the implementation of the full restoration of order, you can specify the log file location, the sample is as follows:
SQL> RECOVER FROM 'C: \ ARCHIVELOG' DATAFILE 5
c) should automatically archive log required for complete recovery if the archived logs are stored in a specific archive directory, you can make the server process automatically apply archived logs.
SQL> RECOVER AUTOMATIC DATAFILE 5

2.4 View recovery files
a) lists the need to restore data files
select file #, error, change # from v $ recover_file;

b) set out to restore archive logs to be used
select sequence #, archive_name from v $ recovery_log;

2.5 restore the backup data files are not
a) view the need to restore the file
select file #, error from v $ recover_file;

b) to make files offline
alter database datafile 6 offline

c) Open the database
alter database open

d) re-establish the data file because there is no backup, so they need to re-create a data file if the original file where the disk is not damaged, you can set up a data file copy of the original position, as follows:
SQL> ALTER DATABASE CREATE DATAFILE 'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ TEST_TBS.DBF'

If the original file where the disk is damaged, you need to build a copy in another location, as follows:
SQL> ALTER DATABASE CREATE DATAFILE 'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ TEST_TBS.DBF' AS 'E: \ ORADATA \ MYDB \ TEST_TBS.DBF'

e) restoration of data files
SQL> RECOVER DATAFILE 6

f) the data file online
SQL> ALTER DATABASE DATAFILE 6 ONLINE

The third part of the user management is not fully restored

3.1 does not fully restore order
a) RECOVER DATABASE UNTIL TIME
The command used to perform time-based incomplete recovery, and when the specified time must be consistent with the date format YYYY-MM-DD HH24: MI: SS. When the execution is not entirely based on the time point of recovery, to ensure that a particular point in time before all the archive logs and redo logs all there.

b) RECOVER DATABASE UNTIL CHANGE
The command used to execute based on the incomplete recovery SCN. When the implementation of the order, to ensure that all before in a particular SCN archived logs and redo logs all there.

c) RECOVER DATABASE UNTIL CANCEL
Cancel the order for execution based on the incomplete recovery, when the execution of the order, if found necessary archive log or redo log does not exist, then specify the CANCEL option to cancel recovery.

d) RECOVER DATABASE ... USING BACKUP CONTROLFILE
The command used to perform a backup control file based on the incomplete recovery. Before the implementation of the command, by looking at the ALERT file to go top of misuse of time and SCN value, then according to time or SCN value recovery.

3.2 Time-based recovery
a) confirm to the point in time recovery
b) Close the database Note: To prevent the failure is not fully restored, before the proposal to close the database to do a full backup of the database

c) loading the database
SQL> start mount

d) Copy all data files backed up. In order to restore the database to the last time, you must copy all the data file backup, and backup files to restore point of time must be before. With the following statement to view the backup time
SQL> select file #, to_char (time, 'yyyy-mm-dd hh24: mi: ss') from v $ recover_file;

e) implementation of the RECOVER DATABASE UNTIL TIME command
SQL> RECOVER DATABASE UNTIL TIME '2007-08-24 17:25:10 '

f) to open the database RESETLOGS
SQL> alter database open resetlogs;

g) back up all data files and the database control file. When to open the database after the RESETLOGS would re-establish the redo log, redo log empty all the contents of Reasons,
And reset the log sequence number 1, you can view the current log ARCHIVE LOG LIST serial number.
SQL> ALTER DATABASE BEGIN BACKUP;
SQL> HOST COPY D: \ ORADATE \ SYSTEM01.DBF E: \ BACKUP
...
...
SQL> ALTER DATABASE END BACKUP;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'E: \ BACKUP \ CTLFILE.CTL' REUSE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; - archiving the current log group

3.3 Based on SCN SCN recovery recovery need to know the number to return to the SCN, and if restored to the SCN number is 99999
a) Close the database

b) loading the database

c) copy the backup files, backup files of the SCN value must be less than the value to be returned to the SCN, copy data back-up files can be backed up by the following statement to view the SCN No.
SQL> SELECT FILE #, CHANGE # FROM V $ RECOVER_FILE;

d) implementation of the RECOVER DATABASE UNTIL CHANGE command to perform a full recovery.
SQL> RECOVER DATABASE UNTIL CHANGE 9999

e) to open the database RESETLOGS.
SQL> ALTER DATABASE OPEN RESETLOGS;

f) back up all data files and the database control file. When to open the database after the RESETLOGS would re-establish the redo log, redo log empty all the contents of Reasons,
And reset the log sequence number 1, you can view the current log ARCHIVE LOG LIST serial number.
SQL> ALTER DATABASE BEGIN BACKUP;
SQL> HOST COPY D: \ ORADATE \ SYSTEM01.DBF E: \ BACKUP
...
...
SQL> ALTER DATABASE END BACKUP;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'E: \ BACKUP \ CTLFILE.CTL' REUSE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; - archiving the current log group

3.4 Based on cancellation of recovery is based on the cancellation of recovery to restore the database to a specific state before the log sequence number, when the result of lost log or redo log file full recovery fails, you can use the implementation of this recovery method is not fully recovered. Assume that the log sequence number 10 bits data file USERS01.DBF media failure occurred, and complete recovery shown in the implementation of the following error message:
SQL> RECOVER DATAFILE 4
ORA-00308: cannot open archived log 'd: \ demo \ archive \ 8_1_537902587.log'
ORA-27041: unable to open file
ODS-04002: unable to open file
O / S-Error: (OS 2) System can not find the file specified

As shown above, because the error can not locate archive logs 8_1_537902587.log. But the data file contains a very important De USERS01.DBF data, and that the file must be restored, in this situation, you can use the incomplete recovery Qu Xiao-based method, as far as possible, loss reduction, following these steps:
a) Close the database

b) loading the database

c) a backup copy of all data files, when copying the data file backup, you must ensure that the backup files to be restored to the SCN value is less than the log sequence number starting SCN value, copy files,
By the following statement queries the backup file of SCN value.
SQL> select file #, change # from v $ recover_file;

By the following statement, determine the log sequence number corresponding to a specific starting SCN value:
SELECT MAX (FIRST_CHANGE #) FROM V $ LOG_HISTORY WHERE SEQUENCE # = 8;

d) implementation of the RECOVER DATABASE UNTIL CANCEL command to restore the database.
SQL> RECOVER DATABASE UNTIL CANCEL
.....
.....

If the archive logs exist in the default location, then the direct application of archive log carriage return encountered no archive log 8_1_537902587.log, enter CANCEL cancel recovery.

e) to open the database RESETLOGS

f) back up all data files and the database control file. When to open the database after the RESETLOGS would re-establish the redo log, redo log empty all the contents of Reasons,
And reset the log sequence number 1, you can view the current log ARCHIVE LOG LIST serial number.
SQL> ALTER DATABASE BEGIN BACKUP;
SQL> HOST COPY D: \ ORADATE \ SYSTEM01.DBF E: \ BACKUP
...
...
SQL> ALTER DATABASE END BACKUP;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'E: \ BACKUP \ CTLFILE.CTL' REUSE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; - archiving the current log group

3.5 Based on the backup control file to restore a backup control file recovery is the use of the database backup control file recovery process. When the mistake delete a table space or database of all the damaged all control files, you can use this recovery method. The following user mistakenly deleted the simulated DBA USERS tablespace example.

a) because the current control file does not contain the table space information, so you must use backup control file recovery has been accidentally deleted a table space, by looking at the ALERT file to determine the time from deletion.

b) Close the database

c) Copy all data files and control file backup

d) loading the database when the implementation is not fully restored, to ensure that the data file backup point in time prior to the resumption point in time, to adopt the following statement can determine the backup time:
SQL> select file #, to_char (time, 'yyyy-mm-dd hh24: mi: ss') from v $ recover_file;

e) implementation of the RECOVER DATABASE ... USING BACKUP CONTROLFILE command.
SQL> RECOVER DATABASE UNTIL TIME '2007-08-24 19:56:33 'USING BACKUP CONTROLFILE

f) to open the database RESETLOGS.

g) back up all data files and the database control file. When to open the database after the RESETLOGS would re-establish the redo log, redo log empty all the contents of Reasons,
And reset the log sequence number 1, you can view the current log ARCHIVE LOG LIST serial number.
SQL> ALTER DATABASE BEGIN BACKUP;
SQL> HOST COPY D: \ ORADATE \ SYSTEM01.DBF E: \ BACKUP
...
...
SQL> ALTER DATABASE END BACKUP;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'E: \ BACKUP \ CTLFILE.CTL' REUSE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; - archiving the current log group

Part IV other recovery issues

4.1 control file to restore control file using the principles are the following: 1, multi control file. 2, when the physical structure of the database backup control file changes

a) a single control file of media failure.
Diversified control file, the number of control files are mirror images, the content exactly the same failure occurs in one file, only need to modify the initialization parameters control_files,
Remove the damaged control file, add a new control file, and then restart the database.
Eg: OLD control_files = d: \ demo \ control01.ctl, e: \ demo \ control02.ctl

NEW control_files = d: \ demo \ control01.ctl, f: \ demo \ control02.ctl

b) failure of all control files in the media all the documents of all damage control, in order to make the database can re-use, must rebuild the control file, although you can use manual methods to establish control file, but because this method more Fuza and error-prone, it is proposed to use establish control file trace file
(1) copy the control file backup. If the initialization parameter control_files contains only one control file, then only need to copy the control file backup to a location, if the file contains more than control,
Then the need to control file backup copy to each relevant location.

(2) generate the text trace file.
SQL> STARTUP FORCE MOUNT
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

(3) edit the text trace file. After the establishment of the text trace file, the trace file contains not only related with the restoration of SQL * PLUS commands and SQL commands, but also contains a large number of text annotation information, and recovery related to the implementation of SQL * PLUS commands and SQL commands, should be removed Note the information, and save its contents to the SQL script (for example: D: \ BACKUP \ CREATE.SQL) edited the following example.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ REDO01.LOG' SIZE 10M,
GROUP 2 'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ REDO02.LOG' SIZE 10M,
GROUP 3 'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ REDO03.LOG' SIZE 10M
- STANDBY LOGFILE
DATAFILE
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ SYSTEM01.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ UNDOTBS01.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ SYSAUX01.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ USERS01.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ USERSTBLSPACE.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ USER_A.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ USER_B.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ USER_C.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ USER_D.DBF',
'D: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ MYDB \ TEST.DBF'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;

(4) Close the database
SQL> SHUTDOWN IMMEDIATE
(5) to restore control file
SQL> @ D: \ BACKUP \ CREATE.SQL

c) to restore redo logs
(1) a log log group members damage
The purpose of diversification is the redo log members of the media in order to prevent failure of the log, assuming that the database consists of three log groups, each group contains two days of member log and a log group with members of different log
Located in different disk, in this case, if a member of a log log group media failure occurs, then the database can still work.
If the log group 1 contains LOG1A, LOG1B two members of the media failure occurs if the LOG1A, LGWR can still change the transaction is written to the members of the LOG1B, which makes the database can still be running when the log group
Members of one of the log media failure occurs, DBA can remove the damaged log members in the database is running, the following statement through media failure occurs can determine the log members:
SQL> SELECT MEMBER FROM V $ LOGFILE WHERE STATUS = 'INVALID'

(2) Delete the failure of members of the media if the members of media failure is the current log log log group members, then members of the logs will not be deleted, in order to delete the current log log group member must manually switch the log, the following example:
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'C: \ DEMO \ REDO01_2.LOG';

(3) members to increase the log
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'C: \ DEMO \ REDO01_3.LOG' TO GROUP 1;
Note: additional members of the state of the log is INVALID

d) non-active log group for all members of all the damaged log group is inactive log log group in INACTIVE state, when the non-active log group for all members of all the log media failure occurs, if the database is in OPEN state, then when the switch to The log group, the database will be in a wait state, the state below to OPEN and CLOSE in the state to solve the log group applauded, for example, shows non-active treatment of damaged log group.

(1) in the OPEN state, groups of non-activity log of all the members of all the log when the database is in OPEN state of damage, if the non-active log group for all members of all the log media failure occurs, then the database can still work. Stage when the switch to the log, when these, because its contents can not be archived, Suo Yi LGWR will be in Dengdaizhuangtai, in the interest LGWR Ke Yi continue to work, should Qingchu the logset Shiliruxia:
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1; - if the media failure occurred log group 1

* When, after the implementation of the above command, ORACLE back to re-establish all the members of log group 1, then LGWR can continue to work properly. But because the contents of the log group has not been filed, it will lead to the data file backup over the past
Not be used.

e) non-activities in the closed state, all log log group members all the damage

In the off state when inactive log group members in all log media failure occurs, you can add a new log group, delete the original log group, then open the database, the sample is as follows:
SQL> ALTER DATABASE ADD LOGFILE
2 ('D: \ DEMO \ REDO04.LOG', 'D: \ DEMO \ REDO04_2.LOG')
3 SIZE 10M;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1; - Remove the damaged log group
SQL> ALTER DATABASE OPEN;

f) the current log group for all members of all the damage the current log log group is talking about change by LGWR write to the transaction log group. If the OPEN state the current log group, all members of the media failure all occur, then ORACLE will terminate the routine; closed state if the current log group, all members of all the log media failure occurs, then the database will not open.

(1) off of the current log group, all members are all damaged when in the off state of the current log group members all appeared all Rizhi media failed, because the data files, control files are always Wanquan the same state, we only need to use the RECOVER DATABASE UNTIL CANCEL command canceled is not based on full recovery, and then open the database with the RESETLOGS option can be. Examples are as follows:
SQL> RECOVER DATABASE UNTIL CANCEL
SQL> ALTER DATABASE OPEN RESETLOGS;

(2) in the OPEN state the current log group members of all the logs all the damage when in OPEN state the current log group all members of the media have emerged failures, things change when the LGWR will write to the log set, the routine will automatically shut down, and display an error message, when there is the case, landing Zhihou In a privileged user must use the Shu Ju file backup, archive logs incomplete implementation of recovery based on cancellation. Examples are as follows:
SQL> STARTUP MOUNT
SQL> HOST COPY D: \ BACKUP \ SYSTEM01.DBF D: \ DEMO
SQL> HOST COPY D: \ BACKUP \ SYSAUX01.DBF D: \ DEMO
....
....
....

SQL> RECOVER DATABASE UNTIL CANCEL

SQL> ALTER DATABASE OPEN RESETLOGS;

g) restoration of temporary files when the implementation of the incomplete recovery or open the database with the RESETLOGS option after the result in the temporary file can not be used in the past. In addition, when a temporary file media failure occurs, the same result in the temporary file can not be used.
When the temporary file can not be used, the sorting operation may fail and display the following error message
ORA-25153: Temporary Tablespace is Empty

At this point as long as the corresponding temporary table space for the addition of a new temporary file, then re-run the corresponding operation can be, example as follows:
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D: \ DEMO \ TEMP.DBF' SIZE 10M REUSE;

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Single-instance and RAC to create the ORACLE STREAM (2)

kods.net — Tue, 19 Jan 2010 16:50:10+0000

After completion of parameter settings, the following to start the master and backup database is set to archive mode. The reason why this part of the single opening is due to single-instance database is set to archive mode and ORACLE RAC is set to archive mode is slightly different, the following were described as follows:

Single-instance, the revised database archiving mode only requires the following four steps:

1, shutdown immediate

2, startup mount

3, alter database archivelog

4, alter database open

You can also set the archive man-made path, do not use the system default archive path, mainly to avoid year after year in archive log files generated by the database where the file system Chengbao, where a separate log will be archived into a single file system "/ arch" the following:

SQL> alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / arch';

Amendments adopted after the completion of the command "archive log list" to confirm:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination / arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

ORACLE RAC, modify the database archiving mode is slightly more complicated, the steps are as follows:

1, create pfile = '/ arch / pfilestorm.ora' from spfile;

2, shutdown immediate (storm1 and storm2 examples should close)

3, using the vi editor generated pfile (pfilestorm.ora), and modify the following parameters:

cluster_database = false

4, to modify the parameter file for the full-length after a separate database in a node starts to nomount Status:

startup nomount pfile = '/ arch / pfilestorm.ora';

5, so that the database to exclusive (exclusive) mode starts to mount status:

alter database mount exclusive

6, to change the archive mode and archive path:

alter database archivelog
alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / arch'; (this step can also write directly Parameter File)

7, shutdown immediate (storm1)

8, using the vi editor generated pfile (pfilestorm.ora), and modify the following parameters:
cluster_database = true

9, startup pfile = '/ arch / pfilestorm.ora'; (At this point the database has been out of archive mode)

10, create spfile from pfile;

11, shutdown immediate (storm1)

12, startup (At this point the database to a server parameter file starts)

13, startup (in the storm2 start)

RAC corresponding two nodes are started, through the command test to see whether the entry into force set up:

storm1:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination / arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

storm2:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination / arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

To this, the main library and prepared by the library archive mode is completed, the following into the other steps.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

rman notes

kods.net — Mon, 18 Jan 2010 00:08:30+0000

First, connect to the database

$ rman target sys @ db;

Then you are prompted for a password

Then back up the table space

$ backup tablespace users format "/ home /% U"

Backup users to the / home directory.

Baocuo, suggesting that the non-log archive mode, can not back up the active directory

Had to turn to non-activated

$ sqlplus / nolog;

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size 2023024 bytes
Variable Size 251658640 bytes
Database Buffers 2281701376 bytes
Redo Buffers 14753792 bytes
Database mounted.
SQL> quit

And then re-backup

$ Rman target sys @ db;

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 18 21:55:57 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:
connected to target database: DB (DBID = 1539873842, not open)

RMAN> backup tablespace users format "/ home /% U";

Starting backup at 18-JAN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 157 devtype = DISK

. . . . . . . . . . . . . . . . . . . . . . .

After the backup, the database back to normal

$ Sqlplus / nolog;

SQL * Plus: Release 10.2.0.1.0 - Production on Mon Jan 18 23:05:45 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba;
Connected.
SQL> alter database open;

Database altered.

Can restrict the size of the backup file

run (
allocate channel d1 type disk maxpiecesize = 10G;
allocate channel d2 type disk maxpiecesize = 10G;
backup full database filesperset 10 include current controlfile format '/ home / oracle / fullbak_% d_% T_% s_% p' plus archivelog format '/ home / oracle / archbak_% d_% T_% s' delete all input;

release channel d1;
release channel d2;
)

Hand to establish two channels, the backup file limited to 10g.

oracle10g increase in compressed backup

You can choose where in the backup command displays the specified compression options:
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
- Only 1,5 Backup file compression
BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;

Can also be configured RMAN specify the compression options:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

But the compressed backup will consume more resources and time.

run (
allocate channel d1 type disk maxpiecesize = 10G;
allocate channel d2 type disk maxpiecesize = 10G;
backup AS COMPRESSED BACKUPSET full database filesperset 10 include current controlfile format '/ home/oracle/3090100/fullbak_% d_% T_% s_% p' plus archivelog format '/ home/oracle/3090100/archbak_% d_% T_% s' delete all input;

release channel d1;
release channel d2;
)

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

RMAN backup and recovery - RAC environment, the database backup (2)

kods.net — Tue, 15 Dec 2009 20:30:20+0000

RAC environment, backup, recovery and general database backup, restore, there are some differences.

RMAN backup and recovery - RAC environment, the database backup (1): http://yangtingkun.itpub.net/post/468/285260

This description is based on the raw device backup RAC environment.

For the establishment of the bare device RAC environment, due to archive can not be stored to the raw device, so that all of the archive must be stored in the local disk. RMAN backups directly through the cause errors.

First, the bare equipment to imitate the situation, to modify the original LOG_ARCHIVE_DEST_1 configuration, each instance of change in the local hard disk archiving:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION = / data1/archivelog' SID = 'testrac1';

The system has changed.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION = / data1/archivelog' SID = 'testrac2';

The system has changed.

And then try to back up the database directly:

$ Rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Fri May 11 16:06:48 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to target database: TESTRAC (DBID = 4291216984)

RMAN> run
2 "(
3> allocate channel c1 device type disk format '/ data1/backup /% U';
4> backup database plus archivelog delete all input;
5 ")

Using target database control file instead of recovery catalog allocated channel: c1 channel c1: sid = 301 instance = testrac1 devtype = DISK

Start backup on from November to May -07 current log archived release channel: c1
MAN-00571: ============================================== =============
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ============================================== =============
RMAN-03002: backup plus archivelog command (on 05/11/2007 16:08:09 I) failed
RMAN-06059: expected archived log not found, archive logs will affect the recoverability of the loss of
ORA-19625: recognize the file / data1/archivelog/2_89_618591128.dbf Error
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

RMAN>

Since node 1 to node 2 can not be accessed on the archive, thus giving rise to the above error. In this case, you can use the CONNECT statement with the ALLOCATE to allocate CHANNEL:

RMAN> run
2 "(
3> allocate channel c1 device type disk format '/ data1/backup /% U' connect sys / test @ testrac1;
4> allocate channel c2 device type disk format '/ data1/backup /% U' connect sys / test @ testrac2;
5> backup database plus archivelog delete all input;
6 ")

Using target database control file instead of recovery catalog allocated channel: c1 channel c1: sid = 288 instance = testrac1 devtype = DISK

The distribution channel: c2-channel c2: sid = 307 instance = testrac2 devtype = DISK

Start backup on from November to May -07 current log archived channel c1: Starting archive log backup set channel c1: is specified archive log backup set input archive log thread = 1 sequence = 164 Record timestamp input archive log thread = 622,309,643 = 1 sequence = 165 timestamp = 622,309,764 records input archive log thread = 1 sequence = 166 Record timestamp = 622,310,875 channel c1: Starting in paragraph 1 in the November-May -07 channel c2: Starting archive log backup set channel c2: are specified archive log backup set input archive log thread = 2 sequence = 89 Record timestamp = 622,309,924 input archive log thread = 2 sequence = 90 Record timestamp = 622,310,884 input archive log thread = 2 sequence = 91 channel recording timestamp = 622,312,599 c2: Starting in paragraph 1 in the November-May -07 channel c1: paragraph 1 has been completed in the November-May -07 Duan handle = / data1/backup/26ihff55_1_1 tag = TAG20070511T163652 Comment = NONE channel c1: backup set has been completed , after time: 00:00:56 channel c1: Deleting archive log archive log filename = / data1/archivelog/1_164_618591128.dbf record timestamp = 622,309,643 archive log filename = / data1/archivelog/1_165_618591128.dbf record time stamp = 622,309,764 archive log filename = / data1/archivelog/1_166_618591128.dbf record timestamp = 622,310,875 channel c2: paragraph 1 has been completed in November-May -07 Duan handle = / data1/backup/27ihff51_1_1 tag = TAG20070511T163652 Notes = NONE channel c2: backup set has been completed, after a time: 00:00:58 channel c2: Deleting archive log archive log filename = / data1/archivelog/2_89_618591128.dbf record timestamp = 622,309,924 archive log filename = / data1/archivelog / 2_90_618591128.dbf record timestamp = 622,310,884 archive log filename = / data1/archivelog/2_91_618591128.dbf record timestamp = 622,312,599 channel c1: Starting archive log backup set channel c1: is specified archive log backup set input archive log thread = 1 sequence = 167 Record timestamp = 622,312,603 channel c1: Starting in paragraph 1 in the November-May -07 channel c1: paragraph 1 has been completed in the November-May -07 Duan handle = / data1/backup/28ihff6v_1_1 tag = TAG20070511T163652 Note = NONE channel c1: backup set has been completed, after a time: 00:00:01 channel c1: Deleting archive log archive log filename = / data1/archivelog/1_167_618591128.dbf record timestamp = 622,312,603 to complete backup on the 11 -- May -07

Start backup on from November to May -07 channel c1: Start all the data file backup set channel c1: backup set is being specified input data file data files fno = 00006 name = + DISK/testrac/datafile/zhejiang01.dbf input data file fno = 00009 name = + DISK/testrac/datafile/zhejiang03.dbf input data file fno = 00012 name = + DISK/testrac/datafile/zhejiang05.dbf input data file fno = 00008 name = + DISK/testrac/datafile/ndmain01.dbf input data file fno = 00010 name = + DISK/testrac/datafile/ndindex01.dbf input data file fno = 00001 name = + DISK/testrac/datafile/system.262.618591167 input data file fno = 00005 name = + DISK / testrac / datafile / users.267.618591279 channel c1: Starting in paragraph 1 in the November-May -07 channel c2: Start all the data file backup sets channel c2: backup set is being specified input data file data files fno = 00007 name = + DISK / testrac / datafile/zhejiang02.dbf input data file fno = 00011 name = + DISK/testrac/datafile/zhejiang04.dbf input data file fno = 00014 name = + DISK/testrac/datafile/zhejiang06.dbf input data file fno = 00002 name = + DISK/testrac/datafile/undotbs1.263.618591197 input data file fno = 00004 name = + DISK/testrac/datafile/undotbs2.266.618591249 input data file fno = 00013 name = + DISK/testrac/datafile/lt_index_ts01.dbf input data file fno = 00003 name = + DISK/testrac/datafile/sysaux.264.618591225 channel c2: Starting in paragraph 1 in the November-May -07 channel c2: paragraph 1 has been completed in the November-May -07 Duan handle = / data1/backup / 2aihff6v_1_1 tag = TAG20070511T163754 Comment = NONE channel c2: backup set has been completed, after a time: 00:13:16 channel c2: Start all the data file backup sets channel c2: backup set is being specified data file backup set including current control file channel c2 : Starting from November to May in paragraph 1 in the -07 channel c2: paragraph 1 has been completed in November-May -07 Duan handle = / data1/backup/2bihffvr_1_1 tag = TAG20070511T163754 Comment = NONE channel c2: backup set has been completed, elapsed time: 00:00:03 channel c2: Start all the data file backup sets channel c2: backup set is being specified data files in the backup set that contains the current SPFILE channel c2: Starting in paragraph 1 in the November-May -07 channel c2 : Section 1 has been completed in November-May -07 Duan handle = / data1/backup/2cihffvv_1_1 tag = TAG20070511T163754 Comment = NONE channel c2: backup set has been completed, after a time: 00:00:02 channel c1: Completed section one was from November to May -07 Duan handle = / data1/backup/29ihff73_1_1 tag = TAG20070511T163754 Comment = NONE channel c1: backup set has been completed, after a Time: 00:15:07 to complete backup was from November to May -07

Start backup on from November to May -07 current log archived channel c1: Starting archive log backup set channel c1: is specified archive log backup set input archive log thread = 1 sequence = 168 Record timestamp = 622,313,584 channel c1: is Start segment 1 was from November to May -07 channel c2: Starting archive log backup set channel c2: is specified archive log backup set input archive log thread = 2 sequence = 92 Record timestamp = 622.31358 million channel c2: Starting in paragraph 1 in November-May -07 channel c1: paragraph 1 has been completed in November-May -07 Duan handle = / data1/backup/2dihfg3m_1_1 tag = TAG20070511T165309 Comment = NONE channel c1: backup set has been completed, after a time: 00: 00:01-channel c1: Deleting archive log archive log filename = / data1/archivelog/1_168_618591128.dbf record timestamp = 622,313,584 channel c2: paragraph 1 has been completed in November-May -07 Duan handle = / data1/backup / 2eihfg3h_1_1 tag = TAG20070511T165309 Comment = NONE channel c2: backup set has been completed, after a time: 00:00:02 channel c2: Deleting archive log archive log filename = / data1/archivelog/2_92_618591128.dbf record timestamp = 622.31358 million to complete backup was from November to May -07 release channel: c1 channel release: c2

RMAN>

Using this method can be successfully backup.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

rac rman backup Note

kods.net — Wed, 02 Dec 2009 02:05:02+0000

rman backup, maintenance of database can be connected to an instance can be backed up, but the backup archivelog, must be connected to the
rac all instances.
rn
Please refer to the following script (note that the following script uses the tape library, and if it is prepared to a disk file system, type is
disk in, env parameters depending on the backup software, modify the backup to disk, they no longer needed)
db backup
run
(
allocate channel t1 type 'sbt_tape' parms
'ENV = (TDPO_OPTFILE = / usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup
filesperset 5
format 'df_% t_% s_% p'
(database);
release channel t1;
)
Archive Backup
run
(
allocate channel t1 type 'sbt_tape' connect sys / oracle @ ORCL1 parms
'ENV = (TDPO_OPTFILE = / usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' connect sys / oracle @ ORCL2 parms
'ENV = (TDPO_OPTFILE = / usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup archivelog all delete input;
sql 'alter system archive log current';
release channel t1;
release channel t2;
)

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle Classic Q & A

kods.net — Thu, 12 Nov 2009 07:25:52+0000

01. Oracle installation is complete the initial password?

02. ORACLE9IAS WEB CACHE initial default user and password?

03. Oracle 8.0.5 how to create database?

04. Oracle 8.1.7 how to create database?

05. Oracle 9i how to create database?

06. Oracle raw device in what is meant?

07. Oracle how to distinguish 64-bit/32bit version? ? ?

08. SVRMGR What does it mean?

09. I ask how to tell the machine where a user is a landing ORACLE it?

10. What statement to query a field in it?

11. How to get triggers, processes, functions to create a script?

12. How to calculate a table the size of the space?

13. How do I see the largest number of sessions?

14. How do I view the system locked up the transaction time?

15. Archivelog way how to run the oracle.

16. How to obtain what the user using the database

17. Data in the table what is the maximum number of fields?

18. How Richard database SID?

19. How Oracle server through SQLPLUS see local IP address?

20. Unix how to adjust the database the next time?

21. ORACLE TABLE how to crawl MEMO type field is empty data record?

22. How to use the information to update the BBB table AAA table information (associated field)

23. P4 Computer Installation Method

24. He query SERVER is OPS?

25. He check the permissions for each user?

26. How to move tablespace the table?

27. How to move the index table space?

28. Under LINUX, UNIX how to activate the DBA STUDIO?

29. Check the status of the lock object has?

30. How do I unlock?

31. SQLPLUS how to modify the editor?

32. ORACLE generates a random function?

33. LINUX competition in the next check disk command?

33. LINUX check CPU under the state of competition in order?

34. Query the current user object?

35. How do I get the error message?

36. How to obtain the link status?

37. View the database character situation?

38. Query table space information?

39. ORACLE's INTERAL users to password?

40. JAVA.EXE there a solution?

41. How to tables, columns, add a comment?

42. How do I view all the table space occupied by the disk situation?

43. If the ORACLE set to MTS or dedicated mode?

44. How can I know the system's current SCN number?

45. I ask how to get milliseconds in ORACLE?

46. How to Enter the string Riga?

47. Chinese is how to sort it?

48. Oracle8i object name can be in Chinese?

49. How do I change the WIN in the SQL * Plus startup options?

50. Oracel How to modify the database's default date?

51. How to keep a small table into the pool?

52. How to check whether you have a patch?

53. How to make the select statement to query results automatically generated serial number?

54. How do you know the data to a table where the pants in the tablespace?

55. How you can quickly make a backup of the same as the original table and the table?

55. Sqlplus, how to modify the procedure?

56. How to lift PROCEDURE was accidentally locked?

57. SQL Reference is what?

58. How do I view the status of the database?

59. I ask how to modify a table's primary key?

60. To change the data file size?

61. How to view the ORACLE What are the procedures for running into?

62. How you can see the number of database tablespace?

63. How to modify the oracle database, the number of user connections?

64. How do I find out a record last updated?

65. How PL / SQL to read and write files?

66. How to "&" into a record?

67. EXP how that can be QUERY parameter?

68. With regard to oracle8i for Simplified and Traditional character set problem?

69. Data Guard What is software?

70. How do I create a SPFILE?

71. Application of the kernel parameters?

72. How to see which users have SYSDBA, SYSOPER privileges?

73. How do I back up one or more separate tables?

74. How do I back up a single or multiple users?

75. How to CLOB fields full-text search?

76. How to display the currently connected users?

77. How do I view the data files placed in the path?

78. How do I view the current rollback its state?

79. How do I change a field, Check the definition of the scope of the original?

80. Oracle system files used what?

81. In connection INNER JOIN?

82. How to outer join?

83. How to implement the script SQL files?

84. How to quickly empty a large table?

85. How do I check the number of database instances?

86. How do I query the database the number of tables?

87. How to test SQL statements used in the implementation of the time?

88. CHR () function is the anti-?

89. String connection

90. How to select, the results lead to a text file?

91. How to estimate SQL execution I / O number?

92. How to change the field size of sqlplus?

93. How to query data in one day?

94. Sql statement, how to insert a whole date?

95. If you modify the table name?

96. How do I get command returns the status value?

97. How do you know the user has permissions?

98. ORACLE9I downloaded from the Internet on the market sell the standard version of What's the difference?

99. How do we judge the database is running in archive mode or to run in non archive mode?

100. Sql> startup pfile and ifile, spfiled What is the difference?

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

1. Oracle installation is complete the initial password?

internal / oracle

sys / change_on_install

system / manager

scott / tiger

sysman / oem_temp

2. ORACLE9IAS WEB CACHE initial default user and password?

administrator / administrator

3. Oracle 8.0.5 how to create database?

With orainst. If you have motif interface, you can use orainst / m

4. Oracle 8.1.7 how to create database?

dbassist

5. Oracle 9i how to create database?

dbca

6. Oracle raw device in what is meant?

Raw device to bypass the file system is a direct access storage space

7. Oracle how to distinguish 64-bit/32bit version? ? ?

$ Sqlplus '/ AS SYSDBA'

SQL * Plus: Release 9.0.1.0.0 - Production on Mon Jul 14 17:01:09 2003

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

SQL> select * from v $ version;

BANNER

-------------------------------------------------- --------------

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

PL / SQL Release 9.0.1.0.0 - Production

CORE 9.0.1.0.0 Production

TNS for Solaris: Version 9.0.1.0.0 - Production

NLSRTL Version 9.0.1.0.0 - Production

SQL>

8. SVRMGR What does it mean?

svrmgrl, Server Manager.

Under 9i not, had been replaced with a SQLPLUS

sqlplus / nolog

Into a type of archive log

9. I ask how to tell the machine where a user is a landing ORACLE it?

SELECT machine, terminal FROM V $ SESSION;

10. What statement to query a field in it?

desc table_name can query the structure of the table

select field_name, ... from ... you can query the value of the field

select * from all_tables where table_name like '%'

select * from all_tab_columns where table_name ='??'

11. How to get triggers, processes, functions to create a script?

desc user_source

user_triggers

12. How to calculate a table the size of the space?

select owner, table_name,

NUM_ROWS,

BLOCKS * AAA/1024/1024 "Size M",

EMPTY_BLOCKS,

LAST_ANALYZED

from dba_tables

where table_name = 'XXX';

Here: AAA is the value of db_block_size;

XXX is the table name you want to check

13. How do I see the largest number of sessions?

SELECT * FROM V $ PARAMETER WHERE NAME LIKE 'proc%';

SQL>

SQL> show parameter processes

NAME TYPE VALUE

------------------------------------ ------- ------- -----------------------

aq_tm_processes integer 1

db_writer_processes integer 1

job_queue_processes integer 4

log_archive_max_processes integer 1

processes integer 200

Here for 200 users.

select * from v $ license;

Sessions_highwater which records the maximum number of sessions has been reached

14. How do I view the system locked up the transaction time?

select * from v $ locked_object;

15. Archivelog way how to run the oracle.

init.ora

log_archive_start = true

RESTART DATABASE

16. How to obtain what the user using the database

select username from v $ session;

17. Data in the table what is the maximum number of fields?

Table or view, the maximum number of columns in 1000

18. How Richard database SID?

select name from v $ database;

Can also directly view the file init.ora

19. How Oracle server through SQLPLUS see local IP address?

select sys_context ( 'userenv', 'ip_address') from dual;

If it is landing the aircraft database, only to return 127.0.0.1, He He

20. Unix how to adjust the database the next time?

su-root

date-u 08010000

21. ORACLE TABLE how to crawl MEMO type field is empty data record?

select remark from oms_flowrec where trim ( '' from remark) is not null;

22. How to use the information to update the BBB table AAA table information (associated field)

UPDATE AAA SET BNS_SNM = (SELECT BNS_SNM FROM BBB WHERE AAA.DPT_NO = BBB.DPT_NO) WHERE BBB.DPT_NO IS NOT NULL;

23. P4 Computer Installation Method

SYMCJIT.DLL into the SYSMCJIT.OLD

24. He query SERVER is OPS?

SELECT * FROM V $ OPTION;

If the PARALLEL SERVER = TRUE, there OPS can be

25. He check the permissions for each user?

SELECT * FROM DBA_SYS_PRIVS;

26. How to move tablespace the table?

ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;

27. How to move the index table space?

ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;

28. Under LINUX, UNIX how to activate the DBA STUDIO?

OEMAPP DBASTUDIO

29. Check the status of the lock object has?

V $ LOCK, V $ LOCKED_OBJECT, V $ SESSION, V $ SQLAREA, V $ PROCESS;

Query lock table approach:

SELECT S. SID SESSION_ID, S. USERNAME, DECODE (LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S / Row-X (SSX)', 6,

'Exclusive', TO_CHAR (LMODE)) MODE_HELD, DECODE (REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4 , 'Share', 5, 'S / Row-X (SSX)',

6, 'Exclusive', TO_CHAR (REQUEST)) MODE_REQUESTED, O. OWNER ||'.'|| O. OBJECT_NAME | | '(' | | O. OBJECT_TYPE ||')', S. TYPE LOCK_TYPE, L.ID1 LOCK_ID1,

L.ID2 LOCK_ID2 FROM V $ LOCK L, SYS.DBA_OBJECTS O, V $ SESSION S WHERE L. SID = S. SID AND L.ID1 = O. OBJECT_ID;

30. How do I unlock?

ALTER SYSTEM KILL SESSION 'SID, SERIR #';

31. SQLPLUS how to modify the editor?

DEFINE _EDITOR = "
To define a new editor, this can also be written in the $ ORACLE_HOME / sqlplus / admin / glogin.sql inside to make it permanent.

32. ORACLE generates a random function?

DBMS_RANDOM.RANDOM

33. LINUX competition in the next check disk command?

Sar-d

33. LINUX check CPU under the state of competition in order?

sar-r

34. Query the current user object?

SELECT * FROM USER_OBJECTS;

SELECT * FROM DBA_SEGMENTS;

35. How do I get the error message?

SELECT * FROM USER_ERRORS;

36. How to obtain the link status?

SELECT * FROM DBA_DB_LINKS;

37. View the database character situation?

SELECT * FROM NLS_DATABASE_PARAMETERS;

SELECT * FROM V $ NLS_PARAMETERS;

38. Query table space information?

SELECT * FROM DBA_DATA_FILES;

39. ORACLE's INTERAL users to password?

Changes SQLNET.ORA

SQLNET.AUTHENTICATION_SERVICES = (NTS)

40. JAVA.EXE there a solution?

Generally is ORACLEORAHOMEXIHTTPSERVER manual activation can change the

X is 8 or 9

41. How to tables, columns, add a comment?

SQL> comment on table the table is 'table comment';

Note has been created.

SQL> comment on column table. Column is 'column comment';

Note has been created.

SQL> select * from user_tab_comments where comments is not null;

42. How do I view all the table space occupied by the disk situation?

SQL> col tablespace format a20

SQL> select

b.file_id document ID number,

b.tablespace_name the table space name,

b.bytes bytes

(b.bytes-sum (nvl (a.bytes, 0))) has been used

sum (nvl (a.bytes, 0)) the remaining space,

sum (nvl (a.bytes, 0)) / (b.bytes) * 100 percentage of the remaining

from dba_free_space a, dba_data_files b

where a.file_id = b.file_id

group by b.tablespace_name, b.file_id, b.bytes

order by b.file_id

43. If the ORACLE set to MTS or dedicated mode?

# dispatchers = "(PROTOCOL = TCP) (SERVICE = SIDXDB)"

Plus is the MTS, note that is, exclusive model, SID is your instance name.

44. How can I know the system's current SCN number?

select max (ktuxescnw * power (2, 32) + ktuxescnb) from x $ ktuxe;

45. I ask how to get milliseconds in ORACLE?

Prior to 9i do not support, 9i began to timestamp.

9i can select systimestamp from dual;

ORACLE in the application when you may encounter a lot of problems seem difficult, especially for novice, and today I simply put it to sum up, release to you, hope all of us help! And everyone to explore, a total of

With the progress!

ORACLE is not on the master to read.

46. How to Enter the string Riga?

select 'Welcome to visit' | | chr (10) | | 'www.CSDN.NET' from dual;

47. Chinese is how to sort it?

Prior to Oracle9i, the Chinese is based on binary coding to sort.

Example: Set shmsys: shminfo_shmseg = 20:

semmni

Meaning: the system the maximum number of semaphore identifierer.

Set Methods: The value of this variable is set for this system, all Oracle instances in the init.ora of the largest that processes that increase the value of 10.

Example: Set semsys: seminfo_semmni = 100

semmns

Meaning: the maximum number of emaphores system.

Setting Method: This value can be calculated by the following ways: inside each Oracle instance initSID.ora the sum of the value of the processes (except the largest Processes parameters) + the largest that

Processes × 2 +10 × Oracle instance number.

Example: Set semsys: seminfo_semmns = 200

semmsl:

Meanings: one set in the maximum number of semaphore.

Set Method: Set a 10 + for all Oracle instances InitSID.ora largest Processes value.

Example: Set semsys: seminfo_semmsl =- 200

72. How to see which users have SYSDBA, SYSOPER privileges?

SQL> conn sys / change_on_install

SQL> select * from V_ $ PWFILE_USERS;

73. How do I back up one or more separate tables?

exp user / password tables = (table 1, ..., table 2)

74. How do I back up a single or multiple users?

exp system / manager owner = (User 1, user 2, ..., user n) file = Export File

75. How to CLOB fields full-text search?

SELECT * FROM A WHERE dbms_lob.instr (aa, 'K', 1,1)> 0;

76. How to display the currently connected users?

SHOW USER

77. How do I view the data files placed in the path?

col file_name format a50

SQL> select tablespace_name, file_id, bytes/1024/1024, file_name from dba_data_files order by file_id;

78. How do I view the current rollback its state?

SQL> col segment format a30

SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS FROM DBA_ROLLBACK_SEGS

79. How do I change a field, Check the definition of the scope of the original?

SQL> alter table xxx drop constraint constraint_name;

After creating a new constraint:

SQL> alter table xxx add constraint constraint_name check ();

80. Oracle system files used what?

Through the following view shows the file information: v $ database, v $ datafile, v $ logfile v $ controlfile v $ parameter;

81. In connection INNER JOIN?

Select a. * from bsempms a, bsdptms b where a.dpt_no = b.dpt_no;

82. How to outer join?

Select a. * from bsempms a, bsdptms b where a.dpt_no = b.dpt_no (+);

Select a. * from bsempms a, bsdptms b wherea.dpt_no (+) = b.dpt_no;

83. How to implement the script SQL files?

SQL> @ $ PATH / filename.sql;

84. How to quickly empty a large table?

SQL> truncate table table_name;

85. How do I check the number of database instances?

SQL> SELECT * FROM V $ INSTANCE;

86. How do I query the database the number of tables?

SQL> select * from all_tables;

87. How to test SQL statements used in the implementation of the time?

SQL> set timing on;

SQL> select * from tablename;

ORACLE in the application when you may encounter a lot of problems seem difficult, especially for novice, and today I simply put it to sum up, release to you, hope all of us help! And everyone to explore, a total of

With the progress!

ORACLE is not on the master to read.

88. CHR () function is the anti-?

ASCII ()

SELECT CHAR (65) FROM DUAL;

SELECT ASCII ( 'A') FROM DUAL;

89. String connection

SELECT CONCAT (COL1, COL2) FROM TABLE;

SELECT COL1 | | COL2 FROM TABLE;

90. How to select, the results lead to a text file?

SQL> SPOOL C: \ ABCD.TXT;

SQL> select * from table;

SQL> spool off;

91. How to estimate SQL execution I / O number?

SQL> SET AUTOTRACE ON;

SQL> SELECT * FROM TABLE;

OR

SQL> SELECT * FROM v $ filestat;

You can view the number of IO

92. How to change the field size of sqlplus?

alter table table_name modify (field_name varchar2 (100));

Change lines, Gaixiao not work (unless is empty)

93. How to query data in one day?

select * from table_name where trunc (date field) = to_date ('2003-05-02 ',' yyyy-mm-dd ');

94. Sql statement, how to insert a whole date?

create table BSYEAR (d date);

insert into BSYEAR

select to_date ('20030101 ',' yyyymmdd ') + rownum-1

from all_objects

where rownum <= to_char (to_date ('20031231 ',' yyyymmdd '),' ddd ');

95. If you modify the table name?

alter table old_table_name rename to new_table_name;

96. How do I get command returns the status value?

sqlcode = 0

97. How do you know the user has permissions?

SELECT * FROM dba_sys_privs;

98. ORACLE9I downloaded from the Internet on the market sell the standard version of What's the difference?

From the function that there is no difference, but the company has expressly provided oracle; from the oracle site to download product not be used for commercial purposes, or infringement.

99. How do we judge the database is running in archive mode or to run in non archive mode?

Into dbastudio, history - "Database ---" archive view.

100. Sql> startup pfile and ifile, spfiled What is the difference?

pfile is a traditional Oracle initialization parameter file, the text format.

ifile is similar to c language inside include, for the introduction of another file

spfile is where the new 9i and is the default parameter files, binary format

startup should only be received after the pfile

modify sys password oracle 9i 2009-04-06 08:09:07
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
Block Oracle paragraph (reprint) 2009-03-26 17:41:14
Oracle XDB relax resolve port conflict of 8080 2009-03-13 16:47:42

RAC environment, the database backup (2)

kods.net — Mon, 15 Dec 2008 14:55:53+0000

rn
RAC environment, backup, recovery and general database backup, recovery is some difference.

rn
RMAN Backup and Recovery - RAC environment database backup (1): http://yangtingkun.itpub.net/post/468/285260

rn
This description is based on the bare device backup RAC environment.

rn

rn

rn
For the establishment of the bare device RAC environment can not be stored due to the raw device file, so that all of the archive must be stored in local disk. RMAN backups directly lead to errors.

rn
First of all, the bare equipment mimic the situation, modify the original LOG_ARCHIVE_DEST_1 configuration file for each instance of change in the local hard disk:

rn
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION = / data1/archivelog' SID = 'testrac1';

rn
System has been changed.

rn
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION = / data1/archivelog' SID = 'testrac2';

rn
System has been changed.

rn
And then try to back up the database directly:

rn
$ Rman target /

rn
Recovery Manager: Release 10.2.0.3.0 - Production on Fri May 11 16:06:48 2007

rn
Copyright (c) 1982, 2005, Oracle. All rights reserved.

rn
Connected to target database: TESTRAC (DBID = 4291216984)

rn
RMAN> run
2> (
3> allocate channel c1 device type disk format '/ data1/backup /% U';
4> backup database plus archivelog delete all input;
5>)

rn
Using target database control file instead of recovery catalog allocated channel: c1 channel c1: sid = 301 instance = testrac1 devtype = DISK

rn
Start the backup in the November-May -07 current log archived release channel: c1
MAN-00571: ============================================== =============
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ============================================== =============
RMAN-03002: backup plus archivelog command (on the 05/11/2007 16:08:09) failed
RMAN-06059: expected archived log not found, the log file will affect the recoverability of the loss
ORA-19625: Identification documents / data1/archivelog/2_89_618591128.dbf Error
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

rn
RMAN>

rn
Since node 1 to node 2 can not access the archive, so there's an error above. In this case, you can use the CONNECT statement with the ALLOCATE to allocate CHANNEL:

rn
RMAN> run
2> (
3> allocate channel c1 device type disk format '/ data1/backup /% U' connect sys / test @ testrac1;
4> allocate channel c2 device type disk format '/ data1/backup /% U' connect sys / test @ testrac2;
5> backup database plus archivelog delete all input;
6>)

rn
Using target database control file instead of recovery catalog allocated channel: c1 channel c1: sid = 288 instance = testrac1 devtype = DISK

rn
Distribution channel: c2 channel c2: sid = 307 instance = testrac2 devtype = DISK

rn
Start the backup in the November-May -07 current log archived channel c1: Starting archive log backup set channel c1: backup set is being specified input archive log archive log thread = 1 sequence = 164 stamp = 622309643 input archive records log thread = 1 sequence = 165 records the time stamp = 622309764 input archive log thread = 1 sequence = 166 records the time stamp = 622310875 channel c1: Starting in paragraph 1 in the November-May -07 channel c2: Starting archive log backup set channel c2: is specified archive log backup set input archive log thread = 2 sequence = 89 stamp = 622309924 input archive records log thread = 2 sequence = 90 stamp = 622310884 input archive records log thread = 2 sequence = 91 stamp = 622312599 channel recording c2: Starting in paragraph 1 in the November-May -07 channel c1: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup/26ihff55_1_1 tag = TAG20070511T163652 comment = NONE channel c1: backup set has been completed through time: 00:00:56 channel c1: Deleting archive log archive log filename = / data1/archivelog/1_164_618591128.dbf record time stamp = 622309643 archive log filename = / data1/archivelog/1_165_618591128.dbf record time stamp = 622309764 archive log filename = / data1/archivelog/1_166_618591128.dbf record time stamp = 622310875 channel c2: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup/27ihff51_1_1 tag = TAG20070511T163652 comment = NONE channel c2: backup set has been completed, elapsed time: 00:00:58 channel c2: Deleting archive log archive log filename = / data1/archivelog/2_89_618591128.dbf record time stamp = 622309924 archive log filename = / data1/archivelog / 2_90_618591128.dbf record time stamp = 622310884 archive log filename = / data1/archivelog/2_91_618591128.dbf record time stamp = 622312599 channel c1: Starting archive log backup set channel c1: is specified archive log backup set input archive log thread = 1 sequence = 167 records the time stamp = 622312603 channel c1: Starting in paragraph 1 in the November-May -07 channel c1: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup/28ihff6v_1_1 tag = TAG20070511T163652 comment = NONE channel c1: backup set has been completed, elapsed time: 00:00:01 channel c1: Deleting archive log archive log filename = / data1/archivelog/1_167_618591128.dbf record timestamp = 622 312 603 complete backup in the 11 - May -07

rn
Start the backup in the November-May -07 channel c1: backup set data file start the All Channel c1: backup set is the data file specified input data file fno = 00006 name = + DISK/testrac/datafile/zhejiang01.dbf input data file fno = 00009 name = + DISK/testrac/datafile/zhejiang03.dbf input data file fno = 00012 name = + DISK/testrac/datafile/zhejiang05.dbf input data file fno = 00008 name = + DISK/testrac/datafile/ndmain01.dbf input data file fno = 00010 name = + DISK/testrac/datafile/ndindex01.dbf input the data file fno = 00001 name = + DISK/testrac/datafile/system.262.618591167 enter the data file fno = 00005 name = + DISK / testrac / datafile / users.267.618591279 channel c1: Starting in paragraph 1 in the November-May -07 channel c2: backup set data file start the All channel c2: backup set is being specified input data file data file fno = 00007 name = + DISK / testrac / datafile/zhejiang02.dbf input data file fno = 00011 name = + DISK/testrac/datafile/zhejiang04.dbf input data file fno = 00014 name = + DISK/testrac/datafile/zhejiang06.dbf input data file fno = 00002 name = + DISK/testrac/datafile/undotbs1.263.618591197 input data file fno = 00004 name = + DISK/testrac/datafile/undotbs2.266.618591249 input data file fno = 00013 name = + DISK/testrac/datafile/lt_index_ts01.dbf input data file fno = 00003 name = + DISK/testrac/datafile/sysaux.264.618591225 channel c2: Starting in paragraph 1 in the November-May -07 channel c2: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup / 2aihff6v_1_1 tag = TAG20070511T163754 comment = NONE channel c2: backup set has been completed, elapsed time: 00:13:16 channel c2: backup set data file start the All channel c2: backup set is being specified data file backup set including current control file channel c2 : Starting in paragraph 1 in the November-May -07 channel c2: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup/2bihffvr_1_1 tag = TAG20070511T163754 comment = NONE channel c2: backup set has been completed, elapsed time: 00:00:03 channel c2: backup set data file start the All channel c2: backup set is being specified data file in the backup set that contains the current SPFILE channel c2: Starting in paragraph 1 in the November-May -07 channel c2 : paragraph 1 has been completed in November-May -07 segment handle = / data1/backup/2cihffvv_1_1 tag = TAG20070511T163754 comment = NONE channel c2: backup set has been completed, elapsed time: 00:00:02 channel c1: Completed section paragraph 1 in November-May -07 handle = / data1/backup/29ihff73_1_1 tag = TAG20070511T163754 comment = NONE channel c1: backup set has been completed, elapsed time: 00:15:07 to complete backup in November-May -07

rn
Start the backup in the November-May -07 current log archived channel c1: Starting archive log backup set channel c1: backup set is being specified input archive log archive log thread = 1 sequence = 168 stamp = 622313584 channel recording c1: is Start segment 1 in November-May -07 channel c2: Starting archive log backup set channel c2: is specified archive log backup set input archive log thread = 2 sequence = 92 stamp = 622313580 channel recording c2: Starting in paragraph 1 in November to May -07 channel c1: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup/2dihfg3m_1_1 tag = TAG20070511T165309 comment = NONE channel c1: backup set has been completed, elapsed time: 00: 00:01 channel c1: Deleting archive log archive log filename = / data1/archivelog/1_168_618591128.dbf record time stamp = 622313584 channel c2: paragraph 1 has been completed paragraph in the November-May -07 handle = / data1/backup / 2eihfg3h_1_1 tag = TAG20070511T165309 comment = NONE channel c2: backup set has been completed, elapsed time: 00:00:02 channel c2: Deleting archive log archive log filename = / data1/archivelog/2_92_618591128.dbf complete record time stamp = 622313580 released in November to May -07 backup channel: c1 channel release: c2

rn
RMAN>

rn
After using this method can successfully backup.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Manually DB Creation with 11G

kods.net — Thu, 17 Jul 2008 11:01:29+0000

Manually database creation is one of the more frequently performed actions of a professional DBA. And, with Oracle 11g it only takes a few minutes to do:)

The procedure is same as previous which we are used for 9i and 10g.

In this database creation I used below features:

1. OMF (Oracle Managed File) for datafiles, redolog files & controlfiles
2. FRA (Flash Recovery Area) for Archivelog or backup files
3. ASM (Automatic Storage Mgmt) for Volume manager

1. Create Required Directories

E:\>mkdir e:\oracle\ORA11G

NOTE: above directory is for TRACE FOLDER.

And for ASM I already created DGRP2 group for database.

2. Set Enviourment Variable (ORACLE_HOME, ORACLE_SID, PATH)

E:\>set ORACLE_HOME=c:\app\m.taj\product\11.1.0\db_1
E:\>set PATH=c:\app\m.taj\product\11.1.0\db_1\bin
E:\>set ORACLE_SID=ora11g

3. Choose a Database Administrator Authentication Method

Two authentication method we can use.
1. Password Authentication method
For Password Authentication method we need to create PASSWORD file through ORAPWD utility.

2. OS Authentication method
For OS Authentication method OS user must be member of OS DBA Group.
And SQLNET.AUTHENTICATION_SERVICES = (NTS) is set in SQLNET.ORA file

NOTE: Here I used OS authentication Method.

3. Creating INIT.ORA parameter file
Set minimum required parameter in INIT.ora file.

Open NOTEPAD file and set below parameters

db_name = 'ORA11G'
#Database name.

db_create_file_dest='+DGRP2'
#OMF configuration for Datafile,controlfile
db_create_online_log_dest_1='+DGRP2'
#OMF configuration for redolog file 

db_recovery_file_dest='+DGRP2'
db_recovery_file_dest_size=10G
#FRA (FLASH RECOVERY AREA configuration)

diagnostic_dest='E:\ORACLE\ORA11G'
#It is new feature with 11g for trace files (bdump,udump,cdump or many others
#folder created in "DIAG" folder inside "e:\oracle\ora11g" folder.

Save it in Temporary folder.
4. Create an Instance

E:\>oradim -NEW -SID ora11g -STARTMODE auto
Instance created.

5. Connect to an Instance

E:\>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 1 18:04:30 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.

6. Create Server Parameter file (SPFILE)

SQL> create SPFILE from PFILE='c:\temp\init.TXT';
File created.

7. Start the Instance

SQL> startup nomount

ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1331740 bytes
Variable Size 92278244 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes

8. Create Database

SQL> create database ORA11G;
Database created.

9. Create Temporary and Additional Tablespace

SQL> create temporary tablespace TEMP TEMPFILE '+DGRP2' size 5m;
Tablespace created.
SQL> create tablespace USERDATA DATAFILE '+DGRP2' size 5m;
Tablespace created.
SQL> alter database default temporary tablespace TEMP;
Database altered.
SQL> alter database default tablespace USERDATA;
Database altered.

10. Run Scripts to build data dictionary views

SQL> @%ORACLE_HOME%/rdbms/admin/catalog.sql
SQL> @%ORACLE_HOME%/rdbms/admin/catproc.sql
SQL> @%ORACLE_HOME%/sqlplus/admin/pupbld.sql

11. Change Database Mode from NO-ARCHIVELOG to ARCHIVELOG.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1331740 bytes
Variable Size 92278244 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
SQL> alter database ARCHIVELOG;
Database altered.
SQL> alter database OPEN;
Database altered.

NOTE: It is recommended to run database in ARCHIVELOG MODE

12. Check all database files how is look in ASM:)

SQL> select NAME from v$datafile 
2 union all 
3 select NAME from v$controlfile 
4 union all 
5 select MEMBER from v$logfile;
NAME
--------------------------------------------------
+DGRP2/ora11g/datafile/system.281.653595347
+DGRP2/ora11g/datafile/sysaux.280.653595371
+DGRP2/ora11g/datafile/sys_undots.279.653595393
+DGRP2/ora11g/datafile/userdata.273.653595537
+DGRP2/ora11g/controlfile/current.285.653595303
+DGRP2/ora11g/onlinelog/group_1.283.653595305
+DGRP2/ora11g/onlinelog/group_2.282.653595325
7 rows selected.

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
RMAN created physical standby 2009-03-30 22:49:29
Oracle 10 client under linux installation instructions at 2009-03-14 11:43:46
AX3.0 + Oracle 10G RAC installation 2009-03-12 19:19:31

ORA-00265 archivelog can not set the error analysis

kods.net — Fri, 06 Jun 2008 07:43:01+0000

ORA-00265 archivelog can not set the error analysis

2007-09-25 12:31

Required routine recovery, can not set ARCHIVELOG mode!

SQL> alter database archivelog;

alter database archivelog

*

ERROR at line 1:

ORA-00265: requested routine recovery, can not set ARCHIVELOG mode

Solution:

SQL> shutdown immediate

ORA-01109: database not open

Database has been uninstalled.

ORACLE routines have been shut down.

SQL> startup restrict ---- Note that this step is necessary.

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Completion of the loading database.

Database has been opened.

SQL> shutdown

Database closed.

Database has been uninstalled.

ORACLE routines have been shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Completion of the loading database.

SQL> alter database archivelog;

Database changed

Switching archiving mode command

alter database noarchivelog;

alter database archivelog;

Automatic archiving mode command switch

alter system archive log stop;

alter system archive log start;

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Archive mode changes. Query

kods.net — Sat, 20 May 2006 11:56:21+0000

Change oracle database archiving modes:
rn
1.shutdown immediate
2.startup mount
3.alter database archivelog
4.alter database open
rn
Replaced by automatic archiving:
rn
alter system set log_archive_start = true scope = spfile;
rn
Search archived information: archive log list

rn

rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

RAC database archiving mode change

kods.net — Fri, 21 Apr 2006 16:23:58+0000

1 Use alter system set CLUSTER_DATABASE = FALSE SCOPE = SPFILE will cluster_database initialization parameter is set to false

2 If it is converted by non-archive mode archive mode, need to pay attention LOG_ARCHIVE_DEST_n, LOG_ARCHIVE_FORMAT parameters set up correctly, LOG_ARCHIVE_START is set to true. LOG_ARCHIVE_FORMAT should be included in% t parameter to verify that the generated archive file contains archived log thread number.

3 Close all instances,

4 start to mount an instance of the state

5 change the archive mode for archivelog or noarchivelog Mode

SQL> ALTER DATABASE ARCHIVELOG

Either

SQL> ALTER DATABASE NOARCHIVELOG

6 will be CLUSTER_DATABASE change the true

7 Close all instances of

8 to start all instances, then the database is in archive mode and non-archive mode.

9 using the archive log list archive verification status, to confirm the change successfully

Release undo tablespace space 2009-04-08 17:50:03
Oracle's db_name and instance_name 2009-04-08 17:29:21
setting to discuss filing oracle10g 2009-04-02 05:19:08
Oracle deadlock related 2009-03-30 16:46:54
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17

RAC environment, modify the way oracle archive

kods.net — Mon, 13 Mar 2006 16:31:10+0000

RAC environment, modify the way the backup process archive

1. Backup init file.
> Create pfile = '/ oracle/init0728.ora' from spfile
2. Modify the archive mode, the location of log files are stored:
> Alter system set log_archive_dest_1 = 'LOCATION = / oracle / archive' scope = spfile;
> Alter system log_archive_start = true scope = spfile;
> alter system log_archive_max_processes = 2 scope = spfile;
> alter system set cluster_datbase = false scope = spfile;
3. Restart the database to open archive library
> shutdown immediate;
> startup mount;> alter database archivelog;
> alter database open;
> archive log list;
> Alter system set cluster_database = true scope = spfile;
4. Restart the database
> shutdown immediate;
> startup;
> archive log list look at the archive situation.
> alter system switch logfile; manually cut once archive

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle's archivelog question

kods.net — Wed, 01 Mar 2006 13:14:17+0000

A few days ago found that one of the DB to use Oracle9i period of time, disk space is now being depleted, 6G of free resources, just two days exhausted, and began to drive has always been thought too small, this morning in the other Two DB on an investigation had discovered each DB has been consume at least 40G of space over the long run, before long, this two are not working.

Use du-sh * search a bit directory space, was originally in the archive directory, checking a bit of information, the original is the archive log function, I do not need this feature that sets DB, so it can be removed, but how to stop "archive model "it, to the net after searching through all the information has not only tells how to" non-archive log mode "to" archive mode ", by:

SQL> shutdown;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination / home / oracle / archive / webdb
Oldest online log sequence 193
Next log sequence to archive 195
Current log sequence 195

Since we have changed the non-archive mode, is not put into archivelog else does, to try several terms, was originally noarchivelog, finished after which they changed the automatic archive mode disable, methods are:

SQL> alter system archive log stop;

But also changed a bit $ ORACLE_HOME / dbs / initv $ instance.ora, the log_archive_start = true into a no, this is like the next, and finally check bar.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / home / oracle / archive / webdb
Oldest online log sequence 193
Current log sequence 195

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle 8.1.6 in a resume!

kods.net — Wed, 12 Oct 2005 20:00:20+0000

August 15, 2006 received a customer's cell phone the afternoon, saying that a data file to a 4GB in the resize the data after the file is not available! Reported that the Executive recover datafile failed ora-283's fault! We need to immediately to the scene to help restore! In fact, this customer a year ago we talked about the root Oracle to maintain / upgrade the case, dragged on for nearly half of the then quit altogether!
rn

rn
To see how the customer environment as: oracle8.1.6.0.0, windows2000
rn
View alert log records are as follows:
rn

rn
Media Recovery Datafile: 'F: \ oradata \ WIPMLCC \ MLCC02.DBF'
Media Recovery Start
Media Recovery Log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 3128 Reading mem 0
Mem # 0 errs 0: F: \ ORADATA \ WIPMLCC \ REDO02.LOG
Tue Aug 15 17:04:47 2006
KCF: write / open error block = 0x38683 online = 0
file = 11 F: \ ORADATA \ WIPMLCC \ MLCC02.DBF
error = 27069 txt: 'OSD-04026: ???????. (OS 231043)'
KCF: write / open error block = 0x37f3a online = 0
file = 11 F: \ ORADATA \ WIPMLCC \ MLCC02.DBF
error = 27069 txt: 'OSD-04026: ???????. (OS 229178)'
KCF: write / open error block = 0x387e6 online = 0
file = 11 F: \ ORADATA \ WIPMLCC \ MLCC02.DBF
error = 27069 txt: 'OSD-04026: ???????. (OS 231398)'
KCF: write / open error block = 0x385db online = 0
rn
.................................................. .......
rn
.................................................. .......
rn

rn
KCF: write / open error block = 0x3824d online = 0
file = 11 F: \ ORADATA \ WIPMLCC \ MLCC02.DBF
error = 27069 txt: 'OSD-04026: ???????. (OS 229965)'
Tue Aug 15 17:04:53 2006
Media Recovery failed with error 1579
ORA-283 signalled during: ALTER DATABASE RECOVER
rn
datafile 'F: \ oradata \ ...

rn
According to metalink interpretation of Oracle816 the bug! The only way is to restore a database to resize the moment before! But the client only in October 2005 in a cold backup! archivelog has in recent months back! No hot full backup, archivelog not have access to! Finally with the customer that can only lose the data files!
rn

rn
8 / 16, again early in the morning customer calls to say that Taiwan's side of the person who asked the Oracle database can be upgraded to 8.1.6.3.8 may be able to recover datafile!
rn

rn
Also to the client site, started to metalink download Patch! Metalink download the patch rate remained very stable in the 14-15K / s! 65MB of Patch DownLoad more than an hour!
rn

rn
Creating a test to restore the environment, playing patch, first hit 8.1.6.3.0 patch through the universal install successfully the installation is complete, open db implementation catalog.sql catproc.sql two scripts! 8.1.6.3.8 of the Patch and then hit copy some files under the Readme file to the specified directory can be!
rn

rn
Re-open the db, recover datafile on their success!
rn

rn

rn

rn

rn

rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

oracle database backup and restore a piece of cake (3)

kods.net — Tue, 26 Aug 2003 14:00:02+0000

oracle database backup and restore a piece of cake (3)

http://www.chinaunix.net Author: Ganku on :2003-08-26 10:49:53

oracle database online backup: (windows platform)

1. Check your database is running in Archive mode.
In order to connect to the database system to run the following query.
SQL> select name, log_mode from v $ database;
NAME LOG_MODE
--------- ------------
DEMO ARCHIVELOG
If your log_mode displayed archivelog, OK you can make online backup of the database. Otherwise, modify the data refer to the document archive mode.

2. The establishment of textbook management directory C: oracleadminadmin script
3. To establish a database backup script in the above directory set up to establish the following two scripts.
3.1 The establishment of the implementation of a backup task script (batch file)
Content of the document is as follows:

@ echo OFF
Rem ================================================= ==========================
Rem NAME - Open_Backup_Run.bat
Rem FUNCTION - Creates a backup script for a User Managed backup
Rem NOTES - This script will create a script and run OS copy commands
Rem on closed (cold) database
Rem MODIFIED - Tianliang Guo 02/08/12
Rem ================================================= ==========================

Rem For Windows, set environment variables for the root path.
set ORACLE_SID = TSTDB
set ORACLE_CONNECTSTRING = tstdb
set ORACLE_BASE = c: oracle
set ORACLE_HOME =% ORACLE_BASE% ora81
set ORACLE_DATA =% ORACLE_BASE% oradata \% ORACLE_SID%
set ORACLE_ADMIN =% ORACLE_BASE% admin \% ORACLE_SID%

% ORACLE_HOME% insqlplus / nolog@Open_Backup.sql> Open_Backup.sql.log

3.2 The establishment of the completion of the backup that the command file (sqlplus be implemented within the sql script)
Content of the document is as follows:

Rem ================================================= ====================================
Rem NAME - Open_Backup.sql
Rem FUNCTION - Creates a backup command file for a User Managed Open database backup
Rem NOTES - This script will create a script and run OS copy commands
Rem on an open (hot) database. The datafile backups will be inconsistent.
Rem MODIFIED - Mr. Tianliang Guo 2002/08/11
Rem ================================================= ====================================

Rem
Rem Set SQL * Plus variables to manipulate output
Rem

set heading off
set pagesize 0
set linesize 600
set verify off
set trimspool on
set feedback off

Rem Set SQL * Plus user variables used in script

define adm = 'sys'
define pwd = 'oracle8i'

Rem Windows User variables
define dir = 'C: oracleadminTSTDBackup'
define fil = 'open_backup_commands.sql'
define spo = 'open_backup_output.lst'
define ctl = '& dircontrol.ctl'
define cpy = 'ocopy'

Rem login database
connect & adm / & pwd as sysdba;

Rem Create a file containing all the file copy commands needed for open user managed backup

set serveroutput on
spool & fil
prompt spool & spo
prompt archive log list;;
prompt alter system switch logfile;;
prompt alter system archive log all;;

DECLARE
CURSOR cur_tablespace IS
SELECT tablespace_name
FROM dba_tablespaces;

CURSOR cur_datafile (tn VARCHAR) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = tn;
BEGIN
FOR ct IN cur_tablespace LOOP
dbms_output.put_line ( 'alter tablespace' | | ct.tablespace_name | | 'begin backup;');
FOR cd IN cur_datafile (ct.tablespace_name) LOOP
dbms_output.put_line ( 'host & cpy' | | cd.file_name | | '& dir');
END LOOP;
dbms_output.put_line ( 'alter tablespace' | | ct.tablespace_name | | 'end backup;');
END LOOP;

END;
/

prompt alter system switch logfile;;
prompt alter database backup controlfile to '& ctl' REUSE;;
prompt alter database backup controlfile to trace;;

prompt archive log list;;
prompt spool off
spool off;

Rem Run the copy file commands from the operating system
@ &

exit;

4. Modify the textbook definition of a variable defined in the textbook is running a number of variables, to change it to a local database installation environment variables.
Need to modify the variables,
The close_Backup_run.bat script:
set ORACLE_SID = TSTDB
set ORACLE_CONNECTSTRING = tstdb
set ORACLE_BASE = c: oracle
set ORACLE_HOME =% ORACLE_BASE% ora81
The close_backup.sql script:
define adm = 'sys'
define pwd = 'oracle8i'
define dir = 'C: oracleadminTSTDBackup'
define ctl = '& dircontrol.ctl'

5. The implementation of the database backup tasks to run in the $ ORACLE_BASEadminadmin_script directory open_Backup_run.bat

6. Implementation of the automatic backup in the windows system, add a scheduled task on a regular basis to run open_Backup_run.bat

If you have questions can contact:
Stations SMS: ganku
mail: peter_guo@pub.szedi.com.cn
guo_tianliang@yahoo.com

ORACLE10G full version centos5 installed (the installation has passed) 2009-03-31 09:49:10
SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
AX3.0 + Oracle 10G RAC installation 2009-03-12 19:19:31
An example of the use of TKPROF 2009-02-26 01:38:19

[Database] Archivelog in Oracle 8i.9.01.9.2 environment setting method

kods.net — Mon, 16 Jun 2003 07:39:28+0000

Archivelog in Oracle 8i, 9.01,9.2 environment setting method of Li Shouliang 2003-06-16

First, steps of 2, the log parameters and the meaning of the three, filing orders and the meaning of four specific examples

To realize these steps:
1, Manager
1) When the environment is oracle 8i:
# Su - oracle
$ Svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup mount [dbname]
SVRMGR> alter database [dbname] archivelog; - only use archive mode
SVRMGR> archive log start - start the automatic archiving mode, restart the database, press the init.ora configuration
SVRMGR> alter database [dbname] open; - Open database
SVRMGR> exit

2) oracle8i or oracle 9i environment when:
# Su - oracle
$ Sqlplus / nolog
> Connect / as sysdba
> Alter database [dbname] archivelog; - only use archive mode
> Archive log start - start the automatic archiving mode, restart the database, press the init.ora configuration
> Alter database [dbname] open; - Open database
> Exit

Note: Switch archiving and non-filing mode, mount the database did not open on time settings, once set archivelog property, and only at the next set alter database noarchivelog can change the properties of non-filing; and vice versa.
The default file path is $ ORACLE_HOME / dbs / arch, if temporarily modify the file path, use the command: alter system archive log start to '/ u02/archive'

2, modify the database initialization parameter file, define archive mode (auto), archive the log file path to archive log file naming method

1) The environment for oracle 8i or oracle 9.01 pm:
Modify the database parameter file $ ORACLE_HOME / admin / [oradb] / pfile / init [ORACLE_SID]. Ora,
Cancel the following statement # comments
log_archive_start = true
log_archive_dest_1 = "location = / u02/archive"
log_archive_format =%% ORACLE_SID%% T% TS% S. ARC

Note: format parameters
% T: thread number, the left filled with 0, as arch0000000001
% T: thread number, do not fill 0, such as arch1
% S: log serial number on the left filled with 0, as arch0000000189
% S: log serial number, do not fill 0, such as arch189

2) the environment for oracle 9.2 when:
Init.ora file archiving log parameter is not set, and in the $ ORACLE_HOME / dbs / spfile [ORACLE_SID]. Ora file settings, set the method is also different.
To ensure that the changes to take effect, it is recommended to use sys as sysdba user login dbastudio, change the "node" -> Example -> Database -> all the database parameters -> spfile in the archive log parameters

3, restart the database to restart the database, read init [oracle_sid]. Ora or spfile [oracle_sid]. Ora settings, archive log permanent.

Second, the meaning of the log parameters:

Archive mode is automatic or manual, true to automatically, false for manual
log_archive_start = true

Archive log files are saved in the path
log_archive_dest_1 = "location = / u02/archive"

Archive log file naming
log_archive_format =%% ORACLE_SID%% T% TS% S. ARC

3, archiving command:
Start automatic archiving mode, the system restarted after the init.ora parameters in log_archive_start will set the value of archiving methods
SVRMGR> archive log start

Start manual archiving mode
SVRMGR> archive log stop

View archived information: whether the redo log archive mode is automatic or manual archiving archive, archive path, the oldest online log sequential number ...
SVRMGR> archive log list

Filed a full, but not archived online redo logs
SVRMGR> archive log next

Archiving of all full, but not archived online redo logs
SVRMGR> archive log all

SVRMGR> alter system switch logfile;
Mandatory system log switch can be immediately observed that the log file generation

SVRMGR> alter system archive log start to '/ u02/archive';
The archive log path to / u02/archive, but if you need a permanent effect, make changes according to different versions of the init *. ora or spfile *. ora files

Fourth, specific examples:

1) oracle8i and 9.01 environment:
# Su - oralce
$ Svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 2000, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production

SVRMGR> connect internal
Successful connection.
SVRMGR> shutdown
Closed the database.
Database has been removed.
ORACLE instance has been closed.
SVRMGR> startup mount
ORACLE instance started.
System global area together with 57,124,108 bytes
Fixed Size 70924 bytes
Variable Size 40198144 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes Database mounted.
SVRMGR> alter database archivelog;
Statements have been processed.
SVRMGR> archive log start
Statements have been processed.
SVRMGR> alter database open;
Statements have been processed.
SVRMGR> alter system switch logfile; - mandatory system log switch to archive the log can be immediately observed the production statement has been processed.
SVRMGR> exit
Server management program ends.

Modify the database parameter file $ ORACLE_HOME / admin / oradb / pfile / init [oracle_sid]. Ora,
Cancel the following statement # comments
log_archive_start = true
log_archive_dest_1 = "location = / u07/archive"
log_archive_format =%% ORACLE_SID%% T% TS% S. ARC
Close the database, restart

View / u07/archive directory, you can see similar ORADBT001S01201.ARC the paper on archiving success

2) oracle 9.2 environment:
$ Sqlplus / nolog

SQL * Plus: Release 9.2.0.1.0 - Production on Monday June 16 10:30:23 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected.

SQL> startup mount
ORACLE instance started.

Total System Global Area 320308312 bytes
Fixed Size 730200 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Completion of the loading database.
SQL> alter database archivelog;

Database has changed.
SQL> archive log start
Statements have been processed
SQL> alter database open;

Database has changed.
SQL> alter system switch logfile;

System has been changed.
SQL> archive log list;
Database archive log mode Archive Mode Automatic to enable archiving destination / opt/oracle/OraHome1/dbs/arch
Summary of the earliest log sequence 3
The next archive log sequence 5
Current log sequence 5
SQL> exit

Using sys as sysdba user login dbastudio, change the "node" -> Example -> Database -> all the database parameters -> spfile in the archive log parameters
log_archive_start = true
log_archive_dest_1 = "location = / u02/archive"
log_archive_format =%% ORACLE_SID%% T% TS% S. ARC
log_archive_stat_1 = enable

Restart the database

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

archivelog

See whether the archive and modify oracle database archiving mode

See whether the archive and modify oracle database archiving mode

Related Posts

Oracle archive log deletion

Related Posts

oracle data dictionary to learn ------ Application (1)

Related Posts

Flashback log management reference documents

Related Posts

RMAN backup and recovery

RMAN configuration and maintenance of command

RMAN backup and recovery management

User Management Backup and Recovery

Related Posts

Single-instance and RAC to create the ORACLE STREAM (2)

Related Posts

rman notes

Related Posts

RMAN backup and recovery - RAC environment, the database backup (2)

Related Posts

rac rman backup Note

Related Posts

Oracle Classic Q & A

Related Posts

RAC environment, the database backup (2)

Related Posts

Manually DB Creation with 11G

Related Posts

ORA-00265 archivelog can not set the error analysis

Related Posts

Archive mode changes. Query

Related Posts

RAC database archiving mode change

Related Posts

RAC environment, modify the way oracle archive

Related Posts

Oracle's archivelog question

Related Posts

Oracle 8.1.6 in a resume!

Related Posts

oracle database backup and restore a piece of cake (3)

Related Posts

[Database] Archivelog in Oracle 8i.9.01.9.2 environment setting method

Related Posts