OraFAQ Forum: Backup & Recovery » Defining Second destination of Archive log list (merged)

Home » RDBMS Server » Backup & Recovery » Defining Second destination of Archive log list (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Defining Second destination of Archive log list (merged) [message #315622]

Tue, 22 April 2008 04:08

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

I want to define second destination for archive log for backup storage.

when i see archive log list it is showing me
following

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 185
Next log sequence to archive 189
Current log sequence 189

What are the steps for defining second destination when
Log_archive_dest_1 to Log_archive_dest_10 has no value

[Updated on: Tue, 22 April 2008 04:42]

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315632 is a reply to message #315622]

Tue, 22 April 2008 04:43

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Manage Archive Destination

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315640 is a reply to message #315632]

Tue, 22 April 2008 05:03

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

After reading the document

it seems that Archive destination "USE_DB_RECOVERY_FILE_DEST" is using log_archive_dest_10 by default but when i do show parameter log_archive_dest_10 , it has no value

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315644 is a reply to message #315622]

Tue, 22 April 2008 05:12

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

It is implicitly set. if no LOG_ARCHIVE_DEST_n is specified.

Check show parameter log_archive_dest.

[Updated on: Tue, 22 April 2008 05:21]

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315647 is a reply to message #315644]

Tue, 22 April 2008 05:22

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

No value in log_archive_dest

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315648 is a reply to message #315622]

Tue, 22 April 2008 05:28

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Paste the output of

show parameter log_archive_dest

And,

select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;

[Updated on: Tue, 22 April 2008 05:35]

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315651 is a reply to message #315648]

Tue, 22 April 2008 05:39

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

output of query
select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;
DEST_ID DEST_NAME DESTINATION
10 LOG_ARCHIVE_DEST_10 USE_DB_RECOVERY_FILE_DEST

[Updated on: Tue, 22 April 2008 05:40]

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315654 is a reply to message #315622]

Tue, 22 April 2008 05:55

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

What you see?

Quote:

Dest_NAME=LOG_ARCHIVE_DEST_10
Destination=USE_DB_RECOVERY_FILE_DEST

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315672 is a reply to message #315651]

Tue, 22 April 2008 06:31

ebrian
Messages: 2794
Registered: April 2006

Senior Member

sho parameter DB_RECOVERY_FILE_DEST

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315676 is a reply to message #315672]

Tue, 22 April 2008 06:36

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
----------------------------- ----------- ------------------------------
db_recovery_file_dest string /home/u01/app/oracle/product/1
0.2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 15G

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315677 is a reply to message #315676]

Tue, 22 April 2008 06:39

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Right, you are using the FRA (/home/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area) for your archive log directory. So if you want to define another location, use LOG_ARCHIVE_DEST_1.

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315681 is a reply to message #315677]

Tue, 22 April 2008 06:54

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

When i use log_archive_dest_2
Original Archive destination is changed from flash recovery area to location of log_archive_dest_2

And i have switch the log file too
What should i do now

Archive log list showing following
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/archive-logs
Oldest online log sequence 187
Next log sequence to archive 190
Current log sequence 191

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315685 is a reply to message #315681]

Tue, 22 April 2008 07:10

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Then you need to also set:

alter system set LOG_ARCHIVE_DEST_10='location=USE_DB_RECOVERY_FILE_DEST';

[Updated on: Tue, 22 April 2008 07:18]

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315696 is a reply to message #315685]

Tue, 22 April 2008 08:00

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

Yes now it is visible but
I have already alter system switch logfile;
before adding log_archive_dest_10 again.

alter system set LOG_ARCHIVE_DEST_10='location=USE_DB_RECOVERY_FILE_DEST', is it mendatory or optional

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315698 is a reply to message #315696]

Tue, 22 April 2008 08:08

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Check V$ARCHIVE_DEST.

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315700 is a reply to message #315698]

Tue, 22 April 2008 08:16

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

DEST_ID DEST_NAME STATUS BINDING NNAMESPACE
1 LOG_ARCHIVE_DEST_1 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
2 LOG_ARCHIVE_DEST_2 ERROR OPTIONAL SYSTEM PRIMARY ARCH ACTIVE /home/archive-logs
3 LOG_ARCHIVE_DEST_3 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
4 LOG_ARCHIVE_DEST_4 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
5 LOG_ARCHIVE_DEST_5 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
6 LOG_ARCHIVE_DEST_6 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
7 LOG_ARCHIVE_DEST_7 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
8 LOG_ARCHIVE_DEST_8 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
9 LOG_ARCHIVE_DEST_9 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
10 LOG_ARCHIVE_DEST_10 VALID OPTIONAL SYSTEM PRIMARY ARCH ACTIVE USE_DB_RECOVERY_FILE_DEST

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315705 is a reply to message #315700]

Tue, 22 April 2008 08:39

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You can specify what you want each destination to be (Specifying Mandatory and Optional Destinations).

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315710 is a reply to message #315705]

Tue, 22 April 2008 09:01

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

i have both path for archive log in place one is flash recovery area where archive log are to be written and another path which in my other server.
I have created a folder in my test database and map this folder in Production database but i am doing alter system switch logfile, it only create archive on the first destination and not creating any file on second destination and giving error

ORA-19504: failed to create file ""

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315722 is a reply to message #315710]

Tue, 22 April 2008 10:02

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You may need to verify the permissions on the mapped location.

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315725 is a reply to message #315722]

Tue, 22 April 2008 10:10

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

In mapped location i give right to oracle of production database to right read and execute

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315727 is a reply to message #315622]

Tue, 22 April 2008 10:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

19504, 00000, "failed to create file \"%s\""
// *Cause: call to create file returned an error
// *Action: check additional messages, check access permissions

When in doubt, trust Oracle error messages!!!!!!

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315728 is a reply to message #315725]

Tue, 22 April 2008 10:14

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Are the mount options configured to allow read & write?

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315729 is a reply to message #315728]

Tue, 22 April 2008 10:15

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

yes according to my system administrator.

Could you please tell me how do we do that, so that i discuss it with SA

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315730 is a reply to message #315729]

Tue, 22 April 2008 10:17

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Check /etc/fstab.

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315732 is a reply to message #315730]

Tue, 22 April 2008 10:21

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

in Testdb which i am mounting on production following is the result of cat fstab

# <file system> <mount point> <type> <options> <dump> <pass>
proc /proc proc defaults 0 0
/dev/sda2 / ext3 defaults,errors=remount-ro 0 1
/dev/sda9 /home xfs defaults 0 2
/dev/sda8 /tmp ext3 defaults 0 2
/dev/sda7 /usr ext3 defaults 0 2
/dev/sda5 /var ext3 defaults 0 2
/dev/sda6 none swap sw 0 0
/dev/hda /media/cdrom0 udf,iso9660 user,noauto 0 0

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315739 is a reply to message #315732]

Tue, 22 April 2008 10:43

ebrian
Messages: 2794
Registered: April 2006

Senior Member

I apologize, for linux the file is actually /etc/exports.

To test permissions you should try to create a file from prod to the test directory as the oracle user.

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315747 is a reply to message #315739]

Tue, 22 April 2008 10:51

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

following is the output and i triedmanullay and create directory remotely

# /etc/exports: the access control list for filesystems which may be exported
# to NFS clients. See exports(5).
#
# Example for NFSv2 and NFSv3:
# /srv/homes hostname1(rw,sync) hostname2(ro,sync)
#
# Example for NFSv4:
# /srv/nfs4 gss/krb5i(rw,sync,fsid=0,crossmnt)
# /srv/nfs4/homes gss/krb5i(rw,sync)
#

/home/db90-logs db90(rw,sync)

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315753 is a reply to message #315747]

Tue, 22 April 2008 10:57

ebrian
Messages: 2794
Registered: April 2006

Senior Member

MIFI wrote on Tue, 22 April 2008 11:51

following is the output and i triedmanullay and create directory remotely

Are you saying you could successfully create a file from the prod node to the test node as the same user account that oracle runs as?

MIFI wrote on Tue, 22 April 2008 11:51

/home/db90-logs db90(rw,sync)

I assume db90 is the node (prod) you are copying FROM ?

What does you archive_log_dest parameter look like exactly?

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315759 is a reply to message #315753]

Tue, 22 April 2008 11:13

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

Yes i can successfully create directory from production by using mapped folder
1 LOG_ARCHIVE_DEST_1 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
2 LOG_ARCHIVE_DEST_2 ERROR OPTIONAL SYSTEM PRIMARY ARCH ACTIVE /home/archive-logs
3 LOG_ARCHIVE_DEST_3 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
4 LOG_ARCHIVE_DEST_4 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
5 LOG_ARCHIVE_DEST_5 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
6 LOG_ARCHIVE_DEST_6 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
7 LOG_ARCHIVE_DEST_7 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
8 LOG_ARCHIVE_DEST_8 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
9 LOG_ARCHIVE_DEST_9 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
10 LOG_ARCHIVE_DEST_10 VALID OPTIONAL SYSTEM PRIMARY ARCH ACTIVE USE_DB_RECOVERY_FILE_DEST

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315762 is a reply to message #315759]

Tue, 22 April 2008 12:34

ebrian
Messages: 2794
Registered: April 2006

Senior Member

So I assume /home/archive-logs on prod is mounted to /home/db90-logs on the test box ??

If so, can you do the following while on prod as the user oracle:

touch /home/archive-logs/test.txt

Report message to a moderator

Re: Defining Second destination of Archive log list [message #315911 is a reply to message #315762]

Wed, 23 April 2008 03:22

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

Yes well said
File created by the name of test.txt

Now one file is created with dbf extension but second file is not created, following is the error in alert log file
and folder structure

folder and its file

drwxr-xr-x 11 root root 139 2008-04-21 17:50 ..
-rw-r----- 1 oracle oinstall 159744 2008-04-23 11:35 1_200_645895824.dbf
drwxrwxr-x 2 oracle oinstall 32 2008-04-23 11:39 .

Alert log error

Wed Apr 23 11:35:39 2008
Beginning log switch checkpoint up to RBA [0xc9.2.10], SCN: 9714818
Thread 1 advanced to log sequence 201
Current log# 6 seq# 201 mem# 0: /home/u01/app/oracle/product/10.2.0/db_1/oradata/db0/redo6.log
Current log# 6 seq# 201 mem# 1: /home/u01/app/oracle/product/10.2.0/db_1/oradata/db0/redo62
Wed Apr 23 11:35:42 2008
Errors in file /home/u01/app/oracle/product/10.2.0/db_1/admin/db0/bdump/demeter_arc0_27244.trc:
ORA-19504: Message 19504 not found; No message file for product=RDBMS, facility=ORA; arguments: [/home/archive-logs/1_200_645895824.dbf]
ORA-27086: Message 27086 not found; No message file for product=RDBMS, facility=ORA
Linux-x86_64 Error: 5: Input/output error
Additional information: 9
Additional information: 5
Wed Apr 23 11:35:42 2008
ARC0: Error 19504 Creating archive log file to '/home/archive-logs/1_200_645895824.dbf'
Wed Apr 23 11:40:16 2008
Completed checkpoint up to RBA [0xc9.2.10], SCN: 9714818

[Updated on: Wed, 23 April 2008 05:46]

Report message to a moderator

Checking size of mounted drive for archive log [message #316018 is a reply to message #315622]

Wed, 23 April 2008 09:02

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

hi,
Could i check the size limitation for mounted drive in linux, i am creating archive log and whenever there is a specific size the error does not let it create more archive log files on second destination which is on mounted drive

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316021 is a reply to message #316018]

Wed, 23 April 2008 09:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Could i check the size limitation for mounted drive in linux,
We give up. Can you?

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316026 is a reply to message #316018]

Wed, 23 April 2008 09:07

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this not directly related to your previous topic?

Regards
Michel

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316031 is a reply to message #316026]

Wed, 23 April 2008 09:14

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

Yes it is because System admin is saying everything is fine but i want to check, whether there is a problem with the size allocation, because sometimes after two archive files, third file is not created

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316033 is a reply to message #316018]

Wed, 23 April 2008 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

How much free space is available on volume & how big are the files being generated/copied?

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316034 is a reply to message #316033]

Wed, 23 April 2008 09:29

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

free space is 180G, files are not greater than 50m, but i want to find out the the directory which is mapped might have restriction and at the time of mounting configuration, we may need to provide size, other wise it may have very small size

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316044 is a reply to message #316034]

Wed, 23 April 2008 10:08

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

which mount configuration you will use for mount directory for archive log

Binaries
Linux (32bit, 64bit, Itanium) - rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3,timeo=600,actime=0

OCR/Voting
Linux (32bit, 64bit, Itanium) - rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600

Datafiles
Linux (32bit, 64bit, Itanium) - rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600

Report message to a moderator

Re: Defining Second destination of Archive log list [message #316103 is a reply to message #315911]

Wed, 23 April 2008 14:54

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Try to un-mount the directory and mount it as follows:

mount -o hard,intr,nolock <test_hostname>:/home/db90-logs

Report message to a moderator

Re: Checking size of mounted drive for archive log [message #316104 is a reply to message #316044]

Wed, 23 April 2008 15:01

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Check your other thread.

[Updated on: Wed, 23 April 2008 15:03]

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	steps to create RMAN
Next Topic:	Can Flashback function recover tablespace that I dropped?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 20:41:10 CDT 2024