| Cannot copy asm to filesystem when login as sysdba [message #673181] |
Sat, 10 November 2018 09:00  |
 |
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 #673196 is a reply to message #673195] |
Sun, 11 November 2018 00:51  |
John Watson
Messages: 8880
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 |
|
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 #673842 is a reply to message #673211] |
Tue, 11 December 2018 19:52 |
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
