AWS EC2: Custom Functions to Verify Passwords

Hello all!
Looking forward to create password functions on EC2? Easy, we just need to use AWS internal functions for it. Check below an example:

# To create it:

begin
    rdsadmin.rdsadmin_password_verify.create_verify_function(
        p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION', 
        p_min_length           => 12, 
        p_min_uppercase        => 2, 
        p_min_digits           => 1, 
        p_min_special          => 1,
        p_disallow_at_sign     => true);
end;
/

In case you want to double check the code created, here goes a quick trick: Check on DBA_SOURCE:

col text format a150
  select TEXT  from DBA_SOURCE 
   where OWNER = 'SYS' and NAME = 'CUSTOM_PASSWORD_FUNCTION' order by LINE;

Hope it helps. Cheers!

AWS EC2: Grant Privileges to SYS Objects

Hello all!
So quick one today: How to grant and revoke privileges from/to SYS objects on EC2 instances? Do we have the ‘grant option’ for those grants?

Easy, have a look:

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'GREPORA',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

Amazon EC2: X11 Forwarding After Sudo SSH Session

Hello all!

So, now with more use of resources like Cloud servers, more and more silent instalations are being done, right? Myself, I do it in silent always I can.

What if I need to export X. Plus, if I need to export it from user oracle, but I can only login with ec2-user, as usual?

Here is the process for that:

1) Connect to AWS EC2 instance

[user@securehost ~]$ ssh -X ec2-user@ipaddress
Last login: Fri Dec 7 14:41:41 2018 from grepora.srv.com
__| __|_ )
 _| ( / Amazon Linux AMI
 ___|\___|___|
https://aws.amazon.com/amazon-linux-ami/2018.03-release-notes/
13 package(s) needed for security, out of 16 available
Run "sudo yum update" to apply all updates.

2) Test xclock works from ec2-user

[ec2-user@ipaddress ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C

3) Show all magic cookie

[ec2-user@ipaddress ~]$ xauth list
ipaddress/unix:12 MIT-MAGIC-COOKIE-1 7e53e7600ff4177d7bbc66bde0a1b1ca
ipaddress/unix:11 MIT-MAGIC-COOKIE-1 e3d1a8915484c929ef3e809b047e6352
ipaddress/unix:10 MIT-MAGIC-COOKIE-1 07b3de3093cef835c19239ea952231b7

4) Show DISPLAY variable

[ec2-user@ipaddress ~]$ env|grep DISPLAY
DISPLAY=localhost:10.0

5) Create /tmp/xauth based on current DISPLAY variable

[ec2-user@ipaddress ~]$ xauth list | grep unix`echo $DISPLAY | cut -c10-12` > /tmp/xauth
[ec2-user@ipaddress ~]$ ll /tmp/xauth ; cat /tmp/xauth 
-rw-rw-r-- 1 ec2-user ec2-user 78 Dec 7 14:47 /tmp/xauth
ipaddress/unix:10 MIT-MAGIC-COOKIE-1 07b3de3093cef835c19239ea952231b7

6) Sudo to oracle

[ec2-user@ipaddress ~]$ sudo su - oracle
Last login: Fri Dec 7 14:43:12 UTC 2018 on pts/0

7) Add and Verify xauth

[oracle@ipaddress ~]$ xauth add `cat /tmp/xauth`
[oracle@ipaddress ~]$ xauth list
ipaddress/unix:10 MIT-MAGIC-COOKIE-1 07b3de3093cef835c19239ea952231b7

8) Verify and Add DISPLAY variable

[oracle@ipaddress ~]$ env|grep DISPLAY
[oracle@ipaddress ~]$ export DISPLAY=localhost:10.0

9) Test xclock works from oracle

[oracle@ipaddress ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C
[oracle@ipaddress ~]$

Now you should be able to see the clock and so other graphical resources, like DBCA and so on.

Hope it helps!

Creating a Free Oracle Database at AWS

Have you ever heard about Amazon RDS?

Amazon RDS is a Relational Database cloud based service, which intends to help you to automate administrative tasks like hardware provisioning, database creation, backups, etc. Currently six database engines are available under this service: Oracle Database, Microsoft SQL Server, Amazon Aurora, PostgreSQL, MySQL and MariaDB.

This post provides a quick step-by-step on how to create your first Oracle Database RDS.

Requirements:
– Have an Amazon AWS account. If you don’t have one, don’t panic! You can still create one at: https://portal.aws.amazon.com/gp/aws/developer/registration/index.html

Now, how to create a RDS? Here it goes:

Continue reading

Database in Cloud: Quickest Procedure to Clone a Schema

Hey all!
So, we all know that operating with files/dump files can be tricky when using DBaaS in Public Cloud. In some situations, like Amazon RDS service, we simply don’t have access to SO.

In this scenario, how can we quickly clone a schema in the database? Using IMPDP with Database Link.

Also note that when working on AWS environments, avoiding to use dumpfiles when dealing with expdp/impdp is also encouraged to save IOPS from local disks (it is capped based on machine type).

This is, of course, also valid for On-Premise environments with limited area for dump files.

For this to work, we only need to create a database link pointing to the same database.
Also, of course, it is mandatory to use remap_schema, with optional clause remap_tablespace.

In the following example the link name is loop and proceed with impdp from a jumpbox with an Oracle client and tns configuration to RDS database.

1. Creating Database Link:

SQL> create database link loop connect to my_sysdba identified by "***" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service-name)))';

Database link created.

SQL> select * from dual@loop;

D
-
X

2. Running IMPDP:

[oracle@jumpbox ~]$ impdp schemas=ORIGINAL_SCHEMA network_link=loop remap_schema=ORIGINAL_SCHEMA:NEW_SCHEMA remap_tablespace=ORIGINAL_TBS:NEW_TBS TRANSFORM=oid:n directory=ANY_DIR logfile=duplicate_original.log

Import: Release 11.2.0.4.0 - Production on Wed Fev 7 21:03:54 2018

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

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_IMPORT_SCHEMA_02": /******** AS SYSDBA schemas=ORIGINAL_SCHEMA network_link=loop remap_schema=ORIGINAL_SCHEMA:NEW_SCHEMA remap_tablespace=ORIGINAL_TBS:NEW_TBS TRANSFORM=oid:n directory=ANY_DIR logfile=duplicate_original.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.481 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NEW_SCHEMA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
...
. . imported "NEW_SCHEMA"."TABLE1" 0 rows
. . imported "NEW_SCHEMA"."TABLE2" 0 rows
. . imported "NEW_SCHEMA"."TABLE3" 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_02" completed with 1 error(s) at Wed Fev 7 21:08:52 2018 elapsed 0 00:04:54

Hope it helps,
Cheers!

How to Setup Automatic Startup and Shutdown of an Oracle Database on Linux (Not Using Oracle-Restart)

Ok, we all have done it several times. I, myself, made some scripts to do it in past. However, do you know there is an official way/script for that?
You can accomplish this with dbstart and dbshut scripts, which are located in the $ORACLE_HOME/bin directory.
This is documented for 12.1 in Stopping and Starting Oracle Software .

Of course that, if you have Oracle Clusterware configured, you can use Oracle Restart and SRVCTL tool, and Clusterware automatically starts and stops the Oracle database instances and listeners. Which is way better.
This post refers to official procedure in case you haven’t Clusterware configured.

Quick Guide:

Continue reading

RMAN Raise ORA-19913 ORA-28365 On Restore from Cloud Backup

First I think was some error with Database Backup To Cloud, when testing. Then I realized it was a simple mistake by myself.

Let me show you. First trying to restore datafile:

[oracle@mydbsrvr archivelogs]$ rman target /
RMAN> restore datafile 6;
Starting restore at 03-MAY-2016 20:00:30
using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=178 device type=DISK
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/03/2016 20:00:34
ORA-19870: error while restoring backup piece 0sr4mdun_1_1
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

Ok, it might happen because I forgot to set encryption password:

RMAN> SET ENCRYPTION ON IDENTIFIED BY "matheusdba" only;
executing command: SET encryption
RMAN> restore datafile 6;
Starting restore at 03-MAY-2016 20:00:30
using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=178 device type=DISK
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/03/2016 20:00:34
ORA-19870: error while restoring backup piece 0sr4mdun_1_1
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

It hapen again?
This point I suspect some kind of bug… But it was my mistake and is not related to Cloud, but to Encryption use. To undestand:
For Backup: Use ENCRYPTION
For Restore/Recover: Use DECRYPTION

Obviously, but take me a minute to realize…

Setting decryption, and problem solved:

RMAN> set DECRYPTION identified by "matheusdba";
executing command: SET decryption
RMAN> restore datafile 6;
Starting restore at 03-MAY-2016 20:00:58
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1
channel ORA_SBT_TAPE_1: piece handle=0sr4mdun_1_1 tag=TAG20160503T193239
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-MAY-2016 20:01:02

See ya!
Matheus.