Oracle Cold Backup Script

First, the principle

2. 1. Cold backup

   The process of completing all physical system file copies in a closed state, also known as offline backup

   Suitable for non-archive mode, the database is in a consistent state

3. Steps

   Start by getting all the physical file locations of the database running in the running library, and then shutting down the database (shutdown) within the plan

   Perform a copy of the physical home to the backup path or backup device

   Start the database immediately after the backup is complete to provide normal service

4. Summary-Benefits

   The concept is easy to understand in cold standby mode, and the files that will need to be backed up are copied to a secure location

   Simple operation, no need for too much intervention

   Easy to recover to a point in time (just copy the file back)

   Can be combined with the archiving method to restore the database "up-to-date state".

5. Summary-Disadvantages

   Database must be in a consistent shutdown state when backing up

   can only provide recovery to a point in time

   Backup time is slow, especially the large performance impact of data volume is relatively large

   Cannot implement table-and user-level data recovery
   Second, the case

    首先应该在相关视图里查出数据库的数据文件，日志文件，控制文件，临时文件所在的位置。注意：不要直接把oradata下的cp就行了，因为生产库里各个文件通常分布在不同的磁盘，不同的地方，所以在去视图里获得真实路径 。

1). To see if the database is CDB
[Email protected]>select name, decode (CDB, ' YES ', ' multitenant Option enabled ', ' Regular 12c Database: ') "multitenant Option ", Open_mode, con_id from V$database;

NAME multitenant Option Open_mode con_id

Newtest multitenant Option enabled READ WRITE 0
Yes indicates that the database is CDB, if no means no-cdb (normal database)
2). View information about instances and databases
[Email protected]>select instance_name,version,status,archiver,database_status from v$instance;

instance_name VERSION STATUS ARCHIVE database_status

Newtest 12.2.0.1.0 OPEN STARTED ACTIVE
[Email protected]>select dbid,name,log_mode from V$database;

  DBID NAME      LOG_MODE

1779551310 newtest ARCHIVELOG
[Email protected]>set linesize 180
[Email protected]>col GUID format A40
[Email protected]>col name format A20
[Email Protected]>select con_id, dbid, GUID, name, open_mode from V$pdbs;

CON_ID       DBID GUID                                     NAME                 OPEN_MODE

     2  603865257 32CAC9A05E614355926257BE671D9AFC         PDB$SEED             READ ONLY     3 3394996704 24549D14BBEB417BAB312BF19BC5259E         PDBTEST              READ WRITE     4   50957894 D4B2FCAAC2B944CC8AF5864EC7CB853C         CLONEPDB_PLUG        READ WRITE

3). View data files and status information

[Email protected]>col file_name format A60
[Email protected]>select file_name,tablespace_name,status,online_status from Dba_data_files;

file_name tablespaceNAME STATUS ONLINE

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSTEM01. DBF System AVAILABLE System
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSAUX01. DBF Sysaux AVAILABLE ONLINE
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01. DBF UNDOTBS1 AVAILABLE ONLINE
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\USERS01. DBF USERS AVAILABLE ONLINE
[Email protected]>col con_id format 99
[Email protected]>col name format A80
[Email protected]>select con_id,file#,name,status,enabled from V$datafile order by 1;

con_id file# NAME STATUS ENABLED

 1 1 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSTEM01. DBF SYSTEM READ WRITE 1 3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSAUX01. DBF ONLINE READ WRITE 1 5 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01. DBF ONLINE READ WRITE 1 7 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\USERS01. DBF ONLINE READ WRITE 2 2 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\SYSTEM01. DBF SYSTEM READ WRITE 2 4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\SYSAUX01. DBF ONLINE READ WRITE 2 6 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\UNDOTBS01. DBF ONLINE READ WRITE 3 8 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\SYSTEM01. DBF SYSTEM READ WRITE 3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\USERS01. DBF ONLINE READ WRITE 3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\UNDOTBS01. DBF ONLINE READ WRITE 3 9 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\SYSAUX01. DBF ONLINE READ WRITE

con_id file# NAME STATUS ENABLED

 4         42 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF         ONLINE  READ WRITE 4         41 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF          ONLINE  READ WRITE 4         40 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF          SYSTEM  READ WRITE 4         43 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF           ONLINE  READ WRITE

Rows selected.
4). View Temporary files
[Email protected]>col name format A100
[Email protected]>select name from V$tempfile;

NAME

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\TEMP01. Dbf
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\TEMP012017-12-13_17-23-31-944-PM. Dbf
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\TEMP012017-12-13_17-23-31-944-PM. Dbf
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\TEMP012017-12-13_17-23-31-944-PM. Dbf

Oracle Cold Backup Script