1

Topic: To create a test DB from standby?

To create a test DB from  standby?
Oracle 10.2
In the documentation of the such did not read, thought that will be easy, on the contrary!
On  once a week is fulfilled incremental  lelel_0, in remaining days lelel_1.
To me  incomplete recovery on test basis for a while  LEVEL_0.

wrote:

RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
143398 B 0 A DISK 2017-09-01 11:22:59 AM 289 1 NO BKUP_LEVEL_0
143399 B 0 A DISK 2017-09-01 1:06:02 PM 284 1 NO BKUP_LEVEL_0
143400 B 0 A DISK 2017-09-01 2:53:04 PM 301 1 NO BKUP_LEVEL_0
143401 B 0 A DISK 2017-09-01 4:42:35 PM 278 1 NO BKUP_LEVEL_0
143403 B A A DISK 2017-09-01 5:31:43 PM 59 1 NO ARCH_BKUP
RMAN> list backupset 143403;
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
143403 58.97G DISK 0:23:00 AM 2017-09-01 5:31:43 PM
List of Archived Logs in backup set 143403
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 506856 270973234317 2017-08-31 7:00:42 PM 270973913778 2017-08-31 7:13:08 PM
1 506857 270973913778 2017-08-31 7:13:08 PM 270974286716 2017-08-31 7:30:00 PM
...
1 506916 271169803396 2017-09-01 3:01:43 PM 271170193395 2017-09-01 3:10:22 PM
1 506917 271170193395 2017-09-01 3:10:22 PM 271170814781 2017-09-01 4:00:34 PM
1 506918 271170814781 2017-09-01 4:00:34 PM 271171294074 2017-09-01 4:31:06 PM
506918 - last No   LEvEL_0
Or to look on  level_0 protocol, what last No .
I will set UNTIL SEQUENCE 506919 =506918+1

On a test server created an init-file without parameters for Data Guard.
Copied  the verifier from the server-standby from $ORACLE_HOME/dbs/snapcf_xxx.f on a test server.
to a test server a disk with rman-bekapami .

wrote:

rman target /
RMAN> set dbid 1502262417;
executing command: SET DBID
run {
startup nomount
restore controlfile from ' /u01/oracle/product/10gr2/dbs/snapcf_xxx.f ';
alter database mount;
set UNTIL sequence 506919;
restore database;
recover database;
alter database open; is I got excited, it was necessary to me: alter database open read only;
}

506919 = last  from  level_0 506918 + 1
The protocol:

wrote:

Oracle instance started
Total System Global Area 16106127360 bytes
Fixed Size 2112088 bytes
Variable Size 1241515432 bytes
Database Buffers 14847836160 bytes
Redo Buffers 14663680 bytes
Starting restore at 2017-09-05 11:52:59 AM
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=4392 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/uxx/oracle/oradata/xxx/control01.ctl
output filename=/uxx3/oracle/oradata/xxx/control02.ctl
Finished restore at 2017-09-05 11:53:06 AM
database mounted
released channel: ORA_DISK_1
executing command: SET until clause
Starting restore at 2017-09-05 11:53:12 AM
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=4392 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile (s) to restore from backup set
...
archive log filename=/uxx/oracle/oradata/xxx/arch1_506917_560367377.dbf thread=1 sequence=506917
archive log filename=/uxx/oracle/oradata/xxx/arch1_506918_560367377.dbf thread=1 sequence=506918
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/uxx/oracle/oradata/xxx/system01.dbf'
media recovery complete, elapsed time: 3:49:50 AM
Finished recover at 2017-09-06 3:31:51 AM
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/06/2017 3:31:52 AM
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/uxx/oracle/oradata/xxx/system01.dbf'
RMAN>

ORA-01152: file 1 was not restored from a sufficiently old backup
-- It is necessary to check up presence on a disk redo!!!
On a disk they are not present, and in the verifier they are, and should be on standby.
That formed redo launched Redo Apply:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Redo on a disk formed, standby-broad gulls - are not present, but they on  a DB and are not necessary.
To disconnect Redo Apply:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
sqlplus / as sysdba
SYS> select name, database_role, switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------------------- ------------------------------------------------ ------------------------------------------------------------
SUNSET PHYSICAL STANDBY SESSIONS ACTIVE
SQL> alter database open read only;
Does not open, I did not write down an error, but like:
ORA-16139: media recovery required
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
What do I do not so?

2

Re: To create a test DB from standby?

Basis in a mismatched type. It is necessary to roll broad gulls for its coercion in the coordinated state. (At present database files have a state on  SCN than last SCN in a broad gull 506918).

3

Re: To create a test DB from standby?

On StandBy to basis before the termination  changes from dens above 506918 have been applied. Why these broad gulls did not get in  a question another (it is necessary to look as is adjusted communication between bases and a script ).

4

Re: To create a test DB from standby?

Taciturn12 wrote:

Basis in a mismatched type. It is necessary to roll broad gulls for its coercion in the coordinated state. (At present database files have a state on  SCN than last SCN in a broad gull 506918).

From an error and so it is clear that a DB in a mismatched state.
What answer, such is a question:
How many it is necessary to roll?

5

Re: To create a test DB from standby?

Taciturn12 wrote:

On StandBy to basis before the termination  changes from dens above 506918 have been applied. Why these broad gulls did not get in  a question another (it is necessary to look as is adjusted communication between bases and a script ).

And and it is exact, looked on  in the first post more attentively:

wrote:

RMAN> list backup summary;
RMAN> list backupset 143403;

BKUP_LEVEL_0 Ended at 4:42:35 PM
And  506918 it was generated 4:31:06 PM
I considered-considered it earlier and did not note.

wrote:

### run backup level 0
show all;
RUN {
BACKUP INCREMENTAL LEVEL 0 AS backupset DATABASE tag ' BKUP_LEVEL_0 ';
BACKUP AS backupset ARCHIVELOG ALL skip inaccessible DELETE ALL INPUT tag ' ARCH_BKUP ';
DELETE OBSOLETE;
}

I understood, why so happens.
This script if it to fulfill on  it at the command of BACKUP ARCHIVELOG the automatic machine fulfills
current log archived
And on  it does not happen.

Whereas to define till what moment it is necessary  ?

As in alert.log on a test server the message about gap was produced:

wrote:

Wed Sep 6 9:50:57 AM 2017
Clearing online redo logfile 12 complete
Media Recovery Waiting for thread 1 sequence 506919
Fetching gap sequence in thread 1, gap sequence 506919-507018
FAL [client]: Error fetching gap sequence, no FAL server specified

I now do here that:
RMAN> catalog start with '/mnt/nfs/sunray ' noprompt;
...
File Name:/mnt/nfs/sunray/fjsdocgg_26_1
File Name:/mnt/nfs/sunray/fjsdocgg_27_1
File Name:/mnt/nfs/sunray/fjsdocgg_28_1
File Name:/mnt/nfs/sunray/fjsdocgg_29_1
File Name:/mnt/nfs/sunray/fjsdocgg_30_1
File Name:/mnt/nfs/sunray/fjsdocgg_31_1
File Name:/mnt/nfs/sunray/c-1502262417-20170906-00
cataloging files...
...
File Name:/mnt/nfs/sunray/fjsdocgg_26_1
File Name:/mnt/nfs/sunray/fjsdocgg_27_1
File Name:/mnt/nfs/sunray/fjsdocgg_28_1
File Name:/mnt/nfs/sunray/fjsdocgg_29_1
File Name:/mnt/nfs/sunray/fjsdocgg_30_1
File Name:/mnt/nfs/sunray/fjsdocgg_31_1
File Name:/mnt/nfs/sunray/c-1502262417-20170906-00
RMAN>
RMAN> recover database UNTIL SEQUENCE 507019;

6

Re: To create a test DB from standby?

To unwinding I think that one if are transferred redologs that clearly why it does not get in  as still  is not.
The first SCN a broad gull registers in , and here with leaking SCN files I will not prompt. In v$datafile SCN last checkpoint only is. It is possible to try to check up it.

7

Re: To create a test DB from standby?

I think it is necessary so:
How to define UNTIL:
On standby:
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
143398 B 0 A DISK 2017-09-01 11:22:59 AM 289 1 NO BKUP_LEVEL_0
143399 B 0 A DISK 2017-09-01 1:06:02 PM 284 1 NO BKUP_LEVEL_0
143400 B 0 A DISK 2017-09-01 2:53:04 PM 301 1 NO BKUP_LEVEL_0
143401 B 0 A DISK 2017-09-01 4:42:35 PM 278 1 NO BKUP_LEVEL_0
143403 B A A DISK 2017-09-01 5:31:43 PM 59 1 NO ARCH_BKUP
RMAN> list backupset 143401;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
143401 Incr 0 277.65G DISK 1:49:27 AM 2017-09-01 4:42:35 PM
List of Datafiles in backup set 143401
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 0 Incr 271167126506 2017-09-01 2:05:00 PM/uxx/oracle/oradata/xxx/system02.dbf
...
549 0 Incr 271167126506 2017-09-01 2:05:00 PM /uxx/oracle/oradata/xxx/center_vs_data_02.dbf
...
set UNTIL SCN 271167126507; = 271167126506 Ckp SCN +1

wrote:

RMAN> set dbid 1502262417;
run {
startup nomount
restore controlfile from ' /u01/oracle/product/10gr2/dbs/snapcf_sunset.f ';
alter database mount;
set UNTIL SCN 271167126507;
restore database;
recover database;
}

And the following batch  appears on  a disk in a day (in  a case)
If recovery of a test DB to do procedure regularly in crontab' it it is not convenient.
Easier: to mount a disk with   and to do incomplete  by a test DB of them.

8

Re: To create a test DB from standby?

And what generally sense  StandBy if is  the main? I understand to throw out  on StandBy if it strongly loads system and hinders operation, but to do both that and another?

9

Re: To create a test DB from standby?

And if
1) recover database until cancel
2) further to press ENTER some times, does not start while to swear
3) and further to enter a command CANCEL

10

Re: To create a test DB from standby?

wrote:

Whereas to define till what moment it is necessary  ?

As at a variant to look there where became

select max (sequence#)
from v$archived_log
where first_time <(select max (completion_time)
from v$backup_set_details
where incremental_level=0)

11

Re: To create a test DB from standby?

Aliona wrote:

From an error and so it is clear that a DB in a mismatched state.

Where it is visible? It is visible only that the verifier fresh, than .
On  the normal phenomenon if not to roll broad gulls to scn the verifier
That shows select distinct CHECKPOINT_TIME from v$datafile_header;?

12

Re: To create a test DB from standby?

Error ORA-01152 unambiguously says that the basis in a mismatched state, advancing SCN of the verifier in comparison with data files does not cause such error and simply writes that the basis needs to be opened not command OPEN, and OPEN RESETLOGS.
Apropos

select distinct CHECKPOINT_TIME from v$datafile_header;

now checked up that on real basis of value from this representation differ from

select current_scn from v$database;

, and on StandBy coincide. So at creation  with StandBy, most likely, it is possible to use this value for search of the necessary dens.

13

Re: To create a test DB from standby?

Taciturn12 wrote:

I understand to throw out  on StandBy if it strongly loads system and hinders operation, but to do both that and another?

That's it, strongly load system, therefore   once a week on Saturdays

14

Re: To create a test DB from standby?

Taciturn12 wrote:

Error ORA-01152 unambiguously says that the basis in a mismatched state, advancing SCN of the verifier in comparison with data files does not cause such error and simply writes that the basis needs to be opened not command OPEN, and OPEN RESETLOGS.
Apropos

select distinct CHECKPOINT_TIME from v$datafile_header;

Now checked up that on real basis of value from this representation differ from

select current_scn from v$database;

, And on StandBy coincide. So at creation  with StandBy, most likely, it is possible to use this value for search of the necessary dens.

Yes  on ! If it is necessary to open and it is not important, for what moment of time of a DB - the main thing that  were in the coordinated state i.e. if is not present RO files -   one record. Then safely we recreate the verifier and open resetlogs.
Once again - if on  the verifier fresh , but they are coordinated () - it does not allow to open basis! .  I do not remember, like without problems.

15

Re: To create a test DB from standby?

Q.Tarantino;
You do not open anything anywhere without redo/resetlogs if v$datafile.checkpoint_change#! = v$datafile_header.checkpoint_change#

16

Re: To create a test DB from standby?

oracloud wrote:

Q.Tarantino;
You do not open anything anywhere without redo/resetlogs if v$datafile.checkpoint_change#! = v$datafile_header.checkpoint_change#

Here spoke about  the verifier, if that...

17

Re: To create a test DB from standby?

Q.Tarantino wrote:

it is passed...
Yes  on ! If it is necessary to open and it is not important, for what moment of time of a DB - the main thing that  were in the coordinated state i.e. if is not present RO files -   one record. Then safely we recreate the verifier and open resetlogs.
Once again - if on  the verifier fresh , but they are coordinated () - it does not allow to open basis! .  I do not remember, like without problems.

v$datafile_header. CHECKPOINT_TIME no any relation to coordinated a state of a DB has and it is exact not an index that the DB can be opened.

18

Re: To create a test DB from standby?

Quite right, but I said that on real open basis this value considerably lags behind from leaking SCN in basis, but on StandBy coincided. I do not know as precisely StandBy rolls broad gulls, whether he writes after record of each unit to title or not, therefore I specified that "most likely" as judging by messages a starter topic (and not only in this subject) I drew an output that it can check up particulars itself and the accurate command set and only idea is not required to it. Well and plus I did not say that v$datafile_header. CHECKPOINT_TIME concerns the coordinated state, and only can help with determination of number of a broad gull to which a minimum it is necessary to roll changes.

19

Re: To create a test DB from standby?

Taciturn12 wrote:

I do not know as precisely StandBy rolls broad gulls, whether he writes after record of each unit to title or not.

Mechanisms  changes both on  and on  are absolutely identical and precisely do not write to title after record of each unit.

Taciturn12 wrote:

Well and plus I did not say that v$datafile_header. CHECKPOINT_TIME concerns the coordinated state, and only can help with determination of number of a broad gull to which a minimum it is necessary to roll changes.

It was Tarantino's answer, instead of you.

20

Re: To create a test DB from standby?

Let's glance on a metalink, the note 1354256.1 How to quickly check that Database is consistent after incomplete recovery
The only thing still should be checked on Fuzziness...
In remaining, just the request from datafile_header also is used.
So in vain on me were outlined. Then already and   on  smile

21

Re: To create a test DB from standby?

Q.Tarantino;
Anybody also was outlined, all is truly written, if all  are coordinated, and  ahead - resetlogs helps.
> And only can help with determination of number of a broad gull to which a minimum it is necessary to roll changes
It is called stop scn in  title .

22

Re: To create a test DB from standby?

oracloud wrote:

anybody also was outlined, all is truly written, if all  are coordinated, and  ahead - resetlogs helps.

Not the fact. If   was it is taken - that is not present. . It is checked up.

23

Re: To create a test DB from standby?

Aliona wrote:

Redo on a disk formed, standby-broad gulls - are not present, but they on  a DB and are not necessary.

And here at me because of it a problem:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
*
ERROR at line 1:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: ' /oradb/oradata/ODBM/standby02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Prompt please what to do?

24

Re: To create a test DB from standby?

Jakupov wrote:

it is passed...
And here at me because of it a problem:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
*
ERROR at line 1:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: ' /oradb/oradata/ODBM/standby02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Prompt please what to do?

Open a new subject and describe explicitly your problem. This subject does not approach you, at you other case.

25

Re: To create a test DB from standby?

Aliona;
Well so you from opened basis?