archive directory http://www.kods.net/ archive directory Fri, 30 Jul 2010 03:57:18+0000 http://www.kods.net/ en Oracle archive log deletion http://www.kods.net/oracle-archive-log-deletion/ http://www.kods.net/oracle-archive-log-deletion/#comments Mon, 31 May 2010 06:47:12+0000 kods.net http://www.kods.net/oracle-archive-log-deletion/
controlfile archivelog still record such information, in the oracle's OEM manager has a visual log show that when we hand-Qing Chu archive directory file, these records have not been removed we are from the controlfile in, but also is the oracle does not know these files does not exist! this time we have to do manual removal, then I passed the following test, you can try this method:

1. Into rman

2. Connect target /

3. Crosscheck archivelog all;

4. Delete expired archivelog all;

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

ORACLE right to delete archive and recovery space approach

ORACLE right to delete archive and recovery space approach

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

Delete archive log process

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

Into the ORACLE data backup tools

rman target /

Or rman target / @ orcl

In the command window inside the implementation of the

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

Explain

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

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

DELETE ARCHIVELOG FROM TIME 'SYSDATE-7';

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

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

This still left a not in the RMAN archive management

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

crosscheck archivelog all;

delete expired archivelog all;

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

3. A brief overview report obsolete command

Report using the report obsolete command expired backup

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of obsolete backups and copies

Type Key Completion Time Filename / Handle

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

Backup Set 125 01-NOV-04

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

Backup Set 131 04-NOV-04

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

....

Backup Set 173 06-DEC-04

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

Backup Set 179 11-DEC-04

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

.....

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

Backup Set 190 17-DEC-04

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

Backup Set 191 20-DEC-04

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

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

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

.....

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

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

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

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

This all-inclusive network transferred from ☆ ★ ★ ☆ - http://www.baoluowanxiang.com reproduced, please indicate the source, tort reserved!
Original link: http://www.baoluowanxiang.com/a/website/database/2010/0522/877.html
Related Posts
]]> http://www.kods.net/oracle-archive-log-deletion/ Oracle database monitoring shell script used http://www.kods.net/oracle-database-monitoring-shell-script-used/ http://www.kods.net/oracle-database-monitoring-shell-script-used/#comments Thu, 01 Apr 2010 22:35:22+0000 kods.net http://www.kods.net/oracle-database-monitoring-shell-script-used/ Check the availability of examples

Check the availability of the listener

Check the alert log file error message

Log file in the store empty place full of old log files before

Analysis of table and index for better performance

Check tablespace usage

Identify invalid objects

Monitoring Users and Services

Basic knowledge of Unix DBA required

Basic UNIX commands

Here are some commonly used Unix commands:

ps - display process grep - Search file in a text mode mailx - read or send mail cat - link files or display them cut - choose to display the column awk - pattern matching language df - display remaining Disk Space

The following is a DBA how to use these commands some examples:

Show the available server instance:

$ Ps-ef | grep smonoracle 21832 1 0 Feb 24? 19:05 ora_smon_oradb1oracle 898 1 0 Feb 15? 0:00 ora_smon_oradb2dliu 25199 19038 0 10:48:57 pts / 6 0:00 grep smonoracle 27798 1 0 05:43: 54? 0:00 ora_smon_oradb3oracle 28781 1 0 Mar 03? 0:01 ora_smon_oradb4,

Display monitor is available on the server:

$ Ps-ef | grep listener | grep-v grep (Translator note: grep command should be added-i parameter, which grep-i listener, the role of the parameter is case insensitive, because the listener is sometimes capitalized, which Time will not see results) oracle 23879 1 0 Feb 24? 33:36 / 8.1.7/bin/tnslsnr listener_db1-inheritoracle 27939 1 0 05:44:02? 0:00 / 8.1.7/bin/tnslsnr listener_db2 -inheritoracle 23536 1 0 Feb 12? 4:19 / 8.1.7/bin/tnslsnr listener_db3-inheritoracle 28891 1 0 Mar 03? 0:01 / 8.1.7/bin/tnslsnr listener_db4-inherit

See Oracle archive directory file system usage

$ Df-k | grep oraarch / dev / vx / dsk / proddg / oraarch 71123968 4754872 65850768 7% / u09/oraarch

Statistics alter.log file lines:

$ Cat alert.log | wc-l2984

Alert.log file lists all the Oracle error message:

$ Grep ORA-alert.logORA-00600: internal error code, arguments: [kcrrrfswda.1], [], [], [], [], [] ORA-00600: internal error code, arguments: [1881], [25,860,496], [25857716], []

CRONTAB basic

A crontab file contains six fields:

0-59 minutes

Hours 0-23

The first few days, 1-31 months

Month 1 - 12

Week 0 - 6, with 0 = Sunday

Unix Shell command or script

To edit a crontab file, type: Crontab-e

To view a crontab file, enter:

Crontab-l0 4 * * 5 / dba/admin/analyze_table.ksh30 3 * * 3,6 / dba / admin / hotbackup.ksh / dev / null 2> & 1

In the example above, the first line of the table shows a script at 4:00 am each week, running 5. The second line shows an implementation of the hot backup script every Wednesday and Saturdays 3:00 a.m. to run.

Shell script to monitor a database of commonly used

Here are the eight shell script DBA coverage for 90% of the daily monitoring of work, you may also need to modify the UNIX environment variables.

Check the availability of Oracle instances

oratab file lists all the databases on the server

$ Cat / var / opt / oracle / oratab

# # / Var / opt / oracle / oratab # #

oradb1: / u01/app/oracle/product/8.1.7: Yoradb2: / u01/app/oracle/product/8.1.7: Yoradb3: / u01/app/oracle/product/8.1.7: Noradb4: / u01 / app/oracle/product/8.1.7: Y

The following script checks oratab file lists all of the database, and find out the status of the database (start or turn off)

# # Ckinstance.ksh ORATAB = / var / opt / oracle / oratabecho `date` echo Oracle Database (s) Status `hostname`: db = `egrep-i: Y |: N $ ORATAB | cut-d:-f1 | grep-v # | grep-v * `pslist =` ps-ef | grep pmon `for i in $ db; doecho $ pslist | grep ora_pmon_ $ i> / dev / null 2> $ 1if (($?)); thenecho Oracle Instance - $ i: Downelseecho Oracle Instance - $ i: Upfidone

Use the following command to verify that the script is executed:

$ Chmod 744 ckinstance.ksh $ ls-l ckinstance.ksh-rwxr--r - 1 oracle dba 657 Mar 5 22:59 ckinstance.ksh *

The following are examples of usability report:

$ Ckinstance.kshMon Mar 4 10:44:12 PST 2002 Oracle Database (s) Status for DBHOST server: Oracle Instance - oradb1: UpOracle Instance - oradb2: UpOracle Instance - oradb3: DownOracle Instance - oradb4: Up

Check the availability of Oracle listener

Following a similar script to check the Oracle listener. If the listener is stopped, the script will restart the listener:

cklsnr.sh bin / kshDBALIST = primary.dba @ company.com, another.dba @ company.com; export DBALISTcd / var / opt / oraclerm-f lsnr.exist ps-ef | grep mylsnr | grep-v grep> lsnr. existif [-s lsnr.exist] thenecho elseecho Alert | mailx-s Listener 'mylsnr' on `hostname` is down $ DBALIST TNS_ADMIN = / var / opt / oracle; export TNS_ADMINORACLE_SID = db1; export ORACLE_SID ORAENV_ASK = NO; export ORAENV_ASKPATH = $ PATH: / bin: / usr / local / bin; export PATH. oraenvLD_LIBRARY_PATH = $ (ORACLE_HOME) / lib; export LD_LIBRARY_PATHlsnrctl start mylsnrfi

Check Alert Logs (ORA-XXXXX)

Each script uses a number of environmental variables can be put in a profile:

oracle.profile EDITOR = vi; export EDITOR ORACLE_BASE = / u01/app/oracle; export ORACLE_BASE ORACLE_HOME = $ ORACLE_BASE/product/8.1.7; export ORACLE_HOME LD_LIBRARY_PATH = $ ORACLE_HOME / lib; export LD_LIBRARY_PATH TNS_ADMIN = / var / opt / oracle ; export TNS_ADMIN NLS_LANG = american; export NLS_LANG NLS_DATE_FORMAT = 'Mon DD YYYY HH24: MI: SS'; export NLS_DATE_FORMAT ORATAB = / var / opt / oracle / oratab; export ORATAB PATH = $ PATH: $ ORACLE_HOME: $ ORACLE_HOME / bin: / usr / ccs / bin: / bin: / usr / bin: / usr / sbin: / sbin: / usr / openwin / bin: / opt / bin:.; export PATH DBALIST = primary.dba @ company.com, another . dba@company.com; export DBALIST

The following script first calls oracle.profile to set all the environment variables. If any of the Oracle error, the script will send a warning to the DBA's email.

# Ckalertlog.sh ###!/ bin / ksh .. / etc / oracle.profilefor SID in `cat $ ORACLE_HOME / sidlist` docd $ ORACLE_BASE / admin / $ SID / bdumpif [-f alert_ $ (SID). Log] thenmv alert_ $ (SID). log alert_work.logtouch alert_ $ (SID). logcat alert_work.log>> alert_ $ (SID). histgrep ORA-alert_work.log> alert.errfiif [`cat alert.err | wc-l` -gt 0] thenmailx-s $ (SID) ORACLE ALERT ERRORS $ DBALIST <alert.errfirm-f alert.errrm-f alert_work.logdone

Remove the old archive

The following script will be 90% in the log file when the empty capacity of the old archive files:

$ Df-k | grep archFilesystem kbytes used avail capacity Mounted on / dev / vx / dsk / proddg / archive 71123968 30210248 40594232 43% / u08/archive

# Clean_arch.ksh! / Bin / kshdf-k | grep arch> dfk.resultarchive_filesystem = `awk-F '(print $ 6)' dfk.result` archive_capacity = `awk-F '(print $ 5)' dfk.result` if [[$ archive_capacity> 90%]] thenecho Filesystem $ (archive_filesystem) is $ (archive_capacity) filled # try one of the following option depend on your needfind $ archive_filesystem-type f-mtime +2-exec rm-r (); tar rmanfi

Analysis of tables and indexes (to get better performance)

Below I will show if the transmission parameters to a script:

analyze_table.sh # #

bin / ksh # input parameter: 1: password # 2: SID if (($#< 1)) then echo "Please enter oracle user password as the first parameter!" exit 0 fi if (($#< 2)) then echo "Please enter instance name as the second parameter!" exit 0 fi

To pass parameters to execute the script, enter:

$ Analyze_table.sh manager oradb1

The first part of the script had a analyze.sql file, which contains analysis of the table with the statement. The second part of the script all the tables:

analyze_table.sh

sqlplus-s <oracle / $ 1 @ $ 2set heading offset feed offset pagesize 200set linesize 100spool analyze_table.sqlselect ANALYZE TABLE | | owner | |. | | segment_name | | ESTIMATE STATISTICS SAMPLE 10 PERCENT; from dba_segmentswhere segment_type = TABLEand owner not in (SYS , SYSTEM); spool offexit! sqlplus-s <oracle / $ 1 @ $ 2 @. / analyze_table.sqlexit!

The following is an example of analyze.sql:

$ Cat analyze.sqlANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE PATROL.P $ LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT;

Check the table space to use

The following script detects the use of table space. If the table space only 10%, it will send a warning email.

ck_tbsp.sh

! / Bin / kshsqlplus-s <oracle / $ 1 @ $ 2set feed offset linesize 100set pagesize 200spool tablespace.alertSELECT F. TABLESPACE_NAME, TO_CHAR ((T. TOTAL_SPACE - F. FREE_SPACE), 999,999) "USED (MB)", TO_CHAR ( F. FREE_SPACE, 999,999) "FREE (MB)", TO_CHAR (T. TOTAL_SPACE, 999,999) "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE / T.TOTAL_SPACE) * 100)), 999) | |% PER_FREEFROM (SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS * (SELECT VALUE/1024FROM V \ $ PARAMETER WHERE NAME = db_block_size) / 1024)) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576) ) TOTAL_SPACEFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) TWHERE F. TABLESPACE_NAME = T. TABLESPACE_NAMEAND (ROUND ((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) <10; spool offexit! if [`cat tablespace.alert | wc-l`-gt 0] thencat tablespace.alert-l tablespace.alert> tablespace.tmpmailx-s "TABLESPACE ALERT for $ (2)" $ DBALIST <tablespace.tmpfi

Examples of output warning email as follows:

TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE SYSTEM 2,047 203 2,250 9% STBS01 302 25 327 8% STBS02 241 11 252 4% STBS03 233 19 252 8%

Find out invalid database objects

Find out the following invalid database objects:

# # Invalid_object_alert.sh # #

#! / Bin / ksh. / Etc / oracle.profile sqlplus-s <oracle / $ 1 @ $ 2 set feed off set heading off column object_name format a30 spool invalid_object.alert SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,

STATUS FROM DBA_OBJECTS WHERE STATUS =

INVALID ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME; spool off exit! If [`cat invalid_object.alert | wc-l`-gt 0] then mailx-s "INVALID OBJECTS for $ (2)" $ DBALIST <invalid_object.alert fi $ cat invalid_object.alertOWNER OBJECT_NAME OBJECT_TYPE STATUS ------------------------------------------- -

HTOMEH DBMS_SHARED_POOL PACKAGE BODY INVALIDHTOMEH X_ $ KCBFWAIT VIEW INVALIDIMON IW_MON PACKAGE INVALIDIMON IW_MON PACKAGE BODY INVALIDIMON IW_ARCHIVED_LOG VIEW INVALIDIMON IW_FILESTAT VIEW INVALIDIMON IW_SQL_FULL_TEXT VIEW INVALIDIMON IW_SYSTEM_EVENT1 VIEW INVALIDIMON IW_SYSTEM_EVENT_CAT VIEW INVALIDLBAILEY CHECK_TABLESPACE_USAGE PROCEDURE INVALIDPATROL P $ AUTO_EXTEND_TBSP VIEW INVALIDSYS DBMS_CRYPTO_TOOLKIT PACKAGE INVALIDSYS DBMS_CRYPTO_TOOLKIT PACKAGE BODY INVALIDSYS UPGRADE_SYSTEM_TYPES_TO_816 PROCEDURE INVALIDSYS AQ $ _DEQUEUE_HISTORY_T TYPE INVALIDSYS HS_CLASS_CAPS VIEW INVALID SYS HS_CLASS_DD VIEW INVALID

Monitor users and services (deadlocks, etc.)

The following script in a deadlock occurs when sending a warning e-mail:

deadlock_alert.sh

# #! / Bin / ksh .. / etc / oracle.profilesqlplus-s <oracle / $ 1 @ $ 2set feed offset heading offspool deadlock.alertSELECT SID, DECODE (BLOCK, 0, NO, YES) BLOCKER, DECODE (REQUEST, 0 , NO, YES) WAITERFROM V $ LOCK WHERE REQUEST> 0 OR BLOCK> 0 ORDER BY block DESC; spool offexit! if [`cat deadlock.alert | wc-l`-gt 0] thenmailx-s "DEADLOCK ALERT for $ ( 2) "$ DBALIST <deadlock.alertfi

Conclusion

0,20,40 7-17 * * 1-5 / dba / scripts / ckinstance.sh> / dev / null 2> & 10,20,40 7-17 * * 1-5 / dba / scripts / cklsnr.sh> / dev / null 2> & 10,20,40 7-17 * * 1-5 / dba / scripts / ckalertlog.sh> / dev / null 2> & 130 * * * 0-6 / dba / scripts / clean_arch.sh> / dev / null 2> & 1 * 5 * * 1,3 / dba / scripts / analyze_table.sh> / dev / null 2> & 1 * 5 * * 0-6 / dba / scripts / ck_tbsp.sh> / dev / null 2> & 1 * 5 * * 0-6 / dba / scripts / invalid_object_alert.sh> / dev / null 2> & 10,20,40 7-17 * * 1-5 / dba / scripts / deadlock_alert.sh> / dev / null 2> & 1

Through the above script, can greatly reduce your work. You can use these is to do more important tasks such as performance tuning.
Related Posts
]]> http://www.kods.net/oracle-database-monitoring-shell-script-used/ Will be set to archive mode oracle method http://www.kods.net/will-be-set-to-archive-mode-oracle-method/ http://www.kods.net/will-be-set-to-archive-mode-oracle-method/#comments Wed, 31 Mar 2010 16:42:50+0000 kods.net http://www.kods.net/will-be-set-to-archive-mode-oracle-method/
Manually create the archive directory on the primary server,

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

$ mkdir-p oradata / szdb / archive # szdb the directory name according to the database SID for the specified number, in fact, this directory can be any specified

$ mkdir-p oradata / szdb / standbyarchive # If the master server may be used as backup server, then the establishment of 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 operation to establish the path to exactly the same

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, so changes take effect the results of

SQL> shutdown immediate;

View archive log 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 the above operation will be in the database spfile@sid.ora and init@sid.ora file to 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 actually exist, and the oracle user has write permission is

Notes: @ sid on behalf of the actual database, sid number, for example, in the practical operation of these two file names are spfileszdb.ora, initszdb.ora
Related Posts
]]> http://www.kods.net/will-be-set-to-archive-mode-oracle-method/ oracle-archive mode http://www.kods.net/oracle-archive-mode/ http://www.kods.net/oracle-archive-mode/#comments Tue, 02 Feb 2010 01:10:44+0000 kods.net http://www.kods.net/oracle-archive-mode/ 1.select name, log_mode from v $ database;
NAME LOG_MODE
------------------ ------------------------
QUERY NOARCHIVELOG
2. Using the ARCHIVE LOG LIST command
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / data/oracle/product/10.2.0/db_1 / / dbs / arch
Oldest online log sequence 739
Current log sequence 741
2. . What is the Oracle archive mode?
Oracle databases online redo logs, this log is to record the changes made to the database, such as insert, delete, update data on these operations are recorded in online redo log inside. General database at least two online redo log group. When an online redo log group is filled, when log switch occurs, when the online redo log group 2 as the current use of the log, when the filled online redo log group 2 when the log switch will happen , to write the online redo log group 1, it is so repeatedly.
If the database is in non archive mode, on-line will be discarded when you log in the switch. While in archive mode, when the log switch occurs when the logs will be switched archiving. For example, the current use of online redo log 1, when a filled at a time when the log switch, start writing online redo log 2, when the contents of an online redo log will be copied to another specified directory. This directory is called the archive directory, copy the file called archived redo log.
When it is running a database using the archive can be disaster recovery.
1. Archive log mode and non-archive log mode of the difference between the non-archive log mode can only do cold backup, and recovery can only do a full backup. The latest full backup to the system error data for the period can not be restored.
Archiving can be done hot backup mode, and can do incremental backups can be done partially.
With the ARCHIVE LOG LIST can see a view mode state, or non-archive mode archive log mode.

Three . Configuration database archiving mode

Changes in the non-archive mode to archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE ARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

5) SQL> do a full backup, as non-archive log mode for the resulting backup archive log mode no longer be used. This step is very important to the non-!

To change the archive mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

3. Enable automatic archiving: LOG_ARCHIVE_START = TRUE
Archiving mode, the log file group not allowed to be overwritten (rewritten), when filled, after the log file, without manual archiving, then the system will hang, know that archiving is completed.
At this time can only be read but not write.
During operation shut down and restart the process of archiving logs
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

4. Manual archiving: LOG_ARCHIVE_START = FALSE
Archive the current log file
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Archive log files serial number 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archiving of all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
To change the archive log target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& PATH';

5. Archive mode and non-archive mode conversion

The reverse process of Step 4.

6. Configure multiple archive processes
Q: When do you need to use multiple archiving process?
A: If the archive process will consume a large amount of time, you can start multiple archiving process, which is a dynamic parameter, you can use ALTER SYSTEM dynamic modification.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;
Oracle9i can specify up to 10 archiving process and the archiving process-related dynamic performance views
v $ bgprocess, v $ archive_processes

7. Configuration archiving target, multi-archiving target, remote archiving goals, objectives LOG_ARCHIVE_DEST_n archive log format archive
Local archiving objectives:
SQL> LOG_ARCHIVE_DEST_1 = "LOCATION = D: ORACLEARCHIVEDLOG";
Remote archive target:
SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1";
Mandatory archiving objectives, and if an error, 600 seconds and try again:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION = E: ORACLEARCHIVEDLOG MANDATORY REOPEN = 600";
Optional archiving objectives, and if wrong, abandon the archive:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION = E: ORACLEARCHIVEDLOG OPTIONAL";

Filed target state: the goals and turn off archiving archiving archiving goals off target 1
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER
Open the archive target 2
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Archive log format
LOG_ARCHIVE_FORMAT

8. Access to archived log information
V $ ARCHVIED_LOG
V $ ARCHVIE_DEST
V $ LOG_HISTORY
V $ DATABASE
V $ ARCHIVE_PROCESSES

ARCHIVE LOG LIST;
Related Posts
]]> http://www.kods.net/oracle-archive-mode/ oracle10g reproduced archive log mode http://www.kods.net/oracle10g-reproduced-archive-log-mode/ http://www.kods.net/oracle10g-reproduced-archive-log-mode/#comments Sun, 17 Jan 2010 06:12:56+0000 kods.net http://www.kods.net/oracle10g-reproduced-archive-log-mode/ 1. See whether the oracle database archive mode:
1.select name, log_mode from v $ database;
NAME LOG_MODE
------------------ ------------------------
QUERY NOARCHIVELOG
2. Using the ARCHIVE LOG LIST command
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / data/oracle/product/10.2.0/db_1 / / dbs / arch
Oldest online log sequence 739
Current log sequence 741
2. . What is the Oracle archive mode?
Oracle databases online redo logs, this log is to record the changes made to the database, such as insert, delete, update data on these operations are recorded in online redo log inside. General database at least two online redo log group. When an online redo log group is filled, when log switch occurs, when the online redo log group 2 as the current use of the log, when the filled online redo log group 2 when the log switch will happen , to write the online redo log group 1, it is so repeatedly.
If the database is in non archive mode, on-line will be discarded when you log in the switch. While in archive mode, when the log switch occurs when the logs will be switched archiving. For example, the current use of online redo log 1, when a filled at a time when the log switch, start writing online redo log 2, when the contents of an online redo log will be copied to another specified directory. This directory is called the archive directory, copy the file called archived redo log.
When it is running a database using the archive can be disaster recovery.
1. Archive log mode and non-archive log mode of the difference between the non-archive log mode can only do cold backup, and recovery can only do a full backup. The latest full backup to the system error data for the period can not be restored.
Archiving can be done hot backup mode, and can do incremental backups can be done partially.
With the ARCHIVE LOG LIST can see a view mode state, or non-archive mode archive log mode.

Three . Configuration database archiving mode <br /> change the non-archive mode to archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE ARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;
5) SQL> do a full backup, as non-archive log mode for the resulting backup archive log mode no longer be used. This step is very important to the non-!
To change the archive mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;


3. Enable automatic archiving: LOG_ARCHIVE_START = TRUE, in sqlplus in: (Note that this point in doubt, do not necessarily need this step)
alter system set log_archive_start = true scop = spfile;

Archiving mode, the log file group not allowed to be overwritten (rewritten), when filled, after the log file, without manual archiving, then the system will hang until the filing is completed.
At this time can only be read but not write.
During operation shut down and restart the process of archiving logs
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

Need to modify the configuration file:

In the init.ora added:

log_archive_start = true
log_archive_dest =% ORACLE_HOME% / admin / clustdb / arch
log_archive_format = "%% ORACLE_SID%% T% TS% S. ARC"
log_archive_max_processes = 3
log_archive_dest_1 = \ 'location = / oradata/orcl01/archive / \'

Information:

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open
alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / oracle / oradata / express / archive';
shutdown immediate;
startup

If it is oracle9i, also need to change the following parameters:

alter system set log_archive_start = true scope = spfile;
However, if the 10g also change these parameters, restart the database when the following prompt:
ORA-32004: obsolete and / or deprecated parameter (s) specified

alter system set log_archive_format = '% t_% s.dbf' scope = spfile;
But if 10g also change these parameters, the database will not start, the following prompt:
ORA-32004: obsolete and / or deprecated parameter (s) specified
ORA-19905: log_archive_format must contain% s,% t and% r




4. Manual archiving: LOG_ARCHIVE_START = FALSE
Archive the current log file
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Archive log files serial number 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archiving of all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
To change the archive log target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& PATH';

5. Archive mode and non-archive mode conversion

The reverse process of Step 4.

6. Configure multiple archive processes
Q: When do you need to use multiple archiving process?
A: If the archive process will consume a large amount of time, you can start multiple archiving process, which is a dynamic parameter, you can use ALTER SYSTEM dynamic modification.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;
Oracle9i can specify up to 10 archiving process and the archiving process-related dynamic performance views
v $ bgprocess, v $ archive_processes

7. Configuration archiving target, multi-archiving target, remote archiving goals, objectives LOG_ARCHIVE_DEST_n archive log format archive
Local archiving objectives:
SQL> LOG_ARCHIVE_DEST_1 = "LOCATION = D: ORACLEARCHIVEDLOG";
Remote archive target:
SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1";
Mandatory archiving objectives, and if an error, 600 seconds and try again:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION = E: ORACLEARCHIVEDLOG MANDATORY REOPEN = 600";
Optional archiving objectives, and if wrong, abandon the archive:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION = E: ORACLEARCHIVEDLOG OPTIONAL";

Filed target state: the goals and turn off archiving archiving archiving goals off target 1
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER
Open the archive target 2
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Archive log format
LOG_ARCHIVE_FORMAT

8. Access to archived log information
V $ ARCHVIED_LOG
V $ ARCHVIE_DEST
V $ LOG_HISTORY
V $ DATABASE
V $ ARCHIVE_PROCESSES

ARCHIVE LOG LIST;

Related Posts
]]> http://www.kods.net/oracle10g-reproduced-archive-log-mode/ oracle archive log mode http://www.kods.net/oracle-archive-log-mode/ http://www.kods.net/oracle-archive-log-mode/#comments Sat, 02 Jan 2010 21:43:25+0000 kods.net http://www.kods.net/oracle-archive-log-mode/ 1.select name, log_mode from v $ database;
NAME LOG_MODE
------------------ ------------------------
QUERY NOARCHIVELOG
2. Using the ARCHIVE LOG LIST command
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / data/oracle/product/10.2.0/db_1 / / dbs / arch
Oldest online log sequence 739
Current log sequence 741
2. . What is the Oracle archive mode?
Oracle databases online redo logs, this log is to record the changes made to the database, such as insert, delete, update data on these operations are recorded in online redo log inside. General database at least two online redo log group. When an online redo log group is filled, when log switch occurs, when the online redo log group 2 as the current use of the log, when the filled online redo log group 2 when the log switch will happen , to write the online redo log group 1, it is so repeatedly.
If the database is in non archive mode, on-line will be discarded when you log in the switch. While in archive mode, when the log switch occurs when the logs will be switched archiving. For example, the current use of online redo log 1, when a filled at a time when the log switch, start writing online redo log 2, when the contents of an online redo log will be copied to another specified directory. This directory is called the archive directory, copy the file called archived redo log.
When it is running a database using the archive can be disaster recovery.
1. Archive log mode and non-archive log mode of the difference between the non-archive log mode can only do cold backup, and recovery can only do a full backup. The latest full backup to the system error data for the period can not be restored.
Archiving can be done hot backup mode, and can do incremental backups can be done partially.
With the ARCHIVE LOG LIST can see a view mode state, or non-archive mode archive log mode.

Three . Configuration database archiving mode

Changes in the non-archive mode to archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE ARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

5) SQL> do a full backup, as non-archive log mode for the resulting backup archive log mode no longer be used. This step is very important to the non-!

To change the archive mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

3. Enable automatic archiving: LOG_ARCHIVE_START = TRUE
Archiving mode, the log file group not allowed to be overwritten (rewritten), when filled, after the log file, without manual archiving, then the system will hang, know that archiving is completed.
At this time can only be read but not write.
During operation shut down and restart the process of archiving logs
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

4. Manual archiving: LOG_ARCHIVE_START = FALSE
Archive the current log file
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Archive log files serial number 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archiving of all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
To change the archive log target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& PATH';

5. Archive mode and non-archive mode conversion

The reverse process of Step 4.

6. Configure multiple archive processes
Q: When do you need to use multiple archiving process?
A: If the archive process will consume a large amount of time, you can start multiple archiving process, which is a dynamic parameter, you can use ALTER SYSTEM dynamic modification.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;
Oracle9i can specify up to 10 archiving process and the archiving process-related dynamic performance views
v $ bgprocess, v $ archive_processes

7. Configuration archiving target, multi-archiving target, remote archiving goals, objectives LOG_ARCHIVE_DEST_n archive log format archive
Local archiving objectives:
SQL> LOG_ARCHIVE_DEST_1 = "LOCATION = D: ORACLEARCHIVEDLOG";
Remote archive target:
SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1";
Mandatory archiving objectives, and if an error, 600 seconds and try again:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION = E: ORACLEARCHIVEDLOG MANDATORY REOPEN = 600";
Optional archiving objectives, and if wrong, abandon the archive:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION = E: ORACLEARCHIVEDLOG OPTIONAL";

Filed target state: the goals and turn off archiving archiving archiving goals off target 1
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER
Open the archive target 2
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Archive log format
LOG_ARCHIVE_FORMAT

8. Access to archived log information
V $ ARCHVIED_LOG
V $ ARCHVIE_DEST
V $ LOG_HISTORY
V $ DATABASE
V $ ARCHIVE_PROCESSES

ARCHIVE LOG LIST;
Related Posts
]]> http://www.kods.net/oracle-archive-log-mode/ Modify the archive mode (turn) http://www.kods.net/modify-the-archive-mode-turn/ http://www.kods.net/modify-the-archive-mode-turn/#comments Wed, 16 Dec 2009 16:51:30+0000 kods.net http://www.kods.net/modify-the-archive-mode-turn/
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 / orcl / archive'; # / oracle / oradata / orcl / archive must be in front of mkdir-p oradata / szdb / archive operation to establish the path to exactly the same 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, so changes take effect the results of SQL> shutdown immediate;

View archive log mode SQL> startup

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / oracle / oradata / orcl / archive

Oldest online log sequence 565

Next log sequence to archive 567

Current log sequence 567

Alter system set the above operation will be in the database spfile@sid.ora and init@sid.ora file to add the following parameters *. log_archive_dest_1 = 'LOCATION = / oracle / oradata / orcl / archive'

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

*. log_archive_start = TRUE

Note: *. log_archive_dest_1-defined folders must actually exist, and the oracle user has write permission is
Related Posts
]]> http://www.kods.net/modify-the-archive-mode-turn/ LOG_ARCHIVE_DEST_n Parameter Properties - DATA GUARD concepts and management of http://www.kods.net/log-archive-dest-n-parameter-properties-data-guard-concepts-and-management-of/ http://www.kods.net/log-archive-dest-n-parameter-properties-data-guard-concepts-and-management-of/#comments Fri, 13 Nov 2009 00:53:14+0000 kods.net http://www.kods.net/log-archive-dest-n-parameter-properties-data-guard-concepts-and-management-of/
ALTERNATE and NOALTERNATE settings are in the archive directory can not write, it will be archived to write to another place;

ARCH and LGWR archive settings are ARCn process, or the LGWR process to write to write documents;

DELAY, and NODELAY set STANDBY whether the delay in application of the database archive log;

DEPENDENCY and archive NODEPENDENCY parameters can be directly by other STANDBY database access, the other of the STANDBY is available in the archive also need to transfer documents;

LOCATION and SERVICE set up a local archive or archive to a STANDBY database;

MANDATORY and OPTIONAL set archive path is mandatory or optional;

MAX_FAILURE and after the failure of NOMAX_FAILURE archiving parameter settings, connect the maximum number of retries;

NET_TIMEOUT and NONET_TIMEOUT set LGWR process to write the number of seconds to wait for archiving information;

QUOTA_SIZE and NOQUOTA_SIZE archive directory can be used to set the space size;

Show QUOTA_USED and NOQUOTA_USED archive directory and the use of the space size;

REGISTER and NOREGISTER settings are recorded in the target site, the location of archived logs;

REOPEN and archiving NOREOPEN set whether to retry after the failure;

SYNC and ASYNC set LGWR process to write IO operation is carried out simultaneously;

TEMPLATE and NOTEMPLATE set the path and format of the template archive information.
Related Posts
]]> http://www.kods.net/log-archive-dest-n-parameter-properties-data-guard-concepts-and-management-of/ Oracle error backup solution http://www.kods.net/oracle-error-backup-solution/ http://www.kods.net/oracle-error-backup-solution/#comments Wed, 29 Jul 2009 18:50:13+0000 kods.net http://www.kods.net/oracle-error-backup-solution/ rn

rn
ORACLE backup solution error

rn

rn
First, backup overview

rn
1, the use of thermal backup, need to run in archive mode, the database can be fully restored, that is, from a backup to the database after the collapse of the data will not be lost;

rn
2, can be used to back up the whole database backup, for special circumstances, you can back up only specific data files, such as backup only the user table space (generally written especially for some of the data files frequently, you can increase the backup frequency alone );

rn
3, if during the recovery process and found that damage to a number of data files that can be used one method to restore data files, can also be used to restore the entire database methods;

rn
4, to run in archive mode will have a lot of archive logs, the log of the restoration of the database is very important, as long as the existence of backup and archiving, the database will be able to achieve full recovery (no loss of data);

rn
5, the recovery process carried out under the mount, if the restoration of the success, and then open the database

rn
Second, Oracle to back up the reasons for the failure

rn
1, did not find the existence of archived logs (Oracle is not running in archive mode)

rn
Third, change the operating mode Oracle

rn
1, $ mkdir / ora_arch1 # create a oracle archive log archive directory

rn
2, $ sqlplus / nolog

rn
SQL> conn / as sysdba;

rn
Connected.

rn
SQL> startup mount; # start to mount the database state

rn
SQL> alter database archivelog; # to oracle model for archiving

rn
SQL> archive log start; # start archiving mode

rn
SQL> archive log list; # See oracle archive case

rn
Database log mode Archive Mode

rn
Automatic archival Enabled

rn
Archive destination / ora_arch1

rn
Oldest online log sequence 　　 565

rn
Next log sequence to archive 　 567

rn
Current log sequence 　 567

rn
SQL> alter database open; # open database

rn
SQL> alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / ora_arch1' # / ora_arhc1 must be in front of mkdir / ora_arch1 operation of the establishment of the same path (the path set oracle archive) SQL> alter system set log_archive_format = '% t_% s.dbf' scope = spfile; # set archive types

rn
SQL> alter system set log_archive_start = true scope = spfile; # set up automatic archiving mode

rn
Restart the database, the settings take effect

rn
SQL> shutdown immediate;

rn
SQL> startup

rn
SQL> archive log list; # See archived case

rn
Database log mode Archive Mode

rn
Automatic archival Enabled

rn
Archive destination / ora_arch1

rn
Oldest online log sequence 　　 565

rn
Next log sequence to archive 　 567

rn
Current log sequence 　 567

rn
Related Posts
]]> http://www.kods.net/oracle-error-backup-solution/ archiving mode oracle10g reprint http://www.kods.net/archiving-mode-oracle10g-reprint/ http://www.kods.net/archiving-mode-oracle10g-reprint/#comments Wed, 29 Jul 2009 11:36:40+0000 kods.net http://www.kods.net/archiving-mode-oracle10g-reprint/ 1. See oracle database to archive mode:
1.select name, log_mode from v $ database;
NAME LOG_MODE
------------------ ------------------------
QUERY NOARCHIVELOG
2. Use ARCHIVE LOG LIST command
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / data/oracle/product/10.2.0/db_1 / / dbs / arch
Oldest online log sequence 739
Current log sequence 741

2. . What is Oracle archive mode?
Oracle database online redo log, the log is recorded on the database changes, such as insert, delete, update data for these operations will be recorded in online redo log ri. General database with at least two online redo log group. When an online redo log group was written when the log switch occurs when the online redo log group 2 has become the use of the log, when the online redo log group 2, when full, will log switch occurs, write to online redo log group 1, it is so repeatedly.
If the database is in non archive mode, on-line log will be discarded at the switch. In archive mode, to switch the event log when the log switch will be archived. For example, the current online redo log in the use of 1, when a full time log switch occurred, began to write online redo log 2, when an online redo log contents will be copied to another designated directory. This directory is called the archive directory, copy the redo log files is called archiving.
Database archiving run can be disastrous when the restoration.
1. Archive log mode and non-archive log mode the difference between non-archive mode can only do cold backup, and recovery can only do a full backup. The most recent full backup to the system error during the data can not be restored.
Archiving can be done hot backup mode, and can do incremental backups, you can do so partially.
ARCHIVE LOG LIST can be used view mode view mode when the filing or non-archive mode.

Three . Configuration database archiving mode <br /> change the non-archive mode to archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE ARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;
5) SQL> do a full backup, because non-archive log mode for the resulting backup archive log mode has not spent. This very important step in the non-!
To change the archiving mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL / IMMEDIATE;
2) SQL> START MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;


3. Enable automatic archiving: LOG_ARCHIVE_START = TRUE, in sqlplus in: (pay attention to this point in doubt, do not necessarily need this step)
alter system set log_archive_start = true scop = spfile;

Archiving mode, the log file group is not allowed to be covered by (rewrite), written when the log file after the archive if there is no manual, then the system will hang until the archiving is completed.
At this time can only be read but not write.
Operation of closing and restarting the process of archiving logs
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

Need to modify configuration files:

Increase in the init.ora:

log_archive_start = true
log_archive_dest =% ORACLE_HOME% / admin / clustdb / arch
log_archive_format = "%% ORACLE_SID%% T% TS% S. ARC"
log_archive_max_processes = 3
log_archive_dest_1 = \ 'location = / oradata/orcl01/archive / \'

Information:

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open
alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = / oracle / oradata / express / archive';
shutdown immediate;
startup

If it is oracle9i, also need to change the following parameters:

alter system set log_archive_start = true scope = spfile;
However, if in 10g also change these parameters, the database will have to restart the following tips:
ORA-32004: obsolete and / or deprecated parameter (s) specified

alter system set log_archive_format = '% t_% s.dbf' scope = spfile;
However, if in 10g also change these parameters, the database will not be activated, the following tips:
ORA-32004: obsolete and / or deprecated parameter (s) specified
ORA-19905: log_archive_format must contain% s,% t and% r




4. Manually archive: LOG_ARCHIVE_START = FALSE
Archive the current log file
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Serial number for the archive log files 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archiving of all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
To change the archive log target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& PATH';

5. Archive mode and non-archive mode conversion

Step 4 of the inverse process.

6. Configure multiple archive processes
Q: When the need for multiple archive process?
A: If the archive process will consume a large amount of time, you can start the process of a number of archiving, this is a dynamic parameter, you can use ALTER SYSTEM dynamic modification.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;
Oracle9i can specify up to 10 archiving process and the archiving process on the dynamic performance view
v $ bgprocess, v $ archive_processes

7. Configuration archiving target, the target number of archiving, remote archiving target, the target archive log archive format LOG_ARCHIVE_DEST_n
Local archiving objectives:
SQL> LOG_ARCHIVE_DEST_1 = "LOCATION = D: ORACLEARCHIVEDLOG";
Remote archiving objectives:
SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1";
The goal of the mandatory filing, if the error, try again after 600 seconds:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION = E: ORACLEARCHIVEDLOG MANDATORY REOPEN = 600";
Optional archiving target, if wrong, abandon the archive:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION = E: ORACLEARCHIVEDLOG OPTIONAL";

Archiving target state: closed archiving goals and objectives of the closure of open archiving archiving Goal 1
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER
Goal 2 open archiving
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Archive log format
LOG_ARCHIVE_FORMAT

8. Access to archive log information
V $ ARCHVIED_LOG
V $ ARCHVIE_DEST
V $ LOG_HISTORY
V $ DATABASE
V $ ARCHIVE_PROCESSES

ARCHIVE LOG LIST;

Source: http://www.cnblogs.com/jacktu/archive/2008/02/27/1083214.html
Related Posts
]]> http://www.kods.net/archiving-mode-oracle10g-reprint/ Configured in RHEL5 Oracle 10g Data Guard http://www.kods.net/configured-in-rhel5-oracle-10g-data-guard/ http://www.kods.net/configured-in-rhel5-oracle-10g-data-guard/#comments Mon, 21 Jul 2008 12:20:30+0000 kods.net http://www.kods.net/configured-in-rhel5-oracle-10g-data-guard/ Configured in RHEL5 Oracle 10g Data Guard
In rhel5 configure Oracle 10g Data Guard

Environment configuration master database SID (ddd)
Global Database Name (ddd)
Service Name (ddd)
Net Server Name (ddd)
IP: 192.168.0.102

DB: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
OS: rhel5

Preparation Library SID (ddd)
Global Database Name (ddd)
Service Name (ddd)
Net Service Name (dddsby)
IP: 192.168.0.109

DB: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
OS: rhel5

Primary is used for the production database, standby oracle software installed, but does not establish a database.

Second, a physical standby database
1. Oracle environment to prepare the primary database:
Edit oracle user's ~ oracle / .bash_profile file, oracle related environment variables as follows:
export ORACLE_BASE = / oracle
export ORACLE_HOME = / oracle/product/10g
export ORACLE_SID = ddd
export PATH = $ ORACLE_HOME / binPATH
export TNS_ADMIN = $ ORACLE_HOME / network / admin
export LANG = zh_CN
# Export NLS_LANG = AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG = "SIMPLIFIED Chinese_CHINA.ZHS16GBK"

2. Modify the main library for the archive mode of the establishment of archive directory:
mkdir-p / oracle / oradata / arch
Modify the archive mode:
alter database force logging;
archive log list;
create pfile from spfile;
Edit $ ORACLE_HOME / dbs / initddd.ora
The main library file configure the following red part is to modify the parameters)
ddd.__db_cache_size = 167772160
ddd.__java_pool_size = 4194304
ddd.__large_pool_size = 4194304
ddd.__shared_pool_size = 83886080
ddd.__streams_pool_size = 0
*. Audit_file_dest = '/ oracle / / admin / ddd / adump'
*. Background_dump_dest = '/ oracle / / admin / ddd / bdump'
*. Compatible = '10 .2.0.1.0 '
*. Control_files = '/ oracle / / oradata/ddd/control01.ctl', '/ oracle / / oradata/ddd/control02.ctl', '/ oracle / / oradata/ddd/control03.ctl'
*. Core_dump_dest = '/ oracle / / admin / ddd / cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'ddd'
*. Db_recovery_file_dest = '/ oracle / / flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dg_broker_start = TRUE
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = dddXDB)'
*. Fal_client = 'ddd' # here fal_client refers to the net service name
*. Fal_server = 'dddsby1' # here fal_server refers to the net service name
*. Job_queue_processes = 10
*. Log_archive_dest_1 = 'location = / oracle / oradata / arch'
*. Log_archive_dest_2 = 'SERVICE = dddsby1 LGWR' # where SERVICE is the net service name that
*. Open_cursors = 300
*. Pga_aggregate_target = 88080384
*. Processes = 150
*. Remote_login_passwordfile = 'EXCLUSIVE'
*. Sga_target = 264241152
*. Standby_archive_dest = '/ oracle / oradata / arch'
*. Standby_file_management = 'AUTO'
*. Undo_management = 'AUTO'
*. Undo_tablespace = 'UNDOTBS1'
*. User_dump_dest = '/ oracle / / admin / ddd / udump'

sqlplus / as sysdba;
shutdown immediate;
create spfile from pfile;
startup mount;
alter database archivelog;
alter database open;

3. On the primary database to do a full hot backup, backup database data access
RMAN> connect target
RMAN> backup database format = '/ home / oracle /% U_% s.bak';
RMAN> sql "Alter System Archive Log Current";
RMAN> Backup filesperset 10 ArchiveLog all format = '/ home / oracle /% U_% s.bak';

cd / home / oracle
scp *. bak 192.168.200.109: / home / oracle /
Or directly packaged database directory oradata, using ftp reached in the solution package prepared by the library
4. In the standby server ready to edit the same oracle environment and primary users of the ~ oracle / .bash_profile file, oracle related environment variables as follows:
export ORACLE_BASE = / oracle
export ORACLE_HOME = / oracle/product/10g
export ORACLE_SID = ddd
export PATH = $ ORACLE_HOME / binPATH
export TNS_ADMIN = $ ORACLE_HOME / network / admin
export LANG = zh_CN
# Export NLS_LANG = AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG = "SIMPLIFIED Chinese_CHINA.ZHS16GBK"

5. Prepare the appropriate directory, such as log file path, file path, the path parameter file, ready to store the data file path, etc.
mkdir-p / oracle / oradata / ddd
mkdir-p / oracle / oradata / arch
mkdir-p $ ORACLE_BASE / admin / ddd / adump
mkdir-p $ ORACLE_BASE / admin / ddd / bdump
mkdir-p $ ORACLE_BASE / admin / ddd / cdump
mkdir-p $ ORACLE_BASE / admin / ddd / udump

6. The establishment of standby database parameter file

ddd.__db_cache_size = 167772160
ddd.__java_pool_size = 4194304
ddd.__large_pool_size = 4194304
ddd.__shared_pool_size = 83886080
ddd.__streams_pool_size = 0
*. Audit_file_dest = '/ oracle / / admin / ddd / adump'
*. Background_dump_dest = '/ oracle / / admin / ddd / bdump'
*. Compatible = '10 .2.0.1.0 '
*. Control_files = '/ oracle / / oradata/ddd/control01.ctl', '/ oracle / / oradata/ddd/control02.ctl', '/ oracle / / oradata/ddd/control03.ctl'
*. Core_dump_dest = '/ oracle / / admin / ddd / cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'ddd'
*. Db_recovery_file_dest = '/ oracle / / flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dg_broker_start = TRUE
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = dddXDB)'
*. Fal_client = 'dddsby1' # here fal_client refers to the net service name
*. Fal_server = 'ddd' # here fal_server refers to the net service name
*. Job_queue_processes = 10
*. Log_archive_dest_1 = 'location = / oracle / oradata / arch'
*. Log_archive_dest_2 = 'SERVICE = ddd LGWR' # where SERVICE is the net service name that
*. Open_cursors = 300
*. Pga_aggregate_target = 88080384
*. Processes = 150
*. Remote_login_passwordfile = 'EXCLUSIVE'
*. Sga_target = 264241152
*. Standby_archive_dest = '/ oracle / oradata / arch'
*. Standby_file_management = 'AUTO'
*. Undo_management = 'AUTO'
*. Undo_tablespace = 'UNDOTBS1'
*. User_dump_dest = '/ oracle / / admin / ddd / udump'

7. From the main server, copy the password file to the standby server

cd $ ORACLE_HOME / dbs /
scp orapwddd 192.168.200.109: / oracle/product/10g/dbs

8. Configure the primary server network connection changes
/ Oracle/product/10g/network/admin/listener.ora file as follows:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ddd)
(ORACLE_HOME = / oracle/product/10g)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = gull) (PORT = 1521))
)

Modify the master server
/ Oracle/product/10g/network/admin/tnsnames.ora file as follows:

DDDSBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.200.109) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)

DDD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.200.102) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (Key = EXTPROC1))
)
(CONNECT_DATA =
(SID = ddd)
(PRESENTATION = RO)
)
)

Modify the backup server
/ Oracle/product/10g/network/admin/listener.ora file as follows:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = / oracle/product/10g)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain) (PORT = 1521))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))
)
)

Modify the backup server
/ Oracle/product/10g/network/admin/tnsnames.ora file as follows:

DDDSBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.200.109) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)

DDD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.200.102) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = ddd)
(PRESENTATION = RO)
)
)

Test:
Start listening:
lsnrctl start
lsnrctl status view monitoring status.
The owners and the respective implementation of the standby machine
tnsping ddd
tnsping dddsby1

9. In the main database server to create standby control file
alter database create standby controlfile as '/ home / oracle / standby.ctl';
Control file created after cp (rcp or scp) to the standby database control file directory where the.
scp standby.ctl 192.168.200.109: / oracle / oradata / ddd /
cp / oracle / oradata / ddd / standby.ctl / oracle/oradata/ddd/control02.ctl
cp / oracle / oradata / ddd / standby.ctl / oracle/oradata/ddd/control02.ctl
cp / oracle / oradata / ddd / standby.ctl / oracle/oradata/ddd/control03.ctl

10. Start standby database
sqlplus / as sysdba
create spfile from pfile;
startup mount;

Restore the database:
RMAN> connect target;
RMAN> restore database;
RMAN> restore archivelog all;
RMAN> recover database;

Into Admin recovery status
SQL> alter database recover managed standby database disconnect from session;

Third, the process of transmission associated with Lgwr log machine maximum performance mode
1. To create the standby database back-log
alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/ oracle/oradata/ddd/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/ oracle/oradata/ddd/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/ oracle/oradata/ddd/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/ oracle/oradata/ddd/stdy_redo08.log') size 10m;
alter database recover managed standby database disconnect from session;

2. Modify the path of the main library's archive
alter system set LOG_ARCHIVE_DEST_2 = 'SERVICE = dddsby1 LGWR' scope = both;
Also, if taking into account the future of the library may be switched to the standby database, you can create the same standby log group:
alter database add standby LOGFILE GROUP 5 ('/ oracle/oradata/ddd/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/ oracle/oradata/ddd/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/ oracle/oradata/ddd/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/ oracle/oradata/ddd/stdy_redo08.log') size 10m;

Fourth, verify backup server is working in the main library in the implementation of the
alter system switch logfile;
Preparation of the library in the view log is being transmitted over
SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME, APPLIED FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;

In the main library:
sqlplus / as sysdba
create user test identified by test;
grant connect, resource to test;
conn test / test @ primary;
create table test (name varchar2 (20));
insert into test values ('Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;

View the log from the library read-only open from the library view insert into test values (Data Guard '); already in effect.
sqlplus / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test / test
select * from test;

Set once again in recovery mode from the library:
alter database recover managed standby database disconnect from session;

5, daily management
1. Standby server management mode and read-only mode (1) start to management
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
(2) start to read-only
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database open read only;
(3) If in the management of recovery mode to read-only mode
SQL> recover managed standby database cancel;
SQL> alter database open read only;
This time, you can increase the temporary data files to the database (this time in the hot backup is no backup over the)
As
alter tablespace temp add tempfile '/ oracle/oradata/ddd/temp01.dbf' size 100M;
(4) to manage recovery from the read-only mode
SQL> recover managed standby database disconnect from session;
Related Posts
]]> http://www.kods.net/configured-in-rhel5-oracle-10g-data-guard/ The oracle set to archive mode is the method http://www.kods.net/the-oracle-set-to-archive-mode-is-the-method/ http://www.kods.net/the-oracle-set-to-archive-mode-is-the-method/#comments Wed, 25 Jun 2008 15:53:40+0000 kods.net http://www.kods.net/the-oracle-set-to-archive-mode-is-the-method/ 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

Related Posts
]]> http://www.kods.net/the-oracle-set-to-archive-mode-is-the-method/ Oracle's archivelog question http://www.kods.net/oracle-s-archivelog-question/ http://www.kods.net/oracle-s-archivelog-question/#comments Wed, 01 Mar 2006 13:14:17+0000 kods.net http://www.kods.net/oracle-s-archivelog-question/
Use du-sh * search a bit directory space, was originally in the archive directory, checking a bit of information, the original is the archive log function, I do not need this feature that sets DB, so it can be removed, but how to stop "archive model "it, to the net after searching through all the information has not only tells how to" non-archive log mode "to" archive mode ", by:

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

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

SQL> alter system archive log stop;

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

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination / home / oracle / archive / webdb
Oldest online log sequence 193
Current log sequence 195
Related Posts
]]> http://www.kods.net/oracle-s-archivelog-question/ Archive logs and online logs http://www.kods.net/archive-logs-and-online-logs/ http://www.kods.net/archive-logs-and-online-logs/#comments Thu, 23 Feb 2006 09:16:21+0000 kods.net http://www.kods.net/archive-logs-and-online-logs/
1, NO ARCHIVELOG
Database has online redo log, the log is to record changes made to the database, such as insert, delete, update data on these operations are recorded in online redo logs. General database at least two online redo log group. When a redo log is filled when the log switch occurs, then the online redo log group 2 as the current use of the log, when the filled online redo log group 2, when the log switch will occur , to write the online redo log group 1, and thus repeated.
Second, ARCHIVELOG
Basic conditions and is consistent with the above, the only difference is that, when the log switch occurred when the log will be switched archiving. For example, the current online redo log in use 1, when a written, a time when the log switch, start writing online redo log 2, then 1, the contents of the online redo logs will be copied to another specified directory. This directory is called the archive directory, copy the archived redo log file is called.
Their difference is an archive, an archive. Database using the archive can be disastrous when it is running to restore.
Related Posts
]]> http://www.kods.net/archive-logs-and-online-logs/ oracle archiving method http://www.kods.net/oracle-archiving-method/ http://www.kods.net/oracle-archiving-method/#comments Tue, 06 Dec 2005 21:29:57+0000 kods.net http://www.kods.net/oracle-archiving-method/
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 / orcl / archive'; # / oracle / oradata / orcl / archive must be in front of mkdir-p oradata / szdb / archive operation to establish the path to exactly the same 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, so changes take effect the results of SQL> shutdown immediate;

View archive log mode SQL> startup

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / oracle / oradata / orcl / archive

Oldest online log sequence 565

Next log sequence to archive 567

Current log sequence 567

Alter system set the above operation will be in the database spfile@sid.ora and init@sid.ora file to add the following parameters *. log_archive_dest_1 = 'LOCATION = / oracle / oradata / orcl / archive'

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

*. log_archive_start = TRUE

Note: *. log_archive_dest_1-defined folders must actually exist, and the oracle user is written permission NOTE: @ sid on behalf of the actual database, sid number, for example, in the practical operation of these two file names are spfileorcl.ora , initorcl.ora
Related Posts
]]> http://www.kods.net/oracle-archiving-method/