Sunday, January 13, 2019

How to copy a file from ASM at host one to ASM at host twho ?


Because of archivelog gap there was the need to copy archive log file from primary Exadata  to standby Exadata. Primary Exadata archivelogs are at ASM disk group RECOC1 at first datacenter, standby Exadata is the same at 2nd datacenter).

Documentation about asmcmd give us some examples to copy files from ASM to local file system and to remote file system. But I found no example to copy from ASM@host1 --> ASM@host2. So, let’s try to do this.

I login to standby (target) server and create the temporary directory:

[oracle@ var01vm01 ~]$ asmcmd

ASMCMD> ls
DATAC1/
RECOC1/
ASMCMD> cd recoc1

ASMCMD> ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    ASM/
                             N    DATA_PUMP/
                             Y    SPUR/
                             Y    SPURSTB/
                             Y    SPURTST/

ASMCMD> mkdir yu
ASMCMD> cd yu

ASMCMD> pwd
+RECOC1/yu

Now the directory created and next step is to know the our service name to copy archivelogs to.
The doc say:
--service service_name Specifies the Oracle ASM instance name if not the default +ASM.

The immediate question here: what we should to use - a service name (ASM) or instance name (ASM1?
Let's look to our listeners:

[oracle@var01vm01 ~]$ ps -ef|grep tns
root        486      2  0  2018 ?        00:00:00 [netns]
oracle   184853 183676  0 11:46 pts/0    00:00:00 grep tns
oracle   291383      1  0  2018 ?        00:05:31 /u01/app/18c/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle   291389      1  0  2018 ?        00:00:44 /u01/app/18c/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
oracle   291460      1  0  2018 ?        00:06:10 /u01/app/18c/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle   291473      1  0  2018 ?        00:03:30 /u01/app/18c/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
oracle   291478      1  0  2018 ?        00:02:33 /u01/app/18c/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
oracle   291486      1  0  2018 ?        00:02:34 /u01/app/18c/grid/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit

Let’s try ASMNET1LSNR_ASM

[oracle@var01vm01 ~]$ lsnrctl stat ASMNET1LSNR_ASM
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-JAN-2019 11:46:55

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))
STATUS of the LISTENER
------------------------
Alias                     ASMNET1LSNR_ASM
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                27-DEC-2018 23:24:22
Uptime                    12 days 12 hr. 22 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/18c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/var01vm01/asmnet1lsnr_asm/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ASMNET1LSNR_ASM)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.12)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.11)(PORT=1525)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 2 handler(s) for this service...
Service "+ASM_DATAC1" has 1 instance(s).
  Instance "+ASM1", status READY, has 2 handler(s) for this service...
Service "+ASM_RECOC1" has 1 instance(s).
  Instance "+ASM1", status READY, has 2 handler(s) for this service...
The command completed successfully

This listener use Infiniband interfaces so it not be listener to copy between two datacenters.

Then let's take the listener with name LISTENER:

[oracle@var01vm01 ~]$ lsnrctl stat LISTENER

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-JAN-2019 11:47:28

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                27-DEC-2018 23:24:24
Uptime                    12 days 12 hr. 23 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/18c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/var01vm01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.140.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.140.2)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
 LISTENER, has 1 handler(s) for this service...
Service "+ASM_DATAC1" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECOC1" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...

OK, this listener is listening on the bondeth0 interfaces and it will be our target. The service is ASM, the instance name is ASM1.

Now we need login to primary server:
[oracle@mr01vm01 ~]$ asmcmd

ASMCMD> ls
DATAC1/
RECOC1/

ASMCMD> cd +recoc1

ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        N    DATA_PUMP/
                                        Y    SPUR/
ASMCMD> cd spur

ASMCMD> ls
ARCHIVELOG/
BACKUPSET/
CONTROLFILE/
FLASHBACK/
ONLINELOG/
STANDBYLOG/

ASMCMD> cd ARCHIVELOG/

ASMCMD> ls
2018_04_05/
2019_01_08/
2019_01_09/

ASMCMD> cd 2018_04_05/

ASMCMD> ls
thread_1_seq_123971.511.972663381


The “thread_1_seq_123971.511.972663381” is the alert log file name we need to copy.
Let’s try service name “+ASM”:


ASMCMD> cp thread_1_seq_123971.511.972663381 sys@172.22.140.1.+ASM:+RECOC1/yu
Enter password: ********
copying +recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381 -> 172.22.140.1:+RECOC1/yu
ASMCMD-8016: copy source '+recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381' and target '+RECOC1/yu' failed
ORA-15056: additional error message
ORA-17627: ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor
ORA-17629: Cannot connect to the remote database server
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 603
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

Then we’ll try instance name “+ASM1”:
ASMCMD> cp thread_1_seq_123971.511.972663381 sys@172.22.140.1.+ASM1:+RECOC1/yu
Enter password: ********
copying +recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381 -> 172.22.140.1:+RECOC1/yu
ASMCMD-8016: copy source '+recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381' and target '+RECOC1/yu' failed
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 603
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

What’s the matter? Let’s try give the name to target file:

ASMCMD> cp thread_1_seq_123971.511.972663381 sys@172.22.140.1.+ASM1:+RECOC1/yu/yu1.arc
Enter password: ********
copying +recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381 -> 172.22.140.1:+RECOC1/yu/yu1.arc

It seems to work
I login to target server to ensure the file is on the target system:

[oracle@var01vm01 ~]$ asmcmd
ASMCMD> cd rec*/yu
ASMCMD> ls -l
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  MIRROR  COARSE   JAN 09 11:00:00  N    yu1.arc => +RECOC1/ASM/ARCHIVELOG/yu1.arc.2169.997098577

OK, Yes, it works !


Let’s try the opposite way: run copy on the target system. The syntax of command is obvious:

ASMCMD> cp sys@10.63.140.1.+ASM1:+recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381 test.arc
Enter password: ********
ASMCMD-8002: entry 'ARCHIVELOG' does not exist in directory '+recoc1/spur/'
ASMCMD-8014: file '+recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381' does not exist

What’s the matter ?

I added the / before +recoc1 and error changed a little:
                                
ASMCMD> cp sys@10.63.140.1.+ASM1:/+recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381 test.arc
Enter password: ********
ASMCMD-8012: cannot determine file type for file
ORA-15056: additional error message
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 518
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

There doesn’t matter the register of the directories:

ASMCMD> cp sys@10.63.140.1.+ASM1:/+RECOC1/SPUR/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381 +RECOC1/yu/test.arc
Enter password: ********
ASMCMD-8012: cannot determine file type for file
ORA-15056: additional error message
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 518
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)



The file really exist on primary :

ASMCMD> ls -l +recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  MIRROR  COARSE   JAN 09 12:00:00  Y    thread_1_seq_123971.511.972663381

ASMCMD> cd  +recoc1/spur/ARCHIVELOG/2018_04_05/

ASMCMD> pwd
+recoc1/spur/ARCHIVELOG/2018_04_05

ASMCMD> ls -l
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  MIRROR  COARSE   JAN 09 12:00:00  Y    thread_1_seq_123971.511.972663381

ASMCMD> ls -l +recoc1/spur/ARCHIVELOG/2018_04_05/thread_1_seq_123971.511.972663381
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  MIRROR  COARSE   JAN 09 12:00:00  Y    thread_1_seq_123971.511.972663381


So there is no reason no copy this file ....

I think the reason is difference in source and target versions:
Source GI version is 12.2.0.1.0.180717 (12.2.0.1 + Jun 2018 quarter patch)
Target GI version is 18.0.0.0.0.181017 (18.4 + Oct 2018 quarter patch)




ASMCMD> help cp
cp
        Copy files between Oracle ASM disk groups on local
        instances to and from remote instances.

Synopsis
        cp [--target <target>] [--service <name>] [--port <port>] [--dest_dbname <name>] [--sparse] [--sparse_merge_begin] <[connect_str:]src_file...> <[connect_str:]tgt_file> [--sparse_merge_end <sparse_merge_end_file>]

Description
        The options for the cp command are described below.

        --target             - Argument for target option could be either ASM,
                               IOS, or APX, depending which type of instance ASMCMD
                               needs to be connected to copy the file or files.
        --service            - Name of the ASM instance name if not default +ASM.
        --port               - Listener port number, default is 1521.
        --dest_dbname        - Only used if target file is in a diskgroup.
                               Specified name will be used as the root directory
                               instead of using the default (either ASM, IOS, or
                               APX, depending which target was specified). If
                               this option is used, the system alias root path
                               will be +<DG>/<name>/
        --sparse             - Indicates that it is a sparse copy of a file.
        --sparse_merge_begin - Beginning depth of a sparse file to be merged.
        --sparse_merge_end   - Ending depth of a sparse file to be merged.
        connect_str          - The connection string to be used with a remote
                               instance copy.
        src_file             - Name of the source file to copy. Use of wild cards
                               is supported.
        tgt_file             - A user alias for the created target file name or
                               alias directory name.
        cp cannot copy files between two remote instances. The local Oracle
        ASM instance must be either the source or the target of the operation.
        You can use the cp command to:
                Copy files from a disk group to the operating system
                Copy files from a disk group to a disk group
                Copy files from the OS file system to a disk group
        Some file types cannot be the source or destination of the cp command.
        These file types include OCR and SPFILE file types.
        To back up, copy, or move an ASM SPFILE, use the spbackup, spcopy,
        or spmove commands.
        To copy OCR backup filetype, the source must be from a disk group.
        connect_str is not required for a local instance copy, which is the
        default case. In the case of a remote instance copy, you must specify
        the connect string and Oracle ASM prompts for a password in a
        non-echoing prompt. The connect_str is in the form of:
                user@host.SID
        user, host, and SID are required in the connect_str parameter. The
        default port number is 1521.
        --service       - name of the ASM instance name if not default +ASM.
        connect_str     - The connection string to be used with a remote instance
        src_file must be either the a fully qualified file name,
        system-generated name, or Oracle ASM alias.
        The format of copied files is portable between Little-Endian and
        Big-Endian systems if the files exist in an ASM disk group. ASM
        automatically converts the format when it writes the files. For copying
        a non-ASM files from or to an ASM disk group, you can copy the file to
        a different endian platform and then use one of the commonly used
        utilities to convert the file.

Examples
        The following are examples of the cp command.
        The first example shows a copy of a file in the data disk group to
        a file on the operating system.
        The second example shows a copy of several files in the data diskgroup
        to a directory on the operating system.
        The third example shows a copy of a file on the operating system to
        the DATA disk group. The --dest_dbname option was used, so the system
        alias for the copied file will be stored under +DATA/DB1/DATAFILE
        instead of using the default +DATA/ASM/DATAFILE.
        The fourth and fifth examples shows how to copy files from ASM to
        remote server (OS file).
        The sixth example shows how to copy a remote OS File to DATA diskgroup.
        The seventh example shows how to copy a remote OS File to DATA diskgroup
        when listener port is not default.
        The eight example shows how to make a sparse copy of a file between
        sparse diskgroups.
        The ninth example shows how to make a sparse copy of multiple sparse
        files.

        Sparse merge: Consider a multi-level sparse file hierarchy having sparse
        files SPARSE.205.14770227--SPARSE.241.14770227--SPARSE.257.71740127--SPARSE.259.91640227
        where SPARSE.205.14770227 is the base parent and SPARSE.241.14770227
        is the parent of SPARSE.257.71740127, which in turn is the parent of
        SPARSE.259.91640227.

        The tenth example shows how to merge multiple sparse files into a
        new destination file (out-of-place merge).
        The eleventh example shows how to merge multiple sparse files into an
        existing sparse file (in-place merge).

          sys           is the user name on the remote server
          password      is the password of the user.
          server        is the remote server name
          /scratch/file OS file.

        If password is not included in the command line, you are prompted to
        enter the password.

        ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295
                     /mybackups/example.bak
        copying +data/orcl/datafile/EXAMPLE.265.691577295 ->
                   /mybackups/example.bak

        ASMCMD [+] > cp +data/orcl/data*/orcl* /mybackups
        copying +data/orcl/DATAFILE/ORCLTABLES.273.924621557 ->
                  /mybackups/ORCLTABLES.273.924621557
        copying +data/orcl/DATAFILE/ORCLTABLES.274.924621557 ->
                   /mybackups/ORCLTABLES.274.924621557
        copying +data/orcl/DATAFILE/ORCLTABLES.275.924621557 ->
                   /mybackups/ORCLTABLES.275.924621557

        ASMCMD [+] > cp /mybackups/examples.bak
                     +data/orcl/datafile/myexamples.bak --dest_dbname DB1
        copying /mybackups/examples.bak -> +data/orcl/datafile/myexamples.bak

        ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295
                     sys@server.asminstance:/scratch/file
        copying +data/orcl/datafile/EXAMPLE.265.691577295 ->
                          /scratch/file

        ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295
                     sys/passwd@server.asminstance:/scratch/file
        copying +data/orcl/datafile/EXAMPLE.265.691577295 ->
                          /scratch/file

        ASMCMD [+] > cp  sys@server.SID:/scratch/file +data/orcl/datafile/file
        copying /scratch/file -> +data/orcl/datafile/file

        ASMCMD [+] > cp --port 2500 sys@server.SID:/scratch/file
                                    +data/orcl/datafile/file
        copying /scratch/file -> +data/orcl/datafile/file

        ASMCMD [+] > cp --sparse +SPARSEDG/SPARSE.259.91640227
                                 +SPARSEDG/sparsefile/sparse_cp
        copying +SPARSEDG/SPARSE.259.91640227 -> +SPARSEDG/sparsefile/sparse_cp

        ASMCMD [+] > cp --sparse +SPARSEDG/sparse_alias.1
                                 +SPARSEDG/sparse_alias.2
                                 +SPARSEDG/dir/sp_alias.3 +SPARSEDG/sparsedir/
        copying +SPARSEDG/sparse_alias.1 -> +SPARSEDG/sparsedir/sparse_alias.1
        copying +SPARSEDG/sparse_alias.2 -> +SPARSEDG/sparsedir/sparse_alias.2
        copying +SPARSEDG/dir/sp_alias.3 -> +SPARSEDG/sparsedir/sp_alias.3

        ASMCMD [+] > cp --sparse_merge_begin +SPARSEDG/SPARSE.259.91640227
                                             +SPARSEDG/sparsefile/sparse_merge
                        --sparse_merge_end +SPARSEDG/SPARSE.205.14770227
        merging +SPARSEDG/SPARSE.259.91640227 through +SPARSEDG/SPARSE.205.14770227
        to +SPARSEDG/sparsefile/sparse_merge

        ASMCMD [+] > cp --sparse_merge_begin +SPARSEDG/SPARSE.259.91640227
                                             +SPARSEDG/SPARSE.259.91640227
                        --sparse_merge_end +SPARSEDG/SPARSE.241.14770227
        merging +SPARSEDG/SPARSE.259.91640227 through +SPARSEDG/SPARSE.241.14770227
        to +SPARSEDG/SPARSE.259.91640227

        NOTE: cp command can be used to copy files from ASM diskgroups to
        namedpipe(fifo).  The following is an example to copy to fifo.

        $bash> mkfifo /tmp/fifotest
        $bash> cp /tmp/fifotest /tmp/file &
        $bash> asmcmd cp +data/orcl/datafile/file /tmp/fifotest
        copying +data/orcl/datafile/file -> /tmp/fifotest


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...