Loading “Views” from One Database to Another

Hi all,

This seems to be a simple request, right?  Let’s go to the regular cases / simple answers:

  • Use DBMS_METDATA.ET_DDL to get the view code: DBMS_METADATA.get_ddl (‘VIEW’, <VIEW_NAME>, <OWNER>)
  • Use datapump with include=VIEW Or even specifying the list INCLUDE=VIEW:\”IN (\’VW_EXAMPLE1\’, \’VW_EXAMPLE2\’)\” Or part of the name: INCLUDE=VIEW:”LIKE ‘VW_%EXEMPLE%'”

However, when supporting a client with this need I see this may have some caveats. The options above load the view purely, not the “content” of the view from an application perspective.

And here let’s leave it clear, a view doesn’t have any data but instead stores a query to retrieve the data from regular tables. We could have underlying tables as support mechanisms in case of materialized views,  but in essence, they are transitory build from the actual database tables. Now how to load views to other databases without the source tables including the underlying data?

This question has 2 answers:

  • Exporting the views as tables.

In general lines I’d do it manually: Create tables from views and then export them. This can mean I would need to have space for creating those tables, which can be a lot, though.

But then I see we have VIEWS_AS_TABLES clause in Datapump which makes exactly that. As a reference: https://docs.oracle.com/database/121/SUTIL/GUID-E4E45E81-5391-43BE-B27D-B763EF79A885.htm#SUTIL3904

However, in this case, the import will bring the views as tables, not as views. Which will theoretically resolve the issue from a data perspective, but may not be what is required: What if I need the views as views on the destination database?

Well, in this case it’s needed to load all the underlying tables. How to extract them? The hard way is opening all the view codes and listing them. The smart way is the second option:

  • Listing the tables referenced by Views from dba_dependencies and exporting them.

select owner as schema_name,
name as view_name,
referenced_owner as referenced_schema_name,
referenced_name,
referenced_type
from sys.dba_dependencies
where type = 'VIEW'
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by owner, name, referenced_name, referenced_owner, referenced_type;

Aaaand, that’s what I actually needed. With the list in place, it’s a matter of exporting with datapump including the tables on the list and the views (as mentioned above).

I hope it helps!

Oracle Database Backup to Cloud: KBHS – 01602: backup piece 13p0jski_1_1 is not encrypted

Hi all!
I’m preparing a material about downloading, configuring using Oracle Database Cloud Backup. My case is about backuping a local database to Cloud.

So, as avant-première for you from the Blog, a quick situation about:

cloud-backup.png

# Error

RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 04/14/2016 13:58:45
ORA-27030: skgfwrt: sbtwrite2 returned error
ORA-19511: non RMAN, but media manager or vendor
specific failure, error text:
KBHS - 01602: backup piece 12p1krsi_1_1 is not encrypted

# Solution (one of)

RMAN> set encryption on identified by "mypassword" only;
executing command: SET encryption


Why?

To use Oracle Database Backup to Cloud you need to use at least one encrypting method.
Oracle offers basically 3:
– Password Encryption
– Transparent Data Encryption (TDE)
– Dual-Mode Encryption (a combination of password and TDE).

In this post I refered the easier, by I recommend you to take a look on KB: https://docs.oracle.com/cloud/latest/dbbackup_gs/CSDBB.pdf

Matheus.

Transporting 12TB Database Over 4.000 km with EMC DDomain

Hi all!
I’m here to share this is great whitepaper by EMC about Transporting a 12 TB Oracle Database almost 4.000km using Data Domain to Backup and restore.

DDTransportation
Take a look:
https://www.emc.com/collateral/software/white-papers/h8238-oracle-remote-recovery-datadomain-wp.pdf

It’s nice to see how deduplication was a key-factor to the success of project.
It’s really a good feature that provides lots of optimization to backup/recover databases, besides diskspace saving, backup window reducing, network interface load reducing, etc.

Have a nice weekend!

Export/Backup directly to Zip using MKNOD!

We all faced that situation when we have to make a logical backup/export and haven’t so much area to do that, right?
We know the export usually compress a lot on zip/gzip… It wouldn’t be great if we can export directly to compressed file?

zip
This situation become much more common because of Datapump, that requires a directory accessible by database server. If you have not possibility to make a mounting point or any other area, this can help…

## BKP with MKNOD
BKP_DEST=/just/example
DATE=`date +%Y%m%d%H%M`
cd $BKP_DEST
mknod bkp_$DATE.dmp p
gzip  bkp_$DATE.dmp.gz &
### Uncomment and Ajust one of:
## MySQL:
#mysqldump -u $user -p$password $database > bkp_$DATE.dmp
## Oracle (Datapump or EXP)
expdp \"/ as sysdba\" dumpfile=bkp_$DATE.dmp full=y directory=DIRECTORY_EXAMPLE logfile=log_bkpzipped.log compress=y
#expdp $user/$password dumpfile=bkp_$DATE.dmp full=y directory=DIRECTORY_EXAMPLE logfile=log_bkpzipped.log
#exp \"/ as sysdba\" file=bkp_$DATE.dmp log=log_bkpzipped.log compress=y [tables=owner.table,..] [owner=schema1,..] [...]

Hugs!
Matheus.

Creating RMAN Backup Catalog

It can soud repetitive, but always good to have notes about 🙂

  1. Create Schema for Catalog on CatalogDB:
-- Create the user
create user RMAN_MYDB identified by &PASS;
-- Grant/Revoke role privileges
grant recovery_catalog_owner to RMAN_MYDB;
-- Grant/Revoke system privileges
grant create session to RMAN_MYDB;

2. Create catalog and register database:

-- Conected to target Database via RMAN
RMAN> connect catalog rman_mydb/password@catdb.grepora.net:1521/catalogdb

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Well done!
Matheus.

RMAN-06059: expected archived log not found

# Error

RMAN-03002: failure of backup command at 06/28/2015 14:56:30
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615
ORA-17503: ksfdopn:2 Failed to open file +DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615
ORA-15012: ASM file '+DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615' does not exist

# Solution
First of all, you need to know which files exists or not:

RMAN> CROSSCHECK ARCHIVELOG ALL;

Then, clear the missing and run another backup.

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

It’s hardly recommended that you make a full backup after that, to ensure you have a recoverable state.

Matheus.