Oracle Advanced Training Course 5th learn the pages in the notes of the notebook and Oracle9i Database Management Basics II ed 1.1 vol.2.pdf correspondence guide: http://blog.csdn.net/magus_yang/archive/2006/10/10/1328283.aspx Author: jackyang (JackYang.sh@gmail.com) Date: this course contains multiple labs and has not yet passed the computer verification. Review the last lesson, which is not completely restored by media, must be performed in archive mode, not in non-archive mode. Incomplete restoration of media, even if only one file is damaged, the data file must be restored. If the control file is not damaged during incomplete media recovery, do not use the original backup control file. Redo log file is a copy of the Oracle database. P93 restores online redo logs based on cancellation. Backup is not allowed in archive mode. P94 if a connection file is lost, you can view the V $ log_history; archive log serial number to 4748 and start online redo log files. Restore all incomplete media. Use resetlogs p96 when you open the database. If the control file is not lost, you do not need to restore it. During the recover database operation, useing backup controlfile P97 will be added later to delete a tablespace by mistake. The restoration method in the previous lesson will not work. Only data files are restored, but control files are not restored. You must use an earlier control file to restore the tablespace.
Lab 1:Objective: to restore the deleted tablespace 1. e:/ora01g> sqlplus/nolog 2. SQL> connect sys/ora123 as sysdba 3. SQL> startup 4. SQL> archive log List 5. SQL> select name from V $ tablespace; 6. SQL> Create tablespace data1 datafile 'e:/ora01g/oradata/db1/data1.dbf' size 2 m; 7. SQL> Create Table dept1 tablespace data1 as select * from Scott. dept; 8. SQL> insert into dept1 (deptno. dname) values (28, 'before drop'); 9. SQL> Create Ta Blespace data2 datafile 'e:/ora01g/oradata/db1/data2.dbf' size 2 m; 10. SQL> Create Table dept2 tablespace data2 as select * from Scott. dept; 11. SQL> insert into dept2 (deptno. dname) values (28, 'before drop'); 12. SQL> select * From dept1; 13. SQL> select * From dept2; 14. SQL> alter database begin backup; To perform hot backup when a backup database is opened, you must first execute the command alter database begin backup to freeze the data files. Write all the modifications to the redo log file instead of the data file. 15. SQL> host copy E:/ora01g/oradata/db1 /*. dbf d:/oradata/db1/hot 16. SQL> alter Database End backup; 17. SQL> alter system archive log current; forcibly archive immediately after the backup ends 18. SQL> alter Database Backup controlfile to 'd:/oradata/db1/hot/con1.ctl 'reuse; backup control files are deleted by mistake due to tablespace, you must use the control file to restore the reuse to overwrite the original file in the Backup Directory 19. SQL> drop tablespace data1 including contents. SQL> select * From dept1; query failed because the tablespace data1 has been deleted 21. SQL> select * From dept2; query successful, because tablespace data2 is not deleted 22. SQL> insert into dept2 (deptno, dname) values (30, 'after drop'); after deleting the tablespace, insert a piece of simulation data 23. SQL> select * From dept2; 24. SQL> commit; 25. SQL> alter system archive log current; 26. SQL> show parameter background_dump_dest; 27. SQL> show parameter user_dump_dest; E:/ora01g/oradata/db1/udump path contains the alter_db1 File A row of drop tablespace data1 including contents indicates the start time and end time for deleting the tablespace data1. 28. SQL> when shutdown immediate is not completely restored, all data files must be restored when the database is closed. Therefore, the database must be closed first. 29. during normal operations, the following should be backed up for bad databases, and the redo log file should also be backed up. This saves time. 30. SQL> host Copy D:/oradata/db1/hot /*. dbf e:/ora01g/oradata/db1 restore all data files 31. SQL> host Copy D:/oradata/db1/hot/con1.ctl E:/ora01g/oradata/db1/con1.ctl 32. SQL> host Copy D:/oradata/db1/hot/con1.ctl E:/ora01g/oradata/db1/con2.ctl two control files are the same, so you only need to back up one. But now we have to restore two 33. SQL> startup Mount 34. SQL> recover database until time '2017-10-12 18:55:20 'using backup controlfile; go to the backup directory to view the existence of the arc archive file and press Enter. If the archive file does not exist, the path of the cancel control file must be specified in the initialization parameter file. Using backup controlfile tells the recover command to restore the control file. 35. SQL> alter database open resetlogs; 36. SQL> select * From dept1; dept1 restores 37. SQL> select * From dept2; before drop records, after drop records no 38. SQL> select * from V $ log; sequence number is restored to 1 39. after SQL> alter system is restored, you must make a full backup of the database. If no full backup is performed, the next Database Failure will make it impossible to recover. Because the current backup is no longer available. P99 the current redo log file is lost and the backup redo log file is lost. The redo logo file content is lost when it is not archived. When the redo logo file of a backup is lost, cancel is used. Currently, this refers to the files currently in use by the database. All data files backed up earlier must be restored. Otherwise, the recover operation cannot be performed.
Lab 2:Objective: to restore a lost redo log file 1. start Oracle service 2. sqlplus/nolog 3. SQL> connect sys/ora123 @ db4 as sysdba network connection 4. SQL> startup 5. SQL> archive log list determines that the database is in archive mode. 6. SQL> alter database begin backup; a backup is required first. SQL> host copy E:/ora01g/oradata/db4 /*. dbf d:/oradata/db4/hot 8. SQL> alter Database End backup; 9. SQL> alter system archive log current; current is currently using the redo log file active to indicate the information recorded in the redo log file, and the database Buffer The dirty data in the partition is not written into the data file. Inactive indicates the information recorded in the redo log file. The dirty data corresponding to the database buffer has been written into the data file. 10. SQL> alter Database Backup controlfile to 'd:/oradata/db4/hot/con1.ctl 'reuse; backup control file 11. SQL> select * from V $ log; 12. SQL> shutdown immediate; after shutting down the database, delete redo01a and redo01b 13 in the ora01g/oradata/db4/path. SQL> startup Mount; 14. SQL> alter database clear logfile group 1; redo01a and redo02b redo log files are rebuilt, but empty. Redologfile whose status is inactive is rebuilt. 15. SQL> alter database open; 16. SQL> select * from V $ log; 17. SQL> shutdown immediate; after shutting down the database, simulate a fault and delete the currently used redo03a and redo03b. 18. SQL> startup Mount; 19. SQL> alter database clear logfile group 3; cannot open 20. SQL> host Copy D:/oradata/db4/hot /*. dbf e:/ora01g/oradata/db4 restore all data files 21. SQL> recover database until cancel; if you do not need to control the file, when you need to recover file 48, cancel 22 is required because the current log file is not archived. SQL> alter database open resetlogs; 23. SQL> select * from Scott. dept; 24. SQL> select name from V $ controlfile; 25. SQL> select name from V $ datafile;
Lab 3:Objective: to access an oracle instance through a network connection 1. SQL> @? /RDMS/admin/utlsampl create Scott database 2. sqlplus/nolog 3. SQL> connect Scott/tiger @ db4 Display Error Access Profile 4. SQL> select * from Dept; query failed 5. SQL> select * from EMP; query failed 6. SQL> connect system/ora123 7. SQL> @? /Sqlplus/admin/pupbld create profile 8. SQL> connect Scott/tiger @ db4 connection successful 9. SQL> exit
Experiment 4:Objective: To connect 1. Set oracle_sid_db4 2. sqlplus/nolog 3. Connect sys/ora123 as DBA by default Using Environment Variables
Lab 5:Tutorial purpose: to configure an oracle network connection, the configuration is divided into two parts: client configuration, and server configuration under the Oracle installation directory with listener under Network/admin. there is a listener in the oranetwork/admin/sample path. ora sample file, but this file is not required. You can use the listener. ora sample file provided by the instructor to configure it on the server. In the file, listener is the name of listener. The Oracle server can have one or more listener versions. 10 Gb can listen for 9i requests, and vice versa. Sid_list_listener is optional from Oracle9i. This section implements static registration. The above is listner1, and the following is the dynamic registration of sid_list_listener1. After the database is started, the database will automatically register with the listener after 1 minute. Tnsnames: The tnsnames file in the Network/admin path under the Oracle Installation Directory of the Oracle client can refer to the sample file provided by the instructor for the initialization parameter file initdb1 and the two lines related to the network connection service_names = 'db1' instance _ name = 'db1' Versions later than Oracle9i must have these two lines, to achieve dynamic registration. In addition, the tnslistener service must start. After the service is turned off, SQL> connect sys/ora123 @ db4 as sysdba will display the no listener Error 1. lsnrctl2. LSNRCTL> start. All static registrations are in the unknown state. Because of static registration, the database is not started and can be registered. Close the command LSNRCTL> stop3. LSNRCTL> service4. LSNRCTL> status5. LSNRCTL> There Is A sqlnet in the Network/Admin directory of the quit Oracle installation directory. names. directory_path = (tnsnames) in order to tell Oracle how to resolve the name> tnsping db1, this command is used to verify whether remote connection can be performed.
Lab 6:Objective: To remotely connect to a database 1. sqlplus/no log 2. SQL> connect sys/ora123 @ db1 as sysdba shows that an idle instance indicates that the database is not opened. Use the startup command to open the database. 3. SQL> startup 4. SQL> select * from V $ log; The remote database file is related to three files and one command. The host in the listener. ora and tnsnames. ora files cannot be set to localhost or 127.0.0.1. The IP address or machine name must be specified. The machine name cannot contain a minus sign or a space.
