archive format

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

scripts of create database

kods.net — Sun, 30 May 2010 18:00:12+0000

1.create init.ora

log_archive_dest_1 = 'LOCATION = / arch /'
log_archive_format =% t_% s_% r.dbf

db_block_size = 8192
db_file_multiblock_read_count = 16

open_cursors = 300

db_domain = ""
db_name = gc

background_dump_dest = / oracle / admin / gc / bdump
core_dump_dest = / oracle / admin / gc / cdump
user_dump_dest = / oracle / admin / gc / udump

control_files = ("/ oracle/oradata/gc/control01.ctl", "/ oracle/oradata/gc/control02.ctl", "/ oracle/oradata/gc/control03.ctl")

job_queue_processes = 10

compatible = 10.2.0.3.0

processes = 150

sga_target = 205520896

audit_file_dest = / oracle / admin / gc / adump
remote_login_passwordfile = EXCLUSIVE

pga_aggregate_target = 68157440

undo_management = AUTO
undo_tablespace = UNDOTBS1

create spfile = '/ oracle / programs / dbs / spfilegc.ora' from pfile = '/ oracle / programs / dbs / init.ora'

SQL> startup nomount;

2.createdb.sql

CREATE DATABASE gc
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/ oracle/oradata/gc/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/ oracle/oradata/gc/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/ oracle/oradata/gc/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/ oracle/oradata/gc/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/ oracle/oradata/gc/redo01.log') SIZE 51200K,
GROUP 2 ('/ oracle/oradata/gc/redo02.log') SIZE 51200K,
GROUP 3 ('/ oracle/oradata/gc/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY ABCabc123 USER SYSTEM IDENTIFIED BY ABCabc123;

3.postcreate

orapwd file = / oracle / programs / dbs / orapwgc password = ABCabc123 force = y

@ / Oracle / programs / rdbms / admin / catalog.sql;
@ / Oracle / programs / rdbms / admin / catblock.sql;
@ / Oracle / programs / rdbms / admin / catproc.sql;
@ / Oracle / programs / rdbms / admin / catoctk.sql;
@ / Oracle / programs / rdbms / admin / owminst.plb;

@ / Oracle / programs / sqlplus / admin / pupbld.sql;

@ / Oracle / programs / sqlplus / admin / help / hlpbld.sql helpus.sql;

alter database archivelog;

alter database default tablespace users;

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

Dataguard create a physical standby steps [RMAN copy]

kods.net — Fri, 28 May 2010 14:03:10+0000

Platform: Oracle 10.2.0.1 on CentOS 5.1
Primary Host Configuration:

1, the installation of production database instance named orcl

2, hostname for the primary

3, the host has to open archive

Standby equipment machine configuration:

1, only install software

2, hostname for the standby

Primary Host Configuration:

Open the logging:

alter database force logging;

Edit primary parameters:

Use alter system set changes [italics according to the actual situation, the static parameter specifies the scope = spfile]:

DB_UNIQUE_NAME = primary

LOG_ARCHIVE_CONFIG = 'DG_CONFIG = (primary, standby)'

LOG_ARCHIVE_DEST_1 =

'LOCATION = / arch

VALID_FOR = (ALL_LOGFILES, ALL_ROLES)

DB_UNIQUE_NAME = primary '

LOG_ARCHIVE_DEST_2 =

'SERVICE = standby_net LGWR ASYNC

VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)

DB_UNIQUE_NAME = standby '

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

LOG_ARCHIVE_FORMAT =% t_% s_% r.arc

LOG_ARCHIVE_MAX_PROCESSES = 30

LOG_FILE_NAME_CONVERT = '/ arch', '/ archlog'

FAL_SERVER = standby_net

FAL_CLIENT = primary_net

STANDBY_FILE_MANAGEMENT = AUTO

Export primary parameter file:

create pfile = '/ tmp / init orcl. ora' from spfile;

Create standby control file:

alter database create standby controlfile as '/ tmp / standby.ctl';

RMAN copy data files out, and its packaging:

mkdir-p / tmp / oradata / orcl

Copy data files using RMAN:

select 'copy datafile' | | file_id | | 'to' | |'''/ tmp / oradata / orcl' | | substr (file_name, instr (file_name ,'/',- 1 ))||'''' | | ';' from dba_data_files order by 1;

RMAN in the implementation of the above statement in the results of the implementation of

tar-cvf oradata.tar oradata /

For convenience, copy files to the same directory:

cp $ ORACLE_HOME / dbs / orapworcl $ ORACLE_BASE

cp $ ORACLE_HOME / dbs / init orcl. ora $ ORACLE_BASE

cp / tmp / oradata.tar $ ORACLE_BASE

cp / tmp / standby.ctl $ ORACLE_BASE

cp / tmp / initorcl.ctl $ ORACLE_BASE

Standby equipment machine configuration:

ftp to the primary, to download all the files:

ftp> bin

200 Switching to Binary mode.

ftp> cd / u01/app/oracle

ftp> mget oradata.tar

ftp> mget orapw orcl

ftp> mget init orcl. ora

ftp> mget standby.ctl

Decompression oradata.tar:

cp oradata.tar $ ORACLE_BASE

cd $ ORACLE_BASE

tar-xvf $ ORACLE_BASE

Use standby.ctl cover oradata three of the control file:

[Oracle @ standby ~] $ cp standby.ctl / u01/app/oracle/oradata/orcl/control01.ctl

[Oracle @ standby ~] $ cp standby.ctl / u01/app/oracle/oradata/orcl/control02.ctl

[Oracle @ standby ~] $ cp standby.ctl / u01/app/oracle/oradata/orcl/control03.ctl

Copy the password file, pfile to the dbs directory:

cd

cp orapworcl $ ORACLE_HOME / dbs

cp initorcl.ora $ ORACLE_HOME / dbs

Edit copied from the primary on initorcl.ora file [bold as the need to modify the place]:

DB_NAME = orcl

DB_UNIQUE_NAME = standby

LOG_ARCHIVE_CONFIG = 'DG_CONFIG = (primary, standby)'

LOG_ARCHIVE_DEST_1 =

'LOCATION = / archlog

VALID_FOR = (ALL_LOGFILES, ALL_ROLES)

DB_UNIQUE_NAME = standby '

LOG_ARCHIVE_DEST_2 =

'SERVICE = primary_net LGWR ASYNC

VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)

DB_UNIQUE_NAME = primary '

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

LOG_ARCHIVE_FORMAT =% t_% s_% r.arc

LOG_ARCHIVE_MAX_PROCESSES = 30

LOG_FILE_NAME_CONVERT = '/ arch', '/ archlog'

FAL_SERVER = primary_net

FAL_CLIENT = standby_net

STANDBY_FILE_MANAGEMENT = AUTO

Parameter file to take effect:

create spfile from pfile;

Create the necessary directories:

mkdir-p / u01/app/oracle/admin/orcl/adump

mkdir-p / u01/app/oracle/admin/orcl/bdump

mkdir-p / u01/app/oracle/admin/orcl/cdump

mkdir-p / u01/app/oracle/admin/orcl/udump

mkdir-p / u01/app/oracle/admin/orcl/dpdump

mkdir-p / archlog

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

Standby machine each modified listener.ora, and restart the monitor, use netca configuration tnsname, and each machine in the standby testing using sqlplus.

tnsname (Oracle Net Service Name) configuration:

the same primary and standby:

primary_net / standby_net [Note: Net Service Name and db_unique_name not be the same, otherwise they will package DGID mismatch error]

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

Prepared to switch to the standby machine database role:

shutdown abort

startup mount;

Check whether the normal archiving transmission

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Test:

In the standby machine test results were used as the command is the same:

select max (sequence #) from v $ archived_log;

In preparation machine with the following command tests whether the log is applied:

SELECT SEQUENCE #, APPLIED FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;

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
Oracle 10 client under linux installation instructions at 2009-03-14 11:43:46
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17
AX3.0 + Oracle 10G RAC installation 2009-03-12 19:19:31

Dataguard steps to create the physical standby [direct copy files / RMAN]

kods.net — Thu, 27 May 2010 13:30:54+0000

Platform: Oracle 10.2.0.1 on CentOS 5.1
primary host configuration:

1, the installation of production database instance named orcl

2, hostname for the primary

standby equipment machine configuration:

1, only install software

2, hostname for the standby

Method 1:

Primary Host Configuration:

1, open the logging:

alter database force logging;

2, open the archive:

If not open, use the following command to open:

shutdown immediate

startup mount

alter database archivelog;

Edit primary parameter file:

1, create pfile from spfile;

2, edited pfile:

Add the following content [italics according to the actual circumstances of the case]:

DB_NAME = orcl

DB_UNIQUE_NAME = primary

LOG_ARCHIVE_CONFIG = 'DG_CONFIG = (primary, standby)'

LOG_ARCHIVE_DEST_1 =

'LOCATION = / arch

VALID_FOR = (ALL_LOGFILES, ALL_ROLES)

DB_UNIQUE_NAME = primary '

LOG_ARCHIVE_DEST_2 =

'SERVICE = standby_net LGWR ASYNC

VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)

DB_UNIQUE_NAME = standby '

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

LOG_ARCHIVE_FORMAT =% t_% s_% r.arc

LOG_ARCHIVE_MAX_PROCESSES = 30

LOG_FILE_NAME_CONVERT = '/ arch', '/ archlog'

FAL_SERVER = standby_net

FAL_CLIENT = primary_net

STANDBY_FILE_MANAGEMENT = AUTO

Create standby control file:

alter database create standby controlfile as '/ tmp / standby.ctl';

Close the main database and data files, control files and online log files Package:

shutdown immediate

tar-cvf oradata.tar oradata /

For convenience, copy dbs password file and pfile under the same directory:

cp orapworcl $ ORACLE_BASE

cp initorcl.ora $ ORACLE_BASE

cp / tmp / standby.ctl $ ORACLE_BASE

Application parameter file:

create spfile from pfile;

Start the database:

startup / alter database open;

Standby equipment machine configuration:

ftp to the primary, to download all the files:

ftp> bin

200 Switching to Binary mode.

ftp> cd / u01/app/oracle

ftp> mget oradata.tar

ftp> mget orapworcl

ftp> mget initorcl.ora

ftp> mget standby.ctl

Decompression oradata.tar:

cp oradata.tar $ ORACLE_BASE

cd $ ORACLE_BASE

tar-xvf $ ORACLE_BASE

Use standby.ctl cover oradata three of the control file:

[Oracle @ standby ~] $ cp standby.ctl / u01/app/oracle/oradata/orcl/control01.ctl

[Oracle @ standby ~] $ cp standby.ctl / u01/app/oracle/oradata/orcl/control02.ctl

[Oracle @ standby ~] $ cp standby.ctl / u01/app/oracle/oradata/orcl/control03.ctl

Copy the password file, pfile to the dbs directory:

cd

cp orapworcl $ ORACLE_HOME / dbs

cp initorcl.ora $ ORACLE_HOME / dbs

Edit copied from the primary on initorcl.ora file [bold as the need to modify the place]:

DB_NAME = orcl

DB_UNIQUE_NAME = standby

LOG_ARCHIVE_CONFIG = 'DG_CONFIG = (primary, standby)'

LOG_ARCHIVE_DEST_1 =

'LOCATION = / archlog

VALID_FOR = (ALL_LOGFILES, ALL_ROLES)

DB_UNIQUE_NAME = standby '

LOG_ARCHIVE_DEST_2 =

'SERVICE = primary_net LGWR ASYNC

VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)

DB_UNIQUE_NAME = primary '

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

LOG_ARCHIVE_FORMAT =% t_% s_% r.arc

LOG_ARCHIVE_MAX_PROCESSES = 30

LOG_FILE_NAME_CONVERT = '/ arch', '/ archlog'

FAL_SERVER = primary_net

FAL_CLIENT = standby_net

STANDBY_FILE_MANAGEMENT = AUTO

Parameter file to take effect:

create spfile from pfile;

Create the necessary directories:

mkdir-p / u01/app/oracle/admin/orcl/adump

mkdir-p / u01/app/oracle/admin/orcl/bdump

mkdir-p / u01/app/oracle/admin/orcl/cdump

mkdir-p / u01/app/oracle/admin/orcl/udump

mkdir-p / u01/app/oracle/admin/orcl/dpdump

mkdir-p / archlog

Start the instance:

startup

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

Standby machine each modified listener.ora, and restart the monitor, use netca configuration tnsname, and each machine in the standby testing using sqlplus.

tnsname (Oracle Net Service Name) configuration:

the same primary and standby:

primary_net / standby_net [Note: Net Service Name and db_unique_name not be the same, otherwise they will package DGID mismatch error]

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

Prepared to switch to the standby machine database role:

shutdown abort

startup mount;

Check whether the normal archiving transmission

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Test:

In the standby machine test results were used as the command is the same:

select max (sequence #) from v $ archived_log;

In preparation machine with the following command tests whether the log is applied:

SELECT SEQUENCE #, APPLIED FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;

Method 2:

mkdir-p / tmp / oradata / orcl

Copy data files using RMAN:

select 'copy datafile' | | file_id | | 'to' | |'''/ tmp / oradata / orcl' | | substr (file_name, instr (file_name ,'/',- 1 ))||'''' | | ';' from dba_data_files order by 1;

RMAN in the implementation of the above statement in the implementation of the results, then ftp the resulting file to the standby database, and other documents and copy the same manner as non-RMAN.

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

Dataguard create a physical standby steps [direct copy files / RMAN]

kods.net — Thu, 27 May 2010 08:07:12+0000

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 summary of knowledge (transfer)

kods.net — Fri, 16 Apr 2010 12:00:46+0000

rman backup of knowledge:

First, enable the rman backup, to enable archive log mode.
See if in archive log mode
SQL> archive log list
Modifications to the archive log mode:

SQL> startup mount

/ / Modify the archivelog file format
SQL> alter system set log_archive_format = '% t_% s_% r.dbf' scope = both;

/ / Change archivelog storage path, following the path for the archive in the diskgroup in ASM in
SQL> alter system set log_archive_dest = '+ DGDATA / jw / arch /' scope = both;

SQL> alter database archivelog;
SQL> archive log list;
SQL> alter system archive log current; / / immediately filed

SQL> alter database open;

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination + DGDATA / jw / arch /
Oldest online log sequence 137
Next log sequence to archive 139
Current log sequence 139

Second, landing rman

# Su - oracle

$ Rman nocatalog directly into rman, non-catalog way back up
rman> connect target / connected to target database
rman> backup database; on the database, all prepared
rman> list backupset; view the situation in the default backup storage of the backup directory: @ ORACLE_BASE / flash_recovery_area / database name / backupset / time /
The parameters of the
rman> show all; view the backup parameters

3, catalog, and the difference between nocatalog

rman's nocatalog manner of information on the control file backup control file which is as a binary file, in linux with the strings command to see the ground inside the control file information.

# String control03.ctl can see the backup of the information appeared.

rman in catalog mode, back up the inside information on the catalog database.

4, incremental backup
incremental backup (level 0 incremental backup is all prepared, but can be used as the basis for post-level incremental backup, full equipment can not)
rman> backup incremental level = 0 database;

Incremental backup (level 1 incremental backup)
rman> backup incremental level = 1 database;
Out of much smaller backup files.

rman> backupset list;

5, rman backup files and information consistent processing

Use rman archivelog on the previous processing equipment to go ahead, then switch to the new archivelog modify the new directory. If the direct switch to the new archive, but did not use before the archivelog rman prepared to go, you should use the following:
# Rman target /
rman> crosscheck archivelog all; / / general in the archive log backup failed, need to re-check.
rman> delete expired archivelog all;

Recorded in the controlfile archivelog with each relevant information, when we put in the OS, delete these files off or unusual physical changes, still recorded in the controlfile of these archivelog information, when we manually clear the archive directory file, These records have not been removed we are from the controlfile in, that is, oracle does not know these files does not exist! this time we have to do manual removal.
rman> crosscheck archivelog all;'s role is to check the control file and the difference between the actual physical file.
rman> delete expired archivelog all; is the synchronization information file and the actual physical file.
If the separate implementation of the crosscheck and delete it failed to implement a backup or fail, because those who control information and the actual file information or different

rman> crosscheck backupset; is to check the backup set and the actual document

rman> delete expired backupset; delete expired backup sets invalid

1, the backup set has two states A (Available, RMAN that the backup media exists) X (Expired, present in the control file backup or restore the directory, but does not physically exist in the backup media)

2, crosscheck the purpose is to check the RMAN catalog and the physical file, if the physical file does not exist in the media, will be marked as Expired. If the physical file exists, will remain Available. If you previously marked as Expired in the backup set again exist in the backup media (such as the restoration of a damaged disk drive is), crosscheck the state will re-mark back from Expired Available.

3, crosscheck the output in two parts. The first part of the list on the backup media to determine the existence of all the backup set piece, the second part of the list does not exist on the backup media backup set piece, and mark it as Expired.
When setting the backup save strategy, a backup date, crosscheck backup discarded after the labeled state still is availabel, you want to remove discarded backup

rman> delete obsolete

5, rman backup operations

# Rman nocatalog to nocatalog log rman
rman> connect target sys / password @ tnsname to connect to the target database, if the database and the listener does not start, it is impossible to be
rman> connect target / prompted to connect to an idle instance
rman> startup sqlplus start the database inside the root of the operation of the same.

Database of all equipment
rman> backup database plus archivelog delete input;
This is a full equipment (including a datafile, controlfile, spfile, archivelog), can not delete input, that the backup is complete, the archive log be removed to save space.

Preparation of the database by

rman> backup incremetal level = 0 database plus archivelog;

rman> backup incremetal level = 1 database plus archivelog;

Table space backup
rman> report schema; show the planning of the entire database table that contains the tablespace, datafile, etc..

rman> backup tablespace users; full backup of tablespace users

Backup control file
rman> backup current controlfile; separate backup controlfile
Or
rman> backup database include current controlfile;

Full back up the database, the default has been included, may not specify. (To increase the controlfile by this mirror)

Image backup, the equivalent of copy:

rman> copy datafile 5 to '/ u01/rmanbak/tbs01bak.dbf';
5 for the corresponding rman> report schema; file number

rman> list copy; show backup copy of the image

rman backup batch command:
rman> run (
allocate channel cha1 type disk;
backup format "/ u01/rmanbak/full_% t"
tag full-backup
database;
release channel cha1;
)

6, automatic backup plan prepared by a week

1, write a backup script

# Vi bakl0
run (
allocate channel cha1 type disk;
backup
incremetal level 0
format "/ u01/rmanbak/inc0_% u_% T"
tag monday_inc0
database;
release channel cha1;
)

# Vi bakl1
run (
allocate channel cha1 type disk;
backup
incremetal level 1
format "/ u01/rmanbak/inc1_% u_% T"
tag monday_inc1
database;
release channel cha1;
)

# Vi bakl2
run (
allocate channel cha1 type disk;
backup
incremetal level 2
format "/ u01/rmanbak/inc2_% u_% T"
tag monday_inc2
database;
release channel cha1;
)

# Crontab-e-u oracle
45 23 * * 0 rman target / msglog = bakl0.log cmdfile = bakl0
45 23 * * 1 rman target / msglog = bakl0.log cmdfile = bakl2
45 23 * * 2 rman target / msglog = bakl0.log cmdfile = bakl2
45 23 * * 3 rman target / msglog = bakl0.log cmdfile = bakl1
45 23 * * 4 rman target / msglog = bakl0.log cmdfile = bakl2
45 23 * * 5 rman target / msglog = bakl0.log cmdfile = bakl2
45 23 * * 6 rman target / msglog = bakl0.log cmdfile = bakl2

# Service crond restart

7, rman restore

Restore control file control file, first close the database
sql> shutdown abort;
# Rman target /
rman> startup nomount;
rman> restore controlfile from autobackup;
rman> quit

# Rman tartget /
rman> alter database mount; / / control has been restored, you can mount up
rman> alter database resetlogs; / / will get an error, it should recover database, will report a problem controlfile.
rman> recover database;
rman> alter database open resetlogs;

spfile is lost, restoration
startup nomount;
set dbid 9815108;
restore spfile from autobackup;
shutdown immediate;
set dbid 9815108;
startup;

redolog file restoration of lost
# Sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database until cancel;
SQL> alter database open resetlogs;
SQL> select * from dual;

datafile missing
rman> sql "alter database datafile 3 offline"; 3 to datafile number
rman> report schema;
rman> restore datafile 3
rman> recover datafile 3
rman> sql "alter database datafile 3 online";

Table space recovery:
rman> sql "alter tablespace users offline";
Or
rman> sql "alter tablespace users offline immediate";
rman> restore tablespace users;
rman> recover tablespace users;
rman> sql "alter tablespace users online";

There is a problem in the database, complete recovery of non-catalog form
startup nomount;
restore controlfile from autobackup;
alter database mount;
restore database;
recover database;
alter database open resetlogs;

8, catalog Database
1, used to create catalog tablespace
SQL> create tablespace rman_ts datafile '/ u01/oracle/rmants.dbf' size 20M;

2, and authorized users to create RMAN
SQL> create user rman identified by rman default
tablespace rman_ts quota unlimited on rman_ts;

SQL> grant recovery_catalog_owner to rman;
SQL> grant connect to rman;

SQL> desc dba_users; / / view the user information
SQL> select username, account_status from dba_users;
SQL> desc dba_sys_privs
SQL> select * from dba_sys_privs where grantee = 'resource';
See authorized resources, the access permissions
SQL> select * from dba_sys_privs where grantee = 'recovery_catalog_owner';

Or:
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace rman_ts temporary tablespace temp;
SQL> alter user rman quota unlimited on rman_ts;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;

3, create a recovery catalog
# Rman catalog rman / rman
rman> create catalog tablespace rman_ts;
rman> exit

4, the use of the media server side, the registered catalog database configuration media server side tnsname connected to the catalog rman Library
# Rman target / catalog rman / rman @ tnsname
rman> register database;

Transfer from: http://blog.chinaunix.net/u3/105031/showart_2083922.html

LISTENER and EZCONNECT 2009-04-01 21:42:20
RMAN created physical standby 2009-03-30 22:49:29
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17
An example of the use of TKPROF 2009-02-26 01:38:19

Oracle database backup methods -2

kods.net — Thu, 10 Jul 2008 06:10:35+0000

4, hot backup program implementation
4.1 prerequisite for hot backup the database: the database is running in archive mode
Oracle database redo log records in the database on all activities. LGWR background process in a cyclic way of writing these log files, from the first redo log to the next, until the group's last one, then starting from the first to write a log.
In the non-archive mode, when the write cycle after the last log file, it overrides the first log. Therefore, the non-archive mode, the only way is to use the database to restore the cold backup.
In archive mode, when the redo log is full, a background process to read all of ARCH redo logs, and then writes the log file. Therefore, you can use the hot backup and point of recovery. In the archive log mode, if the purpose of archiving the log space is full, the database activity will be suspended only after the release of some space, the database can continue to run. Usually, background_dump_destination will produce a trace file to display the archive problem.
Oracle database installed by default to run in non archive mode, the following steps can be non-archive mode to archive mode:
(1) Edit the parameter file init.ora, set the following parameters
# Set the database auto-archiving
log_archive_start = true
# Set the archive log file directory, the directory has been established in advance and have a lot of available space
log_archive_dest_1 = "location =% oracle_base% \ oradata \% oracle_sid% \ archive"
# Set the archive log file name format. % S that sequence number,% t thread number, said.
log_archive_format = "% oracle_sid%% T% S. ARC"
(2) run the following command in svrmgrl
svrmgrl> connect internal / YOUR_PASSWORD
svrmgrl> shutdown immediate;
svrmgrl> startup mount exclusive;
svrmgrl> alter database archivelog;
svrmgrl> alter database open;
svrmgrl> shutdown immediate;
(3) implementation of the database cold backup when the database is running in archive mode, the database recovery process requires the use of cold backup, archive logs are required.
(4) start the database normally and make sure the database is running in archive mode
svrmgrl> startup;
svrmgrl> select * from v $ database; its log_mode will show whether the database archiving mode
svrmgrl> archive log list; will also show whether the database archiving mode if the Oracle database running in archive mode, when the database maintenance, you may need to suspend the database archiving, after completion of maintenance, and then restart the archive mode. Through the following steps to archive mode conversion from non-archive mode:
svrmgrl> connect internal / YOUR_PASSWORD
svrmgrl> shutdown immediate;
svrmgrl> startup mount exclusive;
svrmgrl> alter database noarchivelog;
svrmgrl> alter database open;
svrmgrl> select * from v $ database; its log_mode will show whether the archive mode of the database
svrmgrl> archive log list; will also show whether the database archiving mode
4.2 database hot backup steps (1) copy init.ora file to the backup directory (Data File in the database is turned off after start).
(2) will need to place the backup of a tablespace begin backup mode.
(3) the use of ocopy.exe copy of the table space and then put the end of the table space backup mode (ocopy.exe not be used to directly copy the database file online).
(4) of the database for each table space steps 2 and 3 (you can view dba_tablespaces and v $ datafile view the database in which table spaces and data files).
(5) by svrmgrl perform archive log list command to get the current log sequence number, starting from the oldest online log sequence to the current log sequence for the online redo logs should be part of hot backup.
(6) svrmgrl run alter system switch logfile; command to force log switch to log all are archived.
(7) using the alter database backup controlfile to trace; command was a backup control file, go to% oracle_base% \ admin \% oracle_sid% \ udump directory to find the latest trace file, including all the command control file rebuild.
(8) command using the windows nt in the Copy from% log_archive_dest% archived log files to the backup directory.
4.3 hot backup database script file hotbak.cmd
rem Oracle database hot backup script file name hotbak.cmd
rem for the Windows NT/2000 platform, has been rigorous testing, we can test! ! !
rem This script is executed, in c: \ temp directory created minlog.sql, minlog.cmd,
rem tblspc01.sql, tblspc02.sql, control01.sql, control02.sql,
rem maxlog.sql, maxlog.cmd, archive.sql, archive.cmd in 10 script file.
rem set the SID
set oracle_sid = tmq
rem set the initialization parameter file
set my_pfile = e: \ oracle \ ora81 \ database \ inittmq.ora
set my_ifile = E: \ Oracle \ ADMIN \ tmq \ pfile \ init.ora
rem set the archive log directory
set my_archive_dir = E: \ Oracle \ ORADATA \ tmq \ archive
rem set the password for internal account
set my_password = oracle
the establishment of temporary directory rem
mkdir c: \ temp
set my_temp = c: \ temp
rem create a backup directory, the directory must have enough space for storing the database backup file
mkdir e: \ backup
set my_bakdir = e: \ backup
rem ===== (1) start the backup parameter file =======
echo Backing up the init.ora file
copy% my_pfile%% my_bakdir%
copy% my_ifile%% my_bakdir%
rem ===== (2) create minlog.sql script for the smallest online redo log sequence number =====
echo set heading off;>% my_temp% \ minlog.sql
echo set feedback off;>>% my_temp% \ minlog.sql
echo spool% my_temp% \ minlog.cmd;>>% my_temp% \ minlog.sql
echo select 'set minlog_value =' ^ | ^ | min (sequence #) from v $ log where upper (status) = upper ('INACTIVE');>>% my_temp% \ minlog.sql
echo spool off;>>% my_temp% \ minlog.sql
echo select 'exit;' from dual;>>% my_temp% \ minlog.sql
echo exit;>>% my_temp% \ minlog.sql
rem ===== (3) run minlog.sql to genrate minlog.cmd ========
sqlplus internal /% my_password% @% my_temp% \ minlog.sql
rem ===== (4) run minlog.cmd to set minlog_value ========
call% my_temp% \ minlog.cmd
rem === (5) create tblspc01.sql script used to generate the data file backup script file tblspc02.sql ===
echo set heading off;>% my_temp% \ tblspc01.sql
echo set feedback off;>>% my_temp% \ tblspc01.sql
echo spool% my_temp% \ tblspc02.sql;>>% my_temp% \ tblspc01.sql
echo select 'connect internal /% my_password%;' from dual;>>% my_temp% \ tblspc01.sql
echo select 'alter tablespace' ^ | ^ | tablespace_name ^ | ^ | 'begin backup;' from dba_tablespaces;>>% my_temp% \ tblspc01.sql
echo select 'host start / wait ocopy' ^ | ^ | file_name ^ | ^ | '% my_bakdir%;' from dba_data_files;>>% my_temp% \ tblspc01.sql
echo select 'alter tablespace' ^ | ^ | tablespace_name ^ | ^ | 'end backup;' from dba_tablespaces;>>% my_temp% \ tblspc01.sql
echo spool off;>>% my_temp% \ tblspc01.sql
echo select 'exit;' from dual;>>% my_temp% \ tblspc01.sql
echo exit;>>% my_temp% \ tblspc01.sql
rem ===== (6) run tblspc01.sql to genrate tblspc02.sql ========
sqlplus internal /% my_password% @% my_temp% \ tblspc01.sql
rem ===== (7) on svrmgrl run tblspc02.sql to backup the datafiles ========
svrmgrl @% my_temp% \ tblspc02.sql
rem ===== (8) script to create maxlog.sql maximize the online redo log sequence number =====
echo set heading off;>% my_temp% \ maxlog.sql
echo set feedback off;>>% my_temp% \ maxlog.sql
echo spool% my_temp% \ maxlog.cmd;>>% my_temp% \ maxlog.sql
echo select 'set maxlog_value =' ^ | ^ | max (sequence #) from v $ log where upper (status) = upper ('CURRENT');>>% my_temp% \ maxlog.sql
echo spool off;>>% my_temp% \ maxlog.sql
echo select 'alter system switch logfile;' from dual;>>% my_temp% \ maxlog.sql
echo select 'exit;' from dual;>>% my_temp% \ maxlog.sql
echo exit;>>% my_temp% \ maxlog.sql
rem ===== (9) run maxlog.sql to genrate maxlog.cmd ========
sqlplus internal /% my_password% @% my_temp% \ maxlog.sql
rem ===== (10) run maxlog.cmd to set maxlog_value ========
call% my_temp% \ maxlog.cmd
rem == (11) to create control01.sql script used to generate a backup control file script file control02.sql ===
echo set heading off;>% my_temp% \ control01.sql
echo set feedback off;>>% my_temp% \ control01.sql
echo spool% my_temp% \ control02.sql;>>% my_temp% \ control01.sql
echo select 'connect internal /% my_password%;' from dual;>>% my_temp% \ control01.sql
echo select 'alter database backup controlfile to trace;' from dual;>>% my_temp% \ control01.sql
echo spool off;>>% my_temp% \ control01.sql
echo select 'exit;' from dual;>>% my_temp% \ control01.sql
echo exit;>>% my_temp% \ control01.sql
rem ===== (12) run control01.sql to genrate control02.sql ========
sqlplus internal /% my_password% @% my_temp% \ control01.sql
rem ===== (13) on svrmgrl run control02.sql to backup the datafiles ========
svrmgrl @% my_temp% \ control02.sql
rem ===== (14) to create archive.sql script used to generate the archive log files backup script file archive.cmd =====
echo set heading off;>% my_temp% \ archive.sql
echo set feedback off;>>% my_temp% \ archive.sql
echo spool% my_temp% \ archive.cmd;>>% my_temp% \ archive.sql
rem only backup and hot backup of this log file related to
echo select 'copy' ^ | ^ | name ^ | ^ | '% my_bakdir%' from V_ $ ARCHIVED_LOG where sequence # between% minlog_value% and% maxlog_value% +1;>>% my_temp% \ archive.sql
echo spool off;>>% my_temp% \ archive.sql
echo select 'exit;' from dual;>>% my_temp% \ archive.sql
echo exit;>>% my_temp% \ archive.sql
rem ===== (15) run archive.sql to genrate archive.cmd ========
sqlplus internal /% my_password% @% my_temp% \ archive.sql
rem ===== (16) run archive.cmd to backup the archive log files ========
call% my_temp% \ archive.cmd
rem ===== the end ======
echo ===== the hot backup is completed!!! ======

4, hot backup program implementation
4.1 prerequisite for hot backup the database: the database is running in archive mode
Oracle database redo log records in the database on all activities. LGWR background process in a cyclic way of writing these log files, from the first redo log to the next, until the group's last one, then starting from the first to write a log.
In the non-archive mode, when the write cycle after the last log file, it overrides the first log. Therefore, the non-archive mode, the only way is to use the database to restore the cold backup.
In archive mode, when the redo log is full, a background process to read all of ARCH redo logs, and then writes the log file. Therefore, you can use the hot backup and point of recovery. In the archive log mode, if the purpose of archiving the log space is full, the database activity will be suspended only after the release of some space, the database can continue to run. Usually, background_dump_destination will produce a trace file to display the archive problem.
Oracle database installed by default to run in non archive mode, the following steps can be non-archive mode to archive mode:
(1) Edit the parameter file init.ora, set the following parameters
# Set the database auto-archiving
log_archive_start = true
# Set the archive log file directory, the directory has been established in advance and have a lot of available space
log_archive_dest_1 = "location =% oracle_base% \ oradata \% oracle_sid% \ archive"
# Set the archive log file name format. % S that sequence number,% t thread number, said.
log_archive_format = "% oracle_sid%% T% S. ARC"
(2) run the following command in svrmgrl
svrmgrl> connect internal / YOUR_PASSWORD
svrmgrl> shutdown immediate;
svrmgrl> startup mount exclusive;
svrmgrl> alter database archivelog;
svrmgrl> alter database open;
svrmgrl> shutdown immediate;
(3) implementation of the database cold backup when the database is running in archive mode, the database recovery process requires the use of cold backup, archive logs are required.
(4) start the database normally and make sure the database is running in archive mode
svrmgrl> startup;
svrmgrl> select * from v $ database; its log_mode will show whether the archive mode of the database
svrmgrl> archive log list; will also show whether the database archiving mode if the Oracle database running in archive mode, when the database maintenance, you may need to suspend the database archiving, after completion of maintenance, and then restart the archive mode. Through the following steps to archive mode conversion from non-archive mode:
svrmgrl> connect internal / YOUR_PASSWORD
svrmgrl> shutdown immediate;
svrmgrl> startup mount exclusive;
svrmgrl> alter database noarchivelog;
svrmgrl> alter database open;
svrmgrl> select * from v $ database; its log_mode will show whether the archive mode of the database
svrmgrl> archive log list; will also show whether the database archiving mode
4.2 database hot backup steps (1) copy init.ora file to the backup directory (Data File in the database is turned off after start).
(2) will need to place the backup of a tablespace begin backup mode.
(3) the use of ocopy.exe copy of the table space and then put the end of the table space backup mode (ocopy.exe not be used to directly copy the database file online).
(4) of the database for each table space steps 2 and 3 (you can view dba_tablespaces and v $ datafile view the database in which table spaces and data files).
(5) by svrmgrl perform archive log list command to get the current log sequence number, starting from the oldest online log sequence to the current log sequence for the online redo logs should be part of hot backup.
(6) svrmgrl run alter system switch logfile; command to force log switch to log all are archived.
(7) using the alter database backup controlfile to trace; command was a backup control file, go to% oracle_base% \ admin \% oracle_sid% \ udump directory to find the latest trace file, including all the command control file rebuild.
(8) command using the windows nt in the Copy from% log_archive_dest% archived log files to the backup directory.
4.3 hot backup database script file hotbak.cmd
rem Oracle database hot backup script file name hotbak.cmd
rem for the Windows NT/2000 platform, has been rigorous testing, we can test! ! !
rem This script is executed, in c: \ temp directory created minlog.sql, minlog.cmd,
rem tblspc01.sql, tblspc02.sql, control01.sql, control02.sql,
rem maxlog.sql, maxlog.cmd, archive.sql, archive.cmd in 10 script file.
rem set the SID
set oracle_sid = tmq
rem set the initialization parameter file
set my_pfile = e: \ oracle \ ora81 \ database \ inittmq.ora
set my_ifile = E: \ Oracle \ ADMIN \ tmq \ pfile \ init.ora
rem set the archive log directory
set my_archive_dir = E: \ Oracle \ ORADATA \ tmq \ archive
rem set the password for internal account
set my_password = oracle
the establishment of temporary directory rem
mkdir c: \ temp
set my_temp = c: \ temp
rem create a backup directory, the directory must have enough space for storing the database backup file
mkdir e: \ backup
set my_bakdir = e: \ backup
rem ===== (1) start the backup parameter file =======
echo Backing up the init.ora file
copy% my_pfile%% my_bakdir%
copy% my_ifile%% my_bakdir%
rem ===== (2) create minlog.sql script for the smallest online redo log sequence number =====
echo set heading off;>% my_temp% \ minlog.sql
echo set feedback off;>>% my_temp% \ minlog.sql
echo spool% my_temp% \ minlog.cmd;>>% my_temp% \ minlog.sql
echo select 'set minlog_value =' ^ | ^ | min (sequence #) from v $ log where upper (status) = upper ('INACTIVE');>>% my_temp% \ minlog.sql
echo spool off;>>% my_temp% \ minlog.sql
echo select 'exit;' from dual;>>% my_temp% \ minlog.sql
echo exit;>>% my_temp% \ minlog.sql
rem ===== (3) run minlog.sql to genrate minlog.cmd ========
sqlplus internal /% my_password% @% my_temp% \ minlog.sql
rem ===== (4) run minlog.cmd to set minlog_value ========
call% my_temp% \ minlog.cmd
rem === (5) create tblspc01.sql script used to generate the data file backup script file tblspc02.sql ===
echo set heading off;>% my_temp% \ tblspc01.sql
echo set feedback off;>>% my_temp% \ tblspc01.sql
echo spool% my_temp% \ tblspc02.sql;>>% my_temp% \ tblspc01.sql
echo select 'connect internal /% my_password%;' from dual;>>% my_temp% \ tblspc01.sql
echo select 'alter tablespace' ^ | ^ | tablespace_name ^ | ^ | 'begin backup;' from dba_tablespaces;>>% my_temp% \ tblspc01.sql
echo select 'host start / wait ocopy' ^ | ^ | file_name ^ | ^ | '% my_bakdir%;' from dba_data_files;>>% my_temp% \ tblspc01.sql
echo select 'alter tablespace' ^ | ^ | tablespace_name ^ | ^ | 'end backup;' from dba_tablespaces;>>% my_temp% \ tblspc01.sql
echo spool off;>>% my_temp% \ tblspc01.sql
echo select 'exit;' from dual;>>% my_temp% \ tblspc01.sql
echo exit;>>% my_temp% \ tblspc01.sql
rem ===== (6) run tblspc01.sql to genrate tblspc02.sql ========
sqlplus internal /% my_password% @% my_temp% \ tblspc01.sql
rem ===== (7) on svrmgrl run tblspc02.sql to backup the datafiles ========
svrmgrl @% my_temp% \ tblspc02.sql
rem ===== (8) script to create maxlog.sql maximize the online redo log sequence number =====
echo set heading off;>% my_temp% \ maxlog.sql
echo set feedback off;>>% my_temp% \ maxlog.sql
echo spool% my_temp% \ maxlog.cmd;>>% my_temp% \ maxlog.sql
echo select 'set maxlog_value =' ^ | ^ | max (sequence #) from v $ log where upper (status) = upper ('CURRENT');>>% my_temp% \ maxlog.sql
echo spool off;>>% my_temp% \ maxlog.sql
echo select 'alter system switch logfile;' from dual;>>% my_temp% \ maxlog.sql
echo select 'exit;' from dual;>>% my_temp% \ maxlog.sql
echo exit;>>% my_temp% \ maxlog.sql
rem ===== (9) run maxlog.sql to genrate maxlog.cmd ========
sqlplus internal /% my_password% @% my_temp% \ maxlog.sql
rem ===== (10) run maxlog.cmd to set maxlog_value ========
call% my_temp% \ maxlog.cmd
rem == (11) to create control01.sql script used to generate a backup control file script file control02.sql ===
echo set heading off;>% my_temp% \ control01.sql
echo set feedback off;>>% my_temp% \ control01.sql
echo spool% my_temp% \ control02.sql;>>% my_temp% \ control01.sql
echo select 'connect internal /% my_password%;' from dual;>>% my_temp% \ control01.sql
echo select 'alter database backup controlfile to trace;' from dual;>>% my_temp% \ control01.sql
echo spool off;>>% my_temp% \ control01.sql
echo select 'exit;' from dual;>>% my_temp% \ control01.sql
echo exit;>>% my_temp% \ control01.sql
rem ===== (12) run control01.sql to genrate control02.sql ========
sqlplus internal /% my_password% @% my_temp% \ control01.sql
rem ===== (13) on svrmgrl run control02.sql to backup the datafiles ========
svrmgrl @% my_temp% \ control02.sql
rem ===== (14) to create archive.sql script used to generate the archive log files backup script file archive.cmd =====
echo set heading off;>% my_temp% \ archive.sql
echo set feedback off;>>% my_temp% \ archive.sql
echo spool% my_temp% \ archive.cmd;>>% my_temp% \ archive.sql
rem only backup and hot backup of this log file related to
echo select 'copy' ^ | ^ | name ^ | ^ | '% my_bakdir%' from V_ $ ARCHIVED_LOG where sequence # between% minlog_value% and% maxlog_value% +1;>>% my_temp% \ archive.sql
echo spool off;>>% my_temp% \ archive.sql
echo select 'exit;' from dual;>>% my_temp% \ archive.sql
echo exit;>>% my_temp% \ archive.sql
rem ===== (15) run archive.sql to genrate archive.cmd ========
sqlplus internal /% my_password% @% my_temp% \ archive.sql
rem ===== (16) run archive.cmd to backup the archive log files ========
call% my_temp% \ archive.cmd
rem ===== the end ======
echo ===== the hot backup is completed!!! ======

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

The oracle set to archive mode is the method

kods.net — Wed, 25 Jun 2008 15:53:40+0000

The oracle set to archive mode is the method

First manually create archive directory, start adjusting the way oracle oracle set to archive mode

Manually create the archive directory on the main server

$ Cd $ ORACLE_BASE # $ ORACLE_BASE points to the directory is / opt / oracle /, can be found in the specific ORACLE user environment variable settings

$ Mkdir-p oradata / szdb / archive # szdb the directory name under the SID number for the specified database, in fact, this directory can be arbitrary

$ Mkdir-p oradata / szdb / standbyarchive # if the master server may be used as backup server, then set up the directory

[Oracle @ cqcncdb szdb] $ sqlplus / nolog

SQL> conn / as sysdba;

Connected.

SQL> startup mount;

SQL> alter database archivelog;

SQL> archive log start;

SQL> archive log list;

SQL> alter database open;

SQL> alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / oracle / oradata / szdb / archive'; # / oracle / oradata / szdb / archive must be in front of mkdir-p oradata / szdb / archive path set up exactly the same operation

SQL> alter system set log_archive_format = '% t_% s.dbf' scope = spfile;

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

Restart the database, the changes to take effect the results

SQL> shutdown immediate;

This archive mode

SQL> startup

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / oracle / oradata / szdb / archive

Oldest online log sequence 565

Next log sequence to archive 567

Current log sequence 567

Alter system set above operation will be in the database spfile@sid.ora and init@sid.ora file Add the following parameters

*. Log_archive_dest_1 = 'LOCATION = / oracle / oradata / szdb / archive'

*. Log_archive_format = '% t_% s.dbf'

*. Log_archive_start = TRUE

Note: *. log_archive_dest_1 defined folders must be real, and oracle user has write permission

Note: @ sid sid number representative of the actual database, such as in the actual operation both the file name is spfileszdb.ora, initszdb.ora

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

7.4.2 ORACLE database system

kods.net — Tue, 17 Jun 2008 14:27:11+0000

7.4.2 ORACLE database system

ORACLE system uses the log file group stored database log. Is known as group, because each log file group can contain multiple log files, these log files are called members of the group. A log file of all the members of the group have the same spatial dimensions, contain the same log information. As a group contains multiple members, so even if a member of the document destruction, the log file group can still be used. The following configuration parameters and database logs related to:

LOGFILE: Set the log file group and its members, the location and storage space.

MAXLOGFILES: setting systems can be set using the maximum number of log files.

MAXLOGMEMBERS: Set the log file for each group contained the largest number of members.

LOG_ARCHIVE_START: Set whether the group automatically archive log files.

LOG_ARCHIVE_MAX_PROCESSES: Set archive log file group is used to process the largest number.

LOG_ARCHIVE_DEST: Set the log file group was archived storage location.

LOG_ARCHIVE_FORMAT: Set archive log files are named group format.

1. Does not generate a database log

In exceptional circumstances, to improve the processing speed of database systems, can make the update transaction processing does not produce log information. In the ORACLE system, can not set the database to make all the tables for the database updates do not generate log information processing. We can change the tables, indexes, materialized views of the property, so that they do not mind in the log mode to update the operation of these objects, will not generate log information. If a table space attribute is changed to not remember the log mode, the table space in the newly created object, have not Ji log properties.

In addition to the table, insert data, use SQL * LOADER tools direct path load, can reduce generation of log information. To delete all records in a table of data, you can first remove the entire table, and then re-create the table. Better approach is to use the truncate table command, the command to delete all records in the table, will not generate log information, Zai Hui Shou same table structure to retain possession of Cipan table space.

2. Log file archive

ORACLE database system to support non-archive log archive log and two models, indeed provincial case, the database is in non archive log mode. To make the database is in archive log mode, you can use the command: alter database archivelog change.

In the archive log mode, the system loop to use all of the log file group. Archive the log file group, automatic and manual can be used two ways.

(1) automatic archiving. Set database parameters LOG_ARCHIVE_START = TRUE.

(2) manual archiving. Set database parameters LOG_ARCHIVE_START = FALSE, periodic manual archive log files group.

On the log files automatically or manually archive groups of specific implementation methods can be found in Chapter 9.

3. Long transaction processing

ORACLE system, the number of log file groups and members of the group of space dimensions, are not automatically expanded. Can not guarantee that the system always has enough log space, so long transaction completed successfully. Administrators can set up more log file groups, so that group members have more disk space.

Rollback table space in the update transaction processing, storing data to be updated before the image. The greater the amount of data transactions, the more space needed. Therefore, the administrator in dealing with long transaction, the rollback tablespace should ensure there is sufficient space available.

Oracle database used to maintain day-to-day command 2009-04-06 16:46:33
Oracle10g ASM database table space maintenance 2009-03-31 17:51:25
Block Oracle paragraph (reprint) 2009-03-26 17:41:14

oracle9i began studying the problems in, winxp system

kods.net — Wed, 28 May 2008 07:05:18+0000

1, oracle92 installation if a clean machine, generally have their way, and finally through the dbca configuration.
If you have previously installed oracle10client and so on, leading to environmental variables affect on oracle92 after, dbca tools such as configuration will fail. In this case the default sid did not create success, connect / as sysdba when it will pop up: ORA-12560
Modified methods are: (1) modify the environment variable to start dbca to create the default database, start the service corresponding to sid (I did not do experiments)
(2) the use of oradim Tools:
oradim I did not use it because every time no problem to install oracle, in fact, she was really good, the main features are:
Create, delete, edit, sid, initialization ORACLE_HOMOE \ database \ PWDsid.ORA, modify startmode, specify the pfile
In the cmd in the knock oradim, you can see his parameters, but said two buses, only to create sid, but not initialized pwdsid.ora file, you can conn / as sysdba, but create database, the wrong. startmode in the manual, the manual service in the window start sid services, database does not start, auto, then start the database at the same time.
Here I use the pfile is the default location ORACLE_HOMOE \ database \ initxxx.ora, do not know where the other location designated startup pfile location data is to change the default Note: before the sid that the oracle service is to use windows command sc directly create, start , stop, delete it
2, create a database reference documentation is the administrator reference.pdf oracle 9i documentation on this step I fee old strength, and fully feel the important theory and practice is more important ah. . .
Reference document Manually Creating an Oracle Database
Parameter file I use the ORACLE_HOME \ admin \ sample \ pfile template under initsmpl.ora do note the following points:
Where all the% xxx% are replaced with the actual path, log_archive_format = ARCH% s.LOG a% is correct.
text_enable = TRUE do not know what mean comment out the
compatible = 8.1.5.0.0 be amended to compatible = 9.2.0.0.0
Create database again when the above will result in ora-01092 error.
Of course also be other causes, there is an error to see background_dump_dest = F: \ oracle \ product \ 9.2 \ rdbms \ trace
user_dump_dest = F: \ oracle \ product \ 9.2 \ rdbms \ trace log under the general issue can be found.

Examples of the document create database statement, I also posted:

CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/ vobs/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/ vobs/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/ vobs/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/ vobs/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE '/ vobs/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/ vobs/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

DEFAULT TEMPORARY TABLESPACE tempts1 below the DATAFILE to change TEMPFILE, for the time being related to undo tablespace comment, then say, create the database successfully. . . plsql to try to connect.

Pfile experiment below to make a change:
oradim-shutdown-sid demo
oradim-delete-sid demo
Delete all files created oradata create database under there control file. . .
C: \> oradim-new-sid demo-intpwd trout80-startmode manual-pfile F: \ oracle \ pro
duct \ 9.2 \ admin \ demo \ pfile \ initdemo.ora
sqlplus / nolog
conn / as sysdba
startup nomount
Implementation of the create database, error, and, hey, the parameter file or use the default, it seems oradim specified pfile and startup pfile location has nothing to do with the Arab-Israeli
shutdown immediate
Delete the relevant file just created
startup nomount pfile = F: \ oracle \ product \ 9.2 \ admin \ demo \ pfile \ initdemo.ora
The construction of library success. . .

About undo tablespace
To create database undo tablespace comment below modify the parameters open the file add
undo_management = auto
undo_tablespace = undotbs
Note undo_tablespace = undotbs necessarily have to create database in the name of the same ah. . .
Create success. . . . . .

Postscript: oracle official documents because I do not metalnk account, only to open the document from the Internet, choose pdf open, you can save into a book. Another test procedure is not very well, when finally in sight, we must look at log files subconscious. create database statement is saved in a file in the @ path in the sqlplus call, so save the changes after the create database statement, and convenient. However, note that the last modification time ";", I often forget
Out several mistakes were really feeling it. . . However, DBA is also not a man of God, I hope you a hard.
mail: yixia800924@163.com

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 Diagnostic Case-Spfile case of a (turn)

kods.net — Tue, 19 Sep 2006 18:05:32+0000

Oracle Diagnostic Case-Spfile Case 1
rn
Information Note:
System: SUN Solaris8
Database Version: 9203
Problem Description: Engineering staff reports, database restart does not start normally. Examination revealed UNDO tablespace is lost.
Problem diagnosis and resolution process is as follows:
rn
1. Alert.log landing system checks the file
rn
Check alert.log file is usually the first step in diagnosing the problem database
rn
SunOS 5.8
rn
login: root
Password:
Last login: Thu Apr 1 11:39:16 from 10.123.7.162
Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001
You have new mail.
# Su - oracle
bash-2.03 $ cd $ ORACLE_BASE / admin / * / bdump
bash-2.03 $ vi *. log
rn
"alert_gzhs.log" 7438 lines, 283262 characters
Sat Feb 7 20:30:06 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.3.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 1157627904
large_pool_size = 16777216
java_pool_size = 637534208
control_files = / u01/oradata/gzhs/control01.ctl,
/ u02/oradata/gzhs/control02.ctl,
/ u03/oradata/gzhs/control03.ctl
db_block_size = 8192
db_cache_size = 2516582400
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION = / u06/oradata/gzhs/arch
log_archive_format =% t_% s.dbf
db_file_multiblock_read_count = 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile = EXCLUSIVE
db_domain =
instance_name = gzhs
dispatchers = (PROTOCOL = TCP) (SERVICE = gzhsXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = / oracle / admin / gzhs / bdump
user_dump_dest = / oracle / admin / gzhs / udump
core_dump_dest = / oracle / admin / gzhs / cdump
sort_area_size = 524288
db_name = gzhs
open_cursors = 300
star_transformation_enabled = FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 838860800
aq_tm_processes = 1
PMON started with pid = 2
DBW0 started with pid = 3
LGWR started with pid = 4
CKPT started with pid = 5
SMON started with pid = 6
"alert_gzhs.log" 7438 lines, 283262 characters
USER: terminating instance due to error 30012
Instance terminated by USER, pid = 26433
ORA-1092 signalled during: ALTER DATABASE OPEN ...
Thu Apr 1 11:11:08 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.3.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 1157627904
large_pool_size = 16777216
java_pool_size = 637534208
control_files = / u01/oradata/gzhs/control01.ctl, / u02/oradata/gzhs/control02.ctl, / u03/oradata/gzhs/control03.ctl
db_block_size = 8192
db_cache_size = 2516582400
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION = / u06/oradata/gzhs/arch
log_archive_format =% t_% s.dbf
db_file_multiblock_read_count = 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile = EXCLUSIVE
db_domain =
instance_name = gzhs
dispatchers = (PROTOCOL = TCP) (SERVICE = gzhsXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = / oracle / admin / gzhs / bdump
user_dump_dest = / oracle / admin / gzhs / udump
core_dump_dest = / oracle / admin / gzhs / cdump
sort_area_size = 524288
db_name = gzhs
open_cursors = 300
star_transformation_enabled = FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 838860800
aq_tm_processes = 1
PMON started with pid = 2
DBW0 started with pid = 3
LGWR started with pid = 4
CKPT started with pid = 5
SMON started with pid = 6
RECO started with pid = 7
CJQ0 started with pid = 8
Thu Apr 1 11:11:13 2004
starting up 1 shared server (s) ...
QMN0 started with pid = 9
Thu Apr 1 11:11:13 2004
starting up 1 dispatcher (s) for network address' (ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP ))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid = 12
ARC0: Archival started
ARC1 started with pid = 13
Thu Apr 1 11:11:13 2004
ARCH: STARTING ARCH PROCESSES COMPLETE
Thu Apr 1 11:11:13 2004
ARC0: Thread not mounted
Thu Apr 1 11:11:13 2004
ARC1: Archival started
ARC1: Thread not mounted
Thu Apr 1 11:11:14 2004
ALTER DATABASE MOUNT
Thu Apr 1 11:11:18 2004
Successful mount of redo thread 1, with mount id 1088380178.
Thu Apr 1 11:11:18 2004
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Thu Apr 1 11:11:27 2004
alter database open
Thu Apr 1 11:11:27 2004
Beginning crash recovery of 1 threads
Thu Apr 1 11:11:27 2004
Started first pass scan
Thu Apr 1 11:11:28 2004
Completed first pass scan
1 redo blocks read, 0 data blocks need recovery
Thu Apr 1 11:11:28 2004
Started recovery at
Thread 1: logseq 177, block 2, scn 0.33104793
Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0
Mem # 0 errs 0: / u01/oradata/gzhs/redo03.log
Thu Apr 1 11:11:28 2004
Completed redo application
Thu Apr 1 11:11:28 2004
Ended recovery at
Thread 1: logseq 177, block 3, scn 0.33124794
0 data blocks read, 0 data blocks written, 1 redo blocks read
Crash recovery completed successfully
Thu Apr 1 11:11:28 2004
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 178
Thread 1 opened at log sequence 178
Current log # 1 seq # 178 mem # 0: / u01/oradata/gzhs/redo01.log
Successful open of redo thread 1.
Thu Apr 1 11:11:28 2004
ARC0: Evaluating archive log 3 thread 1 sequence 177
Thu Apr 1 11:11:28 2004
ARC0: Beginning to archive log 3 thread 1 sequence 177
Creating archive destination LOG_ARCHIVE_DEST_1: '/ u06/oradata/gzhs/arch/1_177.dbf'
Thu Apr 1 11:11:28 2004
SMON: enabling cache recovery
ARC0: Completed archiving log 3 thread 1 sequence 177
Thu Apr 1 11:11:28 2004
Errors in file / oracle/admin/gzhs/udump/gzhs_ora_27781.trc:
ORA-30012: \ 263 \ 267 \ 317 \ 373 \ 261 \ 355 \ 277 \ 325 \ 274 \ 344 'UNDOTBS1' \ 262 \ 273 \ 264 \ 346 \ 324 \ 332 \ 273 \ 362 \ 300 \ 340 \ 320 \ 315 \ 262 \ 273 \ 325 \ 375 \ 310 \
267
Thu Apr 1 11:11:28 2004
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012
Instance terminated by USER, pid = 27781
ORA-1092 signalled during: alter database open ...
: q
rn

rn
.............
rn

rn

In the alert log shows the database in the Open at the end of the state because of an error abort.
rn
2. Attempt to restart the database
rn

rn
bash-2.03 $ sqlplus "/ as sysdba" SQL * Plus: Release 9.2.0.3.0 - Production on Thursday, April 1 11:43:52 2004
rn
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
rn
Been connected to the idle routine.
rn
SQL> startup
ORACLE instance started.
rn
Total System Global Area 4364148184 bytes
Fixed Size 736728 bytes
Variable Size 1845493760 bytes
Database Buffers 2516582400 bytes
Redo Buffers 1335296 bytes
The database load is completed.
ORA-01092: ORACLE routine to terminate. Forced disconnect
rn
.............
rn
Engineering staff reports reproduce the problem.
rn
3. Check the data file
bash-2.03 $ cd / u01 / oradata/gzhsbash-2.03 $ ls-ltotal 55702458-rw-r ----- 1 oracle dba 1073750016 Apr 1 11:44 UNDOTBS2.dbf-rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_BILLINGDETAIL.dbf-rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_MAIN.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN10 . dbf-rw-r ----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN11.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN2.dbf-rw-r--- --- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN3.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN4.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN5.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN6.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN7.dbf - rw-r ----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN8.dbf-rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN9.dbf-rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_MVIEW.dbf-rw-r----- 1 oracle dba 1073750016 Mar 24 17:15 WAP12_TEMP1.dbf ................. .....................
rn
Is found documents UNDOTBS2.dbf
rn
4. Mount the database, check the system parameters
rn
bash-2.03 $ sqlplus "/ as sysdba"
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4364148184 bytes
Fixed Size 736728 bytes
Variable Size 1845493760 bytes
Database Buffers 2516582400 bytes
Redo Buffers 1335296 bytes
The database load is completed.
SQL> select name from v $ datafile;
NAME
-------------------------------------------------- ------------------------------
/ u01/oradata/gzhs/system01.dbf
/ u01/oradata/gzhs/cwmlite01.dbf
/ u01/oradata/gzhs/drsys01.dbf
/ u01/oradata/gzhs/example01.dbf
/ u01/oradata/gzhs/indx01.dbf
/ u01/oradata/gzhs/odm01.dbf
/ u01/oradata/gzhs/tools01.dbf
/ u01/oradata/gzhs/users01.dbf
/ u01/oradata/gzhs/xdb01.dbf
.........................
/ u01/oradata/gzhs/UNDOTBS2.dbf

Has selected 23 rows.
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- --- --------------------------- spfile string
......................................
rn

rn
Found that the system does not use spfile, and the initialization parameter setting of the undo tablespace for UNDOTBS1
rn
5. Check Parameter File
rn

rn

bash-2.03 $ cd $ ORACLE_HOME / dbs
bash-2.03 $ ls
init.ora initgzhs.ora initgzhs.ora.old orapwgzhs
initdw.ora initgzhs.ora.hurray lkGZHS snapcf_gzhs.f
bash-2.03 $ vi initgzhs.ora
"initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters
################################################## # # # Copyright (c) 1991, 2001, 2002 by Oracle Corporation ################################### #################
########################################### # Archive ##### ###################################### log_archive_dest_1 = 'LOCATION = / u06/oradata/gzhs / arch 'log_archive_format =% t_% s.dbf
log_archive_start = true
########################################### # Cache and I / O # ########################################## db_block_size = 8192 db_cache_size = 2516582400 db_file_multiblock_read_count = 16 ########################################### # Cursors and Library Cache # ########################################## open_cursors = 300 ..... .................
########################################### # System Managed Undo and Rollback Segments ########################################### undo_management = AUTO undo_retention = 10800 undo_tablespace = UNDOTBS1
: q !.............
rn

rn

This setting is extremely suspicious.
Suspect that the actual database parameter file, and set the discrepancies.
rn
6. Again, check the alert file search operation for the UNDO tablespace
rn
The first part, create a database of information:
rn

rn

Sat Feb 7 20:30:12 2004
CREATE DATABASE gzhs
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/ u01/oradata/gzhs/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/ u01/oradata/gzhs/temp01.dbf' SIZE 1000M REUSE AUTOEXTEND
ON NEXT 250M MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/ u01/oradata/gzhs/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED CHARACTER SET ZHS16GBK NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ( '/ u01/oradata/gzhs/redo01.log') SIZE 256M, GROUP 2 ( '/ u01/oradata/gzhs/redo02.log') SIZE 256M, GROUP 3 ( '/ u01/oradata/gzhs/redo03.log') SIZE 256M .............
rn

rn
Note that this is also referred to two kinds of materials on the OCP to create one of the ways UNDO tablespace
rn
The second part, found in the records to create UNDOTBS2 information:
rn

rn

Wed Mar 24 20:20:58 2004
/ * OracleOEM * / CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE '/ u01/oradata/gzhs/UNDOTBS2.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
Wed Mar 24 20:22:37 2004
Created Undo Segment _SYSSMU11 $
Created Undo Segment _SYSSMU12 $
Created Undo Segment _SYSSMU13 $
Created Undo Segment _SYSSMU14 $
Created Undo Segment _SYSSMU15 $
Created Undo Segment _SYSSMU16 $
Created Undo Segment _SYSSMU17 $
Created Undo Segment _SYSSMU18 $
Created Undo Segment _SYSSMU19 $
Created Undo Segment _SYSSMU20 $
Completed: / * OracleOEM * / CREATE UNDO TABLESPACE "UNDOTBS2" Wed Mar 24 20:24:25 2004 Undo Segment 11 Onlined Undo Segment 12 Onlined Undo Segment 13 Onlined Undo Segment 14 Onlined Undo Segment 15 Onlined Undo Segment 16 Onlined Undo Segment 17 Onlined Undo Segment 18 Onlined Undo Segment 19 Onlined Undo Segment 20 Onlined Successfully onlined Undo Tablespace 15. Undo Segment 1 Offlined Undo Segment 2 Offlined Undo Segment 3 Offlined Undo Segment 4 Offlined Undo Segment 5 Offlined Undo Segment 6 Offlined Undo Segment 7 Offlined Undo Segment 8 Offlined Undo Segment 9 Offlined Undo Segment 10 Offlined Undo Tablespace 1 successfully switched out ..............
rn

rn
Part III, the new UNDO tablespace be applied
rn
Wed Mar 24 20:24:25 2004
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE = MEMORY;
rn
We found that the problem here, created a new UNDO tablespace later, since the use of the pfile file, modify the entry into force only on the current instance, the operation has forgotten to modify the pfile file.
rn
If you are using spfile, change the default range is both, will also modify the spfile file, you can avoid the above problems.
rn
The fourth section, delete the information UNDOTBS1
rn
Wed Mar 24 20:25:01 2004
/ * OracleOEM * / DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
Wed Mar 24 20:25:03 2004
Deleted file / u01/oradata/gzhs/undotbs01.dbf
Completed: / * OracleOEM * / DROP TABLESPACE "UNDOTBS1" INCLUDI .............
This again re-start the database when the question arises, pfile can not find defined UNDOTBS1 and operation is a long time ago, no one can recall, or even not know what people's operations.
7. Change pfile, start the database
rn
Modify the undo tablespace
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management = AUTO
undo_retention = 10800
undo_tablespace = UNDOTBS2
rn
....
rn
bash-2.03 $ sqlplus "/ as sysdba"
SQL> select * from v $ version;
rn
BANNER
-------------------------------------------------- --------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL / SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
rn
SQL> exit
Here we can see that using the spfile can avoid the trouble of manually modify the pfile file, reducing the possibility of making mistakes.
rn
Since Oracle9i provides us with this new feature, we should learn to use it.

RMAN created physical standby 2009-03-30 22:49:29
Oracle deadlock related 2009-03-30 16:46:54
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Turning optimization oracle article 2009-03-26 21:39:35
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17

Oracle9i initialization parameter Chinese note (1)

kods.net — Mon, 14 Aug 2006 22:33:51+0000

To automatically enable this goal (on the assumption that the properties of alternate targets must be valid).
log_archive_dest_state_9:
Note: identify a specific target of the most recent log archive user-defined state.
Value range: ENABLE - If the target attribute is valid, to enable archive log target; DEFER - even if the target attribute and effective, but also delay in processing archive log target; or ALTERNATE - Delay in processing an archive log target, until the failure of another goal lead to an automatic enable this goal (on the assumption that the properties of alternate targets must be valid).
Log_archive_start:
Note: n is only in the database in a "archive log" mode, where applicable. It is specified redo log is automatically or manually copy the. The recommended value is TRUE, that the implementation of automatic archiving; otherwise would require manual intervention, use the ALTER SYSTEM ARCHIVE LOG ... command to stop the routine hangs.
Value range: TRUE | FALSE
Default Value: FALSE
log_archive_duplex_dest:
Description: Specifies LOG_ARCHIVE_DEST another outside the archive target. In Oracle8i Enterprise Edition, this parameter is LOG_ARCHIVE_DEST_n replaced.
Value range: a NULL string or any valid path or device name, except for the original partition.
Default: NULL
log_archive_format:
Description: LOG_ARCHIVE_FORMAT only in the database is in "Archive Log" mode situations. Text string with the variable% s (log sequence number), and% t (thread ID), in combination, is used to specify the archive redo log files the only file names. The string is attached to the LOG_ARCHIVE_DEST parameters.
Value range: any valid file name.
Default value: According to the operating system may be
log_archive_max_processes:
Description: Specifies the required number of ARCH processes. If LOG_ARCHIVE_START = TRUE, the value can be assessed in the routine starts;
You can also via SQL * Plus or SQL syntax when calling ARCH process evaluation.
Value range: 1 - 10 (including endpoints) of any integer.
Default value: 1
log_archive_dest_2:
Description: The five local (designated LOCATION) or remote (Designated SERVICE) target location in the second,
Archived redo log files can be copied on the location of these goals. This parameter is only in the Enterprise Edition oracle8i database or later version of the database effectively.
Value range: The syntax is (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]
Default: NULL
log_archive_min_succeed_dest:
Note: defined in the log file can be overwritten before it must be copied into the minimum number of goals. The value should be greater than or equal to the number of targets LOG_ARCHIVE_DEST_n in MANDATORY.
Value range: 1 - 5 (if LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST common use would be limited to 1-2).
Default value: 1
log_archive_trace:
Description: Control the output generated by the process of archiving logs. This process can be activated by the following
ARCn background process (designated in the output log ARCn)
A clear conversation called foreground process (in the output log is designated as ARCH) or "managed standby database" in the remote file server (RFS) process.
Value range:
0: Disable archive log tracking (which is the default setting)
1: The redo log files to track archive
2: Each archive log archive status of the tracking target
4: Tracking archiving operational phase
8: archive logs to track target activities
16: Tracking a detailed archive log target activities
32: follow-up archive log target parameter changes
64: Tracking ARCn process state activities, the default value: 0
log_archive_dest_10:
log_archive_dest_3:
Description: The five local (designated LOCATION) or remote (Designated SERVICE) target location in the third,
Archived redo log files can be copied on the location of these goals. This parameter is only in the Enterprise Edition oracle8i database or later version of the database effectively.
Value range: The syntax is (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]
Default: NULL
log_archive_dest:
Description: The five local (designated LOCATION) or remote (Designated SERVICE) target location in the third,
Archived redo log files can be copied on the location of these goals. This parameter is only in the Enterprise Edition oracle8i database or later version of the database effectively.
Value range: The syntax is (null_string | SERVICE = tnsnames-service | LOCATION = directory-spec) [MANDATORY | OPTIONAL] [REOPEN = integer]
Default: NULL
log_archive_dest_state_2:
Description: Specifies the appropriate archive log target parameters (only LOG_ARCHIVE_DEST_2) the availability of the state. If enabled, the log target will be archived. If you delay, the target will be excluded from archiving operations until re-enable the outside.
Value range: ENABLE | DEFER
Default: ENABLE
log_archive_dest_5:
Description: The five local (designated LOCATION) or remote (Designated SERVICE) target location in the fifth, archived redo log files can be copied on the location of these goals. This parameter is only in the Enterprise Edition oracle8i database or later version of the database effectively.
Value range: The syntax is (null_string | SERVICE = tn

SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
use sqlldr 1 Introduction 2009-03-27 19:39:01
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
Troubleshooting: the physical set up the backup database 2009-03-13 00:53:17
Diagnosis and principles of order 2009-02-25 20:27:28

The LUNUX created on the server ORACLE DATAGUARD

kods.net — Wed, 19 Jul 2006 00:17:26+0000

1. Check the primary database is running in archive mode:
SQL> archive log list

Database log mode Archive Mode

Enable automatic archiving

Archive destination / arch / ncfc / ncfcarchlog

A summary of the earliest log sequence 21

The next archived log sequence 23

Current log sequence 23

SQL>
If you are running in non-archive mode to archive mode to change it because the standby machine used to send archive log data synchronization.

SQL> shutdown immediat

Database has been closed.

The database has been uninstalled.

ORACLE instance shut down.

At this point in the parameter file, add the following parameters:

Log_archive_start = true

Log_archive_dest_1 = 'LOCATION = / arch / ncfc / ncfcarchlog'

Log_archive_dest_state_1 = enable

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

The database load is completed.

SQL> alter database archivelog;

SQL> alter database open;

2. . Preparation of libraries to create control file:

1, the main library is set to force logging mode
SQL> alter database force logging;

2, close the main library, do a cold backup

3, in the main library to create documents prepared by the library system:

SQL> alter database create standby controlfile as 'path';

Will create a good backup control file with the cold test prepared to prepare machine

3, modify the parameters of the main machine, according to documents prepared by

Main Library:

Add the following parameters:

log_archive_start = true

log_archive_format = archive% t_% s.dbf

log_archive_dest = / arch / ncfcarchlog

log_archive_dest_1 = 'SERVICE = STANDBY

log_archive_dest_state_1 = enable

Preparation of Library:

log_archive_start = true

log_archive_format = archive% t_% s.dbf

log_archive_dest = / arch / ncfcarchlog

log_archive_dest_state_2 = enable

standby_file_management = AUTO

remote_archive_enable = TRUE

standby_archive_dest = / oradata / ncfc / arch

fal_server = 'PRIMARY'

fal_client = 'STANDBY'

Which PRIMAYR STANDBY-based connection service names prepared by the library

Four . In the prepared base to create the same directory as the main library to be used to store data files and parameter file, if the master database and prepared the path to a different database, you need to set two parameters db_file_name_convert and log_file_name_convert

Note: If the primary database and standby database data files or online log files are not in the same directory, you need to set the file corresponds to the relationship between the use of db_file_name_convert and log_file_name_convert two parameters. db_file_name_convert instructions:

Syntax : DB_FILE_NAME_CONVERT = [()'string1' , 'string2' , 'string3' , 'string4' , ...[]]

For example : DB_FILE_NAME_CONVERT = ( '/ dbs/t1 /','/ dbs/t1/s_', 'dbs/t2 /', 'dbs/t2/s_')

For example: the first 1,3 months for the primary database file name string, the first 2,4 months for the standby database file name string, and so on, the length limitation.

log_file_name_convert instructions:

Syntax: LOG_FILE_NAME_CONVERT = [()'string1' , 'string2' , 'string3' , 'string4' , ...[]]

For example : LOG_FILE_NAME_CONVERT = ( '/ dbs/t1 /','/ dbs/t1/s_', 'dbs/t2 /', 'dbs/t2/s_')

5, configuration, standby machine TNSNAMES. ORA file

6 to start a database prepared by machine

$ sqlplus / nolog

SQL * Plus: Release 9.2.0.1.0 - Production on Tuesday November 15 10:24:51 2005

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

slq> conn / as sysdba

sql> startup nomount pfile = '/ u01/app/../primary.ora'

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

sql> alter database mount standby database;

Database has been changed

sql> alter database recover managed standby database disconnect from session;

Database has been changed

7, check the master database is synchronized

In the main library:

SQL> alter system switch logfile;

In the prepared libraries see whether the transmitted archive logs prepared by library

See Preparation Library aler log:

In case of application of the following that success:

Media Recovery Log / oradata / ncfc / arch / ARC00020.001

Media Recovery Log / oradata / ncfc / arch / ARC00021.001

Media Recovery Waiting for thread 1 seq # 22

Media Recovery Log / oradata / ncfc / arch / ARC00022.001

Media Recovery Waiting for thread 1 seq # 23

Media Recovery Log / oradata / ncfc / arch / ARC00023.001

Tue Nov 15 12:02:00 2005

Media Recovery Waiting for thread 1 seq # 24

At this point, DATAGUARD the successful completion of the database to do gymnastics! ! !

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

How To Enable / Disable Archive Log Mode on Oracle9i

kods.net — Fri, 23 Jun 2006 22:48:56+0000

Theme:	How To Enable / Disable Archive Log Mode on Oracle9i Real Application Cluster
	Document ID:	Note: 235,158.1	Type:	HOWTO
	Last revision date:	03-APR-2006	Status:	PUBLISHED

goal: How to Enable / Disable Archive Log In Real Application Cluster
fact: Oracle Server - Enterprise Edition Oracle 9
fact: Oracle9i Real Application Cluster (RAC)

fix:

1. Shut down all instances. 

2. Set the CLUSTER_DATABASE parameter to false on one instance in the parameter 
file. If you are using the server parameter file, make an SID-specific entry 
for this, like:

.CLUSTER_DATABASE= False
     .CLUSTER_DATABASE= False

For Modifying server parameter file (SPFILE) you can refer to :
Note:137483.1 How to Modify the Content of an SPFILE Parameter 
File 

3. Set the LOG_ARCHIVE_START parameter to true. 

4. Set the LOG_ARCHIVE_FORMAT and make sure the parameter containing the %t 
parameter includes the thread number in the archived logfile name. 

5. Set the cluster database wide LOG_ARCHIVE_DEST_1 parameter in the parameter 
file as follows: 

LOG_ARCHIVE_DEST_1='LOCATION=$ORACLE_BASE/oradata//archive'

Note : You can multiplex the destination to up to ten locations, refer to:
Note:66433.1 Oracle8i - Multiple Archive Destinations and 
Remote Archival 

To specify the archive log destinations on a per instance basis for a two-
instance cluster database, for example, set the parameter as follows:

.LOG_ARCHIVE_DEST_1='LOCATION=$ORACLE_BASE/oradata/gt;/archive' 
   .LOG_ARCHIVE_DEST_1='LOCATION=$ORACLE_BASE/oradata/gt;/archive' 

Archive logs can be on the shared disk on platforms that support CFS 
(Cluster File System).  For more information on this see the following note:

Note 183408.1
  Raw Devices and Cluster Filesystems With Real Application Clusters

6. Mount the database in exclusive mode for the instance on which you have set 
CLUSTER_DATABASE to false. 

7. Set the database in ARCHIVELOG mode :

SQL> ALTER DATABASE ARCHIVELOG;

8. Shutdown the instance.

9. Change the value of the CLUSTER_DATABASE parameter to true.

10. Startup all your instances. 

To disable archive logging, follow the same steps but use the NOARCHIVELOG 
clause of the ALTER DATABASE statement.

 I wrote an article  
http://blog.chinaunix.net/u/7667/showart.php?id=103063
 This can be and metalink  notes Together reading  .

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 to modify the oracle archive log mode

kods.net — Thu, 13 Apr 2006 01:06:33+0000

The more distant, and rely on the notes at that time, a simple summary of what, after all, not good to save paper ... ...

oracle of all the default configuration is not good, or else the service is unable to sell the money

After the database is installed by default, all are non-archive mode so that the security of the database, it is a very good, so the database must be amended to archive mode, single-instance database modifications are relatively simple, and rac if there little bit complicated, the following is a specific process:

Environment: AIX5.2 Oracle9204

Before implementation, if necessary, make a backup of the database is best

1, sql> create pfile = '/ home / oracle / t.ora' from spfile;

2, close all instances of

3, select one of the examples instance1, modify the newly generated pfile '/ home / oracle / t.ora', using the vi editor generated pfile, and modify the following parameters:
cluster_database = false

*. log_archive_format = 'log% t_% s.rac'

*. log_archive_start = true

db1.log_archive_dest = 'LOCATION = / archive1'

db2.log_archive_dest = 'LOCATION = / archive2'

/ archive1 / archive2 must be two nodes oracle user can read and write, can be achieved through the NFS

4, modify the parameters after the end to just modify the parameter file to start the database instance instance1

sql> startup nomount pfile = '/ home / oracle / t.ora'

5, so that the database to exclusive mode

sql> alter database mount exclusive

7, to change the archive mode

sql> alter database archivelog

Open Database

sql> alter database open

Now the database is in archive mode, and you can use the archive log list command to see the

8, shutdown the database

9, the pfile / home / oracle / t.ora parameter file cluster_database = true change it back

9, in the instance1 on startup pfile = '/ home / oracle / t.ora'

Update spfile parameters

sql> create spfile = / dev / rlv_spfile from pfile = '/ home / oracle / t.ora'

10, close the database instance

sql> shutdown immediate

Note that the default directory init $ SID.ora revised to spfile = '/ dev / rlv_spfile'

11, start two instances of

sql> startup

sql> startup

At this point the database is in archive mode of the

For the archive formats, automatic archiving, archive paths and other parameters you can alter system set changes, but note that log_archive_format parameter value must be with the% t thread number in order to distinguish between different instances of the generated archive log

In addition the database is a spfile to start, pay attention to the * / dbs directory init $ sid.ora File Recovery for

spfile = '/ dev / rlv_spfile'

Under the single-instance, modify the database archiving mode:

1, shutdown immediate

2, startup mount

3, alter database archivelog

4, alter database open

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

Modify the oracle database archiving properties

kods.net — Tue, 28 Mar 2006 10:49:11+0000

Modify the properties of the database archiving

svrmgrl> shutdown immediate
svrmgrl> startup mount
svrmgrl> alter database noarchivelog;
svrmgrl> alter database open;
svrmgrl> shutdown immediate
Modify the init file
# Log_archive_start = true
# Log_archive_dest =% ORACLE_HOME% \ database \%% ORACLE_SID%% \ archive
# Log_archive_format = "%%% ORACLE_SID%%% T% TS% S. ARC"

svrmgrl> startup

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

Hot backup program implementation

kods.net — Mon, 27 Mar 2006 13:09:14+0000

A prerequisite for hot backup the database: the database is running in archive mode
Oracle database redo log records in the database on all its activities. LGWR background process in a circular way to write these log files, redo logs from the first one to the next one, until the group's last one, then starting from the first to write a log.
In non-archive mode, when the loop writes a log file the last, after the first one on the rewrite log. Therefore, non-archival database recovery mode, the only way is to use cold backup.
In the archive mode, when the redo log is full, an ARCH background process to read all of the redo logs, and then writes archive logs. Therefore, you can use the hot backup and point of recovery. In the archive log mode, if the purpose of archiving the log space is full, the database activity will be suspended, only to release some space, the database can continue to run. Usually, background_dump_destination will produce a trace file to display the archived issues.
Oracle database is installed by default to run in non-archive mode, through the following steps can be converted from non-archive mode archive mode:
(1) Edit parameter file init.ora, set the following parameters
# Set the database auto-archiving
log_archive_start = true
# Set the archive log file directory, the directory must first be established and there are a lot of space available
log_archive_dest_1 = "location =% oracle_base% \ oradata \% oracle_sid% \ archive"
# Set the archive log file name format. % s indicated that sequence number,% t thread number indicated.
log_archive_format = "% oracle_sid%% T% S. ARC"
(2) Run the following command svrmgrl
svrmgrl> connect internal / YOUR_PASSWORD
svrmgrl> shutdown immediate;
svrmgrl> startup mount exclusive;
svrmgrl> alter database archivelog;
svrmgrl> alter database open;
svrmgrl> shutdown immediate;
(3) The implementation of the database cold backup when the database is running in archive mode, the database recovery process requires the use of cold backup, archive logs are required.
(4) normal to start the database and confirm that the database is running in archive mode
svrmgrl> startup;
svrmgrl> select * from v $ database; its log_mode will show whether the database archiving mode
svrmgrl> archive log list; will also show whether the archive mode of the database if the Oracle database is running in archive mode, when the database maintenance, you may need to suspend the database archiving, after the completion of the maintenance and re-start the archive mode. Through the following steps can be converted from a non-archive mode archive mode:
svrmgrl> connect internal / YOUR_PASSWORD
svrmgrl> shutdown immediate;
svrmgrl> startup mount exclusive;
svrmgrl> alter database noarchivelog;
svrmgrl> alter database open;
svrmgrl> select * from v $ database; its log_mode will show whether the database archiving mode
svrmgrl> archive log list; will also show whether the database archiving mode
4.2 hot backup database, steps (1) copy init.ora file to a backup directory (parameter file starts the database is shut down state).
(2) will need to be placed back up a tablespace begin backup mode.
(3) the use ocopy.exe copy of the table space, and then place the end of the table space backup mode (ocopy.exe can not be used directly on-line copy of the database files).
(4) in the database for each table space steps 2 and 3 (you can view and v $ datafile view dba_tablespaces database which table space and data files).
(5) by svrmgrl perform archive log list command to obtain the current log sequence number, from the oldest online log sequence beginning to the current log sequence for the online redo log should be a hot backup part of it.
(6) svrmgrl perform alter system switch logfile; command to force log switch so that all of the logs have been archived.
(7) use alter database backup controlfile to trace; order to obtain a control file backup, can go to% oracle_base% \ admin \% oracle_sid% \ udump directory to find the latest trace file, including reconstruction of all the command control file.
(8) using windows nt command from the% log_archive_dest% copy archived log files to the backup directory.
4.3 hot backup database script files hotbak.cmd
rem Oracle database hot backup script file name hotbak.cmd
rem for the Windows NT/2000 platform, has been rigorous testing, we can test! ! !
rem after the execution of the script in the c: \ temp directory created minlog.sql, minlog.cmd,
rem tblspc01.sql, tblspc02.sql, control01.sql, control02.sql,
rem maxlog.sql, maxlog.cmd, archive.sql, archive.cmd such as 10 script file.
rem set the SID
set oracle_sid = tmq
rem set the initialization parameter file
set my_pfile = e: \ oracle \ ora81 \ database \ inittmq.ora
set my_ifile = E: \ Oracle \ ADMIN \ tmq \ pfile \ init.ora
rem set the archive log directory
set my_archive_dir = E: \ Oracle \ ORADATA \ tmq \ archive
rem set the internal account's password
set my_password = oracle
the establishment of temporary directory rem
mkdir c: \ temp
set my_temp = c: \ temp
rem create a backup directory, the directory must have a large enough space for the storage of the database backup file
mkdir e: \ backup
set my_bakdir = e: \ backup
rem ===== (1) to start the backup parameter file =======
echo Backing up the init.ora file
copy% my_pfile%% my_bakdir%
copy% my_ifile%% my_bakdir%
rem ===== (2) to create a script to obtain minlog.sql smallest online redo log sequence number =====
echo set heading off;>% my_temp% \ minlog.sql
echo set feedback off;>>% my_temp% \ minlog.sql
echo spool% my_temp% \ minlog.cmd;>>% my_temp% \ minlog.sql
echo select 'set minlog_value =' ^ | ^ | min (sequence #) from v $ log where upper (status) = upper ( 'INACTIVE');>>% my_temp% \ minlog.sql
echo spool off;>>% my_temp% \ minlog.sql
echo select 'exit;' from dual;>>% my_temp% \ minlog.sql
echo exit;>>% my_temp% \ minlog.sql
rem ===== (3) run minlog.sql to genrate minlog.cmd ========
sqlplus internal /% my_password% @% my_temp% \ minlog.sql
rem ===== (4) run minlog.cmd to set minlog_value ========
call% my_temp% \ minlog.cmd
rem === (5) to create a script tblspc01.sql backup data files used to generate script file tblspc02.sql ===
echo set heading off;>% my_temp% \ tblspc01.sql
echo set feedback off;>>% my_temp% \ tblspc01.sql
echo spool% my_temp% \ tblspc02.sql;>>% my_temp% \ tblspc01.sql
echo select 'connect internal /% my_password%;' from dual;>>% my_temp% \ tblspc01.sql
echo select 'alter tablespace' ^ | ^ | tablespace_name ^ | ^ | 'begin backup;' from dba_tablespaces;>>% my_temp% \ tblspc01.sql
echo select 'host start / wait ocopy' ^ | ^ | file_name ^ | ^ | '% my_bakdir%;' from dba_data_files;>>% my_temp% \ tblspc01.sql
echo select 'alter tablespace' ^ | ^ | tablespace_name ^ | ^ | 'end backup;' from dba_tablespaces;>>% my_temp% \ tblspc01.sql
echo spool off;>>% my_temp% \ tblspc01.sql
echo select 'exit;' from dual;>>% my_temp% \ tblspc01.sql
echo exit;>>% my_temp% \ tblspc01.sql
rem ===== (6) run tblspc01.sql to genrate tblspc02.sql ========
sqlplus internal /% my_password% @% my_temp% \ tblspc01.sql
rem ===== (7) on svrmgrl run tblspc02.sql to backup the datafiles ========
svrmgrl @% my_temp% \ tblspc02.sql
rem ===== (8) to create a script maxlog.sql maximize online redo log sequence number =====
echo set heading off;>% my_temp% \ maxlog.sql
echo set feedback off;>>% my_temp% \ maxlog.sql
echo spool% my_temp% \ maxlog.cmd;>>% my_temp% \ maxlog.sql
echo select 'set maxlog_value =' ^ | ^ | max (sequence #) from v $ log where upper (status) = upper ( 'CURRENT');>>% my_temp% \ maxlog.sql
echo spool off;>>% my_temp% \ maxlog.sql
echo select 'alter system switch logfile;' from dual;>>% my_temp% \ maxlog.sql
echo select 'exit;' from dual;>>% my_temp% \ maxlog.sql
echo exit;>>% my_temp% \ maxlog.sql
rem ===== (9) run maxlog.sql to genrate maxlog.cmd ========
sqlplus internal /% my_password% @% my_temp% \ maxlog.sql
rem ===== (10) run maxlog.cmd to set maxlog_value ========
call% my_temp% \ maxlog.cmd
rem == (11) to create control01.sql scripts used to generate a backup control file script file control02.sql ===
echo set heading off;>% my_temp% \ control01.sql
echo set feedback off;>>% my_temp% \ control01.sql
echo spool% my_temp% \ control02.sql;>>% my_temp% \ control01.sql
echo select 'connect internal /% my_password%;' from dual;>>% my_temp% \ control01.sql
echo select 'alter database backup controlfile to trace;' from dual;>>% my_temp% \ control01.sql
echo spool off;>>% my_temp% \ control01.sql
echo select 'exit;' from dual;>>% my_temp% \ control01.sql
echo exit;>>% my_temp% \ control01.sql
rem ===== (12) run control01.sql to genrate control02.sql ========
sqlplus internal /% my_password% @% my_temp% \ control01.sql
rem ===== (13) on svrmgrl run control02.sql to backup the datafiles ========
svrmgrl @% my_temp% \ control02.sql
rem ===== (14) to create archive.sql scripts used to generate the backup archive log files script file archive.cmd =====
echo set heading off;>% my_temp% \ archive.sql
echo set feedback off;>>% my_temp% \ archive.sql
echo spool% my_temp% \ archive.cmd;>>% my_temp% \ archive.sql
rem only backup and hot backup of this log files related to
echo select 'copy' ^ | ^ | name ^ | ^ | '% my_bakdir%' from V_ $ ARCHIVED_LOG where sequence # between% minlog_value% and% maxlog_value% +1;>>% my_temp% \ archive.sql
echo spool off;>>% my_temp% \ archive.sql
echo select 'exit;' from dual;>>% my_temp% \ archive.sql
echo exit;>>% my_temp% \ archive.sql
rem ===== (15) run archive.sql to genrate archive.cmd ========
sqlplus internal /% my_password% @% my_temp% \ archive.sql
rem ===== (16) run archive.cmd to backup the archive log files ========
call% my_temp% \ archive.cmd
rem ===== the end ======
echo ===== the hot backup is completed!!! ====== 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

9i for windows startup to automatic archive log mode

kods.net — Mon, 09 May 2005 12:28:07+0000

SQL> connect sys/test as sysdba;
 Connected  .
SQL> archive log list;
 Database log mode non-archive mode  
 Disable the auto-archive  
 Archive destination              D:\oracle\ora92\RDBMS
 The earliest summary log sequence       5
 The current log sequence             7
SQL> shutdown
 The database is closed  .
 The database has been unloaded  .
ORACLE  Routine has been turned off  .
SQL> startup mount;
ORACLE  Routine has been started  .

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
 After the database mount  .
SQL> alter database archivelog;

 The database has changed  .

SQL> archive log start;
 The statement processed  
SQL> archive log list;
 Database log mode archive mode  
 AutoArchive is enabled  
 Archive destination              D:\oracle\ora92\RDBMS
 The earliest summary log sequence       5
 The next archive log sequence     7
 The current log sequence             7
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\test2\arc
ve';

 The system has been changed  .

SQL> alter system set log_archive_format='%t_%s.dbf' scope=spfile;

 The system has been changed  .

SQL> shutdown immediate;
ORA-01109:  The database is not open  

 The database has been unloaded  .
ORACLE  Routine has been turned off  .
SQL> startup
ORACLE  Routine has been started  .

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
 After the database mount  .
 The database is already open  .
SQL> archive log list;
 Database log mode archive mode  
 Disable the auto-archive  
 Archive destination              D:\oracle\oradata\test2\archive
 The earliest summary log sequence       5
 The next archive log sequence     7
 The current log sequence             7
SQL> Alter system set log_archive_start=true scope=spfile;

 The system has been changed  .

SQL>  shutdown immediate;
 The database is closed  .
 The database has been unloaded  .
ORACLE  Routine has been turned off  .
SQL>  startup;
ORACLE  Routine has been started  .

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
 After the database mount  .
 The database is already open  .
SQL>  archive log list;
 Database log mode archive mode  
 AutoArchive is enabled  
 Archive destination              D:\oracle\oradata\test2\archive
 The earliest summary log sequence       5
 The next archive log sequence     7
 The current log sequence             7
SQL>

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

[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

archive format

See whether the archive and modify oracle database archiving mode

See whether the archive and modify oracle database archiving mode

Related Posts

scripts of create database

Related Posts

Dataguard create a physical standby steps [RMAN copy]

Related Posts

Dataguard steps to create the physical standby [direct copy files / RMAN]

Related Posts

Dataguard create a physical standby steps [direct copy files / RMAN]

Related Posts

rman backup summary of knowledge (transfer)

Related Posts

Oracle database backup methods -2

Related Posts

The oracle set to archive mode is the method

Related Posts

7.4.2 ORACLE database system

7.4.2 ORACLE database system

Related Posts

oracle9i began studying the problems in, winxp system

Related Posts

Oracle Diagnostic Case-Spfile case of a (turn)

Related Posts

Oracle9i initialization parameter Chinese note (1)

Related Posts

The LUNUX created on the server ORACLE DATAGUARD

Related Posts

How To Enable / Disable Archive Log Mode on Oracle9i

Related Posts

RAC database archiving mode change

Related Posts

RAC to modify the oracle archive log mode

Related Posts

Modify the oracle database archiving properties

Related Posts

Hot backup program implementation

Related Posts

9i for windows startup to automatic archive log mode

Related Posts

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

Related Posts