El blog de Nelson Javier Martinez Acosta: 2020

lunes, 14 de diciembre de 2020

RMAN RESTORE TABLE TPITR

# SCN

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

RECOVER TABLE TEST.T1
  UNTIL SEQUENCE 200
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

# TIME
RECOVER TABLE TEST.T1
  UNTIL TIME "TO_DATE('2020-12-14 19:00', 'YYYY-MM-DD:HH24:MI:SS')"
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

TECNOLOGIA TPITR

LABORATORIO RESTORE TABLE UNTIL TIME

SQL> !date

Mon Dec 14 19:13:47 -05 2020

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

6003465

SQL> connect hr/hr;

Connected.

SQL>

SQL> update hr.lolito set salary=0, email='NELSONJAVIER63@GMAIL.COM';

107 rows updated.

SQL> commit;

Commit complete.

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 2020-12-14:19:23:07

current log archived

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/14/2020 19:23:09

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "HR"."LOLITO" already exists

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 2020-12-14:19:23:49

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='pfuh'

initialization parameters used for automatic instance:

db_name=ACME

db_unique_name=pfuh_pitr_ACME

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/u01/backup/

log_archive_dest_1='location=/u01/backup/'

#No auxiliary parameter file used

starting up automatic instance ACME

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes

Variable Size 281019648 bytes

Database Buffers 780140544 bytes

Redo Buffers 5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 2020-12-14:19:24:01

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711 tag=TAG20201214T184509

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06

output file name=/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl

Finished restore at 2020-12-14:19:24:07

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 3 to new;

set newname for clone tempfile 1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/ACME/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2020-12-14:19:24:13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ACME/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ACME/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ACME/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 2020-12-14:19:24:59

datafile 1 switched to datafile copy

input datafile copy RECID=11 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=13 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 4 online";

sql clone "alter database datafile 3 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 3 online

Starting recover at 2020-12-14:19:24:59

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705

archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15

media recovery complete, elapsed time: 00:00:09

Finished recover at 2020-12-14:19:25:11

sql statement: alter database open read only

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes

Variable Size 285213952 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes

Variable Size 285213952 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 6 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2020-12-14:19:25:46

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=27 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2020-12-14:19:25:49

datafile 6 switched to datafile copy

input datafile copy RECID=15 STAMP=1059161149 file name=/u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile 6 online";

# recover and open resetlogs

recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 6 online

Starting recover at 2020-12-14:19:25:49

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705

archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15

media recovery complete, elapsed time: 00:00:00

Finished recover at 2020-12-14:19:25:51

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_pfuh_nDAC":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 64 KB

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock)

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock)

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

EXPDP> . . exported "HR"."LOLITO" 17.07 KB 107 rows

EXPDP> Master table "SYS"."TSPITR_EXP_pfuh_nDAC" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_pfuh_nDAC is:

EXPDP> /u01/backup/tspitr_pfuh_82509.dmp

EXPDP> Job "SYS"."TSPITR_EXP_pfuh_nDAC" completed with 2 error(s) at Mon Dec 14 19:27:12 2020 elapsed 0 00:00:52

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_pfuh_pkrj" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_pfuh_pkrj":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "HR"."LOLITO" 17.07 KB 107 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_pfuh_pkrj" successfully completed at Mon Dec 14 19:28:00 2020 elapsed 0 00:00:30

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_temp_hxj0m83n_.tmp deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_3_hxj0nj8c_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_2_hxj0nhnq_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_1_hxj0nh58_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf deleted

auxiliary instance file /u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl deleted

auxiliary instance file tspitr_pfuh_82509.dmp deleted

Finished recover at 2020-12-14:19:28:06

RMAN>

TECNOLOGIA TPITR

LABORATORIO RESTORE TABLE SCN

SQL> create table hr.lolito as (select * from hr.employees);

RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 14-DEC-20

using target database control file instead of recovery catalog

current log archived

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/14/2020 18:58:42

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "HR"."LOLITO" already exists

RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 14-DEC-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=50 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='haer'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=haer_pitr_ORCL

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/u01/backup/

log_archive_dest_1='location=/u01/backup/'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes

Variable Size 281019648 bytes

Database Buffers 780140544 bytes

Redo Buffers 5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until scn 6223707;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-DEC-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp tag=TAG20201214T184254

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl

Finished restore at 14-DEC-20

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until scn 6223707;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 3 to new;

set newname for clone tempfile 1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 5, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 14-DEC-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 14-DEC-20

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=10 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 6223707;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 5 online";

sql clone "alter database datafile 4 online";

sql clone "alter database datafile 3 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 5 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 3 online

Starting recover at 14-DEC-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12

media recovery complete, elapsed time: 00:00:00

Finished recover at 14-DEC-20

sql statement: alter database open read only

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes

Variable Size 285213952 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes

Variable Size 285213952 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until scn 6223707;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 6 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 14-DEC-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 14-DEC-20

datafile 6 switched to datafile copy

input datafile copy RECID=12 STAMP=1059159701 file name=/u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 6223707;

# online the datafiles restored or switched

sql clone "alter database datafile 6 online";

# recover and open resetlogs

recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 6 online

Starting recover at 14-DEC-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12

media recovery complete, elapsed time: 00:00:00

Finished recover at 14-DEC-20

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_haer_peEB":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 64 KB

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock)

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock)

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

EXPDP> . . exported "HR"."LOLITO" 17.07 KB 107 rows

EXPDP> Master table "SYS"."TSPITR_EXP_haer_peEB" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_haer_peEB is:

EXPDP> /u01/backup/tspitr_haer_59331.dmp

EXPDP> Job "SYS"."TSPITR_EXP_haer_peEB" completed with 2 error(s) at Mon Dec 14 19:03:12 2020 elapsed 0 00:00:50

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_haer_pAbb" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_haer_pAbb":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "HR"."LOLITO" 17.07 KB 107 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_haer_pAbb" successfully completed at Mon Dec 14 19:03:56 2020 elapsed 0 00:00:30

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_temp_hxhz666l_.tmp deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_3_hxhz774g_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_2_hxhz76pt_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_1_hxhz76cr_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf deleted

auxiliary instance file /u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl deleted

auxiliary instance file tspitr_haer_59331.dmp deleted

Finished recover at 14-DEC-20

RMAN>

viernes, 11 de diciembre de 2020

BACKUP INCREMENTAL UNTIL TIME

RMAN> backup incremental level 1 format '/u01/backup/dev_%t_%s_level1.dbk' database plus archivelog delete input until time "to_date(to_char(trunc(sysdate) || ':13:00:00'), 'DD-MON-YY:hh24:mi:ss')" ;

FLASHBACK DATABASE TO RESTORE POINT GUARANTEE.

LABORATORIO.

ACTIVAR FLASHBACK DATABASE

CREAR PUNTO DE RESTAURACION

SQL> create restore point before_upgrade guarantee flashback database

SQL> /

Restore point created.

SQL> update hr.prueba set salary=0;

107 rows updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 658509704 bytes

Database Buffers 171966464 bytes

Redo Buffers 2334720 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@localhost ~]$

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPGRADE';

Starting flashback at 11-DEC-20

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished flashback at 11-DEC-20

RMAN> alter database open resetlogs;

Statement processed

RMAN>

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN RSP Time Type Time Name

---------------- --------- ---------- --------- ----

6032442 GUARANTEED 11-DEC-20 BEFORE_UPGRADE

RMAN> drop restore point before_upgrade;

Statement processed

RMAN>

jueves, 10 de diciembre de 2020

FLASHBACK DATABASE

LABORATORIO.

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 14 21:14:29 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 658509704 bytes

Database Buffers 171966464 bytes

Redo Buffers 2334720 bytes

Database mounted.

SQL> flashback database to timestamp TO_TIMESTAMP('2021-05-14 21:11:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>

SQL> alter system set undo_retention=1800 scope=both;

System altered.

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 1800

undo_tablespace string UNDOTBS1

ESTO SE DEBE REALIZAR CON LAS INSTANCIA EN MODO OPEN

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME RETENTION

------------------------------ -----------

SYSTEM NOT APPLY

SYSAUX NOT APPLY

UNDOTBS1 NOGUARANTEE

TEMP NOT APPLY

USERS NOT APPLY

EXAMPLE NOT APPLY

TBS_1 NOT APPLY

7 rows selected.

SQL>

SQL> alter tablespace undotbs1 retention guarantee;

SQL> shutdown immediate;
SQL> startup mount;
ORACLE instance started.

CONFIGURACION EN MINUTOS

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=1440;
System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G;
System altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open;
Database altered.

SQL>alter system set recyclebin=on scope=both;

SQL> select open_mode,log_mode,flashback_on ,current_scn from v$database;

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>

SQL> start mount;
SP2-0310: no se ha podido abrir el archivo "mount.sql"
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>

SQL> flashback database to scn 70812;
flashback database to scn 70812
*
ERROR en linea 1:
ORA-38726: El registro de flashback de la base de datos no esta activado.

SQL>

LABORATORIO FLASHBACK DATABASE

ACTIVACION FLASHBACK

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:39:51 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>
SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 2
Siguiente secuencia de log para archivar 4
Secuencia de log actual 4
SQL>
SQL> alter database flashback on;

Base de datos modificada.

SQL> show parameter db_flashback;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
0 YES

SQL> alter database flashback off;

Base de datos modificada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
0 NO

SQL> alter database flashback on;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 09/07/10 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> alter database open;

Base de datos modificada.

EMPEZAMOS EL LABORATORIO FLASHBACK DATABASE

SQL> connect hr/hr;
Conectado.
SQL>
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
COPIA_REGIONS TABLE

SQL> drop table COPIA_REGIONS;

Tabla borrada.

SQL>

SQL> create table COPIA_EMPLOYEES as (select * from employees);

Tabla creada.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
BIN$ivm+7nDFpazgQAB/AQALUg==$0 TABLE
COPIA_EMPLOYEES TABLE

SQL> delete from COPIA_EMPLOYEES where 1=1;

107 filas suprimidas.

SQL> commit;

Confirmacion terminada.

SQL>

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:55:59 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CURRENT ACTUAL

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
708510

SQL> desc hr.regions;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

SQL> insert into hr.regions values(6,'COLOMBIA');

1 fila creada.

SQL> insert into hr.regions values(7,'PERU');

1 fila creada.

SQL> COMMIT;

Confirmacion terminada.

SQL> select * from hr.regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
6 COLOMBIA
7 PERU

6 filas seleccionadas.

COMO HAGO PARA DEVOLVERME A UN ESTADO ANTERIOR DE LA BASE DE DATOS ANTES DEL DELETE

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 09/07/10 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0

VALIDAR EL RESULTADO DE ESTE QUERY

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

SQL> flashback database to scn 708358;

Flashback terminado.

SQL> alter database open;
alter database open
*
ERROR en linea 1:
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base
de datos

SQL> alter database open resetlogs;

Base de datos modificada.

SQL>

VER NUEVA INCARNATION GENERADA EN LA INSTANCIA

[oracle@oracle11g ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jul 9 15:02:27 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 584972 06-JUL-10
2 2 ACME 1908099333 CURRENT 654164 07-JUL-10

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 584972 06-JUL-10
2 2 ACME 1908099333 PARENT 654164 07-JUL-10
3 3 ACME 1908099333 CURRENT 708361 09-JUL-10

RMAN>

SI TOCA DEVOLVER AL ULTIMO RESETLOGS.

RMAN> FLASHBACK DATASBE TO BEFORE RESETLOGS;

VALIDAR LA DATA A VER SI QUEDO BIEN

SQL> connect hr/hr;
Conectado.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
COPIA_REGIONS TABLE

9 filas seleccionadas.

SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL>

NOTA: REALIZAR BACKUP VIA RMAN FULL DATABASE ARCHIVELOG

5 LABORATORIO FLASHBACK QUERY

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:23:03 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect hr/hr;
Conectado.
SQL> CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER);
2 3 4
Tabla creada.

SQL> INSERT INTO emp VALUES (111, 'Mike', 555);

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> CREATE TABLE dept
(deptno NUMBER,
deptname VARCHAR2(32));
2 3
Tabla creada.

SQL> INSERT INTO dept VALUES (10, 'Accounting');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> UPDATE emp SET salary = salary + 100 WHERE empno = 111;

1 fila actualizada.

SQL> COMMIT;

Confirmacion terminada.

SQL> select * from emp;

EMPNO EMPNAME SALARY
---------- ---------------- ----------
111 Mike 655

SQL>
SQL> INSERT INTO dept VALUES (20, 'Finance');

1 fila creada.

SQL> DELETE FROM emp WHERE empno = 111;

1 fila suprimida.

SQL> commit;

Confirmacion terminada.

SQL>

REALIZAN UNA NUEVA TRANSACCION PARA EL CODIGO 111

a new transaction reinserts employee id 111 with a new employee name into the emp table.

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:30:54 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary FROM hr.emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
2 3 4 5
XID START_SCN END_SCN O EMPNAME SALARY
---------------- ---------- ---------- - ---------------- ----------
0800230006010000 710161 I Tom 927
0600220049010000 710077 D Mike 655
0500290027010000 710057 710077 U Mike 655
04000800DB000000 709971 710057 I Mike 555

SQL>

VALIDAMOS EL undo_sql

SELECT xid, start_scn, commit_scn,
operation, logon_user,
undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('0600220049010000')
/

XID START_SCN COMMIT_SCN OPERATION LOGON_USER
---------------- ---------- ---------- -------------------------------- ------------------------------
UNDO_SQL
0600220049010000 710065 710077 DELETE HR
insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

0600220049010000 710065 710077 INSERT HR
delete from "HR"."DEPT" where ROWID = 'AAAM7pAAEAAAAGcAAB';

0600220049010000 710065 710077 BEGIN HR

•To obtain an SCN to use later with a flashback feature, use

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.

6 MANEJO PAPELERA RECICLAJE
SELECT * FROM RECYCLEBIN;

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

VISTAS

USER_RECYCLEBIN
This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN
This view gives administrators visibility to all dropped objects in the recycle bin

PURGE TABLE nombre_tabla;
Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.

PURGE INDEX nombre_índice;
Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.

PURGE RECYCLEBIN;
Elimina todos los objetos (del usuario que lanza la orden) de la papelera.

PURGE DBA_RECYCLEBIN;
Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.

PURGE TABLESPACE nombre_tablespace;
Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.

PURGE TABLESPACE nombre_tablespace USERS nombre_usuario;
Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.

SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------PENDIENTES BIN$WJ4MAU6RU7XgRAAUT2gJUg==$0 TABLE 2008-10-06:17:17:21PRUEBA BIN$WMDKHy9/HiHgRAAUT2gJUg==$0 TABLE 2008-10-08:10:44:19PRUEBA BIN$WL+UR7qDG8/gRAAUT2gJUg==$0 TABLE 2008-10-08:09:17:41PRUEBA BIN$WL83vpCuGmTgRAAUT2gJUg==$0 TABLE 2008-10-08:08:51:49

SQL> flashback table pendientes to before drop;
Flashback complete.
SQL>

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

Tmabien se puede realizar un query con el nombre de la tabla de esta forma.

example: SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

PURGE TABLE BIN$jsleilx392mk2=293$0;

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Limpia toda la papelera reciclaje

PURGE RECYCLEBIN;

CONSULTAS FLASHBACK

SQL> select estimated_flashback_size,flashback_size from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE

------------------------ --------------

11039744 104857600

SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_F

-------------------- --------

5799514 11/12/20

SQL>select * from v$flashback_database_stat;

lunes, 30 de noviembre de 2020

BACKUP DISKGROUP ASMCMD

Example

The first example shows the use of the backup command when run without the disk group option. This example backs up all the mounted disk groups and creates the backup image in the /scratch/backup/alldgs20100422 file. The second example creates a backup of the data disk group. The metadata backup that this example creates is saved in the /scratch/backup/data20100422 file.

Example 12-40 Using the ASMCMD md_backup command

ASMCMD [+] > md_backup /u01/data.bck
Disk group metadata to be backed up: DATA
Disk group metadata to be backed up: FRA
Current alias directory path: ORCL/ONLINELOG
Current alias directory path: ORCL/PARAMETERFILE
Current alias directory path: ORCL
Current alias directory path: ASM
Current alias directory path: ORCL/DATAFILE
Current alias directory path: ORCL/CONTROLFILE
Current alias directory path: ASM/ASMPARAMETERFILE
Current alias directory path: ORCL/TEMPFILE
Current alias directory path: ORCL/ARCHIVELOG/2010_04_20
Current alias directory path: ORCL
Current alias directory path: ORCL/BACKUPSET/2010_04_21
Current alias directory path: ORCL/ARCHIVELOG/2010_04_19
Current alias directory path: ORCL/BACKUPSET/2010_04_22
Current alias directory path: ORCL/ONLINELOG
Current alias directory path: ORCL/BACKUPSET/2010_04_20
Current alias directory path: ORCL/ARCHIVELOG
Current alias directory path: ORCL/BACKUPSET
Current alias directory path: ORCL/ARCHIVELOG/2010_04_22
Current alias directory path: ORCL/DATAFILE
Current alias directory path: ORCL/CONTROLFILE
Current alias directory path: ORCL/ARCHIVELOG/2010_04_21

ASMCMD [+] > md_backup /u01/backup/ -G data
Disk group metadata to be backed up: DATA
Current alias directory path: ORCL/ONLINELOG
Current alias directory path: ASM
Current alias directory path: ORCL/CONTROLFILE
Current alias directory path: ASM/ASMPARAMETERFILE
Current alias directory path: ORCL/PARAMETERFILE
Current alias directory path: ORCL
Current alias directory path: ORCL/DATAFILE
Current alias directory path: ORCL/TEMPFILE

md_restore

Purpose

The md_restore command restores disk groups from a metadata backup file.

Syntax and Description

md_restore backup_file [--silent]
[--full|--nodg|--newdg -o 'old_diskgroup:new_diskgroup [,...]']
[-S sql_script_file] [-G 'diskgroup [,diskgroup...]']
Table 12-39 describes the options for the md_restore command.

Table 12-39 Options for the md_restore command

Option	Description
`backup_file`	Reads the metadata information from `backup_file`.
`--silent`	Ignore errors. Typically, if `md_restore` encounters an error, it stops. Specifying this flag ignores any errors.
`--full`	Specifies to create a disk group and restore metadata.
`--nodg`	Specifies to restore metadata only.
`--newdg` `-o` `old_diskgroup:new_diskgroup`]	Specifies to create a disk group with a different name when restoring metadata. The `-o` option is required with `--newdg`.
`-S` `sql_script_file`	Write SQL commands to the specified SQL script file instead of executing the commands.
`-G` `diskgroup`	Select the disk groups to be restored. If no disk groups are defined, then all disk groups are restored.

Example

The first example restores the disk group data from the backup script and creates a copy. The second example takes an existing disk group data and restores its metadata. The third example restores disk group data completely but the new disk group that is created is named data2. The fourth example restores from the backup file after applying the overrides defined in the override.sql script file.

Example 12-41 Using the ASMCMD md_restore command

ASMCMD [+] > md_restore –-full –G data –-silent /scratch/backup/alldgs20100422

ASMCMD [+] > md_restore –-nodg –G data –-silent /scratch/backup/alldgs20100422

ASMCMD [+] > md_restore –-newdg -o 'data:data2' --silent /scratch/backup/data20100422

ASMCMD [+] > md_restore -S override.sql --silent /scratch/backup/data20100422

EJEMPLO BACKUP METADATA

ASMCMD> md_backup /u01/backup/backup_asm_data.bck -G DATA
Disk group metadata to be backed up: DATA
Current alias directory path: ASM
Current alias directory path: ACME
Current alias directory path: ACME/PARAMETERFILE
Current alias directory path: ACME/DATAFILE
Current alias directory path: ASM/PASSWORD
Current alias directory path: ACME/CONTROLFILE
Current alias directory path: ACME/TEMPFILE
Current alias directory path: ACME/ONLINELOG
Current alias directory path: ASM/ASMPARAMETERFILE
ASMCMD> md_backup /u01/backup/backup_asm_fra.bck -G FRA
Disk group metadata to be backed up: FRA
Current alias directory path: ACME/ARCHIVELOG/2021_05_04
Current alias directory path: ACME/ARCHIVELOG/2021_05_01
Current alias directory path: ACME/BACKUPSET
Current alias directory path: ACME/ARCHIVELOG/2021_05_03
Current alias directory path: ACME/ARCHIVELOG/2021_05_02
Current alias directory path: ACME/ARCHIVELOG
Current alias directory path: ACME/ONLINELOG
Current alias directory path: ACME/AUTOBACKUP/2021_05_07
Current alias directory path: ACME/BACKUPSET/2021_05_05
Current alias directory path: ACME/ARCHIVELOG/2021_05_06
Current alias directory path: ACME/DATAFILE
Current alias directory path: ACME/CONTROLFILE
Current alias directory path: ACME
Current alias directory path: ACME/ARCHIVELOG/2021_05_07
Current alias directory path: ACME/AUTOBACKUP
Current alias directory path: ACME/ARCHIVELOG/2021_05_05
Current alias directory path: ACME/AUTOBACKUP/2021_05_05
ASMCMD>

RMAN CATALOG NOPROMPT

RMAN> catalog start with 'd:\oracle\backup\DBTEST\';

searching for all files that match the pattern d:\oracle\backup\DBTEST\

List of Files Unknown to the Database
=====================================
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_11_24\O1_MF_S_960913571_F1HYPDOZ_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_11_24\O1_MF_S_960916783_F1J1TTCV_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_12_08\O1_MF_S_962220655_F2O6RSGH_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\BACKUPSET\2017_11_24\O1_MF_NNNDF_TAG20171124T162515_F1HYNNBX_.BKP

Do you really want to catalog the above files (enter YES or NO)?

Enter yes or use catalog start with 'd:\oracle\backup\DBTEST\

If you don’t want to get prompt for each file use noprompt

RMAN> catalog start with 'd:\oracle\backup\DBTEST\' noprompt;

using target database control file instead of recovery catalog
searching for all files that match the pattern d:\oracle\backup\DBTEST\

List of Files Unknown to the Database
=====================================
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_11_24\O1_MF_S_960913571_F1HYPDOZ_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_11_24\O1_MF_S_960916783_F1J1TTCV_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_12_08\O1_MF_S_962220655_F2O6RSGH_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\BACKUPSET\2017_11_24\O1_MF_NNNDF_TAG20171124T162515_F1HYNNBX_.BKP
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_11_24\O1_MF_S_960913571_F1HYPDOZ_.BKP
File Name: D:\ORACLE\BACKUP\DBTEST\AUTOBACKUP\2017_11_24\O1_MF_S_960916783_F1J1TTCV_.BK

RMAN change backupset until time

RMAN> change backupset 77,78,79 keep until time 'sysdate + 40';

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=74 device type=DISK

keep attributes for the backup are changed

backup will be obsolete on date 09-JAN-21

backup set key=77 RECID=77 STAMP=1057863668

keep attributes for the backup are changed

backup will be obsolete on date 09-JAN-21

backup set key=78 RECID=78 STAMP=1057863673

keep attributes for the backup are changed

backup will be obsolete on date 09-JAN-21

backup set key=79 RECID=79 STAMP=1057863674

RMAN>

miércoles, 25 de noviembre de 2020

BACKUP CONTROLFILE FORMAT

RMAN> backup current controlfile format '/u01/backup/control%U.rman';

RMAN> backup current controlfile;

SQL> alter database backup controlfile to trace as '/u01/backup/control.trc';

Database altered.

Database altered.

SQL> alter database backup controlfile to trace;

LUEGO DE EJECUTAR ESTO DEBEMOS IR A LAS VISTA V$DIAG_INFO Y CONSULTAR LA RUTA TRACE QUE ES DONDE GENERA LA COPIA

NOTA: BUSCAR X FECHA SID_ORA_###.trc

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/u01/backup/controlfile.bck';

Starting backup at 21-JUL-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/backup/controlfile.bck tag=TAG20200721T204557 RECID=1 STAMP=1046378759

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 21-JUL-20

Starting Control File and SPFILE Autobackup at 21-JUL-20

piece handle=+FRA/ACME/AUTOBACKUP/2020_07_21/s_1046378761.298.1046378761 comment=NONE

Finished Control File and SPFILE Autobackup at 21-JUL-20

DBA_OUTSTANDING_ALERTS VER ERRORES BASE DE DATOS

select

to_char(creation_time, 'dd-mm-yyyy hh24:mi') crt,

instance_name,

object_type,

message_type,

message_level,

reason,

suggested_action

from

dba_outstanding_alerts

BACKUP FORMAT

RMAN> run

2> {

3> allocate channel c1 device type disk format "/u01/backup/%U";

4> backup as backupset database;

5> }

released channel: ORA_DISK_1

allocated channel: c1

channel c1: SID=41 device type=DISK

Starting backup at 25-NOV-20

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9qwm8fym_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9qwmbsr6_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9qwmf6kp_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_9qwmmkbr_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_htct3xg4_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9qwmf54t_.dbf

channel c1: starting piece 1 at 25-NOV-20

channel c1: finished piece 1 at 25-NOV-20

piece handle=/u01/backup/1evge66h_1_1 tag=TAG20201125T182833 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:55

Finished backup at 25-NOV-20

Starting Control File and SPFILE Autobackup at 25-NOV-20

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_11_25/o1_mf_s_1057429768_hvxt6s15_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 25-NOV-20

released channel: c1

RMAN> host;

[oracle@localhost ~]$ cd /u01

[oracle@localhost u01]$ cd backup/

[oracle@localhost backup]$ ls -ltr

total 2094328

-rw-r--r--. 1 oracle oinstall 879 Nov 13 21:05 hr.log

-rw-r--r--. 1 oracle oinstall 40960 Nov 13 21:05 hr.dmp

-rw-r--r--. 1 oracle oinstall 0 Nov 25 18:12 controlfile.bck

-rw-r-----. 1 oracle dba 2144542720 Nov 25 18:29 1evge66h_1_1

[oracle@localhost backup]$ date

lunes, 23 de noviembre de 2020

RMAN BACKUP NOARCHIVELOG

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 23 21:51:37 2020

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 658509704 bytes

Database Buffers 171966464 bytes

Redo Buffers 2334720 bytes

Database mounted.

SQL>

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 214

Next log sequence to archive 216

Current log sequence 216

SQL>

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 214

Current log sequence 216

SQL>

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Nov 23 21:53:14 2020

connected to target database: ORCL (DBID=1376021099, not open)

RMAN> backup database;

Starting backup at 23-NOV-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=7 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9qwm8fym_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9qwmbsr6_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9qwmf6kp_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_9qwmmkbr_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_htct3xg4_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9qwmf54t_.dbf

channel ORA_DISK_1: starting piece 1 at 23-NOV-20

channel ORA_DISK_1: finished piece 1 at 23-NOV-20

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_11_23/o1_mf_nnndf_TAG20201123T215323_hvrxf43o_.bkp tag=TAG20201123T215323 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished backup at 23-NOV-20

Starting Control File and SPFILE Autobackup at 23-NOV-20

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_11_23/o1_mf_s_1057269068_hvrxgw6o_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 23-NOV-20

RMAN>

viernes, 13 de noviembre de 2020

CONSULTA DATAFILE CON ERRORES

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 658509704 bytes

Database Buffers 171966464 bytes

Redo Buffers 2334720 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '+DATA/ACME/DATAFILE/system.258.848598015'

SQL> select name, error from v$datafile join v$recover_file using(file#);

NAME

--------------------------------------------------------------------------------

ERROR

-----------------------------------------------------------------

+DATA/ACME/DATAFILE/system.258.848598015

FILE NOT FOUND

+DATA/ACME/DATAFILE/auditoria.269.1056056241

FILE NOT FOUND

SQL>

miércoles, 11 de noviembre de 2020

LINUX MANEJO LVM

Agradecimientos Especiales al Ingeniero Nicolas Hernandez sin su colaboracion esta pagina no seria posible

MANEJO LVM

a.Correr el siguiente comando:

 fdisk /dev/sdb

b. Para identificar el número de partición tecleamos p.
c. Tecleamos n para crear una nueva partición. Tecleamos p para que sea primaria.
d. Tecleamos el número de partición, dependiendo de lo que nos mostró en el paso b.
e. Tecleamos Enter dos veces.
f. Tecleamos t para cambiar el ID de la partición.
g. Tecleamos el numero para seleccionar la partición recién creada.
h. Tecleamos 8e para cambiar el Hex Code de la partición por Linux LVM
i. Con w escribimos los cambios a la table de particiones.

Luego vamos a crear el PV:

 pvcreate /dev/sdb1

pvdisplay

Ahora vamos a crear un VG asignadole ese PV creado

vgcreate vg_nombre /dev/sdb1

vgdisplay

Por ultimo vamos a crear el LV asignandole los PE libres del VG creado:

lvcreate -l+10239  -n lv_repositorio vg_nombre

Formateamos la particion:

mkfs.xfs /dev/vg_nombre/lv_repositorio

Ahora vamos a crear una partición y asignarle a esa partición el LV:

mkdir /opt/Ejemplo

Y probamos montarla:

mount -t xfs /dev/vg_nombre/lv_repositorio /opt/Ejemplo

# df -T

S.ficheros                             Tipo     bloques de 1K  Usados Disponibles Uso% Montado en

/dev/mapper/vg_nombre-lv_repositorio xfs           41918468   32928    41885540   1% /opt/Ejemplo

# df -T

S.ficheros                             Tipo     bloques de 1K  Usados Disponibles Uso% Montado en

/dev/mapper/vg_nombre-lv_repositorio xfs           41918468   32928    41885540   1% /opt/Ejemplo

Para que quede montado automáticamente al reiniciar el servidor debemos agregar la linea correspondiente al fstab:

vi /etc/fstab

/dev/mapper/vg_nombre-lv_repositorio /opt/Ejemplo                       xfs     _netdev,noatime        1 2

Para no realizar fsck

/dev/mapper/vg_oracle-lv_oracle1_u01 /u01 xfs defaults 0 0

Para que quede montado automáticamente al reiniciar el servidor

EJEMPLO

[root@localhost javier]# pvcreate /dev/sdb1

Physical volume "/dev/sdb1" successfully created.

[root@localhost javier]#

[root@localhost javier]# pvdisplay

--- Physical volume ---

PV Name /dev/sda2

VG Name ol

PV Size <49,00 GiB / not usable 3,00 MiB

Allocatable yes

PE Size 4,00 MiB

Total PE 12543

Free PE 1

Allocated PE 12542

PV UUID f0aYH4-VTyS-9gy1-Y6Mo-L7gu-dDQ8-AnD1yl

"/dev/sdb1" is a new physical volume of "<30,00 GiB"

--- NEW Physical volume ---

PV Name /dev/sdb1

VG Name

PV Size <30,00 GiB

Allocatable NO

PE Size 0

Total PE 0

Free PE 0

Allocated PE 0

PV UUID bsr5NQ-zX5Z-GKXt-op5A-NpIB-wqTA-zNk56h

[root@localhost javier]#

[root@localhost javier]# pvs

PV VG Fmt Attr PSize PFree

/dev/sda2 ol lvm2 a-- <49,00g 4,00m

/dev/sdb1 lvm2 --- <30,00g <30,00g

[root@localhost javier]#

[root@localhost javier]# vgcreate vg_oracle /dev/sdb1

Volume group "vg_oracle" successfully created

[root@localhost javier]#

[root@localhost javier]# lvcreate -l+7679 -n lv_oracle1 vg_oracle

Logical volume "lv_oracle1" created.

[root@localhost javier]#

[root@localhost javier]# lvremove lv_oracle1

Volume group "lv_oracle1" not found

Cannot process volume group lv_oracle1

[root@localhost javier]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

root ol -wi-ao---- 46,99g

swap ol -wi-ao---- 2,00g

lv_oracle1 vg_oracle -wi-a----- <30,00g

[root@localhost javier]# lvremove lv_oracle1

Volume group "lv_oracle1" not found

Cannot process volume group lv_oracle1

[root@localhost javier]# lvremove lv_oracle1 vg_oracle

Volume group "lv_oracle1" not found

Cannot process volume group lv_oracle1

Do you really want to remove active logical volume vg_oracle/lv_oracle1? [y/n]: y

Logical volume "lv_oracle1" successfully removed

[root@localhost javier]#

[root@localhost javier]# lvcreate -l+7679 -n lv_oracle1_u01 vg_oracle

Logical volume "lv_oracle1_u01" created.

[root@localhost javier]#

[root@localhost javier]# lvdisplay

--- Logical volume ---

LV Path /dev/vg_oracle/lv_oracle1_u01

LV Name lv_oracle1_u01

VG Name vg_oracle

LV UUID 5Q9uB6-a7Y2-mlLZ-bdlG-tZJf-NayM-S8dje8

LV Write Access read/write

LV Creation host, time localhost.localdomain, 2020-11-11 15:45:11 -0500

LV Status available

# open 0

LV Size <30,00 GiB

Current LE 7679

Segments 1

Allocation inherit

Read ahead sectors auto

- currently set to 8192

Block device 249:2

[root@localhost javier]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

root ol -wi-ao---- 46,99g

swap ol -wi-ao---- 2,00g

lv_oracle1_u01 vg_oracle -wi-a----- <30,00g

[root@localhost javier]#

[root@localhost javier]# mkfs.xfs /dev/vg_oracle/lv_oracle1_u01

meta-data=/dev/vg_oracle/lv_oracle1_u01 isize=256 agcount=4, agsize=1965824 blks

= sectsz=512 attr=2, projid32bit=1

= crc=0 finobt=0, sparse=0

data = bsize=4096 blocks=7863296, imaxpct=25

= sunit=0 swidth=0 blks

naming =version 2 bsize=4096 ascii-ci=0 ftype=1

log =internal log bsize=4096 blocks=3839, version=2

= sectsz=512 sunit=0 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

[root@localhost javier]#

[root@localhost /]# mkdir /u01

[root@localhost /]# df -h

S.ficheros Tamaño Usados Disp Uso% Montado en

devtmpfs 855M 0 855M 0% /dev

tmpfs 871M 0 871M 0% /dev/shm

tmpfs 871M 9,5M 862M 2% /run

tmpfs 871M 0 871M 0% /sys/fs/cgroup

/dev/mapper/ol-root 47G 14G 34G 29% /

/dev/sda1 1014M 329M 686M 33% /boot

tmpfs 175M 24K 175M 1% /run/user/1000

[root@localhost /]# cd /u01

[root@localhost u01]# pwd

/u01

[root@localhost u01]# cd ..

[root@localhost /]#

[root@localhost /]# mount /dev/vg_oracle/lv_oracle1_u01 /u01

[root@localhost /]# df -hT

S.ficheros Tipo Tamaño Usados Disp Uso% Montado en

devtmpfs devtmpfs 855M 0 855M 0% /dev

tmpfs tmpfs 871M 0 871M 0% /dev/shm

tmpfs tmpfs 871M 9,5M 862M 2% /run

tmpfs tmpfs 871M 0 871M 0% /sys/fs/cgroup

/dev/mapper/ol-root xfs 47G 14G 34G 29% /

/dev/sda1 xfs 1014M 329M 686M 33% /boot

tmpfs tmpfs 175M 24K 175M 1% /run/user/1000

/dev/mapper/vg_oracle-lv_oracle1_u01 xfs 30G 33M 30G 1% /u01

Ver el tipo de montaje

mount | grep /u01

MERCADOS FINANCIEROS

lunes, 14 de diciembre de 2020

viernes, 11 de diciembre de 2020

jueves, 10 de diciembre de 2020

lunes, 30 de noviembre de 2020

md_restore

miércoles, 25 de noviembre de 2020

lunes, 23 de noviembre de 2020

viernes, 13 de noviembre de 2020

miércoles, 11 de noviembre de 2020

Vistas de página en total

Buscar este blog

Seguidores

Datos personales

Archivo del blog

INSTRUCTOR WDP

Entradas populares

Oracle Seguridad

Etiquetas

Archivo del blog

OCP 11G

OCA11G

SQL

WEBLOGIC

OCA 10G

OCP 10G

Formulario de contacto