rman
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
| rman [2018/12/06 21:05] – created 91.177.234.129 | rman [2024/11/19 10:17] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== | + | * " |
| + | * " | ||
| + | * [[https:// | ||
| - | list - tells you what has already been done< | + | * [[https:// |
| - | report | + | * [[https:// |
| + | * [[https:// | ||
| + | * [[https:// | ||
| * [[https:// | * [[https:// | ||
| * [[http:// | * [[http:// | ||
| Line 16: | Line 20: | ||
| * [[http:// | * [[http:// | ||
| * [[http:// | * [[http:// | ||
| - | =====Change date and time format to get better reporting===== | + | * [[https:// |
| + | * [[http:// | ||
| + | * [http:// | ||
| + | * [https:// | ||
| + | |||
| + | * [[https:// | ||
| + | Essentially use log_file_convert="< | ||
| + | |||
| + | ==== Commvault backups do not start or are stuck at 5% ==== | ||
| + | Don't where else to put this. But this query executed by Commvault before starting to backup, either takes 10 minutes or more to return or just does not return! | ||
| + | < | ||
| + | SELECT ' | ||
| + | </ | ||
| + | To be clear, it is not a Commvault issue. It is a database issue and it is the same old thing that has plagued queries like this for years. | ||
| + | |||
| + | If a query attacks dba_free_space (or cdb_free_space in this case), and there are too many records in the recyclebin, say goodbye to a select on that table. | ||
| + | < | ||
| + | SQL> select count(*) from cdb_recyclebin; | ||
| + | |||
| + | COUNT(*) | ||
| + | ---------- | ||
| + | 3519 | ||
| + | |||
| + | SQL> set timi on | ||
| + | SQL> purge dba_recyclebin; | ||
| + | |||
| + | DBA Recyclebin purged. | ||
| + | |||
| + | Elapsed: 00: | ||
| + | </ | ||
| + | Reran the query and it took less that 1 second. | ||
| + | |||
| + | |||
| + | |||
| + | ==== Ways to create a standby ==== | ||
| + | < | ||
| + | duplicate target database for standby from active database nofilenamecheck; | ||
| + | or | ||
| + | restore standby controlfile from service ' | ||
| + | alter database mount; | ||
| + | restore database from service ' | ||
| + | or | ||
| + | restore standby controlfile from service ' | ||
| + | alter database mount; | ||
| + | recover database from service ' | ||
| + | or | ||
| + | dbca -silent -createDuplicateDB -gdbName standby -dbUniquename standby -primaryDBConnectionString < | ||
| + | </ | ||
| + | |||
| + | |||
| + | ==== Steps RMAN takes during a duplicate ==== | ||
| + | As mentioned in [[https:// | ||
| + | * It restores a controlfile from the target into the auxiliary | ||
| + | * It resets the db_name to the target so the controlfile can be mounted. | ||
| + | * It restores the datafiles setting the auxiliary datafiles in the proper location using either db_file_name_convert or 'set newname', | ||
| + | * It recovers the auxiliary datafiles to the ' | ||
| + | * It recreates the controlfile with the system datafile (datafile #1) | ||
| + | * It catalogs the auxiliary datafiles into the newly created datafiles. | ||
| + | * Executes a ' | ||
| + | * It changes the DBID and opens the database with resetlogs. | ||
| + | |||
| + | ==== SYSAUX datafile corrupted by ' | ||
| + | * [[https:// | ||
| + | Talk about bad hair day. They say a disaster is never one thing but a chain of events in succession. Let's see! | ||
| + | |||
| + | * Created some database links in a database called ' | ||
| + | * Customer not happy. Wants a fixed, defined password. | ||
| + | * Used a procedure to drop the links I just created. | ||
| + | * Procedure dropped all 200 db links by mistake - Oops. | ||
| + | * Seemingly no way to find what the links were. | ||
| + | * After pondering the options (I waited too long to do a flashback query), I chose to restore the database onto another server to a point in time before the drop. | ||
| + | * Duplicate failed. Backups started failing on appli. | ||
| + | Checked alertlog on appli and found | ||
| + | < | ||
| + | 2021-11-09T18: | ||
| + | alter tablespace SYSAUX offline immediate | ||
| + | Completed: alter tablespace SYSAUX offline immediate | ||
| + | alter tablespace SYSTEM offline immediate | ||
| + | ORA-1541 signalled during: alter tablespace SYSTEM offline immediate ... | ||
| + | 2021-11-09T18: | ||
| + | Errors in file / | ||
| + | ORA-01110: data file 2: '/ | ||
| + | 2021-11-09T18: | ||
| + | Checker run found 2 new persistent data failures | ||
| + | 2021-11-09T18: | ||
| + | Errors in file / | ||
| + | ORA-01110: data file 2: '/ | ||
| + | 2021-11-09T18: | ||
| + | Errors in file / | ||
| + | ORA-01110: data file 2: '/ | ||
| + | 2021-11-09T19: | ||
| + | Errors in file / | ||
| + | ORA-01110: data file 2: '/ | ||
| + | </ | ||
| + | The duplicate database command has corrupted the sysaux datafile - Oops. | ||
| + | |||
| + | Attempting to restore/ | ||
| + | < | ||
| + | export NLS_DATE_FORMAT=' | ||
| + | TS=" | ||
| + | rman debug all trace=srdc_rman_debug_${TS}.trc log=srdc_rman_output_${TS}.log | ||
| + | connect target / | ||
| + | connect catalog rman/ | ||
| + | set echo on; | ||
| + | backup validate check logical tablespace SYSAUX; | ||
| + | restore datafile 2 preview; | ||
| + | restore datafile 2; | ||
| + | recover datafile 2; | ||
| + | </ | ||
| + | |||
| + | This is the output of the restore | ||
| + | < | ||
| + | RMAN> restore datafile 2; | ||
| + | RMAN-03090: Starting restore at 11-NOV-2021 134609 | ||
| + | RMAN-12016: using channel ORA_SBT_TAPE_1 | ||
| + | RMAN-12016: using channel ORA_SBT_TAPE_2 | ||
| + | RMAN-12016: using channel ORA_DISK_1 | ||
| + | |||
| + | RMAN-11007: new media label is " | ||
| + | RMAN-08016: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08089: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08610: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08003: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08615: channel ORA_SBT_TAPE_1: | ||
| + | ORA-19507: failed to retrieve sequential file, handle=" | ||
| + | ORA-27029: skgfrtrv: sbtrestore returned error | ||
| + | ORA-19511: non RMAN, but media manager or vendor specific failure, error text: | ||
| + | | ||
| + | |||
| + | RMAN-05016: failover to previous backup | ||
| + | ... | ||
| + | ... | ||
| + | RMAN-05016: failover to previous backup | ||
| + | |||
| + | RMAN-08016: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08089: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08610: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08003: channel ORA_SBT_TAPE_1: | ||
| + | RMAN-08615: channel ORA_SBT_TAPE_1: | ||
| + | ORA-19507: failed to retrieve sequential file, handle=" | ||
| + | ORA-27029: skgfrtrv: sbtrestore returned error | ||
| + | ORA-19511: non RMAN, but media manager or vendor specific failure, error text: | ||
| + | | ||
| + | |||
| + | RMAN-05016: failover to previous backup | ||
| + | |||
| + | RMAN-06064: creating datafile file number=2 name=/ | ||
| + | RMAN-03091: Finished restore at 11-NOV-2021 135711 | ||
| + | </ | ||
| + | |||
| + | So even though it failed a few times it seems to have eventually worked. But... | ||
| + | |||
| + | This is the log of the recover | ||
| + | < | ||
| + | RMAN> recover datafile 2; | ||
| + | RMAN-03090: Starting recover at 11-NOV-2021 135903 | ||
| + | RMAN-12016: using channel ORA_SBT_TAPE_1 | ||
| + | RMAN-12016: using channel ORA_SBT_TAPE_2 | ||
| + | RMAN-12016: using channel ORA_DISK_1 | ||
| + | |||
| + | RMAN-08054: starting media recovery | ||
| + | |||
| + | RMAN-06050: archived log for thread 1 with sequence 398377 is already on disk as file / | ||
| + | RMAN-06050: archived log for thread 1 with sequence 398378 is already on disk as file / | ||
| + | RMAN-06050: archived log for thread 1 with sequence 398379 is already on disk as file / | ||
| + | RMAN-06050: archived log for thread 1 with sequence 398380 is already on disk as file / | ||
| + | RMAN-08060: unable to find archived log | ||
| + | RMAN-08510: archived log thread=1 sequence=1 | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-03002: failure of recover command at 11/11/2021 14:00:13 | ||
| + | RMAN-03009: failure of restoring and applying logs command on default channel at 11/11/2021 14:00:13 | ||
| + | RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 2055 | ||
| + | </ | ||
| + | |||
| + | What has happened? All the failing back through previous backups has caused the datafile to be restored (somehow) to the initial version of 8 years ago and it's asking for sequence number 1! - Oops. | ||
| + | |||
| + | Archivelogs are only kept for a month which is more than enough as an incremental level 0 is taken every week. | ||
| + | |||
| + | So now we have a broken production database and a restored copy of it that is even worse. | ||
| + | < | ||
| + | SQL> select file#, | ||
| + | |||
| + | FILE# NAME STATUS | ||
| + | ---------------- --------------------------------------------------------------------------- ------- ------------------ ---------- | ||
| + | 1 / | ||
| + | 2 / | ||
| + | 3 / | ||
| + | 4 / | ||
| + | 5 / | ||
| + | </ | ||
| + | |||
| + | My thoughts now turn to getting the data out of appli because if the database crashes, it may not come back uo again. | ||
| + | |||
| + | Datapump to the rescue | ||
| + | < | ||
| + | expdp system/ | ||
| + | |||
| + | Export: Release 19.0.0.0.0 - Production on Tue Nov 16 18:47:17 2021 | ||
| + | Version 19.3.0.0.0 | ||
| + | |||
| + | Copyright (c) 1982, 2019, Oracle and/or its affiliates. | ||
| + | |||
| + | UDE-00604: operation generated ORACLE error 604 | ||
| + | ORA-00604: error occurred at recursive SQL level 1 | ||
| + | ORA-00376: file 2 cannot be read at this time | ||
| + | ORA-01110: data file 2: '/ | ||
| + | |||
| + | </ | ||
| + | |||
| + | Ah, no. Datapump needs sysaux - Oops. | ||
| + | |||
| + | Ok, old school exp should do it... | ||
| + | < | ||
| + | exp system/ | ||
| + | |||
| + | Export: Release 19.0.0.0.0 - Production on Tue Nov 16 18:49:49 2021 | ||
| + | Version 19.3.0.0.0 | ||
| + | |||
| + | Copyright (c) 1982, 2019, Oracle and/or its affiliates. | ||
| + | |||
| + | |||
| + | EXP-00008: ORACLE error 604 encountered | ||
| + | ORA-00604: error occurred at recursive SQL level 1 | ||
| + | ORA-00376: file 2 cannot be read at this time | ||
| + | ORA-01110: data file 2: '/ | ||
| + | EXP-00000: Export terminated unsuccessfully | ||
| + | </ | ||
| + | |||
| + | Also a no-go. For some reason exp now needs sysaux as well - Oops. | ||
| + | |||
| + | I won't put all the different combinations I tried here but the only thing that worked was a table level exp. So specifying every table in the databse would work and that would be only marginally preferable to CTAS every table. There would be no packages, triggers, ... a bad situation. | ||
| + | |||
| + | Back to RMAN. Up to now, I've been using RMAN scripts on the dev server. Let's try to get a restore out of Commvault from the console.\\ | ||
| + | This is the pfile from the prod db trimmed down and modified for the restore | ||
| + | < | ||
| + | cat initappli.ora | ||
| + | *.audit_file_dest='/ | ||
| + | *.audit_sys_operations=TRUE | ||
| + | *.audit_syslog_level=' | ||
| + | *.audit_trail=' | ||
| + | *.compatible=' | ||
| + | *.control_file_record_keep_time=28 | ||
| + | *.control_files='/ | ||
| + | *.db_block_size=8192 | ||
| + | *.db_name=' | ||
| + | *.diagnostic_dest='/ | ||
| + | *.filesystemio_options=' | ||
| + | *.job_queue_processes=8 | ||
| + | *.log_archive_dest_1=' | ||
| + | *.log_archive_format=' | ||
| + | *.open_cursors=300 | ||
| + | *.parallel_max_servers=60 | ||
| + | *.parallel_servers_target=60 | ||
| + | *.pga_aggregate_target=524288000 | ||
| + | *.processes=200 | ||
| + | *.remote_login_passwordfile=' | ||
| + | *.sga_max_size=3145728000 | ||
| + | *.sga_target=3145728000 | ||
| + | *.undo_tablespace=' | ||
| + | </ | ||
| + | It doesn' | ||
| + | |||
| + | I'm not a genius with the Commvault console so got a colleague to try a restore. It failed. Tried some different options and go back a few days before the drop of the db links... we got a restore.\\ | ||
| + | I have no idea how but I'll get a copy of the script.\\ | ||
| + | The database came up in mount mode but would not open. | ||
| + | < | ||
| + | QL> select open_mode from v$database; | ||
| + | |||
| + | OPEN_MODE | ||
| + | -------------------- | ||
| + | MOUNTED | ||
| + | |||
| + | SQL> alter database open resetlogs; | ||
| + | alter database open resetlogs | ||
| + | * | ||
| + | ERROR at line 1: | ||
| + | ORA-01666: control file is for a standby database | ||
| + | |||
| + | |||
| + | SQL> select name, open_mode, database_role from v$database; | ||
| + | |||
| + | NAME OPEN_MODE | ||
| + | --------- -------------------- ---------------- | ||
| + | APPLI | ||
| + | |||
| + | </ | ||
| + | WTF? This customer does not use Dataguard and never has. How did this db get restored as a standby? | ||
| + | |||
| + | < | ||
| + | SQL> select name, | ||
| + | |||
| + | NAME DBID CONTROL OPEN_MODE | ||
| + | --------- ---------------- ------- -------------------- ------------ ------------------ ---------------- | ||
| + | APPLI | ||
| + | |||
| + | 1 row selected. | ||
| + | |||
| + | SQL> select name, | ||
| + | |||
| + | NAME DBID CURRENT_SCN LOG_MODE | ||
| + | --------- ---------------- ---------------- ------------ -------------------- | ||
| + | APPLI | ||
| + | |||
| + | 1 row selected. | ||
| + | |||
| + | SQL> select * from v$database_incarnation; | ||
| + | |||
| + | INCARNATION# | ||
| + | ---------------- ----------------- -------------------- ----------------------- -------------------- ------- ---------------- ------------------ -------------------------- | ||
| + | CON_ID | ||
| + | ---------------- | ||
| + | | ||
| + | 0 | ||
| + | |||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select file#, | ||
| + | |||
| + | FILE# NAME STATUS | ||
| + | ---------------- --------------------------------------------------------------------------- ------- ------------------ ---------- | ||
| + | 1 / | ||
| + | 2 / | ||
| + | 3 / | ||
| + | 4 / | ||
| + | 5 / | ||
| + | 6 / | ||
| + | 7 / | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select TS#, | ||
| + | |||
| + | TS# NAME INC FLA | ||
| + | ---------------- --------------------------------------------------------------------------- --- --- | ||
| + | 0 SYSTEM | ||
| + | 1 SYSAUX | ||
| + | 2 UNDOTBS1 | ||
| + | 3 TEMP NO YES | ||
| + | 4 USERS YES YES | ||
| + | 7 TS_ORSYP | ||
| + | 9 TS_KAUPTHING | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select * from v$recover_file; | ||
| + | |||
| + | FILE# ONLINE | ||
| + | ---------------- ------- ------- ----------------------------------------------------------------- ---------------- -------------------- ---------------- | ||
| + | 1 ONLINE | ||
| + | 2 ONLINE | ||
| + | 3 ONLINE | ||
| + | 4 ONLINE | ||
| + | 5 ONLINE | ||
| + | 6 ONLINE | ||
| + | 7 ONLINE | ||
| + | 8 ONLINE | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select * from v$backup; | ||
| + | |||
| + | FILE# STATUS | ||
| + | ---------------- ------------------ ---------------- -------------------- ---------------- | ||
| + | 1 NOT ACTIVE | ||
| + | 2 NOT ACTIVE | ||
| + | 3 NOT ACTIVE | ||
| + | 4 NOT ACTIVE | ||
| + | 5 NOT ACTIVE | ||
| + | 6 NOT ACTIVE | ||
| + | 7 NOT ACTIVE | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select sequence#, first_change#, | ||
| + | |||
| + | | ||
| + | ---------------- ---------------- -------------------- - | ||
| + | 398173 | ||
| + | 398172 | ||
| + | 398175 | ||
| + | 398174 | ||
| + | 398176 | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select file#, | ||
| + | |||
| + | FILE# NAME REC FUZ RESETLOGS_CHANGE# | ||
| + | ---------------- --------------------------------------------------------------------------- --- --- ----------------- ------------------ ---------------- -------------------- | ||
| + | CREATION_TIME | ||
| + | -------------------- -------------------- ------- | ||
| + | 1 / | ||
| + | 20-jul-2013 21:02:20 20-jul-2013 21:02:04 ONLINE | ||
| + | |||
| + | 2 / | ||
| + | 20-jul-2013 21:02:30 20-jul-2013 21:02:04 ONLINE | ||
| + | |||
| + | 3 / | ||
| + | 20-jul-2013 21:02:33 20-jul-2013 21:02:04 ONLINE | ||
| + | |||
| + | 4 / | ||
| + | 20-jul-2013 21:02:48 20-jul-2013 21:02:04 ONLINE | ||
| + | |||
| + | </ | ||
| + | That's the money shot. The checkpoint time on sysaux is the same as all the others, yay! | ||
| + | |||
| + | < | ||
| + | SQL> select status, to_char(checkpoint_change# | ||
| + | 2 count(*) from v$datafile_header | ||
| + | 3 group by status, checkpoint_change#, | ||
| + | |||
| + | STATUS | ||
| + | ------- ---------------------------------------- -------------------- ---------------- | ||
| + | ONLINE | ||
| + | |||
| + | 1 row selected. | ||
| + | |||
| + | </ | ||
| + | |||
| + | Trying to open the database | ||
| + | < | ||
| + | SQL> alter database open; | ||
| + | ORA-10458: standby database requires recovery | ||
| + | ORA-01152: file 1 was not restored from a sufficiently old backup | ||
| + | ORA-01110: data file 1: | ||
| + | '/ | ||
| + | </ | ||
| + | Ah, yes. This is normal trying to open a standby! Let's use the fact that it thinks it's a standby and recover it as such... | ||
| + | < | ||
| + | SQL> recover automatic standby database; | ||
| + | ORA-00279: change 386469492444 generated at 11/07/2021 05:04:41 needed for | ||
| + | thread 1 | ||
| + | ORA-00289: suggestion : | ||
| + | / | ||
| + | ORA-00280: change 386469492444 for thread 1 is in sequence #398177 | ||
| + | ORA-00278: log file | ||
| + | '/ | ||
| + | needed for this recovery | ||
| + | ORA-00308: cannot open archived log | ||
| + | '/ | ||
| + | ORA-27037: unable to obtain file status | ||
| + | IBM AIX RISC System/6000 Error: 2: No such file or directory | ||
| + | Additional information: | ||
| + | |||
| + | |||
| + | Specify log: {< | ||
| + | cancel | ||
| + | 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: | ||
| + | '/ | ||
| + | |||
| + | </ | ||
| + | |||
| + | Need to restore archivelogs from Commvault to the archivelog directory (we must not connect to the catalog as the database name and dbid is the same). | ||
| + | < | ||
| + | RMAN> run { | ||
| + | 2> allocate channel c1 type ' | ||
| + | 3> allocate channel c2 type ' | ||
| + | 4> allocate channel c3 type ' | ||
| + | 5> allocate channel c4 type ' | ||
| + | 6> allocate channel c5 type ' | ||
| + | 7> set archivelog destination to '/ | ||
| + | 8> restore archivelog from logseq 398176 until logseq 398377; | ||
| + | 9> } | ||
| + | allocated channel: c1 | ||
| + | channel c1: SID=233 device type=SBT_TAPE | ||
| + | channel c1: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c2 | ||
| + | channel c2: SID=268 device type=SBT_TAPE | ||
| + | channel c2: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c3 | ||
| + | channel c3: SID=299 device type=SBT_TAPE | ||
| + | channel c3: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c4 | ||
| + | channel c4: SID=332 device type=SBT_TAPE | ||
| + | channel c4: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c5 | ||
| + | channel c5: SID=365 device type=SBT_TAPE | ||
| + | channel c5: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | executing command: SET ARCHIVELOG DESTINATION | ||
| + | |||
| + | Starting restore at 15-NOV-21 17:11:08 | ||
| + | |||
| + | released channel: c1 | ||
| + | released channel: c2 | ||
| + | released channel: c3 | ||
| + | released channel: c4 | ||
| + | released channel: c5 | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-03002: failure of restore command at 11/15/2021 17:11:10 | ||
| + | RMAN-06026: some targets not found - aborting restore | ||
| + | RMAN-06102: no channel to restore a backup or copy of archived log for thread 1 with sequence 398377 and starting SCN of 386702950028 | ||
| + | |||
| + | RMAN> | ||
| + | RMAN> **end-of-file** | ||
| + | |||
| + | RMAN> exit | ||
| + | </ | ||
| + | |||
| + | Ok, go one less. | ||
| + | < | ||
| + | RMAN> run { | ||
| + | 2> allocate channel c1 type ' | ||
| + | 3> allocate channel c2 type ' | ||
| + | 4> allocate channel c3 type ' | ||
| + | 5> allocate channel c4 type ' | ||
| + | 6> allocate channel c5 type ' | ||
| + | 7> set archivelog destination to '/ | ||
| + | 8> restore archivelog from logseq 398176 until logseq 398376; | ||
| + | 9> } | ||
| + | allocated channel: c1 | ||
| + | channel c1: SID=233 device type=SBT_TAPE | ||
| + | channel c1: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c2 | ||
| + | channel c2: SID=268 device type=SBT_TAPE | ||
| + | channel c2: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c3 | ||
| + | channel c3: SID=299 device type=SBT_TAPE | ||
| + | channel c3: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c4 | ||
| + | channel c4: SID=332 device type=SBT_TAPE | ||
| + | channel c4: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | allocated channel: c5 | ||
| + | channel c5: SID=365 device type=SBT_TAPE | ||
| + | channel c5: CommVault Systems for Oracle: Version 11.0.0(BUILD80) | ||
| + | |||
| + | executing command: SET ARCHIVELOG DESTINATION | ||
| + | |||
| + | Starting restore at 15-NOV-21 17:12:28 | ||
| + | |||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | ... | ||
| + | new media label is " | ||
| + | new media label is " | ||
| + | channel c1: starting archived log restore to user-specified destination | ||
| + | archived log destination=/ | ||
| + | channel c1: restoring archived log | ||
| + | archived log thread=1 sequence=398176 | ||
| + | channel c1: restoring archived log | ||
| + | archived log thread=1 sequence=398177 | ||
| + | channel c1: reading from backup piece 70499475_APPLI_u70di3re_1_1 | ||
| + | channel c2: starting archived log restore to user-specified destination | ||
| + | archived log destination=/ | ||
| + | channel c2: restoring archived log | ||
| + | archived log thread=1 sequence=398178 | ||
| + | channel c2: restoring archived log | ||
| + | archived log thread=1 sequence=398179 | ||
| + | channel c2: reading from backup piece 70500970_APPLI_u90di6tn_1_1 | ||
| + | channel c3: starting archived log restore to user-specified destination | ||
| + | archived log destination=/ | ||
| + | channel c3: restoring archived log | ||
| + | ... | ||
| + | channel c2: restoring archived log | ||
| + | archived log thread=1 sequence=398375 | ||
| + | channel c2: restoring archived log | ||
| + | archived log thread=1 sequence=398376 | ||
| + | channel c2: reading from backup piece 70663705_APPLI_4p0dqbhn_1_1 | ||
| + | channel c3: piece handle=70658561_APPLI_4j0dq3t1_1_1 tag=ARCHIVELOG | ||
| + | channel c3: restored backup piece 1 | ||
| + | channel c3: restore complete, elapsed time: 00:00:01 | ||
| + | channel c4: piece handle=70644423_APPLI_3v0dpfp9_1_1 tag=ORACLE_HOURLY | ||
| + | channel c4: restored backup piece 1 | ||
| + | channel c4: restore complete, elapsed time: 00:00:04 | ||
| + | channel c5: piece handle=70659158_APPLI_4l0dq4fn_1_1 tag=ORACLE_HOURLY | ||
| + | channel c5: restored backup piece 1 | ||
| + | channel c5: restore complete, elapsed time: 00:00:01 | ||
| + | channel c1: piece handle=70661451_APPLI_4n0dq805_1_1 tag=ORACLE_HOURLY | ||
| + | channel c1: restored backup piece 1 | ||
| + | channel c1: restore complete, elapsed time: 00:00:01 | ||
| + | channel c2: piece handle=70663705_APPLI_4p0dqbhn_1_1 tag=ORACLE_HOURLY | ||
| + | channel c2: restored backup piece 1 | ||
| + | channel c2: restore complete, elapsed time: 00:00:07 | ||
| + | Finished restore at 15-NOV-21 17:13:51 | ||
| + | released channel: c1 | ||
| + | released channel: c2 | ||
| + | released channel: c3 | ||
| + | released channel: c4 | ||
| + | released channel: c5 | ||
| + | |||
| + | RMAN> | ||
| + | RMAN> **end-of-file** | ||
| + | |||
| + | RMAN> exit | ||
| + | </ | ||
| + | Disco! The apply now takes a while as there are 200 of them to do. Watch the alertlog from another session if necessary | ||
| + | |||
| + | |||
| + | < | ||
| + | SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 15 17:15:34 2021 | ||
| + | Version 19.3.0.0.0 | ||
| + | |||
| + | Copyright (c) 1982, 2019, Oracle. | ||
| + | |||
| + | |||
| + | Connected to: | ||
| + | Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production | ||
| + | Version 19.3.0.0.0 | ||
| + | |||
| + | SQL> recover automatic standby database; | ||
| + | ORA-00279: change 386702950028 generated at 11/10/2021 08:05:10 needed for | ||
| + | thread 1 | ||
| + | ORA-00289: suggestion : | ||
| + | / | ||
| + | ORA-00280: change 386702950028 for thread 1 is in sequence #398377 | ||
| + | ORA-00278: log file | ||
| + | '/ | ||
| + | needed for this recovery | ||
| + | ORA-00308: cannot open archived log | ||
| + | '/ | ||
| + | ORA-27037: unable to obtain file status | ||
| + | IBM AIX RISC System/6000 Error: 2: No such file or directory | ||
| + | Additional information: | ||
| + | |||
| + | |||
| + | Specify log: {< | ||
| + | |||
| + | ORA-00279: change 386778260810 generated at 11/11/2021 06:55:08 needed for | ||
| + | thread 1 | ||
| + | ORA-00289: suggestion : | ||
| + | / | ||
| + | ORA-00280: change 386778260810 for thread 1 is in sequence #398378 | ||
| + | ORA-00278: log file | ||
| + | '/ | ||
| + | needed for this recovery | ||
| + | |||
| + | ... | ||
| + | ORA-00279: change 387010236550 generated at 11/15/2021 08:02:53 needed for | ||
| + | thread 1 | ||
| + | ORA-00289: suggestion : | ||
| + | / | ||
| + | ORA-00280: change 387010236550 for thread 1 is in sequence #398389 | ||
| + | ORA-00278: log file | ||
| + | '/ | ||
| + | needed for this recovery | ||
| + | |||
| + | |||
| + | ORA-00308: cannot open archived log | ||
| + | '/ | ||
| + | ORA-27037: unable to obtain file status | ||
| + | IBM AIX RISC System/6000 Error: 2: No such file or directory | ||
| + | Additional information: | ||
| + | |||
| + | |||
| + | SQL> alter database open; | ||
| + | |||
| + | Database altered. | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> select file#, | ||
| + | |||
| + | FILE# NAME STATUS | ||
| + | ---------------- --------------------------------------------------------------------------- ------- ------------------ ---------- | ||
| + | 1 / | ||
| + | 2 / | ||
| + | 3 / | ||
| + | 4 / | ||
| + | 5 / | ||
| + | 6 / | ||
| + | 7 / | ||
| + | 8 / | ||
| + | |||
| + | </ | ||
| + | |||
| + | Looks good but notice the ENABLED flag is 'READ ONLY' | ||
| + | We are still a standby database. | ||
| + | |||
| + | < | ||
| + | SQL> select open_mode, | ||
| + | |||
| + | OPEN_MODE | ||
| + | -------------------- ---------------- | ||
| + | READ ONLY PHYSICAL STANDBY | ||
| + | |||
| + | SQL> alter database close; | ||
| + | |||
| + | Database altered. | ||
| + | |||
| + | SQL> select open_mode, | ||
| + | |||
| + | OPEN_MODE | ||
| + | -------------------- ---------------- | ||
| + | MOUNTED | ||
| + | |||
| + | SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; | ||
| + | |||
| + | SWITCHOVER_STATUS | ||
| + | -------------------- | ||
| + | NOT ALLOWED | ||
| + | |||
| + | SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; | ||
| + | |||
| + | Database altered. | ||
| + | |||
| + | SQL> SHUTDOWN IMMEDIATE; | ||
| + | ORA-01109: database not open | ||
| + | |||
| + | |||
| + | Database dismounted. | ||
| + | ORACLE instance shut down. | ||
| + | SQL> startup | ||
| + | ORACLE instance started. | ||
| + | |||
| + | Total System Global Area 637483336 bytes | ||
| + | Fixed Size 9058632 bytes | ||
| + | Variable Size | ||
| + | Database Buffers | ||
| + | Redo Buffers | ||
| + | Database mounted. | ||
| + | Database opened. | ||
| + | SQL> select open_mode, | ||
| + | |||
| + | OPEN_MODE | ||
| + | -------------------- ---------------- | ||
| + | READ WRITE | ||
| + | |||
| + | SQL> select tablespace_name, | ||
| + | |||
| + | TABLESPACE_NAME | ||
| + | ------------------------------ --------- | ||
| + | SYSTEM | ||
| + | SYSAUX | ||
| + | UNDOTBS1 | ||
| + | TEMP | ||
| + | USERS ONLINE | ||
| + | TS_ORSYP | ||
| + | TS_KAUPTHING | ||
| + | |||
| + | SQL> alter tablespace sysaux online; | ||
| + | |||
| + | Tablespace altered. | ||
| + | |||
| + | SQL> select tablespace_name, | ||
| + | |||
| + | TABLESPACE_NAME | ||
| + | ------------------------------ --------- | ||
| + | SYSTEM | ||
| + | SYSAUX | ||
| + | UNDOTBS1 | ||
| + | TEMP | ||
| + | USERS ONLINE | ||
| + | TS_ORSYP | ||
| + | TS_KAUPTHING | ||
| + | </ | ||
| + | |||
| + | Ok, we have a working database that we could copy back to production but in the mean time, production has been open and is being used. So to recover completely, the whole exercise needs to be done again with appli on production shut for users. That's ok, we know it can be done now. | ||
| + | |||
| + | There is another way. Copy just the sysaux datafile over to production and recover the database. | ||
| + | This may not be an option finally, as the incarnation is different. The alter database open may have done this. So could do it all again but without the open. | ||
| + | |||
| + | To be continued... | ||
| + | |||
| + | Update:\\ | ||
| + | This is the script used to restore and recover the database | ||
| + | < | ||
| + | run { | ||
| + | allocate channel c1 type ' | ||
| + | trace 0; | ||
| + | restore controlfile; | ||
| + | sql 'alter database mount'; | ||
| + | } | ||
| + | |||
| + | run { | ||
| + | set newname for database to '/ | ||
| + | allocate channel c1 type ' | ||
| + | allocate channel c2 type ' | ||
| + | restore database | ||
| + | switch datafile all; | ||
| + | } | ||
| + | |||
| + | run { | ||
| + | allocate channel c1 type ' | ||
| + | allocate channel c2 type ' | ||
| + | recover database until time = " | ||
| + | } | ||
| + | |||
| + | </ | ||
| + | |||
| + | Indeed did it all again without the open and it worked! The action of either opening the database with resetlogs or forcing a standby to be a Primary with " | ||
| + | You still obviously have the option of using this restored database in its entirety.\\ | ||
| + | Anyway, just like this, the production database got recovered...\\ | ||
| + | |||
| + | On prod db, stop user access and flush the redo | ||
| + | < | ||
| + | alter system set local_listener=''; | ||
| + | alter system switch logfile; | ||
| + | shu immediate | ||
| + | </ | ||
| + | |||
| + | On standby server, ship the archivelogs over so we can recover on the backup server | ||
| + | < | ||
| + | scp hn511:/ | ||
| + | |||
| + | sqlplus / as sysdba | ||
| + | recover automatic standby database; | ||
| + | auto | ||
| + | shu immediate | ||
| + | </ | ||
| + | |||
| + | With the database recovered and shutdown (it only went as far as MOUNT, not OPEN) | ||
| + | < | ||
| + | scp data_D-APPLI_TS-SYSAUX_FNO-2 hn511:/ | ||
| + | </ | ||
| + | |||
| + | On prod db | ||
| + | < | ||
| + | startup mount | ||
| + | recover datafile 2; | ||
| + | |||
| + | set lines 1000 pages 100 | ||
| + | col name for a70 | ||
| + | select FILE#, | ||
| + | |||
| + | FILE# NAME | ||
| + | ---------- ---------------------------------------------------------------------- ---------- ------- | ||
| + | 1 / | ||
| + | 2 / | ||
| + | 3 / | ||
| + | 4 / | ||
| + | 5 / | ||
| + | 6 / | ||
| + | 7 / | ||
| + | 8 / | ||
| + | 9 / | ||
| + | |||
| + | |||
| + | alter database datafile 2 online; | ||
| + | select FILE#, | ||
| + | |||
| + | FILE# NAME | ||
| + | ---------- ---------------------------------------------------------------------- ---------- ------- | ||
| + | 1 / | ||
| + | 2 / | ||
| + | 3 / | ||
| + | 4 / | ||
| + | 5 / | ||
| + | 6 / | ||
| + | 7 / | ||
| + | |||
| + | alter database open; | ||
| + | |||
| + | Database altered. | ||
| + | |||
| + | |||
| + | SQL> col file_name for a70 | ||
| + | SQL> select file_name, | ||
| + | |||
| + | FILE_NAME | ||
| + | ------------------------------------------------------------------- --------- ------- | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | |||
| + | select tablespace_name, | ||
| + | |||
| + | TABLESPACE_NAME | ||
| + | ------------------------------ --------- | ||
| + | SYSTEM | ||
| + | SYSAUX | ||
| + | UNDOTBS1 | ||
| + | TEMP | ||
| + | USERS ONLINE | ||
| + | TS_ORSYP | ||
| + | |||
| + | |||
| + | alter tablespace sysaux online; | ||
| + | |||
| + | Tablespace altered. | ||
| + | |||
| + | select tablespace_name, | ||
| + | |||
| + | TABLESPACE_NAME | ||
| + | ------------------------------ --------- | ||
| + | SYSTEM | ||
| + | SYSAUX | ||
| + | UNDOTBS1 | ||
| + | TEMP | ||
| + | USERS ONLINE | ||
| + | TS_ORSYP | ||
| + | TS_KAUPTHING | ||
| + | TS_DB_SIZING | ||
| + | TS_RATR | ||
| + | |||
| + | </ | ||
| + | |||
| + | All these queries were provided by Oracle Support | ||
| + | < | ||
| + | spool / | ||
| + | set pagesize 20000 | ||
| + | set linesize 180 | ||
| + | set pause offset | ||
| + | set serveroutput on | ||
| + | set feedback on | ||
| + | set echo on | ||
| + | set numformat 999999999999999 | ||
| + | alter session set nls_date_format = ' | ||
| + | |||
| + | archive log list; | ||
| + | select INSTANCE_NAME, | ||
| + | select database_role from v$database; | ||
| + | select name, | ||
| + | select name, | ||
| + | select * from v$database_incarnation; | ||
| + | col name for a75 | ||
| + | select * from v$restore_point; | ||
| + | select flashback_on from v$database; | ||
| + | select parallel from v$instance; | ||
| + | select protection_level from v$database; | ||
| + | select * from dba_streams_administrator; | ||
| + | select file#, | ||
| + | select file#, | ||
| + | select TS#, | ||
| + | select * from v$recover_file; | ||
| + | select * from v$backup; | ||
| + | select * from v$log; | ||
| + | select * from v$logfile; | ||
| + | select sequence#, first_change#, | ||
| + | select file#, | ||
| + | select status, to_char(checkpoint_change# | ||
| + | count(*) from v$datafile_header | ||
| + | group by status, checkpoint_change#, | ||
| + | select count(*), | ||
| + | select count(*), | ||
| + | select count(*), | ||
| + | select count(*), | ||
| + | select min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" | ||
| + | select fhdbn, | ||
| + | select name, status , sequence#, thread#, | ||
| + | TO_CHAR(first_change#, | ||
| + | TO_CHAR(next_change#, | ||
| + | to_char(completion_time,' | ||
| + | from v$archived_log where (select min(checkpoint_change# | ||
| + | spool off | ||
| + | |||
| + | </ | ||
| + | |||
| + | ==== Example of a script generated by Commvault ==== | ||
| + | < | ||
| + | run { | ||
| + | allocate channel ch1 type ' | ||
| + | PARMS=" | ||
| + | TRACE 0; | ||
| + | send " | ||
| + | setlimit channel ch1 maxopenfiles 8; | ||
| + | backup | ||
| + | | ||
| + | | ||
| + | format=' | ||
| + | tag = ' | ||
| + | | ||
| + | | ||
| + | | ||
| + | } | ||
| + | </ | ||
| + | |||
| + | ==== Archivelog destination full and need to back them up? ==== | ||
| + | to be able to allocate a channel without specifying the full PARMS, libobk needs setting up correctly | ||
| + | < | ||
| + | ln -s / | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | run { | ||
| + | allocate channel c1 type sbt_tape; | ||
| + | backup archivelog all not backed up 1 times delete all input; | ||
| + | } | ||
| + | </ | ||
| + | |||
| + | ==== Corrupt datafile? Restore it as part of the tablespace ==== | ||
| + | < | ||
| + | run { | ||
| + | allocate channel c1 type sbt_tape; | ||
| + | sql 'alter tablespace && | ||
| + | restore tablespace & | ||
| + | recover tablespace & | ||
| + | sql 'alter tablespace & | ||
| + | } | ||
| + | </ | ||
| + | |||
| + | ==== Change date and time format to get better reporting ==== | ||
| Set this at unix prompt before starting RMAN | Set this at unix prompt before starting RMAN | ||
| - | < | + | < |
| - | =====Crosscheck the archivelogs===== | + | export NLS_DATE_FORMAT=" |
| - | < | + | </ |
| - | =====What is the size and duration of the RMAN backup?===== | + | ==== Crosscheck the archivelogs ==== |
| - | < | + | < |
| + | change archivelog all crosscheck; | ||
| + | </ | ||
| + | ==== Matching Server Sessions with Channels When One RMAN Session Is Active | ||
| + | When only one RMAN session is active, the easiest method for determining the server session ID for an RMAN channel is to execute the following query on the target database while the RMAN job is executing: | ||
| + | < | ||
| + | COLUMN CLIENT_INFO FORMAT a30 | ||
| + | COLUMN SID FORMAT 999 | ||
| + | COLUMN SPID FORMAT 9999 | ||
| - | =====Delete archivelogs that have been backed up===== | + | SELECT s.SID, p.SPID, s.CLIENT_INFO |
| - | <code> | + | FROM V$PROCESS p, V$SESSION s |
| - | < | + | WHERE p.ADDR |
| - | < | + | AND CLIENT_INFO LIKE ' |
| - | < | + | ; |
| - | < | + | </ |
| + | ==== What is the size and duration of the RMAN backup? | ||
| + | < | ||
| + | set pages 100 lines 200 | ||
| + | col mb for 999,999,999 | ||
| + | col completed for a11 | ||
| + | col sortcol noprint | ||
| - | =====A quick backup | + | select vi.instance_name sid |
| - | < | + | , to_char(completion_time, |
| - | =====A quick backup check!===== | + | , completion_time sortcol |
| - | < | + | , type |
| - | =====A quick backup using TSM===== | + | , round(sum(bytes)/1048576) |
| - | < | + | , round(sum(elapsed_seconds)/ |
| - | =====A quick backup using EMC Networker===== | + | from |
| - | < | + | ( |
| + | select | ||
| + | case | ||
| + | when s.backup_type | ||
| + | when s.controlfile_included | ||
| + | when s.backup_type | ||
| + | when s.backup_type | ||
| + | when s.backup_type | ||
| + | else s.backup_type | ||
| + | end type | ||
| + | , trunc(s.completion_time) completion_time | ||
| + | , p.bytes | ||
| + | , s.elapsed_seconds | ||
| + | from | ||
| + | , v$backup_set | ||
| + | where p.status | ||
| + | and p.recid | ||
| + | union all | ||
| + | select ' | ||
| + | , trunc(completion_time) completion_time | ||
| + | , output_bytes | ||
| + | , 0 elapsed_seconds | ||
| + | from | ||
| + | ) | ||
| + | , v$instance vi | ||
| + | group by vi.instance_name, | ||
| + | order by 1,3,4 | ||
| + | / | ||
| + | </ | ||
| - | =====A quick backup using Commvault===== | + | ==== Delete archivelogs that have been backed up ==== |
| + | < | ||
| + | delete noprompt archivelog all backed up 1 times to sbt_tape; | ||
| + | </ | ||
| + | < | ||
| + | delete noprompt archivelog until time ' | ||
| + | </ | ||
| + | < | ||
| + | delete noprompt archivelog until time ' | ||
| + | </ | ||
| + | < | ||
| + | delete noprompt archivelog until logseq 42000 backed up 1 times to device type ' | ||
| + | </ | ||
| + | < | ||
| + | delete noprompt archivelog from sequence 4250 until sequence 4546 like '/ | ||
| + | </ | ||
| + | < | ||
| + | delete expired archivelog all; | ||
| + | </ | ||
| + | |||
| + | ==== An all round catalog cleanup ==== | ||
| + | < | ||
| + | rman<< | ||
| + | set echo on | ||
| + | connect catalog ${RMANUSR}/ | ||
| + | connect target / | ||
| + | configure retention policy to recovery window of ${RMANRETENTION} days; | ||
| + | configure controlfile autobackup on; | ||
| + | # allocate channel for maintenance type ' | ||
| + | allocate channel for maintenance type ' | ||
| + | crosscheck archivelog all; | ||
| + | crosscheck backup; | ||
| + | crosscheck backup of controlfile; | ||
| + | delete noprompt force obsolete; | ||
| + | delete noprompt force obsolete orphan; | ||
| + | delete noprompt expired backup; | ||
| + | delete noprompt expired copy; | ||
| + | delete noprompt expired archivelog all; | ||
| + | delete noprompt expired backup of controlfile; | ||
| + | report obsolete orphan; | ||
| + | report obsolete; | ||
| + | report need backup; | ||
| + | EORMAN | ||
| + | </ | ||
| + | |||
| + | ==== ORA-01017 Invalid Username/ | ||
| + | Reference: [[https:// | ||
| + | How to find out where the failed logon attempts are coming from. | ||
| + | |||
| + | === With auditing === | ||
| + | Check what is being audited | ||
| + | < | ||
| + | select * from dba_stmt_audit_opts; | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | audit network by access; | ||
| + | </ | ||
| + | then | ||
| + | < | ||
| + | select * | ||
| + | from dba_audit_session | ||
| + | order by sessionid desc; | ||
| + | </ | ||
| + | |||
| + | === Without auditing === | ||
| + | < | ||
| + | create table sys.logon_trigger | ||
| + | ( | ||
| + | username varchar2(30), | ||
| + | userhost varchar2(128), | ||
| + | timestamp date | ||
| + | ); | ||
| + | </ | ||
| + | < | ||
| + | create or replace trigger sys.logon_trigger | ||
| + | after servererror on database | ||
| + | begin | ||
| + | if (is_servererror(1017)) then | ||
| + | insert into logon_trigger values(sys_context(' | ||
| + | commit; | ||
| + | end if; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | set lines 200 | ||
| + | col userhost for a30 | ||
| + | alter session set nls_date_format=' | ||
| + | select * from sys.logon_trigger order by timestamp desc; | ||
| + | </ | ||
| + | |||
| + | |||
| + | |||
| + | ==== A quick backup - suppressing autobackup ==== | ||
| + | < | ||
| + | run { | ||
| + | set nocfau; | ||
| + | backup as copy database format '/ | ||
| + | backup as copy archivelog all format '/ | ||
| + | backup as copy current controlfile format '/ | ||
| + | } | ||
| + | </ | ||
| + | ==== A quick backup check! ==== | ||
| + | < | ||
| + | set lines 1000 | ||
| + | select /*+ rule */ to_char(start_time,' | ||
| + | , to_char(end_time,' | ||
| + | , round((end_time-start_time)*24*60) | ||
| + | , input_type | ||
| + | , status | ||
| + | from | ||
| + | where start_time > trunc(sysdate)-1 | ||
| + | order by start_time desc | ||
| + | / | ||
| + | </ | ||
| + | ==== A quick backup using TSM ==== | ||
| + | < | ||
| + | set echo on | ||
| + | connect target /; | ||
| + | connect catalog rman/ | ||
| + | |||
| + | run | ||
| + | { | ||
| + | allocate channel t0 type sbt_tape parms ' | ||
| + | backup database including archivelog; | ||
| + | } | ||
| + | </ | ||
| + | ==== A quick backup using EMC Networker ==== | ||
| + | < | ||
| + | set echo on | ||
| + | connect target /; | ||
| + | connect catalog rman/ | ||
| + | |||
| + | run | ||
| + | { | ||
| + | allocate channel t0 type sbt_tape parms ' | ||
| + | backup database including archivelog; | ||
| + | } | ||
| + | </ | ||
| + | |||
| + | ==== A quick backup using Commvault ==== | ||
| + | More info on Commvault commandline and xml templates [[https:// | ||
| The library used here is for AIX | The library used here is for AIX | ||
| - | < | + | < |
| + | set echo on | ||
| + | connect target /; | ||
| + | connect catalog rman/rman@rman; | ||
| + | |||
| + | run | ||
| + | { | ||
| + | allocate channel t0 type sbt_tape parms ' | ||
| + | backup database including archivelog; | ||
| + | } | ||
| + | </ | ||
| ...and this works on RHEL | ...and this works on RHEL | ||
| - | < | + | < |
| + | set echo on | ||
| + | connect target | ||
| + | connect catalog rman/ | ||
| - | =====Backup archivelogs that have not yet been backed up then delete any older than 72 hours===== | + | run |
| - | < | + | { |
| + | allocate channel t0 type sbt_tape parms ' | ||
| + | backup database including archivelog; | ||
| + | } | ||
| + | </ | ||
| - | =====Backup archivelogs | + | ==== Backup archivelogs |
| - | < | + | < |
| + | run { | ||
| + | allocate channel type ' | ||
| + | backup archivelog all not backed up 1 times tag=bu20140808t170528p5685; | ||
| + | delete noprompt archivelog until time ' | ||
| + | } | ||
| + | </ | ||
| - | =====Backup the archivelogs to tape then delete the ones on disk===== | + | ==== Backup archivelogs between a range of sequence numbers ==== |
| + | < | ||
| + | run { | ||
| + | allocate channel t1 device type ' | ||
| + | backup archivelog from logseq 224801 | ||
| + | } | ||
| + | </ | ||
| + | |||
| + | ==== Backup the archivelogs to tape then delete the ones on disk ==== | ||
| In a space emergency, backup all the archivelogs to the configured tape and immediately delete them from disk | In a space emergency, backup all the archivelogs to the configured tape and immediately delete them from disk | ||
| - | < | + | < |
| + | backup device type sbt_tape archivelog all delete all input; | ||
| + | </ | ||
| or ensure the correct device... | or ensure the correct device... | ||
| - | < | + | < |
| - | =====Restore archivelogs===== | + | run { |
| - | < | + | allocate channel c1 type sbt_tape parms ' |
| - | =====Monitoring FRA usage===== | + | backup device type sbt_tape archivelog all delete all input; |
| + | } | ||
| + | </ | ||
| + | ==== Restore archivelogs ==== | ||
| + | < | ||
| + | restore archivelog from logseq=38400 until logseq=38499; | ||
| + | restore archivelog logseq=25444; | ||
| + | </ | ||
| + | ==== Recover to a point before RESETLOGS ==== | ||
| + | * [[http:// | ||
| + | When you get RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time. | ||
| + | < | ||
| + | RMAN> LIST INCARNATION; | ||
| + | DB Key Inc Key DB Name DB ID | ||
| + | ------- ------- -------- ----------- ------- ---------- ---------- | ||
| + | (1..7 removed) | ||
| + | 8 | ||
| + | 9 | ||
| + | |||
| + | RMAN> RESET DATABASE TO INCARNATION 8; | ||
| + | </ | ||
| + | Now do the restore again | ||
| + | |||
| + | ==== Monitoring FRA usage ==== | ||
| If the Flash (or Fast) Recovery Area is used for archive redo logs as well as flashback logs, we need to monitor it's size | If the Flash (or Fast) Recovery Area is used for archive redo logs as well as flashback logs, we need to monitor it's size | ||
| - | < | + | < |
| + | col fsname | ||
| + | col space_limit | ||
| + | col space_used heading "Used GB" | ||
| + | col percused heading "% Used" | ||
| + | col space_reclaimable | ||
| + | col percrec | ||
| + | set linesize 1000 | ||
| + | with maxsizes as ( | ||
| + | select name | ||
| + | , floor(space_limit/ | ||
| + | from | ||
| + | ) | ||
| + | , useds as ( | ||
| + | select name | ||
| + | , ceil(space_used/ | ||
| + | from | ||
| + | ) | ||
| + | , reclaimables as ( | ||
| + | select name | ||
| + | , (space_reclaimable/ | ||
| + | from | ||
| + | ) | ||
| + | select m.name | ||
| + | , trunc(m.space_limit) | ||
| + | , trunc(u.space_used) | ||
| + | | ||
| + | , trunc(r.space_reclaimable) | ||
| + | | ||
| + | from | ||
| + | , useds u | ||
| + | , reclaimables r | ||
| + | where m.name = u.name | ||
| + | and u.name = r.name | ||
| + | order by m.name | ||
| + | / | ||
| + | </ | ||
| and in detail... | and in detail... | ||
| - | < | + | < |
| + | set linesize 1000 | ||
| + | select * | ||
| + | from | ||
| + | where percent_space_used > 0; | ||
| + | </ | ||
| size taken up by the flashback logs | size taken up by the flashback logs | ||
| - | < | + | < |
| + | select estimated_flashback_size | ||
| + | from | ||
| + | </ | ||
| + | |||
| + | ==== Using image copy backups allow two recovery options ==== | ||
| + | * [[https:// | ||
| + | This method cuts down on restore time as the images are lready present, they just need switching to with SWITCH DATABASE TO COPY; | ||
| - | =====Validate backups or restores===== | + | ==== Validate backups or restores ==== |
| - | * [[http:// | + | * [[http:// |
| Validate that the database components are free of corruption | Validate that the database components are free of corruption | ||
| - | < | + | < |
| + | backup < | ||
| + | </ | ||
| or validate that the database components on tape are free of corruption | or validate that the database components on tape are free of corruption | ||
| - | < | + | < |
| + | restore < | ||
| + | </ | ||
| eg (using Commvault): | eg (using Commvault): | ||
| - | < | + | < |
| + | connect target / | ||
| + | connect catalog rman/rman@rman | ||
| + | run { | ||
| + | allocate channel c0 type sbt_tape parms ' | ||
| + | set until time " | ||
| + | restore database validate; | ||
| + | } | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | backup database validate; | ||
| + | backup archivelog all validate; | ||
| + | backup current controlfile validate; | ||
| + | backup tablespace users validate; | ||
| + | backup datafile 1 validate; | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | restore database validate; | ||
| + | restore archivelog all validate; | ||
| + | restore spfile validate; | ||
| + | restore tablespace users validate; | ||
| + | </ | ||
| Use " | Use " | ||
| - | =====Tuning RMAN performance===== | + | ==== Tuning RMAN performance ==== |
| - | * [[https:// | + | * [[https:// |
| - | =====Identifying Bottlenecks with V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO===== | + | ==== Identifying Bottlenecks with V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO ==== |
| - | * [[https:// | + | * [[https:// |
| - | =====Build a report of RMAN backup pieces using awk to munge the RMAN output===== | + | ==== Build a report of RMAN backup pieces using awk to munge the RMAN output ==== |
| - | < | + | < |
| + | export NLS_DATE_FORMAT=' | ||
| + | /Finished restore at / | ||
| + | /Recovery must be done beyond SCN /{if ($7>scn) scn=$7 } | ||
| + | /^ *(Piece )Name: / { sub(/^ *(Piece )Name: /,"" | ||
| + | END{ for (i in files) print i > " | ||
| + | ' | ||
| + | </ | ||
| If that approach does not work, try this | If that approach does not work, try this | ||
| - | < | + | < |
| + | vi restval.rman | ||
| + | connect target | ||
| + | connect catalog rman/ | ||
| + | run { | ||
| + | allocate channel t1 type ' | ||
| + | restore controlfile preview; | ||
| + | restore database preview; | ||
| + | release channel t1; | ||
| + | } | ||
| - | =====Script to validate the database and all archivelogs needed for a restore===== | + | |
| + | cat restval.rman | NLS_DATE_FORMAT=' | ||
| + | /Finished restore at / | ||
| + | /Recovery must be done beyond SCN /{if ($7>scn) scn=$7 } | ||
| + | /^ *(Piece )Name: / { sub(/^ *(Piece )Name: /,"" | ||
| + | END{ for (i in files) print i > " | ||
| + | ' | ||
| + | </ | ||
| + | |||
| + | ==== Script to validate the database and all archivelogs needed for a restore ==== | ||
| Environment must be set before running... | Environment must be set before running... | ||
| - | < | + | < |
| + | export NLS_DATE_FORMAT=" | ||
| - | =====Restore and recover datafile while database is running===== | + | rman << |
| - | Had a problem where the name of the data file was created incorrectly. It had non-printable characters in it due to backspace character being setup wrongly and someone making a typing mistake.<br /> | + | set echo on |
| - | < | + | connect target; |
| + | restore database validate; | ||
| + | exit | ||
| + | EORMAN | ||
| + | |||
| + | if [[ $? != 0 ]]; then | ||
| + | echo "*** ERROR: RMAN restore validate of database failed" | ||
| + | exit 1; | ||
| + | fi | ||
| + | |||
| + | cmd=$(sqlplus -S "/ as sysdba" | ||
| + | whenever sqlerror exit sql.sqlcode | ||
| + | whenever oserror exit 1 | ||
| + | set newpa none pages 0 head off veri off feed off term off echo off pause off numw 32 | ||
| + | select ' | ||
| + | exit | ||
| + | EOSQL | ||
| + | ) | ||
| + | |||
| + | if [[ $? != 0 ]]; then | ||
| + | echo "*** ERROR: $cmd" | ||
| + | exit 1; | ||
| + | fi | ||
| + | |||
| + | rman << | ||
| + | set echo on | ||
| + | connect target; | ||
| + | ${cmd}; | ||
| + | exit | ||
| + | EORMAN | ||
| + | |||
| + | if [[ $? != 0 ]]; then | ||
| + | echo "*** ERROR: RMAN restore validate of archivelogs failed" | ||
| + | exit 1; | ||
| + | fi | ||
| + | </ | ||
| + | |||
| + | ==== Restore and recover datafile while database is running ==== | ||
| + | Had a problem where the name of the data file was created incorrectly. It had non-printable characters in it due to backspace character being setup wrongly and someone making a typing mistake.\\ | ||
| + | < | ||
| + | SQL> alter database datafile 67 offline; | ||
| + | |||
| + | rman target / catalog rman/rman@rman12d | ||
| + | run { | ||
| + | allocate channel t1 type ' | ||
| + | set newname for datafile 67 to '/ | ||
| + | restore datafile 67; | ||
| + | switch datafile 67; | ||
| + | recover datafile 67; | ||
| + | } | ||
| + | |||
| + | SQL> alter database datafile 67 online; | ||
| + | </ | ||
| In version 12c file renaming is more like it should be! | In version 12c file renaming is more like it should be! | ||
| - | < | + | < |
| + | alter database move datafile '/ | ||
| + | </ | ||
| - | =====Recover datafile blocks using RMAN===== | + | ==== Recover datafile blocks using RMAN ==== |
| - | < | + | < |
| + | connect target | ||
| - | =====Quick check that the tdpo configuration is correct===== | + | backup validate datafile 00091; |
| + | |||
| + | run { | ||
| + | set maxcorrupt for datafile 91 to 32; | ||
| + | backup validate datafile 00091; | ||
| + | } | ||
| + | |||
| + | blockrecover datafile 91 block 889664; | ||
| + | </ | ||
| + | |||
| + | ==== Quick check that the tdpo configuration is correct ==== | ||
| Channel test | Channel test | ||
| - | < | + | < |
| + | run { | ||
| + | allocate channel c1 type sbt_tape parms ' | ||
| + | } | ||
| + | </ | ||
| - | =====Quick backup to tape via tdpo===== | + | ==== Quick backup to tape via tdpo ==== |
| - | < | + | < |
| - | =====An example of what Networker sends to Unix to be run on the command line===== | + | rman nocatalog target / |
| - | < | + | run { |
| + | allocate channel c1 type sbt_tape parms ' | ||
| + | backup database; | ||
| + | } | ||
| + | </ | ||
| + | ==== An example of what Networker sends to Unix to be run on the command line ==== | ||
| + | < | ||
| + | / | ||
| + | -s hn6000.company.be \\ | ||
| + | -g CTI_2_ARCLOG_HN6014_LBK \\ | ||
| + | -LL \\ | ||
| + | -m hn512 \\ | ||
| + | -a DIRECT_ACCESS=No \\ | ||
| + | -l full | ||
| + | -q \\ | ||
| + | -W 78 \\ | ||
| + | -N RMAN:/ | ||
| + | rman send ' | ||
| + | | ||
| + | | ||
| + | | ||
| + | </code> | ||
| + | |||
| + | ==== Clone (or duplicate) a database ==== | ||
| + | * Connect to destination host | ||
| + | * Set environment for destination (auxiliary) SID - easiest way is make sure SID is in oratab | ||
| + | * Check sufficient disk space for datafiles | ||
| + | * Make a minimal init.ora file - depending on version, may need some memory parameters and compatible parameter in addition to db_name | ||
| + | * Add file_convert parameters to init.ora in case datafile location is different | ||
| + | * Make directories for audit, datafiles and archivelogs (if new db) | ||
| + | * Check source db is accessible via TNS (not necessary but useful depending on restore method) | ||
| + | * Check catalog is accessible via TNS (not necessary but useful depending on restore method) | ||
| + | * Create a password file (if new db) | ||
| + | * Shutdown destination db (if already existing) | ||
| + | * Delete destination db files (if already existing) | ||
| + | * Startup auxiliary instance in nomount mode referencing the minimal pfile | ||
| + | * Run an RMAN file like this one to clone the database. This one is specific to EMC (Legato) Networker | ||
| + | < | ||
| + | export NLS_LANG=american | ||
| + | export NLS_DATE_FORMAT=YYYY-MM-DD: | ||
| + | |||
| + | rman target sys/sys@${FROM_SID} catalog rman/rman@catalog_db auxiliary / | tee -a $HOME/ | ||
| + | |||
| + | run { | ||
| + | allocate auxiliary channel t1 type ' | ||
| + | allocate auxiliary channel t2 type ' | ||
| + | allocate auxiliary channel t3 type ' | ||
| + | allocate auxiliary channel t4 type ' | ||
| + | set until time ' | ||
| + | duplicate target database to ${TO_SID} | ||
| + | nofilenamecheck | ||
| + | spfile | ||
| + | parameter_value_convert '/ | ||
| + | set controlfiles='/ | ||
| + | set db_file_name_convert='/ | ||
| + | set log_file_name_convert='/ | ||
| + | ; | ||
| + | release channel t1; | ||
| + | release channel t2; | ||
| + | release channel t3; | ||
| + | release channel t4; | ||
| + | } | ||
| + | </ | ||
| + | ==== RMAN Recovery using the BACKUP DATABASE TO COPY command ==== | ||
| + | * Background reading: [[https:// | ||
| + | Also useful to understand te basic idea (without the incremental recovery): | ||
| + | * [[https:// | ||
| - | =====Clone (or duplicate) a database===== | ||
| - | * Connect to destination host | ||
| - | * Set environment for destination (auxiliary) SID - easiest way is make sure SID is in oratab | ||
| - | * Check sufficient disk space for datafiles | ||
| - | * Make a minimal init.ora file - depending on version, may need some memory parameters and compatible parameter in addition to db_name | ||
| - | * Add file_convert parameters to init.ora in case datafile location is different | ||
| - | * Make directories for audit, datafiles and archivelogs (if new db) | ||
| - | * Check source db is accessible via TNS (not necessary but useful depending on restore method) | ||
| - | * Check catalog is accessible via TNS (not necessary but useful depending on restore method) | ||
| - | * Create a password file (if new db) | ||
| - | * Shutdown destination db (if already existing) | ||
| - | * Delete destination db files (if already existing) | ||
| - | * Startup auxiliary instance in nomount mode referencing the minimal pfile | ||
| - | * Run an RMAN file like this one to clone the database. This one is specific to EMC (Legato) Networker | ||
| - | < | ||
| - | =====RMAN Recovery (Duplication) using the BACKUP DATABASE TO COPY command===== | ||
| - | * Background reading: [[https:// | ||
| There are several interesting reasons for using backup to copy instead of the default (backupset). Our scenario here is that: | There are several interesting reasons for using backup to copy instead of the default (backupset). Our scenario here is that: | ||
| - | * a 36Tb database has been shrunk to 3Tb and needs reorganising | + | * a 36Tb database has been shrunk to 3Tb and needs reorganising |
| - | * somehow the ckfs command shows that a filesystem needs repairing but the database is still working fine | + | * somehow the ckfs command shows that a filesystem needs repairing but the database is still working fine |
| - | * it is a production database and needs minimal downtime | + | * it is a production database and needs minimal downtime |
| - | ====During the day run the backup so that the files are ready for the intervention at night==== | + | === During the day run the backup so that the files are ready for the intervention at night === |
| - | A filesystem needs to be available with enough space to hold the (new) 3Tb database.<br /> | + | A filesystem needs to be available with enough space to hold the (new) 3Tb database.\\ |
| The %b format means just the filenames are used. | The %b format means just the filenames are used. | ||
| - | < | + | < |
| + | export NLS_DATE_FORMAT=' | ||
| + | </ | ||
| + | < | ||
| + | |||
| + | run | ||
| + | { | ||
| + | allocate channel c1 type disk format '/ | ||
| + | allocate channel c2 type disk format '/ | ||
| + | allocate channel c3 type disk format '/ | ||
| + | allocate channel c4 type disk format '/ | ||
| + | allocate channel c5 type disk format '/ | ||
| + | allocate channel c6 type disk format '/ | ||
| + | allocate channel c7 type disk format '/ | ||
| + | allocate channel c8 type disk format '/ | ||
| + | backup as copy database; | ||
| + | release channel c1; | ||
| + | release channel c2; | ||
| + | release channel c3; | ||
| + | release channel c4; | ||
| + | release channel c5; | ||
| + | release channel c6; | ||
| + | release channel c7; | ||
| + | release channel c8; | ||
| + | } | ||
| + | </ | ||
| This backup failed partially because someone in the past had added a few datafiles to a tablespace but appended a space to the end of the filename so we needed this additional step: | This backup failed partially because someone in the past had added a few datafiles to a tablespace but appended a space to the end of the filename so we needed this additional step: | ||
| The %U format means that a unique filename is generated for each file (but includes the file_id so it can be reassociated with its name). | The %U format means that a unique filename is generated for each file (but includes the file_id so it can be reassociated with its name). | ||
| - | < | + | < |
| + | run { | ||
| + | allocate channel c1 type disk format '/ | ||
| + | backup as copy datafile 170; | ||
| + | backup as copy datafile 171; | ||
| + | release channel c1; | ||
| + | } | ||
| + | </ | ||
| At intervention time, stop the applications accessing the database, stop the listeners and wait for any ongoing transactions to end. | At intervention time, stop the applications accessing the database, stop the listeners and wait for any ongoing transactions to end. | ||
| - | ====Protect the archivelogs==== | + | === Protect the archivelogs === |
| Run an archivelog backup sending any new logs to tape but don't delete them from disk as they will be needed for recovery. | Run an archivelog backup sending any new logs to tape but don't delete them from disk as they will be needed for recovery. | ||
| - | < | + | < |
| + | run { | ||
| + | allocate channel t1 type ' | ||
| + | crosscheck archivelog all; | ||
| + | backup format ' | ||
| + | release channel t1; | ||
| + | } | ||
| + | </ | ||
| - | ====Backup the controlfile==== | + | === Backup the controlfile === |
| - | < | + | < |
| + | run { | ||
| + | allocate channel c1 type disk; | ||
| + | backup as copy current controlfile format '/ | ||
| + | release channel c1; | ||
| + | } | ||
| + | </ | ||
| Take a text copy as well just in case... | Take a text copy as well just in case... | ||
| - | < | + | < |
| + | alter database backup controlfile to trace as ' | ||
| + | </ | ||
| - | ====Create a pfile if a recent one does not exist==== | + | === Create a pfile if a recent one does not exist === |
| - | < | + | < |
| + | create pfile from spfile; | ||
| + | </ | ||
| - | ====Protect the redo logs==== | + | === Protect the redo logs === |
| Copy one member of each group just in case... | Copy one member of each group just in case... | ||
| - | < | + | < |
| - | ====Edit the pfile==== | + | select 'cp -p ' |
| + | </ | ||
| + | === Edit the pfile === | ||
| Change any parameters that have a directory path to reflect the location of the new files. At the very least, change the control_files parameter. | Change any parameters that have a directory path to reflect the location of the new files. At the very least, change the control_files parameter. | ||
| - | ====Startup nomount==== | + | === Startup nomount === |
| Startup the instance using nomount and specify the modified pfile | Startup the instance using nomount and specify the modified pfile | ||
| - | < | + | < |
| - | ====Mount the database==== | + | startup nomount pfile=' |
| + | </ | ||
| + | === Mount the database === | ||
| If all has been modified correctly, monting the database should prove successful | If all has been modified correctly, monting the database should prove successful | ||
| - | < | + | < |
| - | ====Rename the datafiles in the controlfile==== | + | alter database mount |
| - | < | + | </ |
| - | ====Check all files have been renamed and that there are no spaces in the names!==== | + | === Rename the datafiles in the controlfile === |
| - | < | + | < |
| - | ====Disable block change tracking if it was previously enabled==== | + | set lines 1000 pages 0 trims on feed off |
| - | < | + | col txt for a500 |
| + | spool rename_files_in_controlfile.sql | ||
| + | select 'alter database rename file ''' | ||
| + | spool off | ||
| + | host perl -p -i -e 'print if m/ | ||
| + | @rename_files_in_controlfile | ||
| + | host rm rename_files_in_controlfile.sql | ||
| + | </ | ||
| + | === Check all files have been renamed and that there are no spaces in the names! === | ||
| + | < | ||
| + | select name from v$datafile where name not like '/ | ||
| + | select name from v$datafile where name like '% %'; | ||
| + | </ | ||
| + | === Disable block change tracking if it was previously enabled === | ||
| + | < | ||
| + | alter database disable block change tracking; | ||
| + | </ | ||
| - | ====Start the recovery process==== | + | === Start the recovery process === |
| - | <code> | + | < |
| - | ====Moment of truth==== | + | export NLS_DATE_FORMAT=" |
| - | < | + | |
| - | ====Post recovery checks==== | + | |
| - | < | + | |
| - | =====Script to check if sufficient space is available on the destination (auxiliary) filesystem to hold a duplicate copy of the source (target) | + | run { |
| - | < | + | allocate channel c1 device type disk; |
| + | recover database; | ||
| + | release channel c1; | ||
| + | } | ||
| + | </ | ||
| + | === Moment of truth === | ||
| + | < | ||
| + | alter database | ||
| + | </ | ||
| + | === Post recovery checks | ||
| + | < | ||
| + | set lines 1000 pages 1000 | ||
| + | col file_name for a60 | ||
| + | select file_name, | ||
| + | select * from v$tempfile; | ||
| + | select member from v$logfile; | ||
| + | </ | ||
| - | =====Backup database to disk, scp files and duplicate on different host===== | + | ==== RMAN Recovery using the RECOVER COPY OF DATABASE command (allows incremental recovery) |
| + | This is a clever way of restoring a large database quickly with not too much downtime. It could be used as a permanent rolling forward of a database in case of a crash. | ||
| + | |||
| + | The backup is based on image copies not on backupsets. | ||
| + | < | ||
| + | run { | ||
| + | recover copy of database with tag ' | ||
| + | backup incremental level 1 for recover of copy with tag ' | ||
| + | database; | ||
| + | } | ||
| + | </ | ||
| + | Merged incremental backups can reduce the time for Oracle database recovery and are suitable primarily for a disk based backup and recovery strategy primarily because the datafile images need to be updated. | ||
| + | |||
| + | The first time the RMAN script runs it creates a level 0 backup of the datafile. | ||
| + | |||
| + | On subsequent runs, it creates level 1 incremental backups of the datafiles, by using the System Change Number as a reference, and applies all level 1 backups taken since the last full backup to the image. | ||
| + | |||
| + | The RMAN script uses the tag to link the incremental backups to the image copy. | ||
| + | |||
| + | '' | ||
| + | |||
| + | The syntax used in the script does not, however, make it clear how the strategy works. | ||
| + | |||
| + | To understand the script and the strategy, it is necessary to understand the effects of these two commands when no datafile copies or incremental backups exist. | ||
| + | * The RECOVER COPY OF DATABASE WITH TAG... command causes RMAN to apply any available incremental level 1 backups to a set of datafile copies with the specified tag. | ||
| + | * If there is no incremental backup or no datafile copy, the command generates a message but does not generate an error. | ||
| + | * The first time the script runs, this command has no effect, because there is neither a datafile copy nor a level 1 incremental backup. | ||
| + | * The second time the script runs, there is a datafile copy (created by the first BACKUP command), but no incremental level 1 backup, so again, the command has no effect. | ||
| + | * On the third run and all subsequent runs, there is a datafile copy and a level 1 incremental from the previous run, so the level 1 incremental is applied to the datafile copy, bringing the datafile copy up to the checkpoint SCN of the level 1 incremental. | ||
| + | * The BACKUP INCREMENTAL LEVEL 1... FOR RECOVER OF COPY WITH TAG... command does not actually always create a level 1 incremental backup. If there is no level 0 image copy backup of an particular datafile, then executing this command creates an image copy backup of the datafile on disk with the specified tag instead of creating the level 1 backup.\\ | ||
| + | Note: Even when the BACKUP INCREMENTAL LEVEL 1 ... FOR RECOVER OF COPY command is used with DEVICE TYPE SBT to create a backup on tape, the first time it is used it creates the image copy on disk, and does not write any backup on tape. Subsequent incremental level 1 backups can be created on tape once the image copy is on disk. | ||
| + | * Thus, the first time the script runs, it creates the image copy of the datafile needed to begin the cycle of incremental updates. In the second run and all subsequent runs, it produces level 1 incremental backups of the datafile. | ||
| + | * Each time a datafile is added to the database, an image copy of the new datafile is created the next time the script runs. The time after that, the first level 1 incremental for that datafile is created, and on all subsequent runs the new datafile is processed like any other datafile. | ||
| + | * Tags must be used to identify the incremental level 0 datafile copies created for use in this strategy, so that they do not interfere with other backup strategies you implement. If you have multiple incremental backup strategies in effect, RMAN cannot unambiguously create incremental level 1 backups unless you tag level 0 backups. | ||
| + | * The incremental level 1 backups to apply to those image copies are selected based upon the checkpoint SCNs of the image copy datafiles and the available incremental level 1 backups. (The tag used on the image copy being recovered is not a factor in the selection of the incremental level backups.) | ||
| + | * In practice, you would schedule the example script to run once each day, possibly at midnight. On a typical night (that is, after the first two nights), when the script completed the following files would be available for a point-in-time recovery: | ||
| + | * An image copy of the database, as of the checkpoint SCN of the preceding run of the script, 24 hours earlier | ||
| + | * An incremental backup for the changes since the checkpoint SCN of preceding run | ||
| + | * Archived redo logs including all changes between the checkpoint SCN of the image copy and the current time | ||
| + | * If, at some point during the following 24 hours, you need to restore and recover your database from this backup, for either complete or point-in-time recovery, you can restore the datafiles from the incrementally updated datafile copies, and apply changes from the most recent incremental level 1 and the redo logs to reach the desired SCN. At most, you will have 24 hours of redo to apply, which limits how long point-in-time recovery will take. | ||
| + | |||
| + | === Practical example === | ||
| + | A 5Tb database needs to be moved to a different filesystem. Using this method, we can reduce the downtime to minutes instead of a day! | ||
| + | |||
| + | This is the script. | ||
| + | < | ||
| + | # | ||
| + | # ============================================================================== | ||
| + | # Name : incremental_restore_as_copy.ksh | ||
| + | # Description | ||
| + | # | ||
| + | # Parameters | ||
| + | # -f < | ||
| + | # | ||
| + | # Example | ||
| + | # | ||
| + | # Modification History | ||
| + | # ==================== | ||
| + | # When Who | ||
| + | # ========= ================= ================================================== | ||
| + | # 19-FEB-19 Stuart Barkley | ||
| + | # ============================================================================== | ||
| + | |||
| + | PROGNAME=" | ||
| + | |||
| + | # | ||
| + | # Reference: | ||
| + | # https:// | ||
| + | # https:// | ||
| + | # | ||
| + | |||
| + | function usage { | ||
| + | echo | ||
| + | echo " | ||
| + | echo "eg: ${PROGNAME} -i adst -f / | ||
| + | echo | ||
| + | exit 1 | ||
| + | } | ||
| + | |||
| + | |||
| + | # ------------------ | ||
| + | # get the parameters | ||
| + | # ------------------ | ||
| + | while getopts " | ||
| + | do | ||
| + | case " | ||
| + | i) ORACLE_SID=" | ||
| + | ;; | ||
| + | f) FILESYSTEM=" | ||
| + | ;; | ||
| + | *) usage | ||
| + | ;; | ||
| + | esac | ||
| + | done | ||
| + | shift $((OPTIND-1)) | ||
| + | |||
| + | [[ " | ||
| + | [[ ! -d " | ||
| + | |||
| + | |||
| + | export NLS_DATE_FORMAT=' | ||
| + | ORAENV_ASK=NO | ||
| + | grep " | ||
| + | [[ $? -ne 0 ]] && echo " | ||
| + | . oraenv >/ | ||
| + | [[ $? -ne 0 ]] && echo " | ||
| + | |||
| + | cat<< | ||
| + | run | ||
| + | { | ||
| + | allocate channel d1 type disk format ' | ||
| + | allocate channel d2 type disk format ' | ||
| + | allocate channel d3 type disk format ' | ||
| + | allocate channel d4 type disk format ' | ||
| + | allocate channel d5 type disk format ' | ||
| + | allocate channel d6 type disk format ' | ||
| + | allocate channel d7 type disk format ' | ||
| + | allocate channel d8 type disk format ' | ||
| + | |||
| + | recover copy of database with tag " | ||
| + | |||
| + | delete noprompt obsolete recovery window of 2 days device type disk; | ||
| + | |||
| + | backup incremental level 1 | ||
| + | for recover of copy | ||
| + | with tag " | ||
| + | format ' | ||
| + | database; | ||
| + | |||
| + | backup as compressed backupset | ||
| + | tag ' | ||
| + | archivelog all not backed up 1 times | ||
| + | format ' | ||
| + | |||
| + | backup as compressed backupset | ||
| + | tag ' | ||
| + | current controlfile | ||
| + | format ' | ||
| + | |||
| + | backup as compressed backupset | ||
| + | tag ' | ||
| + | spfile | ||
| + | format ' | ||
| + | |||
| + | release channel d1; | ||
| + | release channel d2; | ||
| + | release channel d3; | ||
| + | release channel d4; | ||
| + | release channel d5; | ||
| + | release channel d6; | ||
| + | release channel d7; | ||
| + | release channel d8; | ||
| + | } | ||
| + | EOCAT | ||
| + | |||
| + | rman target / cmdfile="/ | ||
| + | |||
| + | </ | ||
| + | This is the log of the first run... | ||
| + | < | ||
| + | rman target / cmdfile=/ | ||
| + | |||
| + | Recovery Manager: Release 12.1.0.2.0 - Production on Thu Apr 21 10:47:15 2022 | ||
| + | |||
| + | Copyright (c) 1982, 2014, Oracle and/or its affiliates. | ||
| + | |||
| + | connected to target database: ADST (DBID=3477316030) | ||
| + | |||
| + | RMAN> run | ||
| + | 2> { | ||
| + | 3> | ||
| + | 4> | ||
| + | 5> | ||
| + | 6> | ||
| + | 7> | ||
| + | 8> | ||
| + | 9> | ||
| + | 10> | ||
| + | 11> | ||
| + | 12> | ||
| + | 13> | ||
| + | 14> | ||
| + | 15> | ||
| + | 16> | ||
| + | 17> | ||
| + | 18> | ||
| + | 19> | ||
| + | 20> | ||
| + | 21> | ||
| + | 22> | ||
| + | 23> | ||
| + | 24> | ||
| + | 25> } | ||
| + | 26> | ||
| + | using target database control file instead of recovery catalog | ||
| + | allocated channel: d1 | ||
| + | channel d1: SID=771 device type=DISK | ||
| + | |||
| + | allocated channel: d2 | ||
| + | channel d2: SID=820 device type=DISK | ||
| + | |||
| + | allocated channel: d3 | ||
| + | channel d3: SID=962 device type=DISK | ||
| + | |||
| + | allocated channel: d4 | ||
| + | channel d4: SID=1011 device type=DISK | ||
| + | |||
| + | allocated channel: d5 | ||
| + | channel d5: SID=1108 device type=DISK | ||
| + | |||
| + | allocated channel: d6 | ||
| + | channel d6: SID=1155 device type=DISK | ||
| + | |||
| + | allocated channel: d7 | ||
| + | channel d7: SID=1203 device type=DISK | ||
| + | |||
| + | allocated channel: d8 | ||
| + | channel d8: SID=1253 device type=DISK | ||
| + | |||
| + | Starting recover at 21-APR-22 10:47:20 | ||
| + | no copy of datafile 1 found to recover | ||
| + | no copy of datafile 2 found to recover | ||
| + | no copy of datafile 3 found to recover | ||
| + | no copy of datafile 4 found to recover | ||
| + | no copy of datafile 5 found to recover | ||
| + | no copy of datafile 6 found to recover | ||
| + | no copy of datafile 7 found to recover | ||
| + | no copy of datafile 8 found to recover | ||
| + | no copy of datafile 9 found to recover | ||
| + | ... | ||
| + | ... | ||
| + | no copy of datafile 320 found to recover | ||
| + | no copy of datafile 321 found to recover | ||
| + | no copy of datafile 322 found to recover | ||
| + | no copy of datafile 323 found to recover | ||
| + | no copy of datafile 324 found to recover | ||
| + | no copy of datafile 325 found to recover | ||
| + | Finished recover at 21-APR-22 10:47:25 | ||
| + | |||
| + | Starting backup at 21-APR-22 10:47:25 | ||
| + | no parent backup or copy of datafile 3 found | ||
| + | no parent backup or copy of datafile 6 found | ||
| + | no parent backup or copy of datafile 7 found | ||
| + | no parent backup or copy of datafile 8 found | ||
| + | no parent backup or copy of datafile 10 found | ||
| + | no parent backup or copy of datafile 11 found | ||
| + | no parent backup or copy of datafile 14 found | ||
| + | no parent backup or copy of datafile 41 found | ||
| + | ... | ||
| + | ... | ||
| + | no parent backup or copy of datafile 319 found | ||
| + | no parent backup or copy of datafile 146 found | ||
| + | no parent backup or copy of datafile 147 found | ||
| + | no parent backup or copy of datafile 170 found | ||
| + | no parent backup or copy of datafile 12 found | ||
| + | channel d1: starting datafile copy | ||
| + | input datafile file number=00003 name=/ | ||
| + | channel d2: starting datafile copy | ||
| + | input datafile file number=00006 name=/ | ||
| + | channel d3: starting datafile copy | ||
| + | input datafile file number=00007 name=/ | ||
| + | channel d4: starting datafile copy | ||
| + | input datafile file number=00008 name=/ | ||
| + | channel d5: starting datafile copy | ||
| + | input datafile file number=00010 name=/ | ||
| + | channel d6: starting datafile copy | ||
| + | input datafile file number=00011 name=/ | ||
| + | channel d7: starting datafile copy | ||
| + | input datafile file number=00014 name=/ | ||
| + | channel d8: starting datafile copy | ||
| + | input datafile file number=00041 name=/ | ||
| + | output file name=/ | ||
| + | channel d7: datafile copy complete, elapsed time: 00:07:36 | ||
| + | channel d7: starting datafile copy | ||
| + | input datafile file number=00087 name=/ | ||
| + | output file name=/ | ||
| + | channel d1: datafile copy complete, elapsed time: 00:07:51 | ||
| + | channel d1: starting datafile copy | ||
| + | input datafile file number=00181 name=/ | ||
| + | output file name=/ | ||
| + | channel d2: datafile copy complete, elapsed time: 00:07:51 | ||
| + | channel d2: starting datafile copy | ||
| + | input datafile file number=00182 name=/ | ||
| + | output file name=/ | ||
| + | channel d3: datafile copy complete, elapsed time: 00:07:51 | ||
| + | channel d3: starting datafile copy | ||
| + | input datafile file number=00183 name=/ | ||
| + | output file name=/ | ||
| + | channel d4: datafile copy complete, elapsed time: 00:07:51 | ||
| + | channel d4: starting datafile copy | ||
| + | input datafile file number=00184 name=/ | ||
| + | output file name=/ | ||
| + | channel d5: datafile copy complete, elapsed time: 00:07:51 | ||
| + | channel d5: starting datafile copy | ||
| + | input datafile file number=00185 name=/ | ||
| + | output file name=/ | ||
| + | channel d6: datafile copy complete, elapsed time: 00:07:51 | ||
| + | channel d6: starting datafile copy | ||
| + | input datafile file number=00192 name=/ | ||
| + | output file name=/ | ||
| + | channel d8: datafile copy complete, elapsed time: 00:07:52 | ||
| + | channel d8: starting datafile copy | ||
| + | input datafile file number=00193 name=/ | ||
| + | output file name=/ | ||
| + | channel d7: datafile copy complete, elapsed time: 00:09:21 | ||
| + | channel d7: starting datafile copy | ||
| + | input datafile file number=00200 name=/ | ||
| + | output file name=/ | ||
| + | ... | ||
| + | ... | ||
| + | channel d4: datafile copy complete, elapsed time: 00:01:23 | ||
| + | output file name=/ | ||
| + | channel d3: datafile copy complete, elapsed time: 00:01:43 | ||
| + | output file name=/ | ||
| + | channel d2: datafile copy complete, elapsed time: 00:05:23 | ||
| + | output file name=/ | ||
| + | channel d1: datafile copy complete, elapsed time: 00:05:34 | ||
| + | Finished backup at 21-APR-22 13:21:30 | ||
| + | |||
| + | Starting Control File and SPFILE Autobackup at 21-APR-22 13:21:30 | ||
| + | piece handle=/ | ||
| + | Finished Control File and SPFILE Autobackup at 21-APR-22 13:21:33 | ||
| + | |||
| + | released channel: d1 | ||
| + | |||
| + | released channel: d2 | ||
| + | |||
| + | released channel: d3 | ||
| + | |||
| + | released channel: d4 | ||
| + | |||
| + | released channel: d5 | ||
| + | |||
| + | released channel: d6 | ||
| + | |||
| + | released channel: d7 | ||
| + | |||
| + | released channel: d8 | ||
| + | |||
| + | Recovery Manager complete. | ||
| + | </ | ||
| + | |||
| + | It starts the recover and reports "no copy of datafile n found to recover" | ||
| + | |||
| + | It then starts the backup for recover. There are messages saying "no parent backup or copy of datafile n found" | ||
| + | |||
| + | === Run 2 === | ||
| + | The exact same shell is run in all cases. | ||
| + | |||
| + | < | ||
| + | Recovery Manager: Release 12.1.0.2.0 - Production on Fri Apr 22 15:23:33 2022 | ||
| + | |||
| + | Copyright (c) 1982, 2014, Oracle and/or its affiliates. | ||
| + | |||
| + | connected to target database: ADST (DBID=3477316030) | ||
| + | |||
| + | RMAN> run | ||
| + | 2> { | ||
| + | 3> | ||
| + | 4> | ||
| + | 5> | ||
| + | 6> | ||
| + | 7> | ||
| + | 8> | ||
| + | 9> | ||
| + | 10> | ||
| + | 11> | ||
| + | 12> | ||
| + | 13> | ||
| + | 14> | ||
| + | 15> | ||
| + | 16> | ||
| + | 17> | ||
| + | 18> | ||
| + | 19> | ||
| + | 20> | ||
| + | 21> | ||
| + | 22> | ||
| + | 23> | ||
| + | 24> | ||
| + | 25> } | ||
| + | 26> | ||
| + | using target database control file instead of recovery catalog | ||
| + | allocated channel: d1 | ||
| + | channel d1: SID=1253 device type=DISK | ||
| + | |||
| + | allocated channel: d2 | ||
| + | channel d2: SID=1299 device type=DISK | ||
| + | |||
| + | allocated channel: d3 | ||
| + | channel d3: SID=1348 device type=DISK | ||
| + | |||
| + | allocated channel: d4 | ||
| + | channel d4: SID=1395 device type=DISK | ||
| + | |||
| + | allocated channel: d5 | ||
| + | channel d5: SID=1443 device type=DISK | ||
| + | |||
| + | allocated channel: d6 | ||
| + | channel d6: SID=1490 device type=DISK | ||
| + | |||
| + | allocated channel: d7 | ||
| + | channel d7: SID=5 device type=DISK | ||
| + | |||
| + | allocated channel: d8 | ||
| + | channel d8: SID=51 device type=DISK | ||
| + | |||
| + | Starting recover at 22-APR-2022 15:23:38 | ||
| + | no copy of datafile 1 found to recover | ||
| + | no copy of datafile 2 found to recover | ||
| + | no copy of datafile 3 found to recover | ||
| + | no copy of datafile 4 found to recover | ||
| + | no copy of datafile 5 found to recover | ||
| + | no copy of datafile 6 found to recover | ||
| + | no copy of datafile 7 found to recover | ||
| + | no copy of datafile 8 found to recover | ||
| + | no copy of datafile 9 found to recover | ||
| + | no copy of datafile 10 found to recover | ||
| + | no copy of datafile 11 found to recover | ||
| + | no copy of datafile 12 found to recover | ||
| + | ... | ||
| + | ... | ||
| + | no copy of datafile 320 found to recover | ||
| + | no copy of datafile 321 found to recover | ||
| + | no copy of datafile 322 found to recover | ||
| + | no copy of datafile 323 found to recover | ||
| + | no copy of datafile 324 found to recover | ||
| + | no copy of datafile 325 found to recover | ||
| + | Finished recover at 22-APR-2022 15:23:43 | ||
| + | |||
| + | Starting backup at 22-APR-2022 15:23:43 | ||
| + | channel d1: starting incremental level 1 datafile backup set | ||
| + | channel d1: specifying datafile(s) in backup set | ||
| + | input datafile file number=00011 name=/ | ||
| + | input datafile file number=00185 name=/ | ||
| + | input datafile file number=00209 name=/ | ||
| + | input datafile file number=00249 name=/ | ||
| + | input datafile file number=00282 name=/ | ||
| + | input datafile file number=00025 name=/ | ||
| + | input datafile file number=00033 name=/ | ||
| + | input datafile file number=00042 name=/ | ||
| + | input datafile file number=00050 name=/ | ||
| + | input datafile file number=00058 name=/ | ||
| + | input datafile file number=00066 name=/ | ||
| + | input datafile file number=00112 name=/ | ||
| + | input datafile file number=00120 name=/ | ||
| + | input datafile file number=00296 name=/ | ||
| + | input datafile file number=00313 name=/ | ||
| + | input datafile file number=00323 name=/ | ||
| + | input datafile file number=00173 name=/ | ||
| + | input datafile file number=00285 name=/ | ||
| + | input datafile file number=00308 name=/ | ||
| + | input datafile file number=00301 name=/ | ||
| + | channel d1: starting piece 1 at 22-APR-2022 15:23:48 | ||
| + | channel d2: starting incremental level 1 datafile backup set | ||
| + | channel d2: specifying datafile(s) in backup set | ||
| + | input datafile file number=00007 name=/ | ||
| + | input datafile file number=00182 name=/ | ||
| + | input datafile file number=00202 name=/ | ||
| + | input datafile file number=00246 name=/ | ||
| + | input datafile file number=00261 name=/ | ||
| + | input datafile file number=00022 name=/ | ||
| + | input datafile file number=00030 name=/ | ||
| + | input datafile file number=00038 name=/ | ||
| + | input datafile file number=00047 name=/ | ||
| + | input datafile file number=00055 name=/ | ||
| + | input datafile file number=00063 name=/ | ||
| + | ... | ||
| + | ... | ||
| + | input datafile file number=00111 name=/ | ||
| + | input datafile file number=00119 name=/ | ||
| + | input datafile file number=00295 name=/ | ||
| + | input datafile file number=00312 name=/ | ||
| + | input datafile file number=00322 name=/ | ||
| + | input datafile file number=00172 name=/ | ||
| + | input datafile file number=00175 name=/ | ||
| + | input datafile file number=00305 name=/ | ||
| + | input datafile file number=00147 name=/ | ||
| + | input datafile file number=00012 name=/ | ||
| + | channel d8: starting piece 1 at 22-APR-2022 15:23:51 | ||
| + | channel d2: finished piece 1 at 22-APR-2022 16:09:37 | ||
| + | piece handle=/ | ||
| + | channel d2: backup set complete, elapsed time: 00:45:48 | ||
| + | channel d1: finished piece 1 at 22-APR-2022 16:09:47 | ||
| + | piece handle=/ | ||
| + | channel d1: backup set complete, elapsed time: 00:45:59 | ||
| + | channel d3: finished piece 1 at 22-APR-2022 16:09:47 | ||
| + | piece handle=/ | ||
| + | channel d3: backup set complete, elapsed time: 00:45:58 | ||
| + | channel d5: finished piece 1 at 22-APR-2022 16:09:47 | ||
| + | piece handle=/ | ||
| + | channel d5: backup set complete, elapsed time: 00:45:57 | ||
| + | channel d4: finished piece 1 at 22-APR-2022 16:09:57 | ||
| + | piece handle=/ | ||
| + | channel d4: backup set complete, elapsed time: 00:46:08 | ||
| + | channel d6: finished piece 1 at 22-APR-2022 16:09:57 | ||
| + | piece handle=/ | ||
| + | channel d6: backup set complete, elapsed time: 00:46:07 | ||
| + | channel d8: finished piece 1 at 22-APR-2022 16:10:47 | ||
| + | piece handle=/ | ||
| + | channel d8: backup set complete, elapsed time: 00:46:56 | ||
| + | channel d7: finished piece 1 at 22-APR-2022 16:11:17 | ||
| + | piece handle=/ | ||
| + | channel d7: backup set complete, elapsed time: 00:47:26 | ||
| + | Finished backup at 22-APR-2022 16:11:17 | ||
| + | |||
| + | Starting Control File and SPFILE Autobackup at 22-APR-2022 16:11:17 | ||
| + | piece handle=/ | ||
| + | Finished Control File and SPFILE Autobackup at 22-APR-2022 16:11:20 | ||
| + | |||
| + | released channel: d1 | ||
| + | |||
| + | released channel: d2 | ||
| + | |||
| + | released channel: d3 | ||
| + | |||
| + | released channel: d4 | ||
| + | |||
| + | released channel: d5 | ||
| + | |||
| + | released channel: d6 | ||
| + | |||
| + | released channel: d7 | ||
| + | |||
| + | released channel: d8 | ||
| + | |||
| + | Recovery Manager complete. | ||
| + | |||
| + | </ | ||
| + | |||
| + | The recover again starts with “no copy of datafile n found to recover”. This is normal as this is the second run. The first it ran, it created a level 0 backup. This time it created a level 1 backup. So next time (as of run 3) the recover command will have something to apply (the level 1 backup from the previous run). | ||
| + | |||
| + | It then starts the backup for recover. This time the level 1 backup works correctly as we created the level 0 backup last time. | ||
| + | |||
| + | === Run 3 and greater === | ||
| + | From now on, the script runs without error. The recover applies the backup from the previous run to the datafile copies bringing them up-to-date with the changes that happened during the level 1 backup. | ||
| + | |||
| + | Then the (next) level1 incremental backup runs. These will be applied tomorrow (or whenever the script is run again). | ||
| + | |||
| + | '' | ||
| + | |||
| + | * Stop access to the database | ||
| + | * Rerun the (complete) script one last time to recover the datafiles using the previous incrementals. But running again also means a new set of incrementals will be generated with the (final) changes. | ||
| + | These need to be applied using the first part of the script (the recover) but the backup after that will not be needed. | ||
| + | |||
| + | |||
| + | |||
| + | ==== Script to check if sufficient space is available on the destination (auxiliary) filesystem to hold a duplicate copy of the source (target) database ==== | ||
| + | < | ||
| + | - !/ | ||
| + | - ============================================================================== | ||
| + | - Name : db_space_calculation.ksh | ||
| + | - Description | ||
| + | - | ||
| + | - | ||
| + | - Parameters | ||
| + | - | ||
| + | - | ||
| + | - Modification History | ||
| + | - ==================== | ||
| + | - When Who | ||
| + | - ========= ================= ================================================== | ||
| + | - 18-JUL-17 Stuart Barkley | ||
| + | - 17-AUG-17 Stuart Barkley | ||
| + | - 21-AUG-17 Stuart Barkley | ||
| + | - ============================================================================== | ||
| + | - | ||
| + | PROGNAME=$(basename $0) | ||
| + | - increment this every time it changes | ||
| + | VERSION=0.4.3 | ||
| + | |||
| + | |||
| + | printf "\ | ||
| + | %s\ | ||
| + | " " | ||
| + | printf " | ||
| + | read FROM_SID | ||
| + | printf " | ||
| + | read FROM_SYS_PASSWORD | ||
| + | printf " | ||
| + | read TO_SID | ||
| + | |||
| + | - ---------------------------------------------------------------------- | ||
| + | - if destination database exists, we can use it to get datafile location | ||
| + | - ---------------------------------------------------------------------- | ||
| + | grep " | ||
| + | RETVAL=$? | ||
| + | if [[ ${RETVAL} -eq 0 ]]; then | ||
| + | ORAENV_ASK=NO | ||
| + | export ORACLE_SID=${TO_SID} | ||
| + | . oraenv >/ | ||
| + | RETVAL=$((RETVAL+$? | ||
| + | fi | ||
| + | if [[ ${RETVAL} -ne 0 ]]; then | ||
| + | # destination database does not yet exist, ask user | ||
| + | printf " | ||
| + | read TO_DATA_DIR | ||
| + | fi | ||
| + | |||
| + | |||
| + | |||
| + | function calc { awk "BEGIN { print $* }"; } | ||
| + | |||
| + | |||
| + | - -------------------------------------- | ||
| + | - get host name of the FROM_SID database | ||
| + | - -------------------------------------- | ||
| + | FROM_HOST=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | select host_name | ||
| + | from | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | if [[ $? -ne 0 ]]; then | ||
| + | printf "\ | ||
| + | %s\ | ||
| + | " " | ||
| + | cat / | ||
| + | exit 1 | ||
| + | fi | ||
| + | sleep 1 | ||
| + | |||
| + | |||
| + | - -------------------------------------------------------- | ||
| + | printf "\ | ||
| + | %s" | ||
| + | - -------------------------------------------------------- | ||
| + | DATABASE_SIZE=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | with used_space as | ||
| + | ( | ||
| + | select | ||
| + | from v$datafile | ||
| + | union all | ||
| + | select | ||
| + | from v$tempfile | ||
| + | union all | ||
| + | select | ||
| + | from v$log | ||
| + | ) | ||
| + | , free_space as | ||
| + | ( | ||
| + | select sum(bytes) sum_bytes | ||
| + | from dba_free_space | ||
| + | ) | ||
| + | select trim(round(sum(used_space.bytes)/ | ||
| + | from | ||
| + | , used_space | ||
| + | group by free_space.sum_bytes | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | if [[ $? -ne 0 ]]; then | ||
| + | echo " ...NOK" | ||
| + | printf "\ | ||
| + | %s\ | ||
| + | " " | ||
| + | cat / | ||
| + | exit 1 | ||
| + | else | ||
| + | echo " ...OK (${DATABASE_SIZE} Kb)" && rm -f / | ||
| + | fi | ||
| + | sleep 1 | ||
| + | |||
| + | |||
| + | - --------------------- | ||
| + | - get datafile location | ||
| + | - --------------------- | ||
| + | if [[ " | ||
| + | # it was not filled in manually, destination must exist | ||
| + | sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | select distinct substr(name, | ||
| + | EOSQL | ||
| + | fi | ||
| + | |||
| + | |||
| + | - ---------------------------------------------------------------- | ||
| + | printf " | ||
| + | - ---------------------------------------------------------------- | ||
| + | if [[ " | ||
| + | SPACE_AVAILABLE=$(df -k $TO_DATA_DIR | sed 1d | awk 'BEGIN {i=0} {i=i+$3} END {print i}') | ||
| + | else | ||
| + | # Linux, Darwin, Solaris | ||
| + | SPACE_AVAILABLE=$(df -k $TO_DATA_DIR | sed 1d | awk 'BEGIN {i=0} {i=i+$4} END {print i}') | ||
| + | fi | ||
| + | |||
| + | SPACE_USED=$(du -ks ${TO_DATA_DIR} | awk 'BEGIN {i=0;} {i=i+$1} END {print i}') | ||
| + | |||
| + | - do the calculations in the database to avoid scientific notation issues | ||
| + | sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | select sign(${SPACE_USED} + ${SPACE_AVAILABLE} - ${DATABASE_SIZE}), | ||
| + | from dual | ||
| + | / | ||
| + | EOSQL | ||
| + | |||
| + | if [[ ${SPACE_AFTER_INSTALL} -le 0 ]]; then | ||
| + | echo " ...NOK" | ||
| + | printf "\ | ||
| + | %s\ | ||
| + | " " | ||
| + | echo "Size of database ${FROM_SID} : ${DATABASE_SIZE} Kb" | ||
| + | echo "Space available for ${TO_SID}: ${TOTAL_SPACE_AVAILABLE} Kb" | ||
| + | exit 1 | ||
| + | else | ||
| + | echo " ...OK, space is sufficient" | ||
| + | fi | ||
| + | |||
| + | sleep 1 | ||
| + | printf "\ | ||
| + | %s\ | ||
| + | " "other bits useful for a sync..." | ||
| + | sleep 1 | ||
| + | - ------------------------------------------------ | ||
| + | - find the audit file area on source, if available | ||
| + | - ------------------------------------------------ | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select value from v$parameter where name = ' | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | echo " | ||
| + | |||
| + | - ------------------------------------------------- | ||
| + | - find the archive log area on source, if available | ||
| + | - ------------------------------------------------- | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select value from v$parameter where name = ' | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | RETVAL=$(echo ${RETVAL}|sed -e ' | ||
| + | echo " | ||
| + | |||
| + | |||
| + | - ------------------------------------------------------- | ||
| + | - work out the RMAN connection details and Networker pool | ||
| + | - ------------------------------------------------------- | ||
| + | ssh ${FROM_HOST} "cat / | ||
| + | if [[ $? -eq 0 ]]; then | ||
| + | TAPE_POOL=$(cat / | ||
| + | echo " | ||
| + | FROM_RMAN=$(cat / | ||
| + | FROM_RMANCAT_PWD=$2\ | ||
| + | FROM_RMANCAT=$3\ | ||
| + | " if / | ||
| + | echo " | ||
| + | else | ||
| + | echo "Could not connect to ${FROM_HOST} to get the RMAN catalog and Networker pool name. Please check manually" | ||
| + | fi | ||
| + | |||
| + | |||
| + | - ----------------------------------------------------- | ||
| + | - find the audit file area on destination, | ||
| + | - ----------------------------------------------------- | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select value from v$parameter where name = ' | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | echo " | ||
| + | |||
| + | - ------------------------------------------------------ | ||
| + | - find the archive log area on destination, | ||
| + | - ------------------------------------------------------ | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 lines 1000 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select value from v$parameter where name = ' | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | RETVAL=$(echo ${RETVAL}|sed -e ' | ||
| + | echo " | ||
| + | |||
| + | - -------------------------------------------------- | ||
| + | - find out where the controlfiles are on destination | ||
| + | - -------------------------------------------------- | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 lines 1000 headi off newpa none feedb off | ||
| + | col value for a300 | ||
| + | select value from v$parameter where name = ' | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | echo " | ||
| + | |||
| + | - ------------------------------------------------ | ||
| + | - work out the DB_FILE_NAME_CONVERT RMAN parameter | ||
| + | - ------------------------------------------------ | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select distinct substr(name, | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | DB_FILE_NAME_CONVERT="' | ||
| + | |||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select distinct substr(name, | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | echo DB_FILE_NAME_CONVERT=" | ||
| + | |||
| + | |||
| + | - ------------------------------------------------- | ||
| + | - work out the LOG_FILE_NAME_CONVERT RMAN parameter | ||
| + | - ------------------------------------------------- | ||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select distinct substr(member, | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | LOG_FILE_NAME_CONVERT="' | ||
| + | |||
| + | RETVAL=`sqlplus -s sys/ | ||
| + | set numwid 15 headi off newpa none feedb off | ||
| + | col value for a100 | ||
| + | select distinct substr(member, | ||
| + | / | ||
| + | EOSQL | ||
| + | ` | ||
| + | echo LOG_FILE_NAME_CONVERT=" | ||
| + | |||
| + | </ | ||
| + | |||
| + | ==== RMAN backup | ||
| On source host | On source host | ||
| - | < | + | < |
| - | < | + | sqlplus / as sysdba |
| - | < | + | create pfile='/ |
| + | exit | ||
| + | </ | ||
| + | < | ||
| + | - !/ | ||
| + | ORAENV_ASK=NO | ||
| + | export ORACLE_SID=sourcesid | ||
| + | . oraenv | ||
| + | export NLS_DATE_FORMAT=" | ||
| + | rman nocatalog target /<< | ||
| + | configure controlfile autobackup on; | ||
| + | configure device type disk parallelism 4; | ||
| + | configure maxsetsize to unlimited; | ||
| + | run { | ||
| + | allocate channel c1 device type disk maxpiecesize 2048M; | ||
| + | allocate channel c2 device type disk maxpiecesize 2048M; | ||
| + | allocate channel c3 device type disk maxpiecesize 2048M; | ||
| + | allocate channel c4 device type disk maxpiecesize 2048M; | ||
| + | backup as compressed backupset database format "/ | ||
| + | backup as compressed backupset archivelog all format "/ | ||
| + | backup as compressed backupset current controlfile format "/ | ||
| + | release channel c1; | ||
| + | release channel c2; | ||
| + | release channel c3; | ||
| + | release channel c4; | ||
| + | } | ||
| + | EORMAN | ||
| + | </ | ||
| + | < | ||
| + | scp /backup/* dest_server:/ | ||
| + | </ | ||
| On destination server | On destination server | ||
| - | < | + | < |
| + | export NLS_DATE_FORMAT=" | ||
| + | sqlplus / as sysdba | ||
| + | startup nomount pfile='/ | ||
| + | </ | ||
| Make sure there is only one set of backup files in the directory. RMAN could get confused about which controlfile to use even though the documentation says it will take the most recent. | Make sure there is only one set of backup files in the directory. RMAN could get confused about which controlfile to use even though the documentation says it will take the most recent. | ||
| - | < | + | < |
| + | rman auxiliary / | ||
| + | duplicate database to destsid | ||
| + | backup location '/ | ||
| + | nofilenamecheck; | ||
| + | </ | ||
| - | =====Create a self-contained, | + | ==== Create a self-contained, |
| - | * [[http:// | + | * [[http:// |
| - | Maybe find out how many threads there are first and script it.<br /> | + | Maybe find out how many threads there are first and script it.\\ |
| Find the archivelog sequence number before the backup starts. | Find the archivelog sequence number before the backup starts. | ||
| - | < | + | < |
| - | < | + | select min(sequence# |
| + | </ | ||
| + | < | ||
| + | MIN1=$(echo " | ||
| + | </ | ||
| Run the backup switching logfile before and after. | Run the backup switching logfile before and after. | ||
| - | < | + | < |
| + | rman target / nocatalog log=autonomous_backup.log | ||
| + | run { | ||
| + | allocate channel... | ||
| + | sql "alter system archive log current"; | ||
| + | backup database include current controlfile; | ||
| + | sql "alter system archive log current"; | ||
| + | restore database preview; | ||
| + | } | ||
| + | </ | ||
| Find the archivelog sequence number now that the backup has finished. | Find the archivelog sequence number now that the backup has finished. | ||
| - | < | + | < |
| + | MAX1=$(echo " | ||
| + | </ | ||
| Backup just the archivelogs that will be necessary to recover. | Backup just the archivelogs that will be necessary to recover. | ||
| - | < | + | < |
| + | rman target / nocatalog log=autonomous_backup_arch.log | ||
| + | run { | ||
| + | allocate channel... | ||
| + | backup archivelog sequence between ${MIN1} and ${MAX1} thread 1; | ||
| + | } | ||
| + | </ | ||
| - | =====Create a physical standby from an RMAN backup===== | + | ==== Create a physical standby from an RMAN backup ==== |
| - | * another version by [[http:// | + | * another version by [[http:// |
| - | ====Backup primary database to disk==== | + | === Backup primary database to disk === |
| - | < | + | < |
| + | - !/bin/ksh | ||
| + | - | ||
| + | - Inspired by the book RMAN Recipes for Oracle database 11g | ||
| - | ====Copy RMAN backup files==== | + | export ORACLE_SID=JDBEOP1 |
| - | Copy over the backup files from source to destination server< | + | |
| - | * using scp | + | |
| - | < | + | |
| - | * using rsync | ||
| - | < | ||
| - | * using zfs disk (Solaris) | + | echo `date` |
| + | HOSTNAME=`hostname` | ||
| + | TODAY=`date +%Y%m%d_%H%M` | ||
| + | |||
| + | ORAENV_ASK=NO | ||
| + | . oraenv | ||
| + | |||
| + | export nls_date_format=" | ||
| + | |||
| + | sqlplus / as sysdba<< | ||
| + | set numwidth 20 lines 2000 | ||
| + | col status for a12 | ||
| + | select sysdate, current_scn from v\\$database; | ||
| + | select group#, | ||
| + | EOSQL | ||
| + | |||
| + | rman target / nocatalog msglog rman_database_${ORACLE_SID}_${TODAY}.log<< | ||
| + | run { | ||
| + | allocate channel d1 type disk; | ||
| + | allocate channel d2 type disk; | ||
| + | allocate channel d3 type disk; | ||
| + | allocate channel d4 type disk; | ||
| + | allocate channel d5 type disk; | ||
| + | allocate channel d6 type disk; | ||
| + | backup format '/ | ||
| + | sql 'alter system archive log current'; | ||
| + | backup format '/ | ||
| + | backup format '/ | ||
| + | backup format '/ | ||
| + | release channel d1; | ||
| + | release channel d2; | ||
| + | release channel d3; | ||
| + | release channel d4; | ||
| + | release channel d5; | ||
| + | release channel d6; | ||
| + | } | ||
| + | EORMAN | ||
| + | |||
| + | echo `date` | ||
| + | ls -altr / | ||
| + | </ | ||
| + | |||
| + | === Copy RMAN backup files === | ||
| + | Copy over the backup files from source to destination server\\ | ||
| + | | ||
| + | < | ||
| + | scp -p / | ||
| + | </ | ||
| + | |||
| + | * using rsync | ||
| + | < | ||
| + | rsync -uav --progress / | ||
| + | </ | ||
| + | |||
| + | * | ||
| On source server unmount the disk and comment the appropriate line in /etc/vfstab | On source server unmount the disk and comment the appropriate line in /etc/vfstab | ||
| - | < | + | < |
| + | umount / | ||
| + | vxdg deport clonedg | ||
| + | vi / | ||
| + | |||
| + | - / | ||
| + | |||
| + | </ | ||
| On destination server uncomment the appropriate line in /etc/vfstab and mount the disk | On destination server uncomment the appropriate line in /etc/vfstab and mount the disk | ||
| - | < | + | < |
| + | vi / | ||
| + | |||
| + | / | ||
| + | |||
| + | vxdg import clonedg | ||
| + | mount / | ||
| + | </ | ||
| - | ====Edit the tnsnames.ora file on the destination server==== | + | === Edit the tnsnames.ora file on the destination server === |
| Standby database needs to be able to connect to the primary via tns | Standby database needs to be able to connect to the primary via tns | ||
| - | < | + | < |
| + | vi $TNS_ADMIN/tnsnames.ora | ||
| - | ====Restore the database on the destination server==== | + | PRIMARY |
| - | < | + | (DESCRIPTION |
| + | (ADDRESS_LIST | ||
| + | (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1555)) | ||
| + | ) | ||
| + | (CONNECT_DATA | ||
| + | (SERVER = DEDICATED) | ||
| + | (SERVICE_NAME = JDBEOP1) | ||
| + | ) | ||
| + | ) | ||
| - | ====init_clone.ora | + | STANDBY |
| + | (DESCRIPTION | ||
| + | (ADDRESS_LIST | ||
| + | (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1555)) | ||
| + | ) | ||
| + | (CONNECT_DATA | ||
| + | (SERVER = DEDICATED) | ||
| + | (SERVICE_NAME = JDBEOP1_DR) | ||
| + | ) | ||
| + | ) | ||
| + | </ | ||
| + | |||
| + | === Restore the database on the destination server === | ||
| + | < | ||
| + | - !/ | ||
| + | |||
| + | HOSTNAME=`hostname` | ||
| + | TODAY=`date +%Y%m%d_%H%M`; | ||
| + | |||
| + | - =================== | ||
| + | - set the environment | ||
| + | - =================== | ||
| + | export ORACLE_SID=JDBEOP1 | ||
| + | ORAENV_ASK=NO | ||
| + | . oraenv | ||
| + | |||
| + | echo $ORACLE_SID | ||
| + | echo $ORACLE_HOME | ||
| + | |||
| + | - ================================== | ||
| + | - kill any previous (failed) attempt | ||
| + | - ================================== | ||
| + | sqlplus / as sysdba << | ||
| + | shutdown abort | ||
| + | EOSQL | ||
| + | |||
| + | - ====================== | ||
| + | - start a dummy instance | ||
| + | - ====================== | ||
| + | sqlplus / as sysdba << | ||
| + | startup nomount pfile='/ | ||
| + | EOSQL | ||
| + | |||
| + | - | ||
| + | - start the duplication | ||
| + | - ===================== | ||
| + | echo `date` | ||
| + | export NLS_DATE_FORMAT=' | ||
| + | rman nocatalog cmdfile=duplicate4standby.cmd msglog=duplicate_${HOSTNAME}_${ORACLE_SID}_${TODAY}.log | ||
| + | echo `date` | ||
| + | |||
| + | </ | ||
| + | |||
| + | === init_clone.ora file === | ||
| Used for the initial start of the standby database in the above script | Used for the initial start of the standby database in the above script | ||
| - | < | + | < |
| + | DB_NAME=JDBEOP1 | ||
| + | DB_UNIQUE_NAME=JDBEOP1_DR | ||
| + | </ | ||
| - | ====duplicate4standby.cmd file==== | + | === duplicate4standby.cmd file === |
| RMAN command file used in the above script | RMAN command file used in the above script | ||
| - | < | + | < |
| + | connect target sys/& | ||
| + | connect auxiliary / | ||
| + | run { | ||
| + | allocate channel c1 device type disk; | ||
| + | allocate channel c2 device type disk; | ||
| + | allocate auxiliary channel a1 device type disk; | ||
| + | allocate auxiliary channel a2 device type disk; | ||
| + | set newname for tempfile 1 to '/ | ||
| + | set newname for tempfile 2 to '/ | ||
| + | set newname for datafile 518 to '/ | ||
| + | set newname for datafile 555 to '/ | ||
| + | set newname for datafile 550 to '/ | ||
| + | duplicate target database for standby | ||
| + | nofilenamecheck | ||
| + | dorecover | ||
| + | spfile set db_unique_name | ||
| + | set sga_max_size | ||
| + | set sga_target | ||
| + | set db_cache_size | ||
| + | set fal_server | ||
| + | set fal_client | ||
| + | set db_block_checksum | ||
| + | set db_lost_write_protect | ||
| + | set db_recovery_file_dest | ||
| + | set log_archive_dest_1 | ||
| + | set standby_archive_dest | ||
| + | ; | ||
| + | } | ||
| + | </ | ||
| - | =====Use clonedb to create an almost instant clone of a database===== | + | ==== Use clonedb to create an almost instant clone of a database ==== |
| - | Harnesses the power of an RMAN image copy backup to generate clones that can be used for dev/qa etc.<br /> | + | Harnesses the power of an RMAN image copy backup to generate clones that can be used for dev/qa etc.\\ |
| - | Only the blocks different from those in the RMAN datafiles are kept in the cloned datafiles making huge space savings.<br /> | + | Only the blocks different from those in the RMAN datafiles are kept in the cloned datafiles making huge space savings.\\ |
| - | * [[http:// | + | * [[http:// |
| - | * [[https:// | + | * [[https:// |
| - | * [[http:// | + | * [[http:// |
| - | =====A collection of ways to do High Availability with Oracle===== | + | ==== A collection of ways to do High Availability with Oracle ==== |
| - | * [[http:// | + | * [[http:// |
| - | Includes the following methods...<br /> | + | Includes the following methods...\\ |
| - | * Data Guard | + | * |
| - | * Active Data Guard | + | * |
| - | * Recovery Manager (RMAN) | + | * |
| - | * Flashback Technology | + | * |
| - | * Data Guard and Applications | + | * |
| - | * Oracle Secure Backup (OSB) | + | * |
| - | * Global Data Services (GDS) | + | * |
| - | * Oracle GoldenGate | + | * |
| - | * Oracle Sharding | + | * |
| - | * Cloud MAA | + | * |
| - | =====Backup the control file===== | + | ==== Backup the control file ==== |
| - | < | + | < |
| - | =====Take a binary copy of the controlfile that can be used without a catalog to recover an instance somewhere else===== | + | backup current controlfile; |
| - | < | + | </ |
| - | =====Take a text copy of the controlfile that can be used without a catalog to rebuild an instance (worst case)===== | + | ==== Take a binary copy of the controlfile that can be used without a catalog to recover an instance somewhere else ==== |
| - | < | + | < |
| + | backup as copy current controlfile format '/ | ||
| + | </ | ||
| + | ==== Take a text copy of the controlfile that can be used without a catalog to rebuild an instance (worst case) ==== | ||
| + | < | ||
| + | backup current controlfile to trace format '/ | ||
| + | </ | ||
| or from SQL prompt... | or from SQL prompt... | ||
| - | < | + | < |
| + | alter database backup controlfile to trace as '/ | ||
| + | </ | ||
| - | =====Check the status of currently running backups===== | + | ==== Check the status of currently running backups ==== |
| Monitor progress of RMAN backups | Monitor progress of RMAN backups | ||
| - | < | + | < |
| + | select sid | ||
| + | , serial# | ||
| + | , context | ||
| + | , sofar | ||
| + | , totalwork | ||
| + | , round(sofar/ | ||
| + | from | ||
| + | where 1=1 | ||
| + | and opname | ||
| + | and opname not like ' | ||
| + | and totalwork != 0 | ||
| + | and sofar != totalwork | ||
| + | / | ||
| + | </ | ||
| - | =====Check the status of the database backups for the past n days===== | + | ==== Check the status of the database backups for the past n days ==== |
| Run this one from sys on the database server | Run this one from sys on the database server | ||
| - | < | + | < |
| + | set pages 500 lines 200 | ||
| + | col operation | ||
| + | col backup | ||
| + | col start_time | ||
| + | col end_time | ||
| + | col input_type | ||
| + | col status | ||
| + | col duration | ||
| + | col days_ago | ||
| + | |||
| + | select /*+ rule */ vrbsd.operation | ||
| + | , vrbsd.command_id | ||
| + | , to_char(vrbsd.start_time,' | ||
| + | , to_char(vrbsd.end_time,' | ||
| + | , vrbd.input_type | ||
| + | , vrbsd.status | ||
| + | , (vrbd.elapsed_seconds/ | ||
| + | , trunc(sysdate)-trunc(vrbsd.start_time) | ||
| + | from | ||
| + | , v$rman_backup_subjob_details | ||
| + | where 1=1 | ||
| + | and vrbd.session_key = vrbsd.session_key | ||
| + | and vrbsd.start_time > sysdate-& | ||
| + | order by vrbsd.start_time | ||
| + | / | ||
| + | </ | ||
| - | =====Check the status of the database backups from the RMAN catalog===== | + | ==== Check the status of the database backups from the RMAN catalog ==== |
| Run from the RMAN catalog owner, this checks to see how long ago a full backup was taken from all databases in the catalog | Run from the RMAN catalog owner, this checks to see how long ago a full backup was taken from all databases in the catalog | ||
| - | < | + | < |
| + | set lines 300 pages 66 | ||
| + | col status for a15 | ||
| + | select s.db_name | ||
| + | , s.db_key | ||
| + | , s.status | ||
| + | , max(s.start_time) | ||
| + | , max(round(s.mbytes_processed/ | ||
| + | , max(round((end_time-start_time)*60*24)) | ||
| + | , trunc(sysdate)-trunc(s.start_time) | ||
| + | from | ||
| + | where 1=1 | ||
| + | and s.operation | ||
| + | and s.object_type = 'DB FULL' | ||
| + | and s.start_time | ||
| + | ( | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | ) | ||
| + | group by s.db_name | ||
| + | , s.db_key | ||
| + | , s.object_type | ||
| + | , s.operation | ||
| + | , s.status | ||
| + | , trunc(sysdate)-trunc(s.start_time) | ||
| + | order by trunc(sysdate)-trunc(s.start_time) desc | ||
| + | / | ||
| + | </ | ||
| Run from the RMAN catalog owner, this shows the backup history for a particular database in the catalog | Run from the RMAN catalog owner, this shows the backup history for a particular database in the catalog | ||
| - | < | + | < |
| + | set lines 300 pages 100 | ||
| + | col status for a15 | ||
| + | col objtype for a15 | ||
| + | select s.db_name | ||
| + | , s.db_key | ||
| + | , s.object_type | ||
| + | , s.status | ||
| + | , s.start_time | ||
| + | , round(s.mbytes_processed) | ||
| + | , round((end_time-start_time)*60*24) | ||
| + | , round((sysdate-s.start_time)*24) | ||
| + | from | ||
| + | where 1=1 | ||
| + | and db_name | ||
| + | and s.operation | ||
| + | and s.object_type | ||
| + | order by trunc(sysdate)-trunc(s.start_time) desc | ||
| + | / | ||
| + | </ | ||
| Run from the RMAN catalog owner, this shows the archivelog backup history for a particular database in the catalog | Run from the RMAN catalog owner, this shows the archivelog backup history for a particular database in the catalog | ||
| - | < | + | < |
| + | set lines 300 pages 100 | ||
| + | col status for a15 | ||
| + | col objtype for a15 | ||
| + | select s.db_name | ||
| + | , s.db_key | ||
| + | , s.object_type | ||
| + | , s.status | ||
| + | , s.start_time | ||
| + | , round(s.mbytes_processed) | ||
| + | , round((end_time-start_time)*60*24) | ||
| + | , round((sysdate-s.start_time)*24) | ||
| + | from | ||
| + | where 1=1 | ||
| + | and db_name | ||
| + | and s.operation | ||
| + | and s.object_type | ||
| + | order by trunc(sysdate)-trunc(s.start_time) desc | ||
| + | / | ||
| + | </ | ||
| - | =====Clean up the RMAN catalog===== | + | ==== Clean up the RMAN catalog ==== |
| Sometimes the catalog needs cleaning up. Maybe the backup scripts are not doing a crosscheck and delete properly. | Sometimes the catalog needs cleaning up. Maybe the backup scripts are not doing a crosscheck and delete properly. | ||
| - | < | + | < |
| + | list backupset of database summary completed between ' | ||
| + | </ | ||
| If this produces results, records exist that go back before the retention period and may need deleting (depending on the policy). | If this produces results, records exist that go back before the retention period and may need deleting (depending on the policy). | ||
| - | < | + | < |
| + | change backupset ${key} delete; | ||
| + | </ | ||
| If you get this error... | If you get this error... | ||
| - | < | + | < |
| - | Allocate a fake tape channel (but not in a run block!)<br /> | + | RMAN-06091: no channel allocated for maintenance (of an appropriate type) |
| + | </ | ||
| + | Allocate a fake tape channel (but not in a run block!)\\ | ||
| [[http:// | [[http:// | ||
| - | < | + | < |
| + | allocate channel for maintenance device type sbt parms ' | ||
| + | configure retention policy to recovery window of 28 days; | ||
| + | delete force obsolete; | ||
| + | </ | ||
| - | =====Which tapes are the backups on?===== | + | ==== Which tapes are the backups on? ==== |
| Not really necessary as RMAN can work it out but if you need to know... | Not really necessary as RMAN can work it out but if you need to know... | ||
| + | < | ||
| | | ||
| , to_char(completion_time,' | , to_char(completion_time,' | ||
| Line 307: | Line 3044: | ||
| | | ||
| | | ||
| + | </ | ||
| - | =====Block change tracking===== | + | ==== Block change tracking ==== |
| Check / Set it | Check / Set it | ||
| - | < | + | < |
| - | or check from the Operating System for the CTWR background process<br /> | + | set lines 1000 |
| + | select * from v$block_change_tracking; | ||
| + | show parameter db_create_file_dest | ||
| + | alter system set db_create_file_dest='/ | ||
| + | </ | ||
| + | or check from the Operating System for the CTWR background process\\ | ||
| Here we see 3 of the 8 databases on this machine have block change tracking enabled | Here we see 3 of the 8 databases on this machine have block change tracking enabled | ||
| - | < | + | < |
| + | hn5114 / | ||
| + | oracle | ||
| + | oracle | ||
| + | | ||
| + | |||
| + | </ | ||
| Enable it | Enable it | ||
| - | < | + | < |
| + | alter database enable block change tracking; | ||
| + | alter database enable block change tracking using file '/ | ||
| + | </ | ||
| Disable it | Disable it | ||
| - | < | + | < |
| + | alter database disable block change tracking; | ||
| + | </ | ||
| Rename / Move it | Rename / Move it | ||
| - | < | + | < |
| + | alter database disable block change tracking; | ||
| + | alter database enable block change tracking using file '< | ||
| + | </ | ||
| + | |||
| + | ==== Read and write statistics when Block Change Tracking (BCT) is on or off ===== | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | select file# | ||
| + | , used_change_tracking | ||
| + | , incremental_level | ||
| + | , datafile_blocks | ||
| + | , block_size | ||
| + | , blocks_read | ||
| + | , round((blocks_read/ | ||
| + | , blocks blocks_written | ||
| + | , round((blocks/ | ||
| + | from | ||
| + | / | ||
| + | set lines 80 | ||
| + | </ | ||
| + | When block change tracking is off, you can see the whole datafile is read all the time | ||
| + | < | ||
| + | FNO BCT | ||
| + | ---------- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | ||
| + | 5 NO | ||
| + | 9 NO | ||
| + | 14 NO | ||
| + | 15 NO | ||
| + | 6 NO | ||
| + | 7 NO | ||
| + | 8 NO | ||
| + | 10 NO | ||
| + | 16 NO | ||
| + | 11 NO | ||
| + | 12 NO | ||
| + | 13 NO | ||
| + | 3 NO | ||
| + | </ | ||
| + | |||
| + | When block change tracking is on, you can see the datafile is read minimally | ||
| + | < | ||
| + | FNO BCT | ||
| + | ---------- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | ||
| + | 26 YES 1 2483584 | ||
| + | 24 YES 1 1388160 | ||
| + | 25 YES 1 1272576 | ||
| + | 56 YES 1 | ||
| + | 61 YES 1 | ||
| + | 65 YES 1 | ||
| + | 70 YES 1 | ||
| + | 91 YES 1 | ||
| + | 97 YES 1 | ||
| + | 41 YES 1 | ||
| + | 44 YES 1 | ||
| + | 48 YES 1 | ||
| + | 89 YES 1 | ||
| + | 93 YES 1 12800 | ||
| + | 94 YES 1 12800 | ||
| + | </ | ||
| + | If you still see 100% read even though BCT is YES, it probably means that a full incremental level 0 has not happened yet | ||
| - | =====To remove all rows from v$rman_status===== | + | ==== To remove all rows from v$rman_status ==== |
| If the status of an RMAN backup is stuck in RUNNING, the next backup will not start up. Reset the view with this... | If the status of an RMAN backup is stuck in RUNNING, the next backup will not start up. Reset the view with this... | ||
| - | < | + | < |
| - | =====To reset all RMAN configuration back to defaults===== | + | exec sys.dbms_backup_restore.resetcfilesection(28); |
| - | < | + | </ |
| + | ==== To reset all RMAN configuration back to defaults ==== | ||
| + | < | ||
| + | execute dbms_backup_restore.resetconfig; | ||
| + | </ | ||
| - | =====Convert timestamp to SCN===== | + | ==== Convert timestamp to SCN ==== |
| - | < | + | < |
| + | select timestamp_to_scn(to_timestamp(' | ||
| + | </ | ||
| - | =====Convert SCN to timestamp===== | + | ==== Convert SCN to timestamp ==== |
| - | < | + | < |
| + | select scn_to_timestamp(1234567890) timestamp from dual; | ||
| + | </ | ||
| - | =====Check for hanging RMAN sessions by checking wait===== | + | ==== Check for hanging RMAN sessions by checking wait ==== |
| - | < | + | < |
| + | COLUMN EVENT FORMAT a10 | ||
| + | COLUMN SECONDS_IN_WAIT FORMAT 999 | ||
| + | COLUMN STATE FORMAT a20 | ||
| + | COLUMN CLIENT_INFO FORMAT a30 | ||
| - | =====Useful RMAN commands===== | + | SELECT p.SPID |
| - | ====Create RMAN catalog schema==== | + | , EVENT |
| - | < | + | , SECONDS_IN_WAIT SEC_WAIT |
| + | , sw.STATE | ||
| + | , CLIENT_INFO | ||
| + | FROM | ||
| + | , V$SESSION s | ||
| + | , V$PROCESS p | ||
| + | WHERE 1=1 | ||
| + | and (sw.EVENT LIKE ' | ||
| + | AND s.SID | ||
| + | AND s.PADDR | ||
| + | ; | ||
| + | </ | ||
| - | ====Connect | + | ==== Useful |
| - | < | + | === Create RMAN catalog schema |
| + | < | ||
| + | create tablespace rmanAGRDWHP1 datafile '/oracle/ | ||
| + | alter database datafile '/ | ||
| - | ====Connect RMAN to the recovery catalog (assuming db environment is set)==== | + | create user rmanTDPOTEST identified by threeguesses |
| - | < | + | |
| + | | ||
| + | quota unlimited on rmanTDPOTEST; | ||
| + | |||
| + | grant connect, resource, recovery_catalog_owner to rmanTDPOTEST; | ||
| + | </ | ||
| + | |||
| + | === Connect RMAN to the database controlfile (assuming db environment is set) === | ||
| + | < | ||
| + | rman target / | ||
| + | </ | ||
| + | |||
| + | === Connect RMAN to the recovery catalog (assuming db environment is set) === | ||
| + | < | ||
| + | rman target / catalog catowner/ | ||
| + | </ | ||
| Problem with this method is that anyone doing a 'ps -ef' will see the catalog password! Do it this way and the password will not be visible... | Problem with this method is that anyone doing a 'ps -ef' will see the catalog password! Do it this way and the password will not be visible... | ||
| - | < | + | < |
| + | rman target | ||
| + | Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jun 8 10:49:57 2014 | ||
| - | ====Show what backups are available==== | + | Copyright (c) 1982, 2011, Oracle and/or its affiliates. |
| - | < | + | |
| - | ====Show all the configured options==== | + | connected to target database: EUDSTGP2 (DBID=1013086362) |
| - | < | + | |
| - | ====What needs backing up to satisfy retention policy (times backed up/recovery window etc)?==== | + | RMAN> connect catalog catowner/ |
| - | < | + | |
| - | ====What can be deleted?==== | + | connected to recovery catalog database |
| - | < | + | </ |
| - | ====Delete what is not needed==== | + | |
| - | * [[https:// | + | === Show what backups are available === |
| - | Obsolete - No longer needed as they are older than the retention period. They will go back longer if there was no full backup within the retention period.<br /> | + | < |
| + | list backup of database; | ||
| + | list backup of spfile; | ||
| + | list backup of controlfile; | ||
| + | LIST ARCHIVELOG ALL; | ||
| + | LIST BACKUP OF ARCHIVELOG ALL; | ||
| + | LIST BACKUP OF DATAFILE 1; | ||
| + | LIST BACKUP SUMMARY; | ||
| + | LIST INCARNATION; | ||
| + | LIST BACKUP BY FILE; | ||
| + | LIST BACKUP OF DATAFILE 11 SUMMARY; | ||
| + | LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437; | ||
| + | LIST CONTROLFILECOPY "/ | ||
| + | LIST BACKUPSET OF DATAFILE 1; | ||
| + | LIST FAILURE; | ||
| + | LIST FAILURE 641231 detail; | ||
| + | </ | ||
| + | === Show all the configured options === | ||
| + | < | ||
| + | show all; | ||
| + | </ | ||
| + | === What needs backing up to satisfy retention policy (times backed up/recovery window etc)? === | ||
| + | < | ||
| + | report need backup; | ||
| + | </ | ||
| + | === What can be deleted? === | ||
| + | < | ||
| + | report obsolete; | ||
| + | </ | ||
| + | === Delete what is not needed === | ||
| + | * [[https:// | ||
| + | Obsolete - No longer needed as they are older than the retention period. They will go back longer if there was no full backup within the retention period.\\ | ||
| Expired - The backup file exists in the control file / catalog but no longer exists in reality. | Expired - The backup file exists in the control file / catalog but no longer exists in reality. | ||
| - | < | + | < |
| + | show retention; | ||
| + | configure retention policy to recovery window of 28 days; | ||
| + | crosscheck backup; | ||
| + | list backup summary; | ||
| + | delete noprompt expired archivelog all; | ||
| + | delete noprompt expired backup; | ||
| + | delete noprompt obsolete; | ||
| + | list backup summary; | ||
| + | </ | ||
| - | ====Check whether the backup pieces registered in the controlfile still exist==== | + | === Check whether the backup pieces registered in the controlfile still exist === |
| - | < | + | < |
| + | crosscheck backup; | ||
| + | crosscheck copy; | ||
| + | crosscheck backup of database; | ||
| + | crosscheck backup of controlfile; | ||
| + | crosscheck archivelog all; | ||
| + | </ | ||
| - | =====Restore database from TSM (tape) to different server using RMAN===== | + | ==== Restore database from TSM (tape) to different server using RMAN ==== |
| **Scenario** | **Scenario** | ||
| - | <br /> | + | |
| - | Customer wants a complete database restored to a different server so that they can retrieve data that is no longer available on the source database.<br /> | + | Customer wants a complete database restored to a different server so that they can retrieve data that is no longer available on the source database.\\ |
| - | It is not possible to restore from an export as the required dump files are no longer available.<br /> | + | It is not possible to restore from an export as the required dump files are no longer available.\\ |
| - | <br /> | + | |
| - | Source: | + | Source: |
| - | Destination: | + | Destination: |
| - | <br /> | + | |
| **Method** | **Method** | ||
| - | * Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server | + | * Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server |
| - | * Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem | + | * Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem |
| - | * Recreate the / | + | * Recreate the / |
| - | * Update dsm.sys with SID stanza from source server | + | * Update dsm.sys with SID stanza from source server |
| - | * Test the tdpo access from destination server | + | * Test the tdpo access from destination server |
| - | * Add destination SID to oratab | + | * Add destination SID to oratab |
| - | * Create a minimal init.ora file | + | * Create a minimal init.ora file |
| - | * Check that the desired data can be restored (validate) | + | * Check that the desired data can be restored (validate) |
| - | * Create an RMAN command file to perform the work | + | * Create an RMAN command file to perform the work |
| - | * Create a shell to run the process | + | * Create a shell to run the process |
| - | <br /> | + | |
| - | <br /> | + | |
| - | **Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server**<br /> | + | **Request Unix team to attach a temporary filesystem big enough to hold the complete database (and an extra bit for contingency) to the destination server**\\ |
| - | Either attach the storage directly to the destination server or nfs mount it (with hard mount options) from another server<br /> | + | Either attach the storage directly to the destination server or nfs mount it (with hard mount options) from another server\\ |
| - | <br /> | + | |
| - | **Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem**<br /> | + | **Request Unix team to restore (or copy from source) the Oracle software to the mounted filesystem**\\ |
| - | The whole of the Oracle source code can be copied from the source server to the destination server. The contents of the dbs directory can be removed.<br /> | + | The whole of the Oracle source code can be copied from the source server to the destination server. The contents of the dbs directory can be removed.\\ |
| - | <br /> | + | |
| - | **Recreate the / | + | **Recreate the / |
| - | For this customer, we keep all the TSM related files in this directory. These include the tdpo.opt, dsm.opt and TSM.PWD files.<br /> | + | For this customer, we keep all the TSM related files in this directory. These include the tdpo.opt, dsm.opt and TSM.PWD files.\\ |
| - | < | + | < |
| - | <br /> | + | test002:/oracle/ |
| - | **Update dsm.sys with SID stanza from source server**<br /> | + | total 62 |
| + | -rwxrwx--- | ||
| + | -rwxrwx--- | ||
| + | -rwxrwx--- | ||
| + | drwxrwx--- | ||
| + | -rwxrwx--- | ||
| + | -rwxrwx--- | ||
| + | drwxrwx--- | ||
| + | </code> | ||
| + | |||
| + | **Update dsm.sys with SID stanza from source server**\\ | ||
| The stanza from the source server relating to this database should be copied into the dsm.sys on the destination server so that tdpo can access the TSM server. | The stanza from the source server relating to this database should be copied into the dsm.sys on the destination server so that tdpo can access the TSM server. | ||
| - | < | + | < |
| - | <br /> | + | SErvername |
| - | **Test the tdpo access from destination server**<br /> | + | |
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | </code> | ||
| + | |||
| + | **Test the tdpo access from destination server**\\ | ||
| Use tdpoconf to test access to the TSM server and to check the related files are all usable. | Use tdpoconf to test access to the TSM server and to check the related files are all usable. | ||
| - | < | + | < |
| - | <br /> | + | tdpoconf showenv -tdpo_opt=/oracle/ |
| - | **Add destination SID to oratab**<br /> | + | </code> |
| + | |||
| + | **Add destination SID to oratab**\\ | ||
| Add a line with the new SID | Add a line with the new SID | ||
| - | < | + | < |
| - | <br /> | + | echo " |
| - | **Create a minimal init.ora file**<br /> | + | </code> |
| + | |||
| + | **Create a minimal init.ora file**\\ | ||
| This is just a dummy file so that we can start an idle instance. | This is just a dummy file so that we can start an idle instance. | ||
| - | < | + | < |
| - | <br /> | + | echo " |
| - | **Check that the desired data can be restored (validate)**<br /> | + | </code> |
| - | Use an RMAN preview command to ensure that files can be restored as far back as the required date.<br /> | + | |
| + | **Check that the desired data can be restored (validate)**\\ | ||
| + | Use an RMAN preview command to ensure that files can be restored as far back as the required date.\\ | ||
| If you have no access to the catalog, you will need to use "set dbid=< | If you have no access to the catalog, you will need to use "set dbid=< | ||
| - | < | + | < |
| - | <br /> | + | rman target / catalog catuser/ |
| - | **Create an RMAN command file to perform the work**<br /> | + | |
| + | run { | ||
| + | set until time " | ||
| + | restore controlfile preview; | ||
| + | } | ||
| + | </ | ||
| + | |||
| + | **Create an RMAN command file to perform the work**\\ | ||
| This syntax does not require a connection to the target database | This syntax does not require a connection to the target database | ||
| - | < | + | < |
| - | <br /> | + | connect catalog catuser/ |
| + | connect auxiliary | ||
| + | |||
| + | run { | ||
| + | allocate auxiliary channel d1 device type disk; | ||
| + | allocate auxiliary channel a1 device type sbt_tape parms ' | ||
| + | allocate auxiliary channel a2 device type sbt_tape parms ' | ||
| + | allocate auxiliary channel a3 device type sbt_tape parms ' | ||
| + | allocate auxiliary channel a4 device type sbt_tape parms ' | ||
| + | allocate auxiliary channel a5 device type sbt_tape parms ' | ||
| + | |||
| + | duplicate database PROD1 dbid 2992702833 to PRODX | ||
| + | until time " | ||
| + | spfile | ||
| + | set db_unique_name = ' | ||
| + | nofilenamecheck | ||
| + | ; | ||
| + | |||
| + | } | ||
| + | </code> | ||
| **Create a shell to run the process** | **Create a shell to run the process** | ||
| - | < | + | < |
| - | <br /> | + | - !/ |
| + | |||
| + | HOSTNAME=`hostname` | ||
| + | export TODAY=`date +%Y%m%d_%H%M` | ||
| + | |||
| + | export ORACLE_SID=PRODX | ||
| + | ORAENV_ASK=NO | ||
| + | . oraenv | ||
| + | |||
| + | echo $ORACLE_SID | ||
| + | echo $ORACLE_HOME | ||
| + | |||
| + | sqlplus / as sysdba | ||
| + | shutdown abort | ||
| + | EOSQL | ||
| + | |||
| + | sqlplus | ||
| + | startup nomount pfile='/ | ||
| + | EOSQL | ||
| + | |||
| + | echo `date` | ||
| + | export NLS_DATE_FORMAT=' | ||
| + | rman cmdfile=/ | ||
| + | echo `date` | ||
| + | </code> | ||
| - | =====Bad Hair Day===== | + | ==== Bad Hair Day ==== |
| When media failure strikes, look in the alertlog for more info if you can still get to it. | When media failure strikes, look in the alertlog for more info if you can still get to it. | ||
| - | ====Restore and recover tablespace==== | + | === Restore and recover tablespace === |
| If some/all datafiles in one tablespace are damaged, you can recover the tablespace. | If some/all datafiles in one tablespace are damaged, you can recover the tablespace. | ||
| - | < | + | < |
| + | rman target / | ||
| + | startup mount; | ||
| + | restore tablespace users; | ||
| + | recover tablespace users; | ||
| + | alter database open; | ||
| + | </ | ||
| or while database is open... | or while database is open... | ||
| - | < | + | < |
| + | rman target / | ||
| + | sql 'alter tablespace users offline immediate'; | ||
| + | restore tablespace users; | ||
| + | recover tablespace users; | ||
| + | sql 'alter tablespace data_ts online'; | ||
| + | </ | ||
| or until a particular moment in time (TSPITR) | or until a particular moment in time (TSPITR) | ||
| - | < | + | < |
| - | NOTE 1: If you do not specify auxiliary destination, | + | RMAN> run { |
| - | NOTE 2: There is a bug which ignores the fact that a tape channel has been allocated.<br /> | + | allocate channel t1 type ' |
| + | recover tablespace " | ||
| + | } | ||
| + | </ | ||
| + | NOTE 1: If you do not specify auxiliary destination, | ||
| + | NOTE 2: There is a bug which ignores the fact that a tape channel has been allocated.\\ | ||
| The workaround is to use CONFIGURE to allocate a channel. Do not forget to reset it later. | The workaround is to use CONFIGURE to allocate a channel. Do not forget to reset it later. | ||
| - | < | + | < |
| + | rman target / | ||
| + | configure channel device type ' | ||
| + | run { | ||
| + | sql 'alter tablespace TS_BAMBOO offline immediate'; | ||
| + | recover tablespace " | ||
| + | sql 'alter tablespace TS_BAMBOO online'; | ||
| + | } | ||
| + | configure channel device type ' | ||
| + | </ | ||
| Output of the above script: | Output of the above script: | ||
| - | < | + | < |
| + | rman cmdfile=tspitr_restore_devtools.rman | ||
| + | |||
| + | Recovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 21 15:59:20 2018 | ||
| + | |||
| + | Copyright (c) 1982, 2014, Oracle and/or its affiliates. | ||
| + | |||
| + | RMAN> | ||
| + | 1> connect target * | ||
| + | 2> connect catalog * | ||
| + | 3> run { | ||
| + | 4> | ||
| + | 5> } | ||
| + | 6> | ||
| + | connected to target database: DEVTOOLS (DBID=2968626886) | ||
| + | |||
| + | connected to recovery catalog database | ||
| + | |||
| + | Starting recover at 21-MAR-18 | ||
| + | starting full resync of recovery catalog | ||
| + | full resync complete | ||
| + | allocated channel: ORA_DISK_1 | ||
| + | channel ORA_DISK_1: SID=485 device type=DISK | ||
| + | allocated channel: ORA_SBT_TAPE_1 | ||
| + | channel ORA_SBT_TAPE_1: | ||
| + | channel ORA_SBT_TAPE_1: | ||
| + | |||
| + | Creating automatic instance, with SID=' | ||
| + | |||
| + | initialization parameters used for automatic instance: | ||
| + | db_name=DEVTOOLS | ||
| + | db_unique_name=aslo_pitr_DEVTOOLS | ||
| + | compatible=12.1.0.2.0 | ||
| + | db_block_size=8192 | ||
| + | db_files=200 | ||
| + | diagnostic_dest=/ | ||
| + | _system_trig_enabled=FALSE | ||
| + | sga_target=2560M | ||
| + | processes=200 | ||
| + | db_create_file_dest=/ | ||
| + | log_archive_dest_1=' | ||
| + | - No auxiliary parameter file used | ||
| + | |||
| + | |||
| + | starting up automatic instance DEVTOOLS | ||
| + | |||
| + | Oracle instance started | ||
| + | |||
| + | Total System Global Area 2684354560 bytes | ||
| + | |||
| + | Fixed Size | ||
| + | Variable Size 654312216 bytes | ||
| + | Database Buffers | ||
| + | Redo Buffers | ||
| + | Automatic instance created | ||
| + | Running TRANSPORT_SET_CHECK on recovery set tablespaces | ||
| + | TRANSPORT_SET_CHECK completed successfully | ||
| + | |||
| + | contents of Memory Script: | ||
| + | { | ||
| + | - set requested point in time | ||
| + | set until time " | ||
| + | - 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'; | ||
| + | - avoid unnecessary autobackups for structural changes during TSPITR | ||
| + | sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); | ||
| + | - resync catalog | ||
| + | resync catalog; | ||
| + | } | ||
| + | executing Memory Script | ||
| + | |||
| + | executing command: SET until clause | ||
| + | |||
| + | Starting restore at 21-MAR-18 | ||
| + | allocated channel: ORA_AUX_DISK_1 | ||
| + | channel ORA_AUX_DISK_1: | ||
| + | allocated channel: ORA_AUX_SBT_TAPE_1 | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | |||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | output file name=/ | ||
| + | Finished restore at 21-MAR-18 | ||
| + | |||
| + | sql statement: alter database mount clone database | ||
| + | |||
| + | sql statement: alter system archive log current | ||
| + | |||
| + | sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); | ||
| + | |||
| + | starting full resync of recovery catalog | ||
| + | full resync complete | ||
| + | |||
| + | contents of Memory Script: | ||
| + | { | ||
| + | - set requested point in time | ||
| + | set until time " | ||
| + | plsql <<< | ||
| + | declare | ||
| + | sqlstatement | ||
| + | pdbname | ||
| + | offline_not_needed exception; | ||
| + | pragma exception_init(offline_not_needed, | ||
| + | begin | ||
| + | | ||
| + | sqlstatement := 'alter tablespace ' | ||
| + | krmicd.writeMsg(6162, | ||
| + | krmicd.execSql(sqlstatement, | ||
| + | exception | ||
| + | when offline_not_needed then | ||
| + | null; | ||
| + | end; >>>; | ||
| + | - set destinations for recovery set and auxiliary set datafiles | ||
| + | set newname for clone datafile | ||
| + | set newname for clone datafile | ||
| + | set newname for clone datafile | ||
| + | set newname for clone tempfile | ||
| + | set newname for datafile | ||
| + | "/ | ||
| + | set newname for datafile | ||
| + | "/ | ||
| + | - switch all tempfiles | ||
| + | switch clone tempfile all; | ||
| + | - restore the tablespaces in the recovery set and the auxiliary set | ||
| + | restore clone datafile | ||
| + | |||
| + | switch clone datafile all; | ||
| + | } | ||
| + | executing Memory Script | ||
| + | |||
| + | executing command: SET until clause | ||
| + | |||
| + | sql statement: alter tablespace TS_BAMBOO offline immediate | ||
| + | |||
| + | executing command: SET NEWNAME | ||
| + | |||
| + | executing command: SET NEWNAME | ||
| + | |||
| + | executing command: SET NEWNAME | ||
| + | |||
| + | executing command: SET NEWNAME | ||
| + | |||
| + | executing command: SET NEWNAME | ||
| + | |||
| + | executing command: SET NEWNAME | ||
| + | |||
| + | renamed tempfile 1 to / | ||
| + | |||
| + | Starting restore at 21-MAR-18 | ||
| + | using channel ORA_AUX_DISK_1 | ||
| + | using channel ORA_AUX_SBT_TAPE_1 | ||
| + | |||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | channel ORA_AUX_SBT_TAPE_1: | ||
| + | Finished restore at 21-MAR-18 | ||
| + | |||
| + | datafile 1 switched to datafile copy | ||
| + | input datafile copy RECID=4 STAMP=971367011 file name=/ | ||
| + | datafile 3 switched to datafile copy | ||
| + | input datafile copy RECID=5 STAMP=971367011 file name=/ | ||
| + | datafile 2 switched to datafile copy | ||
| + | input datafile copy RECID=6 STAMP=971367011 file name=/ | ||
| + | |||
| + | contents of Memory Script: | ||
| + | { | ||
| + | - set requested point in time | ||
| + | set until time " | ||
| + | - online the datafiles restored or switched | ||
| + | sql clone "alter database datafile | ||
| + | sql clone "alter database datafile | ||
| + | sql clone "alter database datafile | ||
| + | sql clone "alter database datafile | ||
| + | sql clone "alter database datafile | ||
| + | - recover and open resetlogs | ||
| + | recover clone database tablespace | ||
| + | alter clone database open resetlogs; | ||
| + | } | ||
| + | executing Memory Script | ||
| + | |||
| + | executing command: SET until clause | ||
| + | |||
| + | sql statement: alter database datafile | ||
| + | |||
| + | sql statement: alter database datafile | ||
| + | |||
| + | sql statement: alter database datafile | ||
| + | |||
| + | sql statement: alter database datafile | ||
| + | |||
| + | sql statement: alter database datafile | ||
| + | |||
| + | Starting recover at 21-MAR-18 | ||
| + | using channel ORA_AUX_DISK_1 | ||
| + | using channel ORA_AUX_SBT_TAPE_1 | ||
| + | |||
| + | starting media recovery | ||
| + | |||
| + | archived log for thread 1 with sequence 13773 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13774 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13775 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13776 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13777 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13778 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13779 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13780 is already on disk as file / | ||
| + | archived log for thread 1 with sequence 13781 is already on disk as file / | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | archived log file name=/ | ||
| + | media recovery complete, elapsed time: 00:00:41 | ||
| + | Finished recover at 21-MAR-18 | ||
| + | |||
| + | database opened | ||
| + | |||
| + | contents of Memory Script: | ||
| + | { | ||
| + | - make read only the tablespace that will be exported | ||
| + | sql clone 'alter tablespace | ||
| + | - create directory for datapump import | ||
| + | sql " | ||
| + | / | ||
| + | - create directory for datapump export | ||
| + | sql clone " | ||
| + | / | ||
| + | } | ||
| + | executing Memory Script | ||
| + | |||
| + | sql statement: alter tablespace | ||
| + | |||
| + | sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as /// | ||
| + | |||
| + | sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as /// | ||
| + | |||
| + | Performing export of metadata... | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | Export completed | ||
| + | |||
| + | |||
| + | contents of Memory Script: | ||
| + | { | ||
| + | - shutdown clone before import | ||
| + | shutdown clone abort | ||
| + | - drop target tablespaces before importing them back | ||
| + | sql 'drop tablespace | ||
| + | } | ||
| + | executing Memory Script | ||
| + | |||
| + | Oracle instance shut down | ||
| + | |||
| + | sql statement: drop tablespace | ||
| + | |||
| + | Performing import of metadata... | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | Import completed | ||
| + | |||
| + | |||
| + | contents of Memory Script: | ||
| + | { | ||
| + | - make read write and offline the imported tablespaces | ||
| + | sql 'alter tablespace | ||
| + | sql 'alter tablespace | ||
| + | - enable autobackups after TSPITR is finished | ||
| + | sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); | ||
| + | - resync catalog | ||
| + | resync catalog; | ||
| + | } | ||
| + | executing Memory Script | ||
| + | |||
| + | sql statement: alter tablespace | ||
| + | |||
| + | sql statement: alter tablespace | ||
| + | |||
| + | sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); | ||
| + | |||
| + | starting full resync of recovery catalog | ||
| + | full resync complete | ||
| + | |||
| + | Removing automatic instance | ||
| + | Automatic instance removed | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file / | ||
| + | auxiliary instance file tspitr_aslo_93713.dmp deleted | ||
| + | Finished recover at 21-MAR-18 | ||
| + | |||
| + | Recovery Manager complete. | ||
| + | / | ||
| + | |||
| + | SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 16:16:52 2018 | ||
| + | |||
| + | Copyright (c) 1982, 2013, Oracle. | ||
| + | |||
| + | |||
| + | Connected to: | ||
| + | Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production | ||
| + | With the Partitioning, | ||
| + | |||
| + | |||
| + | SQL> select tablespace_name, | ||
| + | |||
| + | TABLESPACE_NAME | ||
| + | ------------------------------ --------- | ||
| + | SYSTEM | ||
| + | SYSAUX | ||
| + | UNDOTBS1 | ||
| + | TEMP | ||
| + | USERS ONLINE | ||
| + | TS_STASH | ||
| + | TS_BAMBOO | ||
| + | TS_SONAR | ||
| + | |||
| + | 8 rows selected. | ||
| + | |||
| + | SQL> alter tablespace TS_BAMBOO online; | ||
| + | |||
| + | Tablespace altered. | ||
| + | |||
| + | SQL> select tablespace_name, | ||
| + | |||
| + | TABLESPACE_NAME | ||
| + | ------------------------------ --------- | ||
| + | SYSTEM | ||
| + | SYSAUX | ||
| + | UNDOTBS1 | ||
| + | TEMP | ||
| + | USERS ONLINE | ||
| + | TS_STASH | ||
| + | TS_BAMBOO | ||
| + | TS_SONAR | ||
| + | |||
| + | 8 rows selected. | ||
| + | |||
| + | SQL> exit | ||
| + | </ | ||
| ...or even better if flashbask is enabled (this takes seconds instead of hours) - but this is the whole database not just a tablespace!! | ...or even better if flashbask is enabled (this takes seconds instead of hours) - but this is the whole database not just a tablespace!! | ||
| - | < | + | < |
| + | SQL> flashback database to timestamp to_timestamp (' | ||
| - | ====Recover a dropped table==== | + | Flashback complete. |
| - | Used the purge option? Pisser - no flashback. However, from 12c, it is possible (causing RMAN to work very hard) to restore a table.<br /> | + | |
| + | SQL> alter database open resetlogs; | ||
| + | |||
| + | Database altered. | ||
| + | |||
| + | SQL> | ||
| + | </ | ||
| + | |||
| + | === Recover a dropped table === | ||
| + | Used the purge option? Pisser - no flashback. However, from 12c, it is possible (causing RMAN to work very hard) to restore a table.\\ | ||
| It's possible in any version doing the steps manually (so long as RMAN is being used and database is in archivelog mode), but 12c automates it down to 1 line! | It's possible in any version doing the steps manually (so long as RMAN is being used and database is in archivelog mode), but 12c automates it down to 1 line! | ||
| - | * [[https:// | + | * [[https:// |
| - | < | + | < |
| + | recover table adam.sales until time ' | ||
| + | </ | ||
| - | ====Restore | + | === Restore |
| To get a list of datafiles and their numbers, use ' | To get a list of datafiles and their numbers, use ' | ||
| - | < | + | < |
| + | rman target / | ||
| + | startup mount; | ||
| + | restore datafile 1; | ||
| + | recover datafile 1; | ||
| + | alter database open; | ||
| + | </ | ||
| or while database is open... | or while database is open... | ||
| - | < | + | < |
| + | rman target / | ||
| + | sql 'alter datafile 3 offline'; | ||
| + | restore datafile 3; | ||
| + | recover datafile 3; | ||
| + | sql 'alter tdatafile 3 online'; | ||
| + | </ | ||
| - | ====Restore datafile to a different location and recover==== | + | === Restore datafile to a different location and recover === |
| - | < | + | < |
| - | =====Restore database using RMAN===== | + | rman target / |
| - | If all else fails, restore the database to a time before the bad thing happened, then recover it by rolling forward with the archivelogs.<br /> | + | startup mount; |
| + | run { | ||
| + | set newname for datafile 2 to '/ | ||
| + | restore tablespace users; | ||
| + | switch datafile all; | ||
| + | recover tablespace users; | ||
| + | alter database open; | ||
| + | } | ||
| + | </ | ||
| + | ==== Restore database using RMAN ==== | ||
| + | If all else fails, restore the database to a time before the bad thing happened, then recover it by rolling forward with the archivelogs.\\ | ||
| Brief list of actions involved. Not all steps will be needed. Depends on same/ | Brief list of actions involved. Not all steps will be needed. Depends on same/ | ||
| - | < | + | < |
| - | Can do it by time, scn, logseq.<br /> | + | - create dsm.sys stanza to setup access to TSM server |
| + | - create tdpo.opt to allow Oracle access to TSM | ||
| + | - create dsm.opt to link tdpo.opt to dsm.sys | ||
| + | - create other required logging directories/ | ||
| + | - start idle Oracle instance | ||
| + | - restore recent controlfile from TSM | ||
| + | - shutdown instance and restart in mount mode | ||
| + | - restore database from backup | ||
| + | - recover database and roll forward to desired point-in-time | ||
| + | </ | ||
| + | Can do it by time, scn, logseq.\\ | ||
| Can do it in one step or two. | Can do it in one step or two. | ||
| - | < | + | < |
| + | export ORACLE_SID=< | ||
| + | . oraenv | ||
| + | sqlplus / as sysdba | ||
| + | startup mount | ||
| + | exit | ||
| + | |||
| + | cat start_restore.ksh<< | ||
| + | rman target / | ||
| + | connect catalog catusr/ | ||
| + | run { | ||
| + | allocate channel c1 device type ' | ||
| + | set until time " | ||
| + | restore database; | ||
| + | release channel c1; | ||
| + | } | ||
| + | EORMAN | ||
| + | </ | ||
| Add this if connecting to a catalog | Add this if connecting to a catalog | ||
| connect catalog catusr/ | connect catalog catusr/ | ||
| - | < | + | < |
| + | cat start_recover.ksh<< | ||
| + | rman target / | ||
| + | run { | ||
| + | allocate channel c1 device type ' | ||
| + | recover database until time " | ||
| + | release channel c1; | ||
| + | } | ||
| + | EORMAN | ||
| + | </ | ||
| or by scn... | or by scn... | ||
| - | < | + | < |
| + | rman target / catalog catusr/ | ||
| + | run { | ||
| + | set until scn=10328571660050; | ||
| + | recover database; | ||
| + | } | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| - | ====Lost the control files as well?==== | + | export ORACLE_SID=< |
| + | . oraenv | ||
| + | rman target / catalog catusr/ | ||
| + | startup mount; | ||
| + | run { | ||
| + | set until logseq 101145; | ||
| + | restore database; | ||
| + | recover database; | ||
| + | } | ||
| + | </ | ||
| + | === Lost the control files as well? === | ||
| Assuming database is started in NOMOUNT mode... | Assuming database is started in NOMOUNT mode... | ||
| - | ===Catalog available=== | + | == Catalog available == |
| Restore controlfile(s) to original location(s) | Restore controlfile(s) to original location(s) | ||
| - | < | + | < |
| + | RESTORE CONTROLFILE; | ||
| + | </ | ||
| Restore the controlfile but only to a location other than those mentioned in the CONTROL_FILES parameter | Restore the controlfile but only to a location other than those mentioned in the CONTROL_FILES parameter | ||
| - | < | + | < |
| + | RESTORE CONTROLFILE ... TO ' | ||
| + | </ | ||
| Restore controlfile(s) from specified file to original locations unless to ' | Restore controlfile(s) from specified file to original locations unless to ' | ||
| - | < | + | < |
| + | RESTORE CONTROLFILE ... FROM 'piece handle'; | ||
| + | RESTORE CONTROLFILE ... FROM 'piece handle' | ||
| + | RESTORE CONTROLFILE ... FROM TAG ' | ||
| + | RESTORE CONTROLFILE ... FROM TAG ' | ||
| + | </ | ||
| - | ===No catalog available=== | + | == No catalog available == |
| - | Note: If controlfile was backed up with autobackup format set to non-default location, format must be specified for restore with: SET CONTROLFILE AUTOBACKUP FORMAT '< | + | Note: If controlfile was backed up with autobackup format set to non-default location, format must be specified for restore with: SET CONTROLFILE AUTOBACKUP FORMAT '< |
| If catalog is not available, following is not possible. RMAN cannot know from where to restore it | If catalog is not available, following is not possible. RMAN cannot know from where to restore it | ||
| - | < | + | < |
| + | RESTORE CONTROLFILE; | ||
| + | </ | ||
| Restore controlfile(s) to original locations | Restore controlfile(s) to original locations | ||
| - | < | + | < |
| + | RESTORE CONTROLFILE FROM AUTOBACKUP; | ||
| + | </ | ||
| Restore the controlfile to specified location | Restore the controlfile to specified location | ||
| - | < | + | < |
| + | RESTORE CONTROLFILE FROM AUTOBACKUP... TO ' | ||
| + | </ | ||
| Restore controlfile(s) from specified file to original locations unless to ' | Restore controlfile(s) from specified file to original locations unless to ' | ||
| - | < | + | < |
| + | SET DBID=< | ||
| + | RESTORE CONTROLFILE ... FROM 'piece handle'; | ||
| + | RESTORE CONTROLFILE ... FROM 'piece handle' | ||
| + | </ | ||
| If catalog is not available, following is not possible. Catalog needs to be available to be able to restore from a tag | If catalog is not available, following is not possible. Catalog needs to be available to be able to restore from a tag | ||
| - | < | + | < |
| - | ====Another method using EMC Netbackup / Networker==== | + | RESTORE CONTROLFILE ... FROM TAG ' |
| - | < | + | RESTORE CONTROLFILE ... FROM TAG ' |
| + | </ | ||
| + | === Another method using EMC Netbackup / Networker === | ||
| + | < | ||
| + | RMAN> | ||
| + | connected to target database: INSA (not mounted) | ||
| - | =====Restore database after failed upgrade===== | + | RMAN> connect catalog * |
| - | ====Problem==== | + | connected to recovery catalog database |
| - | Upgrade has failed part way<br /> | + | |
| - | Catalog is still on old ORACLE_HOME<br /> | + | RMAN> run { |
| + | 2> set dbid=3273853992; | ||
| + | 3> | ||
| + | 4> | ||
| + | 5> } | ||
| + | executing command: SET DBID | ||
| + | database name is " | ||
| + | |||
| + | allocated channel: t1 | ||
| + | channel t1: SID=155 device type=SBT_TAPE | ||
| + | channel t1: NMDA Oracle v8.2.1 | ||
| + | |||
| + | Starting restore at 21-MAR-18 | ||
| + | |||
| + | channel t1: starting datafile backup set restore | ||
| + | channel t1: restoring control file | ||
| + | channel t1: reading from backup piece c-3273853992-20180319-0b | ||
| + | channel t1: piece handle=c-3273853992-20180319-0b tag=TAG20180319T103116 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:25 | ||
| + | output file name=/ | ||
| + | output file name=/ | ||
| + | output file name=/ | ||
| + | Finished restore at 21-MAR-18 | ||
| + | released channel: t1 | ||
| + | |||
| + | RMAN> **end-of-file** | ||
| + | |||
| + | RMAN> exit | ||
| + | |||
| + | rman | ||
| + | RMAN> connect target / | ||
| + | |||
| + | connected to target database: INSA (DBID=3273853992, | ||
| + | |||
| + | RMAN> run { | ||
| + | 2> allocate channel t1 type ' | ||
| + | 3> restore database; | ||
| + | 4> } | ||
| + | |||
| + | using target database control file instead of recovery catalog | ||
| + | allocated channel: t1 | ||
| + | channel t1: SID=155 device type=SBT_TAPE | ||
| + | channel t1: NMDA Oracle v8.2.1 | ||
| + | |||
| + | Starting restore at 21-MAR-18 | ||
| + | |||
| + | channel t1: starting datafile backup set restore | ||
| + | channel t1: specifying datafile(s) to restore from backup set | ||
| + | channel t1: restoring datafile 00006 to / | ||
| + | channel t1: reading from backup piece dbfull_INSA_3273853992_970341263_5178_1 | ||
| + | channel t1: piece handle=dbfull_INSA_3273853992_970341263_5178_1 tag=TAG20180309T191421 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:11:25 | ||
| + | ... | ||
| + | ... | ||
| + | channel t1: starting datafile backup set restore | ||
| + | channel t1: specifying datafile(s) to restore from backup set | ||
| + | channel t1: restoring datafile 00023 to / | ||
| + | channel t1: reading from backup piece dbfull_INSA_3273853992_970353194_5201_1 | ||
| + | channel t1: piece handle=dbfull_INSA_3273853992_970353194_5201_1 tag=TAG20180309T191421 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:25 | ||
| + | channel t1: starting datafile backup set restore | ||
| + | channel t1: specifying datafile(s) to restore from backup set | ||
| + | channel t1: restoring datafile 00003 to / | ||
| + | channel t1: reading from backup piece dbfull_INSA_3273853992_970353229_5202_1 | ||
| + | channel t1: piece handle=dbfull_INSA_3273853992_970353229_5202_1 tag=TAG20180309T191421 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:15 | ||
| + | channel t1: starting datafile backup set restore | ||
| + | channel t1: specifying datafile(s) to restore from backup set | ||
| + | channel t1: restoring datafile 00005 to / | ||
| + | channel t1: reading from backup piece dbfull_INSA_3273853992_970353254_5203_1 | ||
| + | channel t1: piece handle=dbfull_INSA_3273853992_970353254_5203_1 tag=TAG20180309T191421 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:15 | ||
| + | channel t1: starting datafile backup set restore | ||
| + | channel t1: specifying datafile(s) to restore from backup set | ||
| + | channel t1: restoring datafile 00004 to / | ||
| + | channel t1: reading from backup piece dbfull_INSA_3273853992_970353261_5204_1 | ||
| + | channel t1: piece handle=dbfull_INSA_3273853992_970353261_5204_1 tag=TAG20180309T191421 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:15 | ||
| + | Finished restore at 21-MAR-18 | ||
| + | released channel: t1 | ||
| + | |||
| + | RMAN> exit | ||
| + | |||
| + | SQL> | ||
| + | SQL> select open_mode from v$database; | ||
| + | |||
| + | OPEN_MODE | ||
| + | -------------------- | ||
| + | MOUNTED | ||
| + | |||
| + | SQL> select controlfile_type from v$database; | ||
| + | |||
| + | CONTROL | ||
| + | ------- | ||
| + | BACKUP | ||
| + | |||
| + | SQL> exit | ||
| + | Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production | ||
| + | With the Partitioning, | ||
| + | / | ||
| + | |||
| + | Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 21 13:33:19 2018 | ||
| + | |||
| + | Copyright (c) 1982, 2011, Oracle and/or its affiliates. | ||
| + | |||
| + | RMAN> exit | ||
| + | |||
| + | |||
| + | / | ||
| + | |||
| + | Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 21 13:44:20 2018 | ||
| + | |||
| + | Copyright (c) 1982, 2011, Oracle and/or its affiliates. | ||
| + | |||
| + | RMAN> connect target / | ||
| + | |||
| + | connected to target database: INSA (DBID=3273853992, | ||
| + | |||
| + | RMAN> connect catalog rman/ | ||
| + | |||
| + | connected to recovery catalog database | ||
| + | |||
| + | RMAN> run { | ||
| + | 2> allocate channel t1 type ' | ||
| + | 3> recover database ; | ||
| + | 4> } | ||
| + | |||
| + | allocated channel: t1 | ||
| + | channel t1: SID=155 device type=SBT_TAPE | ||
| + | channel t1: NMDA Oracle v8.2.1 | ||
| + | |||
| + | Starting recover at 21-MAR-18 | ||
| + | |||
| + | starting media recovery | ||
| + | |||
| + | channel t1: starting archived log restore to default destination | ||
| + | channel t1: restoring archived log | ||
| + | archived log thread=1 sequence=1242 | ||
| + | channel t1: reading from backup piece arclog_INSA_3273853992_970346643_5184_1 | ||
| + | channel t1: piece handle=arclog_INSA_3273853992_970346643_5184_1 tag=TAG20180309T204403 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:15 | ||
| + | archived log file name=/ | ||
| + | ... | ||
| + | ... | ||
| + | channel t1: starting archived log restore to default destination | ||
| + | channel t1: restoring archived log | ||
| + | archived log thread=1 sequence=1338 | ||
| + | channel t1: reading from backup piece arclog_INSA_3273853992_971166624_5584_1 | ||
| + | channel t1: piece handle=arclog_INSA_3273853992_971166624_5584_1 tag=TAG20180319T083024 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:15 | ||
| + | archived log file name=/ | ||
| + | channel t1: starting archived log restore to default destination | ||
| + | channel t1: restoring archived log | ||
| + | archived log thread=1 sequence=1339 | ||
| + | channel t1: reading from backup piece arclog_INSA_3273853992_971173822_5588_1 | ||
| + | channel t1: piece handle=arclog_INSA_3273853992_971173822_5588_1 tag=TAG20180319T103022 | ||
| + | channel t1: restored backup piece 1 | ||
| + | channel t1: restore complete, elapsed time: 00:00:15 | ||
| + | archived log file name=/ | ||
| + | unable to find archived log | ||
| + | archived log thread=1 sequence=1340 | ||
| + | released channel: t1 | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-03002: failure of recover command at 03/21/2018 14:21:52 | ||
| + | RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1340 and starting SCN of 274665299833 | ||
| + | |||
| + | RMAN> alter database open resetlogs; | ||
| + | |||
| + | database opened | ||
| + | new incarnation of database registered in recovery catalog | ||
| + | starting full resync of recovery catalog | ||
| + | full resync complete | ||
| + | |||
| + | RMAN> exit | ||
| + | </ | ||
| + | |||
| + | ==== Restore database after failed upgrade ==== | ||
| + | === Problem === | ||
| + | Upgrade has failed part way\\ | ||
| + | Catalog is still on old ORACLE_HOME\\ | ||
| Database is already on new ORACLE_HOME | Database is already on new ORACLE_HOME | ||
| - | ====Solution==== | + | === Solution === |
| - | * Shut the database down | + | * Shut the database down |
| - | < | + | < |
| - | * Set old home in oratab | + | shutdown immediate |
| - | < | + | </ |
| - | * Restore a controlfile from before the upgrade | + | * Set old home in oratab |
| - | < | + | < |
| - | * Read the controlfile | + | vi / |
| - | < | + | </ |
| - | * Restore the database | + | * Restore a controlfile from before the upgrade |
| - | < | + | < |
| - | * Recover the database | + | rman target / |
| - | < | + | connect catalog user/pwd@cat |
| - | * Start the database | + | startup nomount; |
| - | < | + | list backup of controlfile; |
| + | restore controlfile from autobackup; | ||
| + | </ | ||
| + | * Read the controlfile | ||
| + | < | ||
| + | startup mount; | ||
| + | </ | ||
| + | * Restore the database | ||
| + | < | ||
| + | run { | ||
| + | allocate channel c1 device type ' | ||
| + | set until time " | ||
| + | restore database; | ||
| + | release channel c1; | ||
| + | } | ||
| + | </ | ||
| + | * Recover the database | ||
| + | < | ||
| + | run { | ||
| + | allocate channel c1 device type ' | ||
| + | set until time " | ||
| + | recover database; | ||
| + | release channel c1; | ||
| + | } | ||
| + | </ | ||
| + | * Start the database | ||
| + | < | ||
| + | alter database open resetlogs; | ||
| + | </ | ||
| - | =====Duplicating an 11gR2 Oracle database with no connection to the target===== | + | ==== Duplicating an 11gR2 Oracle database with no connection to the target ==== |
| - | * from [[http:// | + | * from [[http:// |
| - | One of the new Backup and Recovery features of Oracle 11g Release 2 (11gR2) is the ability to duplicate a database without connecting to the target database.<br /> | + | One of the new Backup and Recovery features of Oracle 11g Release 2 (11gR2) is the ability to duplicate a database without connecting to the target database.\\ |
| - | In RMAN terminology, | + | In RMAN terminology, |
| - | In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, data file copies, archived logs, and control file copies for database duplication.<br /> | + | In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, data file copies, archived logs, and control file copies for database duplication.\\ |
| - | This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.<br /> | + | This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.\\ |
| - | <br /> | + | |
| - | This came in handy for me recently on a customer project. They wanted me to test a disaster recovery scenario in which the production database server was completely gone and we needed to restore it from tape onto another server.<br /> | + | This came in handy for me recently on a customer project. They wanted me to test a disaster recovery scenario in which the production database server was completely gone and we needed to restore it from tape onto another server.\\ |
| - | This is a fairly typical DR situation, but in this case it was made more difficult because the directory structure on the test server didn’t match that of the production server.<br /> | + | This is a fairly typical DR situation, but in this case it was made more difficult because the directory structure on the test server didn’t match that of the production server.\\ |
| - | Specifically, | + | Specifically, |
| - | This means that all file locations need to be changed as part of the duplicate procedure.<br /> | + | This means that all file locations need to be changed as part of the duplicate procedure.\\ |
| - | This can all be done in one single RMAN command.<br /> | + | This can all be done in one single RMAN command.\\ |
| - | <br /> | + | |
| - | Note: One thing that makes this process easier is to enable controlfile autobackups on the production database.<br /> | + | Note: One thing that makes this process easier is to enable controlfile autobackups on the production database.\\ |
| - | This is a good practice in general and should be configured for all databases.< | + | This is a good practice in general and should be configured for all databases.\\ |
| - | < | + | |
| - | < | + | <code> |
| - | Here are the steps that I followed:<br /> | + | rman target |
| - | <br /> | + | configure controlfile autobackup on; |
| - | Step1) Copy the most recent backup files and archivelog files to the test server.<br /> | + | </ |
| - | In this case, we restored from tape to the location c:\prod backup files. The backup files and archivelogs come from the following locations:<br /> | + | Here are the steps that I followed:\\ |
| - | <br /> | + | |
| - | E: | + | Step1) Copy the most recent backup files and archivelog files to the test server.\\ |
| - | E: | + | In this case, we restored from tape to the location c:\\prod backup files. The backup files and archivelogs come from the following locations:\\ |
| - | E: | + | |
| - | <br /> | + | E:\\app\\flash_recovery_area\\prod\\ARCHIVELOG\\2013_03_21\\ |
| - | Step 2) Create a file called INITPROD2.ORA in %ORACLE_HOME%\database containing one line: db_name=PROD2<br /> | + | E:\\app\\flash_recovery_area\\prod\\BACKUPSET\\2013_03_21\\ |
| - | <br /> | + | E:\\app\\flash_recovery_area\\prod\\AUTOBACKUP\\2013_03_21\\ |
| - | Step 3) At a command prompt:<br /> | + | |
| - | < | + | Step 2) Create a file called INITPROD2.ORA in %ORACLE_HOME%\\database containing one line: db_name=PROD2\\ |
| - | Step 4) At the RMAN prompt:<br /> | + | |
| - | <br /> | + | Step 3) At a command prompt:\\ |
| - | < | + | < |
| - | That's all it takes! As you can see, I changed all file location parameters and converted datafile and logfile names with the _CONVERT parameters.<br /> | + | set oracle_sid=prod2 |
| - | You can set any parameters for the new database in this way. For example you may want to disable all database jobs in the duplicate database by setting job_queue_processes=0.<br /> | + | oradim –new –sid prod2 |
| - | Or, change the size of the SGA for a smaller server. RMAN will create the PROD2 database, perform as much recovery as possible given the available archive logs and open the database with the resetlogs option.<br /> | + | sqlplus / as sysdba |
| + | startup nomount | ||
| + | exit | ||
| + | rman auxiliary / msglog=c: | ||
| + | </ | ||
| + | Step 4) At the RMAN prompt:\\ | ||
| + | |||
| + | < | ||
| + | duplicate database to prod2 | ||
| + | spfile | ||
| + | set control_files=' | ||
| + | set db_file_name_convert=' | ||
| + | set log_file_name_convert=' | ||
| + | set db_recovery_file_dest=' | ||
| + | set diagnostic_dest=' | ||
| + | backup location " | ||
| + | </ | ||
| + | That's all it takes! As you can see, I changed all file location parameters and converted datafile and logfile names with the _CONVERT parameters.\\ | ||
| + | You can set any parameters for the new database in this way. For example you may want to disable all database jobs in the duplicate database by setting job_queue_processes=0.\\ | ||
| + | Or, change the size of the SGA for a smaller server. RMAN will create the PROD2 database, perform as much recovery as possible given the available archive logs and open the database with the resetlogs option.\\ | ||
| This is a really nice feature of RMAN. | This is a really nice feature of RMAN. | ||
| - | =====Update RMAN configuration parameters from Sql*Plus===== | + | ==== Update RMAN configuration parameters from Sql*Plus ==== |
| - | If you backup the controlfile with:<br /> | + | If you backup the controlfile with:\\ |
| - | alter database backup controlfile to trace as '/ | + | alter database backup controlfile to trace as '/ |
| This example found in backup controlfile | This example found in backup controlfile | ||
| - | < | + | < |
| + | -- Configure snapshot controlfile filename | ||
| + | EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/ | ||
| + | -- Configure RMAN configuration record 1 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | -- Configure RMAN configuration record 2 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | -- Configure RMAN configuration record 3 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | -- Configure RMAN configuration record 4 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | -- Configure RMAN configuration record 5 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | -- Configure RMAN configuration record 6 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | -- Configure RMAN configuration record 7 | ||
| + | VARIABLE RECNO NUMBER; | ||
| + | EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(' | ||
| + | </ | ||
| - | =====Script to reorganise datafiles into neat new filesystems===== | + | ==== Script to reorganise datafiles into neat new filesystems ==== |
| - | This script can generate 'set newname' | + | This script can generate 'set newname' |
| If the files are scattered all over the place, this will gather them together and spread the datafiles evenly over the available space. | If the files are scattered all over the place, this will gather them together and spread the datafiles evenly over the available space. | ||
| - | < | + | < |
| - | =====RMAN reporting by André Araujo (check the status of the backups using the output in the database)===== | + | -- ============================================================================= |
| - | Reference: [[http:// | + | -- Filename |
| + | -- Description : Shares the datafiles on one host over several | ||
| + | -- | ||
| + | -- | ||
| + | -- Notes : Fill the l_datafile_tab and optionally the l_archive_tab | ||
| + | -- with the names of the new filesystems | ||
| + | -- | ||
| + | -- Modification History | ||
| + | -- ==================== | ||
| + | -- Who | ||
| + | -- ================= ========= ================================================= | ||
| + | -- Stuart Barkley | ||
| + | -- Stuart Barkley | ||
| + | -- Stuart Barkley | ||
| + | -- ============================================================================= | ||
| + | |||
| + | spool reorganise_datafiles | ||
| + | set serveroutput on format wrapped size unlimited | ||
| + | set termo on | ||
| + | set trims on | ||
| + | set lines 200 | ||
| + | set pages 1000 | ||
| + | set feedb off | ||
| + | set verif off | ||
| + | create or replace type l_filesystem_obj as object | ||
| + | ( | ||
| + | name varchar2(100) | ||
| + | , | ||
| + | ) | ||
| + | / | ||
| + | |||
| + | create or replace type l_filesystem_tabtype is table of l_filesystem_obj | ||
| + | / | ||
| + | |||
| + | declare | ||
| + | |||
| + | -- ========================================================= | ||
| + | -- add/ | ||
| + | -- of the new mount points for normal datafiles | ||
| + | -- ========================================================= | ||
| + | l_datafile_tab | ||
| + | l_filesystem_obj( '/ | ||
| + | , l_filesystem_obj( '/ | ||
| + | , l_filesystem_obj( '/ | ||
| + | , l_filesystem_obj( '/ | ||
| + | ); | ||
| + | |||
| + | -- ========================================================= | ||
| + | -- add/ | ||
| + | -- of the new mount points for archive log datafiles | ||
| + | -- ========================================================= | ||
| + | l_archive_tab | ||
| + | l_filesystem_obj( '/ | ||
| + | ); | ||
| + | |||
| + | -- ===================================== | ||
| + | -- gather info about data and temp files | ||
| + | -- ===================================== | ||
| + | cursor c_datafiles is | ||
| + | select file_id | ||
| + | , file_name | ||
| + | , substr(file_name, | ||
| + | , round(sum(bytes)/ | ||
| + | , ' | ||
| + | from | ||
| + | where 1=1 | ||
| + | and upper(file_name) not like ' | ||
| + | group by file_id | ||
| + | , file_name | ||
| + | union | ||
| + | select file_id | ||
| + | , file_name | ||
| + | , substr(file_name, | ||
| + | , round(sum(bytes)/ | ||
| + | , ' | ||
| + | from | ||
| + | where 1=1 | ||
| + | group by file_id | ||
| + | , file_name | ||
| + | order by 4 desc | ||
| + | ; | ||
| + | |||
| + | -- =================================== | ||
| + | -- gather info about archive log files | ||
| + | -- =================================== | ||
| + | cursor c_archivefiles is | ||
| + | select file_id | ||
| + | , file_name | ||
| + | , substr(file_name, | ||
| + | , round(sum(bytes)/ | ||
| + | , ' | ||
| + | from | ||
| + | where 1=1 | ||
| + | and upper(file_name) like ' | ||
| + | group by file_id | ||
| + | , file_name | ||
| + | order by 4 desc | ||
| + | ; | ||
| + | |||
| + | l_running_total | ||
| + | l_fs_num | ||
| + | l_total_db_size | ||
| + | |||
| + | begin | ||
| + | |||
| + | dbms_output.enable(null); | ||
| + | |||
| + | for r_row in c_datafiles | ||
| + | loop | ||
| + | |||
| + | -- =========================================== | ||
| + | -- when we get to the last bucket, start again | ||
| + | -- =========================================== | ||
| + | if l_fs_num > nvl(l_datafile_tab.count, | ||
| + | | ||
| + | end if; | ||
| + | |||
| + | -- ============================== | ||
| + | -- update the size of this bucket | ||
| + | -- ============================== | ||
| + | l_datafile_tab(l_fs_num).bigness := l_datafile_tab(l_fs_num).bigness + r_row.gb; | ||
| + | |||
| + | -- ===================== | ||
| + | -- print out a good line | ||
| + | -- ===================== | ||
| + | dbms_output.put_line ( 'set newname for ' | ||
| + | l_fs_num := l_fs_num+1; | ||
| + | |||
| + | end loop; -- c_datafiles | ||
| + | |||
| + | |||
| + | -- ========================================= | ||
| + | -- datafiles done, now the archive log files | ||
| + | -- ========================================= | ||
| + | l_fs_num := 1; | ||
| + | for r_row in c_archivefiles | ||
| + | loop | ||
| + | |||
| + | -- =========================================== | ||
| + | -- when we get to the last bucket, start again | ||
| + | -- =========================================== | ||
| + | if l_fs_num > nvl(l_archive_tab.count, | ||
| + | | ||
| + | end if; | ||
| + | |||
| + | -- ============================== | ||
| + | -- update the size of this bucket | ||
| + | -- ============================== | ||
| + | l_archive_tab(l_fs_num).bigness := l_archive_tab(l_fs_num).bigness + r_row.gb; | ||
| + | |||
| + | -- ===================== | ||
| + | -- print out a good line | ||
| + | -- ===================== | ||
| + | dbms_output.put_line ( 'set newname for ' | ||
| + | l_fs_num := l_fs_num+1; | ||
| + | |||
| + | end loop; -- c_archivefiles | ||
| + | |||
| + | |||
| + | -- ========= | ||
| + | -- summarise | ||
| + | -- ========= | ||
| + | dbms_output.put_line (null); | ||
| + | dbms_output.put_line ( ' | ||
| + | dbms_output.put_line ( ' | ||
| + | for l_fs_num in 1 .. nvl(l_datafile_tab.count, | ||
| + | loop | ||
| + | dbms_output.put_line ( 'Mount point ' | ||
| + | end loop; | ||
| + | |||
| + | for l_fs_num in 1 .. nvl(l_archive_tab.count, | ||
| + | loop | ||
| + | dbms_output.put_line ( 'Mount point ' | ||
| + | end loop; | ||
| + | dbms_output.put_line (null); | ||
| + | |||
| + | |||
| + | exception | ||
| + | |||
| + | when others then | ||
| + | dbms_output.put_line(sqlerrm); | ||
| + | raise_application_error(-20000,' | ||
| + | |||
| + | end; | ||
| + | / | ||
| + | |||
| + | spool off | ||
| + | |||
| + | drop type l_filesystem_tabtype | ||
| + | / | ||
| + | |||
| + | drop type l_filesystem_obj | ||
| + | / | ||
| + | </ | ||
| + | ==== RMAN reporting by André Araujo (check the status of the backups using the output in the database) ==== | ||
| + | Reference: [[http:// | ||
| Reproduced here in case it disappears from the internets. | Reproduced here in case it disappears from the internets. | ||
| - | A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views.<br /> | + | A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views.\\ |
| - | These views show past RMAN jobs as well as jobs currently running.<br /> | + | These views show past RMAN jobs as well as jobs currently running.\\ |
| - | Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.<br /> | + | Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.\\ |
| - | <br /> | + | |
| - | For the queries in this post I need only four of those views:<br /> | + | For the queries in this post I need only four of those views:\\ |
| - | <br /> | + | |
| - | * V$BACKUP_SET | + | * V$BACKUP_SET |
| - | * V$BACKUP_SET_DETAILS | + | * V$BACKUP_SET_DETAILS |
| - | * V$RMAN_BACKUP_JOB_DETAILS | + | * V$RMAN_BACKUP_JOB_DETAILS |
| - | * GV$RMAN_OUTPUT | + | * GV$RMAN_OUTPUT |
| **Query 1 (Backup jobs' status and metadata)** | **Query 1 (Backup jobs' status and metadata)** | ||
| - | < | + | < |
| + | set lines 220 | ||
| + | set pages 1000 | ||
| + | col cf for 9,999 | ||
| + | col df for 9,999 | ||
| + | col elapsed_seconds heading " | ||
| + | col i0 for 9,999 | ||
| + | col i1 for 9,999 | ||
| + | col l for 9,999 | ||
| + | col input_type for a13 | ||
| + | col dow for a11 | ||
| + | col start_time for a23 | ||
| + | col end_time | ||
| + | col output_mbytes for 9,999,999 heading " | ||
| + | col session_recid for 999999 heading " | ||
| + | col session_stamp for 99999999999 heading " | ||
| + | col status for a10 trunc | ||
| + | col time_taken_display for a10 heading " | ||
| + | col output_instance for 9999 heading " | ||
| + | select j.session_recid | ||
| + | , j.session_stamp | ||
| + | , to_char(j.start_time, | ||
| + | , to_char(j.end_time, | ||
| + | , (j.output_bytes/ | ||
| + | , j.status | ||
| + | , j.input_type | ||
| + | , decode(to_char(j.start_time, | ||
| + | , 1, ' | ||
| + | , 3, ' | ||
| + | , 5, ' | ||
| + | , 7, ' | ||
| + | , j.elapsed_seconds | ||
| + | , j.time_taken_display | ||
| + | , x.cf, x.df, x.i0, x.i1, x.l | ||
| + | , ro.inst_id output_instance | ||
| + | from V$RMAN_BACKUP_JOB_DETAILS j | ||
| + | left outer join (select | ||
| + | | ||
| + | | ||
| + | | ||
| + | and d.backup_type||d.incremental_level = ' | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count | ||
| + | where s.input_file_scan_only = ' | ||
| + | group by d.session_recid, | ||
| + | on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp | ||
| + | left outer join (select o.session_recid, | ||
| + | from GV$RMAN_OUTPUT o | ||
| + | group by o.session_recid, | ||
| + | ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp | ||
| + | where j.start_time > trunc(sysdate)-& | ||
| + | order by j.start_time; | ||
| + | </ | ||
| **Query 2 (Backup set details)** | **Query 2 (Backup set details)** | ||
| - | < | + | < |
| + | set lines 220 | ||
| + | set pages 1000 | ||
| + | col backup_type for a4 heading " | ||
| + | col controlfile_included heading " | ||
| + | col incremental_level heading "INCR LVL" | ||
| + | col completion_time for a23 | ||
| + | col pieces for 999 heading " | ||
| + | col elapsed_seconds heading " | ||
| + | col device_type for a10 trunc heading " | ||
| + | col compressed for a4 heading " | ||
| + | col output_mbytes for 9,999,999 heading " | ||
| + | col input_file_scan_only for a4 heading " | ||
| + | select | ||
| + | d.bs_key, d.backup_type, | ||
| + | to_char(d.start_time, | ||
| + | to_char(d.completion_time, | ||
| + | d.elapsed_seconds, | ||
| + | from V$BACKUP_SET_DETAILS d | ||
| + | join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count | ||
| + | where session_recid = & | ||
| + | and session_stamp = & | ||
| + | order by d.start_time; | ||
| + | </ | ||
| **Query 3 (Backup job output)** | **Query 3 (Backup job output)** | ||
| - | < | + | < |
| + | set lines 200 pages 1000 verif off headi off newpa none feedb off | ||
| + | select output | ||
| + | from | ||
| + | where session_recid = & | ||
| + | and session_stamp = & | ||
| + | order by recid; | ||
| + | </ | ||
| + | |||
| + | ==== RMAN tracing/ | ||
| + | < | ||
| + | 1. Please login with rman log and trace >> rman target <un/pw@target_db> | ||
| + | 2. Please rename the log and the tracefiles following your naming convention. | ||
| + | 3. Select two datafiles as samples, one biggest datafile and one smallest datafile. | ||
| + | 4. Take two samples of backup one onto u03 and one onto u01 naming the log and the tracefiles accordingly. You can follow this example : | ||
| + | run | ||
| + | { | ||
| + | allocate channel d1 device type disk format ' | ||
| + | backup database include current controlfile; | ||
| + | release channel d1; | ||
| + | } | ||
| + | 5.During the backup, we will hanganalyze the take the system state dump. Performance team will analyze them | ||
| + | |||
| + | Hang Analyzer and System state Dump: | ||
| + | =================================== | ||
| + | $ sqlplus /nolog | ||
| + | connect / as sysdba | ||
| + | oradebug setmypid | ||
| + | oradebug unlimit | ||
| + | oradebug tracefile_name | ||
| + | oradebug hanganalyze 3 | ||
| + | wait 90 seconds | ||
| + | oradebug hanganalyze 3 | ||
| + | exit | ||
| + | |||
| + | |||
| + | $ sqlplus /nolog | ||
| + | connect / as sysdba | ||
| + | oradebug setmypid | ||
| + | oradebug unlimit | ||
| + | oradebug tracefile_name | ||
| + | oradebug dump systemstate 255 | ||
| + | wait 90 seconds | ||
| + | oradebug dump systemstate 255 | ||
| + | wait 90 seconds | ||
| + | oradebug dump systemstate 255 | ||
| + | exit | ||
| + | </ | ||
| - | =====RMAN tracing/ | ||
| - | < | ||
rman.1544130327.txt.gz · Last modified: 2018/12/06 21:05 by 91.177.234.129