Home » Server Options » RAC & Failsafe » Cannot copy asm to filesystem when login as sysdba (12.1.0.2 Redhat 7.3)
Cannot copy asm to filesystem when login as sysdba [message #673181] Sat, 10 November 2018 09:00 Go to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear all,

we are having separate user for grid infrastructure (grid) and database installation (oracle)

[oracle@MESRACDB1 ~]$ asmcmd --privilege sysdba ls -ld DATA/mesdb/dumpset/schema/ecg --permission
Type  Redund  Striped  Time             Sys  User  Group  Permission  Name
                                        N                             ecg/
[oracle@MESRACDB1 ~]$ asmcmd --privilege sysdba ls -l DATA/mesdb/dumpset/schema/ecg --permission
Type     Redund  Striped  Time             Sys  User  Group  Permission  Name
DUMPSET  UNPROT  COARSE   NOV 02 04:00:00  N                  rw-rw-rw-  ecg.dmp => +DATA/MESDB/dumpset/SYSTEMEXPDP_ECO_144841_1.322.991037859
[oracle@MESRACDB1 ~]$

asmcmd --privilege sysdba cp DATA/mesdb/dumpset/schema/ecg /u01/app/oracle/admin/mesdb/dumpset/schema/ecg

=>cannot copy from asm to filesystem as oracle user or sysdba user




Type  Redund  Striped  Time             Sys  User  Group  Permission  Name
                                        N                             ecg/
[grid@MESRACDB1 ~]$ asmcmd ls -l DATA/mesdb/dumpset/schema/ecg --permission
Type     Redund  Striped  Time             Sys  User  Group  Permission  Name
DUMPSET  UNPROT  COARSE   NOV 02 04:00:00  N                  rw-rw-rw-  ecg.dmp => +DATA/MESDB/dumpset/SYSTEMEXPDP_ECO_144841_1.322.991037859

asmcmd cp DATA/mesdb/dumpset/schema/ecg/ecg.dmp /home/grid/dba/dpdump

asmcmd cp DATA/mesdb/dumpset/schema/ecg/ecg.dmp /home/grid/dba/dpdump
copying +DATA/mesdb/dumpset/schema/ecg/ecg.dmp -> /home/grid/dba/dpdump/ecg.dmp

=>can copy from asm to filesystem as grid user.

[grid@MESRACDB1 ~]$ id grid
uid=54422(grid) gid=54321(oinstall) groups=54321(oinstall),54327(asmdba),54329(asmadmin),54328(asmoper)
[grid@MESRACDB1 ~]$ id oracle
uid=54421(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54327(asmdba),54325(dgdba),54326(kmdba),54328(asmoper)


what do I need to do to enable oracle user to be able to copy from asm to filesystem?

asmcmd cp command syntax
Quote:

https://docs.oracle.com/database/121/OSTMG/GUID-54815B29-C25E-4286-86FF-8E84F6B51CDC.htm#OSTMG94456
asmcmd ls command syntax
Quote:

https://docs.oracle.com/database/121/OSTMG/GUID-37CE621F-BDEB-4555-9813-8ED515E2E24D.htm#OSTMG94474
many thanks in advance!
Re: Cannot copy asm to filesystem when login as sysdba [message #673182 is a reply to message #673181] Sat, 10 November 2018 09:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You may need the sysasm privilege rather than sysdba.
Re: Cannot copy asm to filesystem when login as sysdba [message #673195 is a reply to message #673182] Sat, 10 November 2018 19:39 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John,

I still could not copy asm to filesystem using sysasm privilege.

[oracle@MESRACDB1 ~]$ asmcmd --privilege sysasm cp DATA/mesdb/dumpset/schema/ecg/ecg.dmp /u01/app/oracle/admin/mesdb/dumpset/schema/ecg/

If we are using filesystem we can copy whatever we export dump to any destination we own. so in my own honest opinion, there is some thing not right here.
Re: Cannot copy asm to filesystem when login as sysdba [message #673196 is a reply to message #673195] Sun, 11 November 2018 00:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You will not have the privilege if you are not in the appropriate operating system group. What is your sysasm group, and is your OS user a member of it? Usually only the grid user is in that group.
Also, I have never tried to copy an ASM file without using the + symbol as a prefix to the diskgroup name. Are you sure your syntax without + is correct?
Re: Cannot copy asm to filesystem when login as sysdba [message #673205 is a reply to message #673196] Mon, 12 November 2018 01:32 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John

What is your sysasm group =>asmadmin
, and is your OS user a member of it? =>no

Usually only the grid user is in that group.=>based on my understanding, it should not be the case.

Members of the ASM Database Administrator group (OSDBA for ASM) are granted read and write access to files managed by Oracle ASM=>user oracle need to have asmdba not asmadmin.

based on non asm system

root user is supposed to mount the system create directories and assign correct ownership and permission to directories root created for oracle user.

oracle user is supposed to create directories and files based on what root gives it.

similarly grid user is supposed to do what a non asm root user is supposed to do.

English is not my natural tongue, but I don't think my English is that bad until I misunderstand what it means.

Put it this way. as oracle user I can create the dump but I cannot copy the dumpfile to a directory i own. =>does it makes sense?

https://docs.oracle.com/database/121/CWLIN/usrgrps.htm#BABGCHAD

Quote:


OSASM Group for Oracle ASM Administration (typically asmadmin)

Create this group as a separate group if you want to have separate administration privileges groups for Oracle ASM and Oracle Database administrators. Members of this group are granted the SYSASM system privileges to administer Oracle ASM. In Oracle documentation, the operating system group whose members are granted privileges is called the OSASM group, and in code examples, where there is a group specifically created to grant this privilege, it is referred to as asmadmin.


Members of the OSASM group can use SQL to connect to an Oracle ASM instance as SYSASM using operating system authentication. The SYSASM privileges permit mounting and dismounting disk groups, and other storage administration tasks. SYSASM privileges provide no access privileges on an RDBMS instance.
Quote:


OSDBA for ASM Database Administrator group for ASM, typically asmdba)

Members of the ASM Database Administrator group (OSDBA for ASM) are granted read and write access to files managed by Oracle ASM. The Oracle Grid Infrastructure installation owner and all Oracle Database software owners must be a member of this group, and all users with OSDBA membership on databases that have access to the files managed by Oracle ASM must be members of the OSDBA group for ASM.

id grid
uid=54422(grid) gid=54321(oinstall) groups=54321(oinstall),54327(asmdba),54329(asmadmin),54328(asmoper)

uid=54421(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54327(asmdba),54325(dgdba),54326(kmdba),54328(asmoper)

Are you sure your syntax without + is correct? => I tried this time, again it fails
[oracle@MESRACDB1 ~]$ asmcmd --privilege sysdba cp +DATA/mesdb/dumpset/schema/ecg/ecg.dmp /u01/app/oracle/admin/mesdb/dumpset/schema/ecg

Re: Cannot copy asm to filesystem when login as sysdba [message #673206 is a reply to message #673205] Mon, 12 November 2018 01:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Well, do on: try the cp when you are logged on as a member of the asmadmin group. And be sure to use +data in the file name.
Re: Cannot copy asm to filesystem when login as sysdba [message #673208 is a reply to message #673205] Mon, 12 November 2018 01:58 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John,

yes definitely as grid user I can copy. But I imho it is still a bug, but nevertheless, it is a good workaround.

asmcmd cp DATA/mesdb/dumpset/schema/ecg/ecg.dmp /home/grid/dba/dpdump

asmcmd cp DATA/mesdb/dumpset/schema/ecg/ecg.dmp /home/grid/dba/dpdump
copying +DATA/mesdb/dumpset/schema/ecg/ecg.dmp -> /home/grid/dba/dpdump/ecg.dmp

thanks
Re: Cannot copy asm to filesystem when login as sysdba [message #673211 is a reply to message #673208] Mon, 12 November 2018 02:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you want to copy files as the database owner rather than the grid own, I would use dbms_file_transfer.
Re: Cannot copy asm to filesystem when login as sysdba [message #673842 is a reply to message #673211] Tue, 11 December 2018 19:52 Go to previous message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John,

you are absolutely correct.

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
   source_directory_object=>'test',
   source_file_name=>'test.dmp',
   destination_directory_object=>'test_log',
   destination_file_name=>'test.dmp');
END;
/

Of course there's a slight difference in the outcome between the two methods.

this one is using asmcmd
[grid@ol73-12102-rac1 ~]$ ls -lrt ~/dba/scripts/datapump/schema/test/log/test.dmp
-rw-r-----. 1 grid oinstall 319488 Dec 10 12:59 /home/grid/dba/scripts/datapump/schema/test/log/test.dmp


this one is using dbms_file_transfer.copy_file



-rw-r-----. 1 oracle asmadmin 319488 Dec 10 13:11 /home/oracle/dba/scripts/datapump/schema/test/log/test.dmp

the one using asmcmd generate group oinstall , the one using dbms_file_transfer.copy_file generate group asmadmin.

In fact for all file copy, delete or whatever operations if using asmcmd does not work, we should go back to basics, using SQL and PL/SQL

for example deleting files and aliases

https://docs.oracle.com/database/121/OSTMG/GUID-7AE5B8CA-B563-4864-A934-4C170A9590F5.htm#OSTMG10060

this is the overall link for Administering Oracle ASM Files, Directories, and Templates

https://docs.oracle.com/database/121/OSTMG/GUID-BBCDD96B-B9D5-49D9-AE38-A6C3C097DD54.htm

many thanks
Previous Topic: change ssh port 22 to another port
Next Topic: Private IPs are not Pingable from Node1 to Node2 :x
Goto Forum:
  


Current Time: Thu Mar 28 08:38:08 CDT 2024