Thursday 12 July 2018

export multiple tables from multiple schemas expdp



create directory dumps as '/u01/dumps';


CREATE TABLE XXCRC_TLIST(  T );



 expdp \'/ as sysdba\' DUMPFILE=AhmedTables.dmp  LOGFILE=AhmedTables.log DIRECTORY=DUMPS INCLUDE=TABLE:\"in\(SELECT t FROM  apps.xxcrc_tlist\)\"  schemas=XXCRC,APPS,SYS

 impdp \'/ as sysdba\' DUMPFILE=AhmedTables.dmp  LOGFILE=AhmedTables.log DIRECTORY=DUMPS remap_schema=APPS:XXCRC table_exists_action=replace


Tuesday 10 July 2018

Oracle Cloud backup DBCS/DBaaS



Backup Log Files:
/var/opt/oracle/bkup_api/log/bkup_api.log

OBKUP:: Uploading config files to cloud storage
Starting OSS
Logfile is /var/opt/oracle/log/oss/oss_2018-07-10_10:28:29.log
Config file is /var/opt/oracle/oss/oss.cfg


Path: /var/opt/oracle/log/ORCL/obkup
cat /var/opt/oracle/log/ORCL/obkup/obkup2018-07-09_10:12:20.log
OBKUP:: Oracle database state is up and running
catalog mode  no
################################ PERIODICAL BACKUP STARTING ################################
logfile: /var/opt/oracle/log/ORCL/obkup/obkup_2018-07-10_10:23:07.log
OBKUP:: DB instance: ORCL
OBKUP:: Determining if the filesystem is not full
Case OK : Your partition /u03 has 11% used space
Case OK : Your partition /u01 has 24% used space
Case OK : Your partition /u02 has 45% used space
OBKUP:: ...... OK
OBKUP:: Validating the backup repository ......
OBKUP::      All backup pieces are ok
OBKUP:: Performing Incremental Backup to Cloud Storage
OBKUP:: Executing rman instructions
Connection string: /u01/app/oracle/product/12.1.0/dbhome_1/bin/rman msgno target /
 -> SET ENCRYPTION ON;
 -> BACKUP DEVICE TYPE SBT AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE TAG 'dbaas_incr_backup';
 -> DELETE NOPROMPT OBSOLETE;
 -> BACKUP DEVICE TYPE SBT AS COMPRESSED BACKUPSET ARCHIVELOG ALL;
 ->  RMAN OUTPUT:

      Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 10 10:25:24 2018

      Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

      RMAN-06005: connected to target database: ORCL (DBID=1484881322)

      RMAN>
      RMAN-03029: echo set on


      RMAN> SET ENCRYPTION ON;
      RMAN-03023: executing command: SET encryption
      RMAN-06009: using target database control file instead of recovery catalog


      RMAN> BACKUP DEVICE TYPE SBT AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE TAG 'dbaas_incr_backup';
      RMAN-03090: Starting backup at 10-JUL-2018 10:25:27
      RMAN-08030: allocated channel: ORA_SBT_TAPE_1
      RMAN-08500: channel ORA_SBT_TAPE_1: SID=308 device type=SBT_TAPE
      RMAN-08526: channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.2
      RMAN-08047: channel ORA_SBT_TAPE_1: starting compressed incremental level 1 datafile backup set
      RMAN-08010: channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
      RMAN-08522: input datafile file number=00009 name=/u02/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf
      RMAN-08522: input datafile file number=00016 name=/u02/app/oracle/oradata/ORCL/4E0E6F8C1EAA259CE053F64FC40A46B6/datafile/o1_mf_sp485189_dxw3oolg_.dbf
      RMAN-08522: input datafile file number=00025 name=/u02/app/oracle/oradata/ORCL/4E0E6F8C1EAA259CE053F64FC40A46B6/datafile/o1_mf_sp245732_dxyd926s_.dbf
      RMAN-08522: input datafile file number=00034 name=/u02/app/oracle/oradata/ORCL/4E0E6F8C1EAA259CE053F64FC40A46B6/datafile/o1_mf_sp848680_dy96dc9j_.dbf
      RMAN-08522: input datafile file number=00008 name=/u02/app/oracle/oradata/ORCL/pdb1/system01.dbf
      RMAN-08522: input datafile file number=00035 name=/u02/app/oracle/oradata/SP84868041_soainfra.dbf
      RMAN-08522: input datafile file number=00017 name=/u02/app/oracle/oradata/SP485189748_soainfra.dbf
      RMAN-08522: input datafile file number=00026 name=/u02/app/oracle/oradata/SP24573291_soainfra.dbf
.
.
.
.
  RMAN-03091: Finished backup at 10-JUL-2018 10:26:47

      RMAN-03090: Starting Control File and SPFILE Autobackup at 10-JUL-2018 10:26:47
      RMAN-08503: piece handle=c-1484881322-20180710-09 comment=API Version 2.0,MMS Version 12.2.0.2
      RMAN-03091: Finished Control File and SPFILE Autobackup at 10-JUL-2018 10:27:02


      RMAN> DELETE NOPROMPT OBSOLETE;
      RMAN-06524: RMAN retention policy will be applied to the command
      RMAN-06510: RMAN retention policy is set to recovery window of 30 days
      RMAN-12016: using channel ORA_SBT_TAPE_1
      RMAN-08030: allocated channel: ORA_DISK_1
      RMAN-08500: channel ORA_DISK_1: SID=72 device type=DISK
      RMAN-06287: Deleting the following obsolete backups and copies:
      RMAN-06281: Type                 Key    Completion Time    Filename/Handle
      RMAN-06282: -------------------- ------ ------------------ --------------------
      RMAN-06284: Backup Set           27485  10-JUN-2018 00:31:10
      RMAN-06285:   Backup Piece       27485  10-JUN-2018 00:31:10 cgt5287e_1_1
.
.
...
     RMAN-03090: Starting Control File and SPFILE Autobackup at 10-JUL-2018 10:28:02
      RMAN-08503: piece handle=c-1484881322-20180710-0a comment=API Version 2.0,MMS Version 12.2.0.2
      RMAN-03091: Finished Control File and SPFILE Autobackup at 10-JUL-2018 10:28:17


      RMAN> set echo off;
      RMAN-03030: echo set off

      RMAN>

      Recovery Manager complete.
OBKUP:: ....... OK
OBKUP:: Incremental Backup to Cloud Storage is Completed
OBKUP:: Clean MOTD.
OBKUP:: Starting backup of config files
############### CFG FILES BACKUP #####################
OBKUP:: Executing rman instructions
Connection string: /u01/app/oracle/product/12.1.0/dbhome_1/bin/rman msgno target /
 -> SPOOL LOG to '/var/opt/oracle/ocde/assistants/bkup/tmp/list';
 -> list backup summary;
 -> SPOOL LOG OFF;
 ->  RMAN OUTPUT:

      Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 10 10:28:21 2018

      Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

      RMAN-06005: connected to target database: ORCL (DBID=1484881322)

      RMAN>
      RMAN-03029: echo set on


      RMAN> SPOOL LOG to '/var/opt/oracle/ocde/assistants/bkup/tmp/list';RMAN> RMAN>
      RMAN-03039: Spooling for log turned off

      Recovery Manager12.1.0.2.0


      RMAN> set echo off;
      RMAN-03030: echo set off

      RMAN>

      Recovery Manager complete.
unlink /var/opt/oracle/ocde/assistants/bkup/tmp/list
unlink /var/opt/oracle/ocde/assistants/bkup/tmp/tags
rmdir /var/opt/oracle/ocde/assistants/bkup/tmp
OBKUP:: at time: 2018-07-10:10:28:02
OBKUP:: Determining the oracle database id
ownership of `/var/opt/oracle/ocde/assistants/bkup/dbid' retained as oracle:oinstall
OBKUP::  DBID: 1484881322
OBKUP:: Creating directories to store config files
OBKUP:: /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles .... OK
OBKUP:: /u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles .... OK
OBKUP:: Determining the oracle database id
ownership of `/var/opt/oracle/ocde/assistants/bkup/dbid' retained as oracle:oinstall
OBKUP::  DBID: 1484881322
 List of files to be included in tar
files to be included @ /var/opt/oracle/ocde/assistants/bkup/oscfg.spec.e: -> oscfg.spec
 -> /etc/rc.d
 -> /home/oracle/bkup
 -> /home/oracle/.bashrc
 -> /etc/crontab
 -> /etc/sysctl.conf
 -> /etc/passwd
 -> /etc/group
 -> /etc/oraInst.loc
 -> /etc/oratab
 -> /etc/fstab
 List of files to be included in tar
files to be included @ /var/opt/oracle/ocde/assistants/bkup/dbcfg.spec.e: -> dbcfg.spec
 -> dbid
 -> /u01/app/oracle/admin/ORCL/xdb_wallet
 -> /u01/app/oracle/admin/ORCL/db_wallet
 -> /u01/app/oracle/admin/ORCL/tde_wallet
 -> /u01/app/oracle/product/12.1.0/dbhome_1/dbs/opcORCL.ora
 -> /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL
 -> /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
 -> /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 -> /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
 -> /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/env_rdbms.mk
 -> /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ins_rdbms.mk
 -> /var/opt/oracle/creg/ORCL.ini
OBKUP:: Compressing config files into tar files
OBKUP:: Uploading config files to cloud storage
Starting OSS
Logfile is /var/opt/oracle/log/oss/oss_2018-07-10_10:28:29.log
Config file is /var/opt/oracle/oss/oss.cfg

INFO: OSS Connection verified
Token is :
Uploading file /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_07_10/oscfgfiles_20180710_1028.tar.gz, you can monitor progress via /var/opt/oracle/log/oss/putfile.out
File /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_07_10/oscfgfiles_20180710_1028.tar.gz uploaded

Starting OSS
Logfile is /var/opt/oracle/log/oss/oss_2018-07-10_10:28:37.log
Config file is /var/opt/oracle/oss/oss.cfg

INFO: OSS Connection verified
Token is :
Uploading file /u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles/2018_07_10/ohcfgfiles_20180710_1028.tar.gz, you can monitor progress via /var/opt/oracle/log/oss/putfile.out
File /u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles/2018_07_10/ohcfgfiles_20180710_1028.tar.gz uploaded

changed ownership of `/u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_07_10/oscfgfiles_20180710_1028.tar.gz' to oracle:oinstall
changed ownership of `/u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles/2018_07_10/ohcfgfiles_20180710_1028.tar.gz' to oracle:oinstall
OBKUP:: Completed at time: 2018-07-10:10:28:44
Cleaning backups older than 31 days
unlink /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_05_03/oscfgfiles_20180503_0118.tar.gz
unlink /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_05_03/oscfgfiles_20180503_0033.tar.gz
unlink /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_05_03/oscfgfiles_20180503_0055.tar.gz
rmdir /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_05_03
unlink /u03/app/oracle/fast_recovery_area/ORCL/oscfgfiles/2018_05_09/oscfgfiles_20180509_0153.tar.gz

unlink /u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles/2018_02_28/ohcfgfiles_20180228_1941.tar.gz
unlink /u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles/2018_02_28/ohcfgfiles_20180228_0841.tar.gz
rmdir /u03/app/oracle/fast_recovery_area/ORCL/ohcfgfiles/2018_02_28
Cleaning cfg file backups of cloud storage older than 30 days
OBKUP:: at time: 2018-07-10:10:28:44
Delete oss cfg file backup...   tahalufdbcs-1484881322/ohcfgfiles_20180227_0038.tar.gz

Delete oss cfg file backup...   tahalufdbcs-1484881322/ohcfgfiles_20180227_0140.tar.gz

Delete oss cfg file backup...   tahalufdbcs-1484881322/ohcfgfiles_20180227_0241.tar.gz

Delete oss cfg file backup...   tahalufdbcs-1484881322/ohcfgfiles_20180227_0341.tar.gz

Delete oss cfg file backup...   tahalufdbcs-1484881322/ohcfgfiles_20180227_0441.tar.gz

Delete oss cfg file backup...   tahalufdbcs-1484881322/ohcfgfiles_20180227_0540.tar.gz
.
.
..
.
OBKUP:: Config files backup ended successfully
OBKUP:: Clean MOTD.
OBKUP:: All requested tasks are completed
############################### BACKUP COMPLETED SUCCESSFULLY ##############################


Updating Cloud backup tool by Using the dbpatchm Subcommand:




Check latest available update:
[root@tahalufdbcs opc]# dbaascli dbpatchm --run -list_tools
DBAAS CLI version 1.0.0
Executing command dbpatchm --run -list_tools -cli
/var/opt/oracle/patch/dbpatchm -list_tools -cli
Use of uninitialized value $cplat in pattern match (m//) at /var/opt/oracle/patch/dbpatchm line 6106.
Use of uninitialized value $service in pattern match (m//) at /var/opt/oracle/patch/dbpatchm line 3993.
Starting DBPATCHM
Logfile is /var/opt/oracle/log/dbpatchm/dbpatchm_2018-07-10_08:28:45.log
Config file is /var/opt/oracle/patch/dbpatchm.cfg

INFO: cdb is set to : yes
INFO: dbversion detected : 12102
INFO: patching type : psu


Patchid : 18.1.2.1.0_180228.1052

Patchid : 18.1.2.1.0_180301.0107

Patchid : 18.1.2.1.0_180309.0517

Patchid : 18.1.4.1.0_180309.0546

Patchid : 18.1.4.1.0_180312.1800

Patchid : 18.1.2.1.0_180327.0219

Patchid : 18.1.4.1.0_180406.2023

Patchid : 18.1.2.1.0_180407.0948

Patchid : 18.1.4.1.0DBCS_180412.0105

Patchid : 18.1.2.1.0_180418.0606

Patchid : 18.1.4.1.0DBCS_180418.0614

Patchid : 18.1.4.1.0_180420.0000

Patchid : 18.1.4.1.0_180425.0000

Patchid : 18.2.3.1.0_180505.1604

Patchid : 18.2.3.1.0_180516.0000

Patchid : 18.1.4.1.0_180509.0000

Patchid : 18.1.2.1.0_180511.0801

Patchid : 18.2.3.1.0_180523.0000

Patchid : 18.1.4.1.0_180523.0000

Patchid : 18.1.4.1.0_180525.0000

Patchid : 18.1.4.1.0_180530.0000

Patchid : 18.2.3.1.0_180530.0000

Patchid : 18.1.4.1.0_180606.0000

Patchid : 18.2.3.1.0_180606.0000

Patchid : 18.1.4.1.0_180613.0000

Patchid : 18.2.3.1.0_180613.0000

Patchid : 18.1.2.1.0_180613.0000

Patchid : 18.2.3.1.0_180620.0000

Patchid : 18.1.4.1.0_180620.0000

Patchid : 18.1.4.1.0_180627.0000

Patchid : 18.2.3.1.0_180627.0000

Patchid : 18.1.4.1.0_180704.0000

Patchid : 18.2.3.1.0_180704.0000
dbpatchm Execution completed


Compare with current version:

[root@tahalufdbcs opc]# rpm -qa|grep -i dbaastools
dbaastools-1.0-1+18.1.2.1.0_180215.0915.x86_64


If update in higher version like my case, apply latest update:
[root@tahalufdbcs opc]# dbaascli dbpatchm --run -toolsinst -rpmversion=18.2.3.1.0_180704.0000
DBAAS CLI version 1.0.0
Executing command dbpatchm --run -toolsinst -rpmversion=18.2.3.1.0_180704.0000 -cli
/var/opt/oracle/patch/dbpatchm -toolsinst -rpmversion=18.2.3.1.0_180704.0000 -cli
Use of uninitialized value $cplat in pattern match (m//) at /var/opt/oracle/patch/dbpatchm line 6106.
Use of uninitialized value $service in pattern match (m//) at /var/opt/oracle/patch/dbpatchm line 3993.
Starting DBPATCHM
Logfile is /var/opt/oracle/log/dbpatchm/dbpatchm_2018-07-10_09:31:31.log

WARN : Parameter toolsinst passed via cmdline is not a valid parameter. Please check the usage.
Config file is /var/opt/oracle/patch/dbpatchm.cfg

INFO: cdb is set to : yes
INFO: dbversion detected : 12102
INFO: patching type : psu

INFO: existing dbaastools version - dbaastools-1.0-1+18.1.2.1.0_180215.0915.x86_64

INFO: updated dbaastools rpm to - dbaastools-1.0-1+18.2.3.1.0_180704.0000.x86_64
dbpatchm Execution completed


Reset the backup configuration:
[root@tahalufdbcs opc]# /var/opt/oracle/ocde/assistants/bkup/bkup
Starting BKUP
Logfile is /var/opt/oracle/log/bkup/bkup_2018-07-10_10:07:15.log
Config file is /var/opt/oracle/ocde/assistants/bkup/bkup.cfg
dbname: ORCL
Dataguard configuration
Enabled: 0
Mode: PRIMARY
Looking for previous configuration:
Directory : /home/oracle/bkup/ORCL
  -> dbcfg.spec
  -> oscfg.spec
  -> obkup
 -> Found: 3 files
 -> Moving previous configuration to /home/oracle/bkup/ORCL_20180710100726
DB edition: standard
OSS channels: 1
INFO: OSS connection using supplied creds passed.
INFO : Cfg files recovery window set to 30
No initial bkup of PFILE needed.
Configuring Backup to OSS
Preparing OPC library.
INFO : OPC opc install
Running OPC installer...
INFO : libopc found at /u01/app/oracle/product/12.1.0/dbhome_1/lib. Move to /tmp directory.
Installing OPC installer local copy.
Running OPC installer.....
using file : /var/opt/oracle/log/ORCL/bkup/temp.sh
Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2018-06-12
Oracle Database Cloud Backup Module credentials are valid.
Oracle Database Cloud Backup Module wallet created in directory /u01/app/oracle/admin/ORCL/opc_wallet.
Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/opcORCL.ora created.
Skipping library download because option -libDir is not specified.
OPC installer completed successfully.
OSS specific RMAN config for libopc
Common RMAN Config
Instantiating obkup
Instantiating dbcfg.spec
Configuring backup of Config File
Updating Control File Record Keep Time
Enabling block change tracking
Updating RMAN defaults
Adding entry to crontab
INFO: Archivelog management enabled.
Adding entry to crontab
Accessing to your Database ID ..
The DBaaS instance database id is: 1484881322
#### Completed Execution.

Number of Visitors