Oracle Advanced Training Course 6th Study Notes by jackyang (JackYang.sh@gmail.com) Date: This course contains multiple experiments, has not yet passed the computer verification. Tspitr (recovery of tablespaces Based on Time points) is introduced to reduce losses. Advanced than dbpitr. Design Philosophy: If a table in a production database is deleted by mistake. First clone the original production database, and then create a secondary database that is exactly the same as the original one. The cloned database is called a secondary database, and the original database is called a production database. Perform tspitr on the secondary database. Restore the secondary database to a backup point. Then, the auxiliary database contains the tablespace Export (export) of the table to be deleted ). Import the exported tablespace to the production database. In this way, other tablespaces in the production database are not affected, but are only damaged and restored. The restoration operations on the secondary database are the same as those on the restoration of a table. Tspitrtake a quick look at the document tspitr.txt (1) prepare Product Database: 1. checking self contained for recovery setsql> execute dbms_tts.transport_set_check ('data2 ', true); SQL> select * From transport_set_violation; No rows selected checklist whether the space is self-contained. Cloning a database only involves the system tablespace, temporary tablespace, and damaged tablespace. Other tablespaces do not need to be cloned into Auxiliary tablespaces. Self-contained means that one table in the damaged tablespace has an index. If this index is used by other tablespaces, The tablespace will also be cloned. 2. simulate before fault: SQL> Create Table dept1 tablespace data1 as select * from Scott. dept; SQL> Create Table dept2 tablespace data2 as select * from Scott. dept; SQL> insert into dept1 (deptno, dname) values (28, 'before drop'); SQL> insert into dept2 (deptno, dname) values (28, 'Before drop'); SQL> commit; SQL> alter system archive log current; 3. backup recovery set and auxiliary set before tspitr t Ime SQL> alter database begin backup; SQL> host Copy D:/ora101g/oradata/db1 /*. dbf d:/backup/db1sql> alter Database End backup; SQL> alter system archive log current; SQL> alter Database Backup controlfile to 2' D:/backup/db1/conl. CTL 'reuse; SQL> alter system archive log current; back up the database 4. simulate after fault: SQL> select to_char (sysdate, 'yyyy-MM-DD: hh24: MI: ss') from dual; recoding the sysdate! SQL> truncate table dept2; SQL> insert into dept1 (deptno, dname) values (38, 'after drop'); SQL> commit; SQL> alter system archive log current; time taken out. Delete a table to simulate corrupt operations. Insert a row of data to verify that the recovery result is successful. (2) Prepare auxiliary database: 1. prepare OS environment: DOS> mkdir F:/auxdb1dos> mkdir F:/auxdb1/bdumpdos> mkdir F:/auxdb1/udumpdos> mkdir F:/auxdb1/ARC 2. create Oracle service for auxiliary database: DOS> oradim-New-Sid auxdb1-intpwd ora123 create service 3. prepare parameter file for auxiliary database :( dos> Copy D:/ora101g/database/initdb1.ora D:/ora101g/database/iniauxdb1.oraedit D:/ora101g/databa SE/initauxdb1.ora: the content of the initauxdb1.ora file provided by the instructor is as follows: db_unique_name = 'auxdb1 'db _ file_name_convert = 'd:/ora101g/oradata/db1', 'f: /auxdb1 'Log _ file_name_convert = 'd:/ora101g/oradata/db1', 'f:/auxdb1', 'd:/ora101g/oradata/db1', 'f: /auxdb1 'background _ dump_dest = 'f:/auxdb1/bdump 'compatible = '10. 1.0.2.0 'control _ FILES = ('f:/auxdb1/con1.ctl ', 'f:/auxdb1/con2.ctl') core_dump_dest = 'f: /auxdb1/cdump 'db _ name = 'db1' remote _ login_pas Swordfile = 'clusive 'sessions = 20undo_management = 'auto' undo _ tablespace = 'undotbs' user _ dump_dest = 'f:/auxdb1/udump 'Log _ archive_dest_1 = 'location = D: /backup/db1/arc'log _ archive_format = '% S _ % T _ % R. arc 'service _ names = 'auxdb1' instance _ name = 'auxdb1' copy the initialization parameter file and make minor modifications to it: add db_uniqure_name db_file_name and log_file_name_convert without changing db_name to convert the original path of db1 to the path of the secondary database. Log_archive_dest_1 remains unchanged. Archive logs are used to restore the cloned database. The secondary database does not archive logs, and the production database does not archive logs, so this path remains unchanged. Change SERVICE_NAME and instance_name 4. restore recovery set and auxiliary setsql> host Copy D:/backup/db1 /*. dbf f:/auxdb1sql> host Copy D:/backup/db1/con1.ctl F:/auxdb1/con1.ctlsql> host Copy D:/backup/db1/con1.ctl F: /auxdb1/con2.ctl copy the backup file of the original production database to the auxiliary database. 5. start aux DB: SQL> connect sys/ora123 @ auxdb1 as sysdba SQL> startup nomount SQL> alter database Mount clone database; SQL> alter database datafile 'f: /auxdb1/sys01.dbf 'Online; SQL> alter database datafile 'f:/auxdb1/undotbs. DBF 'Online; SQL> alter database datafile 'f:/auxdb1/data2.dbf' online; SQL> recover database until time '2017-04-01 17:28:43 'using backup controlfile; SQL> alter database o Pen resetlogs; check the table dept2 exist: SQL> select * From dept2; SQL> alter tablespace temp add tempfile 'f:/auxdb1/temp. DBF 'size 5 m reuse; start the auxiliary database for clone operation using backup controlfile refers to the backup control file. The control file cannot be modified during cloning, and the control file can be modified during recover execution, because recover can use redo log (3) export and Import recovery set1. export recovery setdos> exp 'sys/ora123 as sysdba 'Created = y tablespaces = data2 file = F:/auxdb1/data2.dmp export terminated successfully without warnings. log on to sysdba and use the exp command to export the specified tablespace. 2. import recovery setsql> connect sys/ora123 @ db1 as sysdbasql> alter tablespace data2 offline; dos> copy F:/auxdb1/data2.dbf D: /ora101g/oradata/db1dos> imp 'sys/ora123 @ db1 as sysdba 'point_in_time_recover = y datafiles = D:/ora101g/oradata/db1/data2.dbf file = F: /auxdb1/data2.dmp import terminated successfully without warnings. connect to the production database to make the production database corrupt tablespace offline. 3. Check the tspitr result: SQL> alter tablespace data2 online; SQL> select * From dept2; SQL> select * From dept1; check whether the restored table is restored. For tables that violate the deletion rules, all records must be deleted from the secondary database in the 4. Delete auxiliary dB.
Lab 1:Tutorial objective: to practice tspitr1. SQL> select * From dept1; 2. SQL> select * From dept2; there are two tables in the database: dept1 and dept2. The fault is that the dept1 in the tablespace data1 is deleted by mistake. The restored tablespace dept1 does not affect the dept2 3 in the tablespace data2. SQL> alter database begin backup; 4. SQL> host copy E:/ora01g/oradata/db1 /*. dbf e:/backup/db1/hot 5. SQL> alter Database End backup; 6. SQL> alter Database Backup controlfile to 'e:/backup/db1/hot/con1.ctl 'reuse; 7. SQL> alter system archive log current; after backup, the database is manually archived immediately. 8. SQL> select to_char (sysdate, 'yyyy-MM-DD hh24: MI: ss') from dual; get a time for recovery 9. SQL> drop table dept1; Delete table, simulate fault 10. SQL> select * From dept1; query failed 11. SQL> insert into dept2 (deptno, dname) values (99, 'after 123'); 12. SQL> insert into dept2 (deptno, dname) values (66, 'after 123'); insert two pieces of data to verify whether the recovery result is successful. 13. Open a terminal window to clone the database oradim-New-Sid auxdb1-intpwd ora123 to create the secondary Database Service 14. Copy it to initialize the parameter file and modify it. Modified: db_file_name_convertlog_file_name_convertbackground_dump_destuser_dump_dest control_filescore_dump_destservice_nameinstance_name and then started the database using the initialization parameter file 15. copy E:/backup/db1/hot /*. dbf e:/auxdb1e:/data2.dbf in auxdb1 does not need to copy 16. copy E:/backup/db1/hot/con1.ctl E:/auxdb1 17. copy E:/backup/db1/hot/con1.ctl E:/auxdb1/con2.ctlcopy control file 18. sqlplus/nolog 19. SQL> connect sys/ora123 @ auxdb1 as sysdba 20. SQ L> startup nomount 21. SQL> alter database Mount clone database; 22. SQL> alter database datafile 'e:/auxdb1/system01.dbf' online; 23. SQL> alter database datafile 'e:/auxdb1/undotbs. dbf'online; 24. SQL> alter database datafile 'e:/auxdb1/sysaux01.dbf' online; 25. SQL> alter database datafile 'e:/auxdb1/data1.dbf' online; 26. SQL> recover database until time '2017-10-17 19:08:05 'using backup con Trolfile; the recovery operation fails. archive the production database again. Return to the terminal window of the production database and perform the following SQL> alter system archive log current; 27. SQL> alter database open resetlogs; 28. SQL> select * From dept1; The dept1 table is restored, but currently it is in the auxiliary database, and then export with the exp command. 29. SQL> host exp 'sys/ora123 @ auxdb1 as sysdba 'point_in_time_recover = y tablespaces = data1 file = data1.dmp. The 'export terminated successfully without Warn' statement is displayed on the screen, the export operation is successful. The steps for self-contained check are omitted. 30. return to the terminal window of the production database 31. SQL> alter tablespace data1 offline; 32. SQL> host copy E:/auxdb1/data1.dbf E:/ora01g/oradata/db1 33. SQL> host imp 'sys/ora123 @ db1 as sysdba 'point_in_time_recover = y datafiles = 'e: /ora01g/oradata/db1/data1.dbf 'file = data1.dmp: 'import terminated successfully without warnings is displayed on the screen. 'indicates that the import operation is successful. 34. SQL> alter tablespace data1 online; 35. SQL> select * From dept1; 36. SQL> select * From dept2; the two records inserted earlier are all in. The pages below 99 after 1908 and 66 After 1908 correspond to the Guide to Oracle9i Database Management Basics II ed 1.1 vol.2.pdf: http://blog.csdn.net/magus_yang/archive/2006/10/10/1328283.aspx p121 used dedicated mode for Oracle servers before configuring the sharing mode for Oracle servers in dedicated server mode, each user process corresponds to a server process. A waste of resources. In Shared Server mode, a server process can serve multiple users at the same time. In addition to the shared service process (snnn), p125 must also have scheduling programs. P127oracle Shared Server advantage scheduler scheduling to achieve Load Balancing p128tnsname. ora file content is Server = dedicated, using dedicated server mode. Without this parameter, Oracle tries its best to use the Shared Server mode in the Shared Server mode. The shutdown and startup commands cannot be issued. The two commands must be used to establish a connection in dedicated server mode. In the p129 Shared Server mode, you cannot use the set oracle_sid method to connect to the database. Instead, you must use the network connection method because you must first connect to the Oracle listener. P130 is very important. The connection user sends a request to the listener. The listener sends requests to one of the three schedulers with the lowest value. All schedulers share one request queue (one instance has only one request queue ). When the sharing server process is empty, it goes to the Request queue to check whether there are any requests and then provides services. Each scheduler has its own response queue. The response queue corresponds to the scheduler one by one. The Oracle scheduler goes to its own response queue to check whether the response is returned from the shared server process. There is an invisible ghost in the process, called the virtual circuit. In the Shared Server mode, each user process corresponds to a virtual circuit (essentially a small space in the memory. The number of data and sessions in a virtual circuit is equal. Once a user process starts to establish a connection with the scheduler, the subsequent requests are processed by the scheduler instead of another scheduler. However, the shared service process processes requests in the Request queue randomly. Each scheduler can serve a maximum of 1024 users. Generally, one scheduler serves 200 users. In the p132 Shared Server mode, the size of the shared pool needs to be expanded to some pusers. Dispatchers indicates the number of scheduling programs to be started when a routine is started. Shared_servers. Indicates how many shared service processes are started when a routine is started. The default value is 0, indicating that only the dedicated server mode can be executed. Max_dispatchers: the maximum number of scheduling programs started by the system. The OS-related parameter max_shared_servers, the maximum number of shared service processes started by the system, circuits, and virtual circuit. Shared_server_sessions: the number of shared sessions. The value is equal to the value of sessions minus 5, which is left to the dedicated server. It is used to execute commands such as startup and shutdown. P134 is connected through different protocols. Each protocol must create dispatcher p136 to set this parameter. This dynamically increases the number of service processes created when the scheduling program P138 is started. The size of p141circuits affects the size of SGA. p142 reduces sessions by 5 and leaves it to the dedicated server p143large_pool_sizesga. By default, the size is equal to 0, and related information is stored in shared_pool. When large_pool_size is set, related information is stored in large_pool. P145listener must listen to the scheduler and register it in listener. This is automatic registration (register within 60 seconds after dB startup ). Use LSNRCTL services to check whether the registration is successful. In p147 Shared Server mode, there is one request queue and multiple response queues. The dynamic view for viewing queue conditions is in OLAP, with a large number of users. When the operation time is short, there are many sharing server modes. There are many dedicated server modes in the DSS data warehouse.
Lab 2:Objective: to configure the Shared Server Mode for Oracle v9.2. The initialization parameter file initdb91.ora does not contain the following parameters. After the database is started, you can only set three dispather in dedicated server mode because they correspond to different protocols. 1. sqlplus/nolog 2. SQL> connect sys/ora123 @ dbbench as sysdba 3. SQL> startup 4. SQL> host 5. LSNRCTL 6. when the LSNRCTL> Service database is started, we can see that all dispatchers are started. Dispatchers is automatically registered. Check whether the dispatcher in the database is started. The number of connections established by each dispatcher is displayed. 7. LSNRCTL> exit 8. exit 9. SQL> show parameter DISPATCHER: view the dispatcher 10 set in the parameter. SQL> select * from V $ queue; 00 is the request queue, and the last three are the response queues. Tatalq indicates the number of requests that have been processed. 11. SQL> select * from V $ circuit; more information 12. SQL> the value of show parameter circuitcircuits is 38 13. SQL> the value of show parameter sessionssessions is 38shared_server_sessions, and the value of show parameter sessionssessions is 33 14. SQL> Get perf601 check the service process's busyness and use/to execute the specific executed SQL statement in the perf601. SQL file provided by the instructor. 15. SQL> alter system set shared_servers = 4; forcibly set the number of shared service processes to 4 16. SQL> DESC v $ shared_server 17. SQL> select name, status from V $ shared_server; list 4 shared service processes 18. SQL> alter system set shared_servers = 1; 19. SQL> DESC v $ dispatcher 20. SQL> column network format A15 21. SQL> column status format A5 22. SQL> select name, network, status from V $ dispatcher; server = Shared Server Mode in Network/admin/tnsnames File Server = dedica In the TED dedicated server mode, sysdba uses db91dsql> connect sys/ora123 @ db1_as sysdba to ensure that it uses the dedicated server mode, so that important files in the db10doc directory provided by the instructor can be shutdown: initauxdb1.oratspitr.txtstandby.txt the disaster recovery backup database that Huateng provides to the rail transit system. How to save SQL statements SQL> Save perfcache. SQL Save the saved SQL statement SQL> Get perf602sql> // Yes
