Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

PLSQL promises vs dbms_scheduler chains

10 lines of code vs 80 lines of code! When implementing JS promises in PLSQL, one of the main use cases
is the ability that promises gives, to easily chain together dependent steps defined as functions. Also the
ability to easily pass output from one step along to the next step in the chain.

10 Things I Love About Tech- Python Edition

So since I already complained about what I hate about tech from a diversity and culture perspective, I felt I needed to explain all the things I love about tech and why I wouldn’t want to be anywhere else.

Everyone would expect me to start with databases or virtualization, but I thought I’d keep everyone on their toes and start with my favorite programming language- PYTHON!

1.Timed Pauses, Stops and Waits

Sometimes systems are just too fast for humans.  It’s important to have proper delays between tasks so that people are able to interact or systems are able to respond to each other in the proper amount of time.  This is where the “sleep” command comes in.  Just like in Bash shell, sleep is used to tell a command to wait:

@logged("%b %d %Y - %H:%M:%S")
def add2(x, y):
    time.sleep(2)
    return x + y

The command above translates to “Time to sleep [in seconds] and then proceed with the next command[s].”  This comes in handy as you time out spaces between tasks and is a common command you’ll see in any python program.

2. Context Managers

The ability to write to files or create scripts dynamically is an important one in any programming language.  We write and append to files as needed and this is something we can do with Python using context management.  Simply open a file, write to it and then remember to close it afterwards, (which I didn’t add

>>>file = open(“/var/testfile.txt”,”w”) 
 
>>>file.write(“#Fix File Script”) 
>>>file.write(“mv /var/testfile.txt /usr/mvfile.sh”) 
>>>file.write(“chmod 774 /var/testfile.txt”) 
>>>file.write(“exit”) 
 
>>>file.close()

3.  Generators

As DBAs, we understand the value of a sequence.  We use them all the time in databases and create them with the simple command:

CREATE SEQUENCE emp_seq
 START WITH     1
 INCREMENT BY   1;

In programming languages, often its just as easy and with Python, we’re able to do all kinds of tasks via a function in a sequential manner.  There are distinct requirements to use a generator, but one of them is to use the word yield, as each function call must yield a value of some sort.

>>> def f():
...   print("-- Ready --")
...   yield 1
...   print("-- Set --")
...   yield 2
...   print("-- Go --")
...   yield 3
>>> gen = f()
>>> next(gen)
-- Ready --
1
>>> next(gen)
-- Set --
2
>>> next(gen)
-- Go --
3

Each of the yield values are stored in the object created by this function.  As you can see, this could be a bit cumbersome, so how can we do it in a simpler manner with Python?

4.  Iterators

Another way to perform something sequentially and simply, is to use an iterator.  While the iterator can save us a lot of time by building out from a list-

>>> f = [1, 2, 3]  
>>> iter(f)
<...iterator object at ...>
>>> f.__iter__()

We can save even more time by using a file as an iterator.  Yes, you heard me-  The file is the iterator and at no time does it create a separate object.  Keep in mind, only sequential access with a single thread is allowed here, too.

>>> f = open('/home/python/dbakevlar/file.txt')
>>> f is f.__iter__()
True
5.  Replacing Values
Sometimes what data is shown, isn’t the data you want returned.  This is pretty easy to change in Python and why its such a common language to use.
>>> local = [80031, 80204, 80221] 
>>> "Westminster","Denver","Aurora" = local

>>> Westminster
80031 
>>> Denver
80204
>>> Aurora
80221
6.  Decorator Function
No, this doesn’t mean we all become Martha Stewart, but these are gorgeous functions that make it easy to do a lot with very little.  In Python, decorators are functions (or objects that can be called) that require an input of optional arguments, a function or class, and return a function or class.

 

from inspect import getargspec as gas   

def decorate(fn):
    argspec = gas(fn)
    scd_argname = argspec[0][1]
    def inner(*args, **kwargs):
        special_value = (kwargs[scd_argname] 
                         if scd_argname in kwargs else args[1])
        if special_value == 2:
            print "nada"
        else:
            print "nada happening"
        return fn(*args, **kwargs)
    return inner

@decorate
def nada(a, b, c=3):
    pass

nada(1,2,3)
nada(1,b=2,c=4)
nada(1,3,5)
nada(1,b=6,c=5)
Note that nada can return the correct values depending on the arguments and is as flexible as we need it to be for a given program, wrapping functions inside another function.
7.  Unpacking with a Range
So I’ve decided I’m working with a range of 10, but I’ll have a set to the first value of 10, (which starts with 0, not 1, peeps) and then B can be anywhere in the range:
>>> a, *rest, b = range(10) 
>>> a 
0
>>> b 
9 
>>> rest [1, 2, 3, 4, 5, 6, 7, 8]
We could also just say we have b that’s a value anywhere in our range:
>>> *rest, b = range(10) 
>> rest 
[0, 1, 2, 3, 4, 5, 6, 7, 8] 
>>> b 
9
8. Reversing Any String
Sometimes you just need to reverse what you see.  Now for reversing searches on zipcodes, it’s very valuable to search by reversing the zipcode and index to remove “hot spots” on location searches.  The ability to reverse data easily in Python can ease this:
>>> Geocode = "80031" 

>>> print a[::-1] 

13008
9. Named String Formatting
Formatting of text is quite simple in Python.  There’s not a lot of complicated additions, just give a few parameter names and go!
print("The %(foo)s is %(bar)i." % {'foo': 'answer', 'bar':42})
The answer is always 42… </p />
</div></div></div>

    	  	<div class=

AWS Database Blog – Added To My Blog Roll

This is just a brief blog post to share that I’ve added the AWS Database Blog to my blogroll.  I recommend you do the same! Let’s follow what’s going on over there.

Some of my favorite categories under the AWS Database Blog are:

 

 

Readers: I do intent to eventually get proper credentials to make some posts on that blog. All in proper time and with proper training and clearance.

Filed under: oracle

12c nologging and Data Guard

The title sounds weird because Data Guard synchronisation is based on the redo stream, so it makes no sense to do nologging operations on the primary. And this is the reason why we set FORCE LOGGING on a Data Guard configuration. However, to lower the downtime of a migration done with Data Pump, you may want to import with minimal logging and then re-synchronize the standby. This post is about the re-synchronisation in 12.1

Nologging Data Pump

When you want to lower the downtime for a migration, you can disable force logging (alter database no force logging), and run impdp with the following: transform=disable_archive_logging:y
Don’t forget to re-enable force_logging at the end and to re-synchronize the standby.

nonlogged (aka unrecoverable)

So, you have nonlogged blocks, we also call that unrecoverable because it cannot be recovered with the redo stream. If you are in 12.2 then everything is easy with recover database nonlogged block; and I explained that in a previous post: https://blog.dbi-services.com/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard/

If you are in 12.2 then it is half easy only. You can see where you have nonlogged blocks:
RMAN> select file#,reason,count(*) from v$nonlogged_block group by file#,reason;
&bsp;
FILE# REASON COUNT(*)
---------- ------- ----------
5 UNKNOWN 158
6 UNKNOWN 159
7 UNKNOWN 336
8 UNKNOWN 94
9 UNKNOWN 16
10 UNKNOWN 14

and this is the right way to query them. If you use RMAN ‘report unrecoverable’ it will not display the datafiles that had nologging operations on the primary.

In 12.1 you can RESTORE FROM SERVICE to recover from the primary rather than from a backup. It is straightforward. I’m just writing this blog post in case you see the following when you try to do this because the message can be misinterpreted:


RMAN> restore database from service 'MYDB_SITE1_dgmgrl';
 
Starting restore at 03-MAY-2017 13:22:12
using channel ORA_DISK_1
 
skipping datafile 1; already restored to SCN 3849354
skipping datafile 2; already restored to SCN 3849356
skipping datafile 3; already restored to SCN 3849358
skipping datafile 4; already restored to SCN 3849360
skipping datafile 5; already restored to SCN 3849365
skipping datafile 6; already restored to SCN 3849372
skipping datafile 7; already restored to SCN 3849382
skipping datafile 8; already restored to SCN 3849389
skipping datafile 9; already restored to SCN 3849395
skipping datafile 10; already restored to SCN 3849398
restore not done; all files read only, offline, or already restored
Finished restore at 03-MAY-2017 13:22:12

RMAN is clever enough: the data files are ok, according to their header and it skipped the restore. But you know that they are not ok, because some blocks are marked as corrupt because of nologging operations. Then what to do? There is a FORCE option in the restore command. But you probably don’t need it. If you get the previous message, it means that the datafiles are synchronized, which means that the APPLY is running. And, anyway, in order to restore you need to stop the APPLY.


DGMGRL> edit database orclb set state=apply-off;

Of course, once you stopped the apply, you run your RESTORE DATABASE FORCE. But you probably don’t need it. Now, the datafiles are stale and RMAN will not skip them even without the FORCE keyword.


RMAN> restore database from service 'MYDB_SITE1_dgmgrl';
 
Starting restore at 03-MAY-2017 13:22:37
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service MYDB_SITE1_dgmgrl
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /media/raid-db/MYDB/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service MYDB_SITE1_dgmgrl
...
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-MAY-2017 13:25:30
RMAN> exit

Don’t forget to re-enable the Data Guard Apply at the end.

So what?

When you see all datafiles skipped, that probably means that you didn’t stop the APPLY. With APPLY stopped, and you probably stop it before the import as you plan to restore the standby later, then you probably don’t need the FORCE command. However, I’ll always recommend using the FORCE in this case because RMAN will skip the files without looking at the unlogged blocks. Imagine that you put a tablespace in read-only after the non-logged import but before stopping the apply. Then this one will be skipped.

 

Cet article 12c nologging and Data Guard est apparu en premier sur Blog dbi services.

Ten Things I Really Hate About Tech Today

I love working in tech.  I can honestly say that nothing gives me greater satisfaction than working on a technical challenge or having a brilliant conversation about technology with peers.  Other days I feel like a broken record having the same conversations with the women in technology around me, facing a lack of diversity and the consistent exclusion in tech.  Until this changes, the beatings will continue and my expression may often look similar to Alan Rickman’s below:

“Rants are good for the soul and even better for the world. Some times, some things just need to be said.”

Delphix has done an incredible job with diversity and inclusion.  They’ve actually created a Diversity Counsil, a charter and initiatives to address many of these challenges I’ve listed below.  It’s an impressive and powerful move by an incredible company.

So for the rest of you, listen up, build a bridge, get past it and just stop doing the things on this list.  It hurts me to my core to hear that so many are still experiencing these-

  1. Interview for Team Acceptance

“I’d like you to come in for a final interview to make sure you fit in with the team.”

This is almost a guaranteed step in the interview process and yes, upon first view, it sounds reasonable that a manager would want to ensure a candidate worked well with their peers. This practice was first embraced in the technical field with good intentions, but it has direct correlation to limiting team diversity.

Remember back to school- think about how each clique developed simply by how you looked, your background and your preferences? Yeah, this is exactly what continues to happen in tech- white, geeky dude clique.

One of the things I noticed from many of the articles on team interviews is that they often occur in a social situation, over drinks, including beers or other alcoholic beverages. This is bound to deter from diversity in candidates who might find it uncomfortable for one reason or another and hinder them from making it to the final interview stages.

The end result is teams with limited skills and they aren’t able to balance out for each other’s weaknesses and strengths. Often the team members are so much alike they’re unable to innovate at the level that’s required in tech.

Yo, hiring manager- kudos having the team meet the candidate, but do your job and create a team that has the diversity to weather storms, differing demands and requirements. Create a tech team that has the diversity that will create the technology of tomorrow to serve the diverse ecosystem of today.

  1. Hurt Feelings

“It’s difficult working with women, they get their feelings hurt.”

I don’t know how often I’ve heard this from both men and women about working with women. Don’t be surprised; if you say to me, “I don’t want to hurt your feelings…” I stop you mid-sentence and tell you it’s not my feelings you need to worry about. I’m more analytical than 90% of the people I work with and my assertiveness or passion isn’t any different than my male counterparts.

Even CNN distinguishes between women’s feelings and men’s ego and yet, I know for a fact that talk of ego resonates with me more than feelings. The same ego persists for the guys that have delayed deadlines in projects or sabotaged a colleague, all because their “egos were bruised.” I think it’s easier to refer to a man’s ego than ever refer to his feelings, but let’s be honest- the situation often stems from the same source.

Culture often dictates to people, “just stroke his ego and you’ll get along fine with him.”   We’ll often claim a bruised ego in the safety of male approval, where we brush off a woman who’s dared to claim value for herself. Instead of just asking women to toughen up and tell others to just suffer through a man’s bruised ego, tell everyone to stand down- We’re all in this together.

  1. The Silent Treatment

“Women fail at collaboration.”

I was reading an article on why women fail at collaboration. If I had a dollar for every time I’ve contributed to a conversation among a group of tech guys on a forum or in a slack channel and heard nothing but crickets, well, I’d most likely be here instead of writing this post. Seriously, the same conversation, one of my male peers enters the conversation or says almost the same thing and there are a slew of responses.

Many women mention this issue and we’re all a bit perplexed by it, but what I have gleamed so far is- ½ the folks, (both men and women) are simply observing, trying to figure the woman contributor out and the other ½ is freaked out and doesn’t know how to respond. If this continues for an extended period of time, the woman simply gives up and stops wasting her time.

Women are still told, (not as directly as in the olden days) to not speak unless we’re spoken to.  This often creates a scenario where women end up looking for an invitation to be part of the conversation, yet we don’t offer this and when they do speak up, (I’m one of the mouthy ones) I can confirm that someone will advise her to speak less or to be more conservative. I’ve NEVER heard anyone tell a man to step back when owning a conversation.

Even worse are those hell bent on silencing the woman in the conversation. These guys are commonly “equal-opportunity bullies” and have no doubt, they will go after men in the group they see as a threat, too. The only target identifier these guys need is a willingness to give someone else the benefit of the doubt.

My recommendation- If you’re not engaging the women in the room- start doing so. If a woman asks a question, assume she already knows 90% of the answer, but would like to collaborate. This will eliminate 80% of our favorite hobby- “mansplaining.” </p />
</p></div></div>

    	  	<div class=

Aliases

Here’s a performance problem that came up on OTN recently. The following query (reformatted) takes “ages” to run – how do you address the problem:

SELECT
	COUNT(*) 
FROM
	smp_dbuser2.workflow_step_report
WHERE
	report_ID IN (
		SELECT	report_id
		FROM	smp_dbuser2.workflow_report
		WHERE	trunc(start_time) = '28-Apr-2017'
		AND	user_id = 'nbi_ssc'
	)
;


Various pieces of relevant information were supplied (the workflow_report table holds 1.4M rows the workflow_step_report table holds 740M rows and some indexes were described), but most significantly we were given the execution plan:

--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |     1 |     6 |    10T  (1)|999:59:59 |       |       |
|   1 |  SORT AGGREGATE       |                      |     1 |     6 |            |          |       |       |
|*  2 |   FILTER              |                      |       |       |            |          |       |       |
|   3 |    PARTITION HASH ALL |                      |   731M|  4187M|  5363K  (1)| 17:52:47 |     1 |   128 |
|   4 |     TABLE ACCESS FULL | WORKFLOW_STEP_REPORT |   731M|  4187M|  5363K  (1)| 17:52:47 |     1 |   128 |
|*  5 |    FILTER             |                      |       |       |            |          |       |       |
|   6 |     PARTITION HASH ALL|                      |     2 |    38 | 14161   (1)| 00:02:50 |     1 |    32 |
|*  7 |      TABLE ACCESS FULL| WORKFLOW_REPORT      |     2 |    38 | 14161   (1)| 00:02:50 |     1 |    32 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT 0 FROM "SMP_DBUSER2"."WORKFLOW_REPORT" "WORKFLOW_REPORT" WHERE :B1=:B2
              AND "USER_ID"='nbi_ssc' AND TRUNC(INTERNAL_FUNCTION("START_TIME"))=TO_DATE(' 2017-04-28 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
   5 - filter(:B1=:B2)
   7 - filter("USER_ID"='nbi_ssc' AND TRUNC(INTERNAL_FUNCTION("START_TIME"))=TO_DATE(' 2017-04-28
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

You’ll notice that the optimizer has transformed the IN subquery into an EXISTS subquery – operation 2 is a FILTER operation, and you can see that the filter predicate at operation 2 shows the existence subquery that would be executed.

If you look carefully at the execution plan (all of it), what can you deduce from it ? What, then, should be your next step in dealing with this performance problem ?

Observations

I did emphasise the need to examine ALL of the execution plan – and the important feature appears not in the body of the plan but in the predicate section.

The body tells us that Oracle has executed the query with a FILTER subquery approach, and we can see that the IN subquery has been transformed into an EXISTS subquery. In many cases Oracle could unnest the subquery and turn it into a join (often a semi-join), but it hasn’t in this case and we might wonder why not. Look closely at the text given for the subquery in the filter predicate section:


SELECT  0 
FROM   "SMP_DBUSER2"."WORKFLOW_REPORT" "WORKFLOW_REPORT" 
WHERE  :B1=:B2
AND    "USER_ID"='nbi_ssc' 
AND    TRUNC(INTERNAL_FUNCTION("START_TIME"))=TO_DATE(' 2017-04-28 00:00:00','syyyy-mm-dd hh24:mi:ss')

When an IN subquery is transformed into an EXISTS subquery, then we usually see: “outer.column in (select inner.column …)” turning into a predicate in the existence subquery of the form “inner.column = :B1”, so why do we have “:B1 = :B2” when we expect to see “workflow_report.report_id = :B1” ?

The (obvious, if you know your optimizer) answer is that there is no column report_id in table workflow_report but “column capture” means the optimizer has assumed that report_id in the subquery refers to workflow_step_report.report_id – hence “:B1 = :B2”. The consequence of this strange predicate is that the subquery may execute once for every row in the outer table (though scalar subquery caching may reduce the number of executions) performning a tablescan as it does so.

The correct next step is to check whether this was a simple typing error – the query as it stands is valid but not very sensible, so what was the intention. It turned out that there was a column workflow_report.id, and that was the column that should have been selected in the subquery. (The OP also changed the trunc(start_date) to use a carefully constructed range-based clause – but that wasn’t really terribly important; and several people noted that some efficiency could be gained through suitable indexes – but that was missing the important point.)

Here’s the new query, with execution plan:


SELECT  COUNT(*) 
FROM    smp_dbuser2.workflow_step_report    wsr
WHERE   wsr.report_ID IN (
                SELECT  wr.id
                FROM    smp_dbuser2.workflow_report    wr
                WHERE   wr.start_time >= to_date( '28-Apr-2017','dd-mon-yyyy') 
                and     wr.start_time <  to_date( '28-Apr-2017','dd-mon-yyyy') + 1
                AND     wr.user_id = 'nbi_ssc'
        )
;

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                                |     1 |    31 | 22760   (1)| 00:04:34 |       |       |
|   1 |  SORT AGGREGATE           |                                |     1 |    31 |            |          |       |       |
|   2 |   NESTED LOOPS            |                                | 34458 |  1043K| 22760   (1)| 00:04:34 |       |       |
|   3 |    PARTITION HASH ALL     |                                |    72 |  1800 | 22759   (1)| 00:04:34 |     1 |    32 |
|*  4 |     TABLE ACCESS FULL     | WORKFLOW_REPORT                |    72 |  1800 | 22759   (1)| 00:04:34 |     1 |    32 |
|   5 |    PARTITION HASH ITERATOR|                                |   477 |  2862 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |     INDEX RANGE SCAN      | WORKFLOW_STEP_REPORT_REPORT_ID |   477 |  2862 |     1   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------

The modified query completed in 5 seconds – presumably because there were only a few relevant rows in the workflow_report table and the related workflow_step_report rows were well clustered and accessible through a suitable “foreign key” index (there’s also a hint in the plan costs that the partitioning column for workflow_step_report is the report_id)

The final point to note about the rewritten query is the use of table aliases – both tables have a short alias (wsr and wr), and every column is qualified by its table alias. If this approach had been taken in the original code then the attempt to run it would have resulted in an error like:

ERROR at line 7:
ORA-00904: "WR"."REPORT_ID": invalid identifier

Always give every table (or view) an alias.
Keep aliases short
Qualify every column with its table (or view) alias

Quick tip–identity columns

Lets say I’ve been reading about schema separation, and thus I am going to have a schema which owns all of my objects, which I’ll call APP_OWNER, which will have no connection privilege and a separate schema called APP_ADMIN which will take care of all of the DDL tasks.

Here’s my setup:


SQL> create user app_owner identified by app_owner;
 
User created.
 
SQL> grant
  2    create session,
  3    create any table,
  4    create any sequence,
  5  to app_admin identified by app_admin;
 
Grant succeeded.

I have granted APP_ADMIN the ability to create tables in any schema, and create sequences in any schema. I did the latter, because I know that if I use the “IDENTITY” clause for a column, then behind the scenes I’ll be creating a sequence to populate those ascending values.  So it looks like I am ready to go and create my objects.  Let’s create that first table


SQL> conn app_admin/app_admin
Connected.
 
SQL> create table app_owner.t(pk integer generated always as identity);
create table app_owner.t(pk integer generated always as identity)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Hmmm….that is not what we were expecting. It turns out that to create an identity column in another schema you need more than just CREATE ANY SEQUENCE. You also need SELECT ANY SEQUENCE.


SQL> conn / as sysdba
Connected. 
SQL> grant
  2    create session,
  3    create any table,
  4    create any sequence,
  5    select any sequence
  6  to app_admin identified by app_admin;
 
Grant succeeded.
 
SQL> conn app_admin/app_admin
Connected.
 
SQL> create table app_owner.t(pk integer generated always as identity);
 
Table created.

And there we go Smile

Footnote: If you’ve never seen the syntax “grant
to identified by
” it is a quick shortcut to both create the user account and assign privileges in a single command

RTFM

Imagine you’re fairly new to Oracle and don’t have a lot of background information at your fingertips; then one day someone tells you to read the manual pages for the view dba_free_space. Look carefully at this sentence:

Note that if a data file (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information.

Can you spot the error ? Did you spot the error when you first read the sentence – or did you fill in the gap without noticing what you were doing ?

Let’s demonstrate the accuracy of the statement (simple cut-n-paste from an SQL*Plus session on 12.1.0.2 running in archivelog mode, and with a locally managed tablespace consisting of 4 (oracle managed) files on a filesystem):


SQL> select * from dba_free_space where tablespace_name = 'LOB_TEST';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
LOB_TEST                                4        128   51380224       6272            4
LOB_TEST                                7        128   51380224       6272            7
LOB_TEST                                8        640   47185920       5760            8
LOB_TEST                                9        128   51380224       6272            9

4 rows selected.

SQL> select file#, ts#, name from v$datafile;

     FILE#        TS# NAME
---------- ---------- ----------------------------------------------------------------------
         1          0 /u02/app/oracle/oradata/OR32/datafile/o1_mf_system_cbcysq2o_.dbf
         2          9 /u02/app/oracle/oradata/OR32/datafile/o1_mf_undotbs_d84db0s2_.dbf
         3          1 /u02/app/oracle/oradata/OR32/datafile/o1_mf_sysaux_cbcyrmyd_.dbf
         4         15 /u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpchn57_.dbf
         5          6 /u02/app/oracle/oradata/OR32/datafile/o1_mf_test_8k__cbd120yc_.dbf
         6          4 /u02/app/oracle/oradata/OR32/datafile/o1_mf_users_cbcyv47y_.dbf
         7         15 /u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpchnnq_.dbf
         8         15 /u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpcho47_.dbf
         9         15 /u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpchok1_.dbf

9 rows selected.

SQL> alter database datafile '/u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpchnnq_.dbf' offline;

Database altered.

SQL> select * from dba_free_space where tablespace_name = 'LOB_TEST';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
LOB_TEST                                4        128   51380224       6272            4
LOB_TEST                                8        640   47185920       5760            8
LOB_TEST                                9        128   51380224       6272            9

3 rows selected.

SQL> recover datafile '/u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpchnnq_.dbf';
Media recovery complete.
SQL> alter database datafile '/u02/app/oracle/oradata/OR32/datafile/o1_mf_lob_test_dhpchnnq_.dbf' online;

Database altered.

SQL> select * from dba_free_space where tablespace_name = 'LOB_TEST';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
LOB_TEST                                4        128   51380224       6272            4
LOB_TEST                                7        128   51380224       6272            7
LOB_TEST                                8        640   47185920       5760            8
LOB_TEST                                9        128   51380224       6272            9

4 rows selected.

SQL> spool off

See the bit in the middle where I have “3 rows selected” for the lob_test tablespace: the manual says I “will not see any extent information” – but the only change in the output is the absence of information about the one data file that I’ve put offline.

You may want to argue that “obviously” the statement was only about the data file that was offline – but is that a couple of years experience allowing you to interpret the text ? Some might assume (with a little prior experience and if they hadn’t done the experiment and given the parenthetical reference to “entire tablespace”) that the statement was about the effect on a single tablespace  – and maybe others would criticise them for making unwarranted assumptions.

But maybe you’re a novice and believed what the manual actually said.

It’s a fairly silly example, of course, but the point of this note is that when you tell someone to RTFM remember that they might actually do exactly that and not have the benefit of being able to know (unthinkingly) that the manual is wrong. If you go one step further and tell them to “stop making assumptions and RTFM” then just remember that you probably make a lot of assumptions without realising it when you read the manuals, and maybe it’s your assumptions that lead you to the correct interpretation of the manual.

Footnote:

If you’re feeling in the mood to split hairs, don’t forget that dba_free_space doesn’t usually give you any information about extents when it’s reporting locally managed tablespaces, it tells you about the space in which extents can be created; the one exception (that I know of) is when you have an object in the recyclebin and each extent of that object is listed as free space (see this article and the footnote here).  It’s only for dictionary managed tablespaces that dba_free_space reports extent information – the rows stored in the fet$ table.

 

Oracle 12cR2 Security - Listener Port

I downloaded Oracle 12cR2 from Oracle when it became available in March and installed a legacy SE2 database and also a single PDB multitenant database and started some investigations to discover and look at the new security features added in....[Read More]

Posted by Pete On 01/05/17 At 01:03 PM

12cR2 partial PDB backup

I had a recent question about the following mention from the 12cR2 Multitenant book, about Partial PDB backups:
CapturePArtialPDBBackup.
Here is an example in 12.2 with local undo to illustrate the answer, which may help to understand what is a partial PDB backup.

Of course, since 12cR1 you can backup PDB individually, without the CDB$ROOT, in the same way you can backup only a few tablespaces subset of a CDB. It can be part of your backup strategy, but it is not to be considered as a backup that you can restore elsewhere later. A PDB is not self-consistent without the PDB$ROOT except if is has been closed and unplugged. In 12.1 you cannot restore a partial PDB backup if you don’t have the CDB$ROOT at the same point in time, because the recovery phase will need to rollback the ongoing transactions, and this requires to have the UNDO tablespace recovered at the same point in time.

However, in 12.2 with LOCAL UNDO, the partial PDB backup contains the local UNDO tablespace and then it can be sufficient to do a PDB Point In Time Recovery within the same CDB. And, in this case only, it is not required to have a backup of the root.

Let’s test it. I explicitly delete all backups


Recovery Manager: Release 12.2.0.1.0 - Production on Sun Apr 30 22:11:38 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
 
RMAN>
echo set on
 
RMAN> connect target /
connected to target database: CDB1 (DBID=914521258)
 
RMAN> delete noprompt backup;
 
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
specification does not match any backup in the repository
 
 
RMAN> list backup;
specification does not match any backup in the repository

No backup

I have only one PDB here:


RMAN> report schema;
Report of database schema for database with db_unique_name CDB1A
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 820 SYSTEM YES /u01/oradata/CDB1A/system01.dbf
3 630 SYSAUX NO /u01/oradata/CDB1A/sysaux01.dbf
4 70 UNDOTBS1 YES /u01/oradata/CDB1A/undotbs01.dbf
5 250 PDB$SEED:SYSTEM NO /u01/oradata/CDB1A/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX NO /u01/oradata/CDB1A/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/oradata/CDB1A/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/oradata/CDB1A/pdbseed/undotbs01.dbf
103 250 PDB1:SYSTEM YES /u01/oradata/CDB1A/PDB1/system01.dbf
104 350 PDB1:SYSAUX NO /u01/oradata/CDB1A/PDB1/sysaux01.dbf
105 100 PDB1:UNDOTBS1 YES /u01/oradata/CDB1A/PDB1/undotbs01.dbf
 
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 131 TEMP 32767 /u01/oradata/CDB1A/temp01.dbf
2 64 PDB$SEED:TEMP 32767 /u01/oradata/CDB1A/pdbseed/temp012017-04-08_22-24-09-441-PM.dbf
4 64 PDB1:TEMP 32767 /u01/oradata/CDB1A/PDB1/temp012017-04-08_22-24-09-441-PM.dbf

all datafiles need backup:


RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/oradata/CDB1A/system01.dbf
3 0 /u01/oradata/CDB1A/sysaux01.dbf
4 0 /u01/oradata/CDB1A/undotbs01.dbf
5 0 /u01/oradata/CDB1A/pdbseed/system01.dbf
6 0 /u01/oradata/CDB1A/pdbseed/sysaux01.dbf
7 0 /u01/oradata/CDB1A/users01.dbf
8 0 /u01/oradata/CDB1A/pdbseed/undotbs01.dbf
103 0 /u01/oradata/CDB1A/PDB1/system01.dbf
104 0 /u01/oradata/CDB1A/PDB1/sysaux01.dbf
105 0 /u01/oradata/CDB1A/PDB1/undotbs01.dbf

Partial backup not including the root

I backup only the pluggable database PDB1


RMAN> backup pluggable database PDB1;
Starting backup at 30-APR-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00104 name=/u01/oradata/CDB1A/PDB1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-17
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00103 name=/u01/oradata/CDB1A/PDB1/system01.dbf
channel ORA_DISK_2: starting piece 1 at 30-APR-17
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00105 name=/u01/oradata/CDB1A/PDB1/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 30-APR-17
channel ORA_DISK_1: finished piece 1 at 30-APR-17
piece handle=/u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk827s_.bkp tag=TAG20170430T221146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_3: finished piece 1 at 30-APR-17
piece handle=/u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk83go_.bkp tag=TAG20170430T221146 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 30-APR-17
piece handle=/u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk830z_.bkp tag=TAG20170430T221146 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 30-APR-17
 
Starting Control File and SPFILE Autobackup at 30-APR-17
piece handle=/u01/fast_recovery_area/CDB1A/autobackup/2017_04_30/o1_mf_s_942703909_djdk85m1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-APR-17

Here is the proof that only PDB1 has a backup, the CDB$ROOT has no backup:


RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/oradata/CDB1A/system01.dbf
3 0 /u01/oradata/CDB1A/sysaux01.dbf
4 0 /u01/oradata/CDB1A/undotbs01.dbf
5 0 /u01/oradata/CDB1A/pdbseed/system01.dbf
6 0 /u01/oradata/CDB1A/pdbseed/sysaux01.dbf
7 0 /u01/oradata/CDB1A/users01.dbf
8 0 /u01/oradata/CDB1A/pdbseed/undotbs01.dbf

Restore the PDB

I will do PDB Point In Time Recovery, using a restore point


RMAN> create restore point RP;
Statement processed
 
RMAN> alter pluggable database PDB1 close;
Statement processed
 

Here is the restore


RMAN> restore pluggable database PDB1 until restore point RP;
Starting restore at 30-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=149 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=268 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=398 device type=DISK
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00104 to /u01/oradata/CDB1A/PDB1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk827s_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00105 to /u01/oradata/CDB1A/PDB1/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk83go_.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00103 to /u01/oradata/CDB1A/PDB1/system01.dbf
channel ORA_DISK_3: reading from backup piece /u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk830z_.bkp
channel ORA_DISK_2: piece handle=/u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk83go_.bkp tag=TAG20170430T221146
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
channel ORA_DISK_3: piece handle=/u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk830z_.bkp tag=TAG20170430T221146
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: piece handle=/u01/fast_recovery_area/CDB1A/4E68DF57035A648FE053684EA8C01C78/backupset/2017_04_30/o1_mf_nnndf_TAG20170430T221146_djdk827s_.bkp tag=TAG20170430T221146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 30-APR-17

and the recover


RMAN> recover pluggable database PDB1 until restore point RP;
Starting recover at 30-APR-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Finished recover at 30-APR-17

Fimnally, I open resetlogs


RMAN> alter pluggable database PDB1 open resetlogs;
Statement processed

Thanks to LOCAL UNDO there is no need to restore the CDB$ROOT into an auxiliary instance, as it was the case for PDBPITR in 12.1 and then we can do PDBPITR without a backup of the root.

So what?

In theory, and as demonstrated above, including CDB$ROOT into a partial PDB backup is not mandatory in 12cR2 in local undo mode. However, keep in mind that this is for academic purpose only, not for real-life production. For short-term point in time, you will not use backups but flashback. For long-term restore, then you may have different reasons to restore the PDB elsewhere with its CDB$ROOT at the same point in time: some common objects (users, roles, directories, etc) may have changed. And anyway, your backup strategy should be at CDB level.

 

Cet article 12cR2 partial PDB backup est apparu en premier sur Blog dbi services.